How to convert an Excel Worksheet into a JSON document with C#

How you are going to do this? Dump it into a DataSet and serialize objects to JSON?, make an array and do the same? or automate Excel itself? (please don't do this). There are many ways to read excel data and convert it into a JSON object.

Here I am going to explain about two options to convert an excel worksheet into a JSON document. First option is using the OLE DB Connection and Second is using the ExcelDataReader library.

This is how my sample excel document looks like.

Using OleDbConnection

I used to read and write excel files using Interop Objects. Since it is unmanaged and due to its performance issues, I started using OLE DB. It comes under System.Data.OleDb Namespace. In the below example I am using Microsoft ACE OLEDB 12.0 to read the excel worksheet and convert it into a JSON document format; Microsoft ACE OLEDB 12.0 can connect to Excel 2007 and later files with the .xlsx file extension.

            private static void UsingOleDb(string inFilePath, string outFilePath, string sheetName)
            {
                //"HDR=Yes;" indicates that the first row contains column names, not data.
                var connectionString = $@"
                    Provider=Microsoft.ACE.OLEDB.12.0;
                    Data Source={inFilePath};
                    Extended Properties=""Excel 12.0 Xml;HDR=YES""";
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = $@"SELECT * FROM [{sheetName}$]";
                    using (var dr = cmd.ExecuteReader())
                    {
                        var query =
                            (from DbDataRecord row in dr
                                select row).Select(x =>
                                {
                                    var data = new Dictionary<string,object>
                                        {
                                            {dr.GetName(0), x[0]},
                                            {dr.GetName(1), x[1]},
                                            {dr.GetName(2), x[2]},
                                            {dr.GetName(3), x[3]}                                           
                                        };
                                    return data;
                                });
                
                            var json = JsonConvert.SerializeObject(query);
                            File.WriteAllText(outFilePath, json);
                    }
                }
            }
        

Using ExcelDataReader

ExcelDataReader is a Lightweight and fast library written in C# for reading Microsoft Excel files. It's really useful; it took less than an hour to get exactly what I wanted.

            private static void UsingExcelDataReader(string inputFile, string outputFile)
            {
                using (var inFile = File.Open(inputFile, FileMode.Open, FileAccess.Read))
                using (var outFile = File.CreateText(outputFile))
                using (var reader = ExcelReaderFactory.CreateReader(inFile,
                new ExcelReaderConfiguration {FallbackEncoding = Encoding.GetEncoding(1252)}))
                using (var writer = new JsonTextWriter(outFile))
                {
                    writer.Formatting = Formatting.Indented;
                    writer.WriteStartArray();
                    //You can skip the first row, as it contains the titles.
                    reader.Read();
                    do
                    {
                        while (reader.Read())
                        {
                            //We don't need an empty object
                            var firstName = reader.GetString(0);
                            if (string.IsNullOrEmpty(firstName)) break;
                            
                            writer.WriteStartObject();
                            //Select Columns and values
                            writer.WritePropertyName("FirstName");
                            writer.WriteValue(firstName);
                            
                            writer.WritePropertyName("LastName");
                            writer.WriteValue(reader.GetString(1));
                            
                            writer.WritePropertyName("Gender");
                            writer.WriteValue(reader.GetString(2));
                            
                            writer.WritePropertyName("State");
                            writer.WriteValue(reader.GetString(3));
                            
                            writer.WriteEndObject();
                        }
                    } while (reader.NextResult());
                    
                    writer.WriteEndArray();
                }
            }
        

Source code is available in GitHub https://github.com/nidps/ExcelToJson