Converting excel into JSON using C# with first row in excel serving as the keys

Question

I am looking to read data from excel 2010 by specifying the sheet name. Then I want to convert the data into JSON format. Assume I have an excel sheet like this.

Name             |   Age  |    Country
Waugh, Timothy       10        UK
Freeman, Neil         20       USA
Andy, Robert          30       Poland

Should results in:

    [{"Name":"Waugh, Timothy","Age":10.0,"Country":"UK"},
{"Name":"Freeman, Neil","Age":20.0,"Country":"USA"},
{"Name":"Andy, Robert","Age":30.0,"Country":"Poland"}]

So far I am using this code:

var pathToExcel = @"C:\temp\file.xlsx";
            var sheetName = "sheetOne";

            //This connection string works if you have Office 2007+ installed and your 
            //data is saved in a .xlsx file
            var connectionString = String.Format(@"
                Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source={0};
                Extended Properties=""Excel 12.0 Xml;HDR=YES""
            ", pathToExcel);

            //Creating and opening a data connection to the Excel sheet 
            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = String.Format(
                    @"SELECT * FROM [{0}$]",
                    sheetName
                    );


                using (var rdr = cmd.ExecuteReader())
                {

                    //LINQ query - when executed will create anonymous objects for each row
                    var query =
                        from DbDataRecord row in rdr
                        select new
                        {
                            Name = row[0],
                            Age = row[1],
                            Country = row[2]
                        };

                    //Generates JSON from the LINQ query
                    var json = JsonConvert.SerializeObject(query);
                    return json;
                }
            }

Issues:

I have to hard code the columns name. Column names are not dynamic.
With this code, i am not able to read the first row (which should ideally serve as the column name)

Have you tried reading it into a `DataTable` first? See [Best /Fastest way to read an Excel Sheet into a DataTable?](http://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable/23638843#23638843). — dbc, Jan 28 '16 at 17:47
See also the first converter from [JSON.net serialize directly from oledbconnection](https://stackoverflow.com/questions/33835729/json-net-serialize-directly-from-oledbconnection). — dbc, Jan 28 '16 at 17:54
Just checked - the `DataReaderConverter` from [JSON.net serialize directly from oledbconnection](https://stackoverflow.com/questions/33835729/json-net-serialize-directly-from-oledbconnection) produces the JSON you want without hardcoded column names. — dbc, Jan 28 '16 at 18:12

score 2 · Accepted Answer · edited Jan 28 '16 at 18:35

Think you will find the column names under

rdr.GetName(0); //First column name
rdr.GetName(1); //Second column name
rdr.GetName(2); //Third column name

Then you need to put it in a dynamic object of sort. You cannot generate an anonymous object at runtime.

 var list = rdr.Select(x => {
     dynamic itm = new ExpandoObject();
     itm.Add(rdr.GetName(0), x[0];
     itm.Add(rdr.GetName(1), x[1];
     itm.Add(rdr.GetName(2), x[2];
     return itm;
 }).ToList();

think you will be able to take it from there.

Adding full example

var pathToExcel = @"C:\temp\file.xlsx";
        var sheetName = "sheetOne";

        //This connection string works if you have Office 2007+ installed and your 
        //data is saved in a .xlsx file
        var connectionString = String.Format(@"
            Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source={0};
            Extended Properties=""Excel 12.0 Xml;HDR=YES""
        ", pathToExcel);

        //Creating and opening a data connection to the Excel sheet 
        using (var conn = new OleDbConnection(connectionString))
        {
            conn.Open();

            var cmd = conn.CreateCommand();
            cmd.CommandText = String.Format(
                @"SELECT * FROM [{0}$]",
                sheetName
                );


            using (var rdr = cmd.ExecuteReader())
            {
                //LINQ query - when executed will create anonymous objects for each row
                var query =
                    (from DbDataRecord row in rdr
                    select row).Select(x => 
                    {


             //dynamic item = new ExpandoObject();
Dictionary<string,object> item = new Dictionary<string, object>();
                            item.Add(rdr.GetName(0), x[0]);
                            item.Add(rdr.GetName(1), x[1]);
                            item.Add(rdr.GetName(2), x[2]);
                        return item;

                    });

                //Generates JSON from the LINQ query
                var json = JsonConvert.SerializeObject(query);
                return json;
            }
        }

how can i do this with dynamic number of columns? – super-user Jul 13 '16 at 09:26 — super-user, Jul 13 '16 at 09:26

score 2 · Answer 2 · answered Sep 02 '20 at 13:45

For making the answer by fhogberg work for dynamic number of columns, just make this modification:

                        var query =
                        (from DbDataRecord row in rdr
                         select row).Select(x =>
                         {
                         //dynamic item = new ExpandoObject();
                         Dictionary<string, object> item = new Dictionary<string, object>();
                             for (int i=0; i < x.FieldCount; i++)
                                 item.Add(rdr.GetName(i), x[i]);
                             return item;
                         });

Converting excel into JSON using C# with first row in excel serving as the keys

2 Answers2

Linked