c# LINQ查询从.xlsx文件到JSON

本文关键字:文件 JSON xlsx LINQ 查询 | 更新日期: 2023-09-27 18:13:56

我对c#很陌生,需要一些帮助来尝试从电子表格中获得正确的数据范围。

下面代码的LINQ查询部分是否有一种方法来指定从数据集中提取哪些确切的列/行/行(不确定是否正确的术语)?这是正确的方法吗?

目前似乎我只能得到指定的列/s。

protected void Button3_Click(object sender, EventArgs e)
        {
            var pathToExcel = @"C:'Users'ab'Documents'Visual Studio 2012'Projects'Project1'Project1'data'HouseholdIncomeData2014.xlsx";
            var sheetName = "hinc01";
            var destinationPath = @"C:'Users'ab'Documents'Visual Studio 2012'Projects'Project1'Project1'data'HouseholdIncomeData.json";
            //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 [hinc01$B95:AQ107]", //@"SELECT * FROM [{0}$]"
                    sheetName
                );
                using (var rdr = cmd.ExecuteReader())
                {
                    //LINQ query will create anonymous objects for each row
                    var query =
                        from DbDataRecord row in rdr
                        select new
                        {
                            //blah1 = row[0],
                            //blah2 = row[1],
                            //blah3 = row[2]
                            name = row[0]
                        };
                    //Generates JSON using LINQ query
                    var json = JsonConvert.SerializeObject(query);
                    //Write the file to the destination path    
                    File.WriteAllText(destinationPath, json);
                }
            }
        }

c# LINQ查询从.xlsx文件到JSON

我不会使用OLEDB来读取Excel文件。有一个很好的(没有双关语的意思)NuGet包使处理Excel文件非常方便:ExcelDataReader。