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);
}
}
}
我不会使用OLEDB来读取Excel文件。有一个很好的(没有双关语的意思)NuGet包使处理Excel文件非常方便:ExcelDataReader。