使用C#将excel的第一行用作键，使用C#将excel转换为JSON [英] Converting excel into JSON using C# with first row in excel serving as the keys

问题描述

我希望通过指定工作表名称从 excel 2010 中读取数据.然后，我想将数据转换为JSON格式.假设我有一个这样的Excel工作表.

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

应该导致:

    [{"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;
                }
            }

问题:

1. 我必须对列名称进行硬编码.列名不是动态的.
2. 使用此代码，我无法读取第一行(理想情况下应作为列名)

推荐答案

认为您会在以下找到列名

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.

添加完整示例

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;
            }
        }

这篇关于使用C#将excel的第一行用作键，使用C#将excel转换为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！