更新时间:2023-02-09 15:20:17
通过ADO.NET OleDb提供程序连接到Excel工作表。然后,使用C#的JSON库生成JSON字符串,并保存该文件。 (或使用JavascriptSerialzer,如@boades建议)
此示例使用JSON.NET库。
使用系统;
使用System.Linq;
使用System.Data.OleDb;
使用System.Data.Common;
使用Newtonsoft.Json;
使用System.IO;
命名空间ConsoleApplication1 {
类程序{
static void Main(string [] args){
var pathToExcel = @C:\path\to\\ \\excel\file.xlsx;
var sheetName =NameOfSheet;
var destinationPath = @C:\path\to\save\json\file.json;
//如果您安装了Office 2007+驱动程序,则使用此连接字符串
//您的数据保存在.xlsx文件中
var connectionString = String.Format(@
Provider = Microsoft.ACE.OLEDB.12.0;
数据源= {0};
扩展属性=Excel 12.0 Xml; HDR = YES
pathToExcel);
//创建和打开到Excel表的数据连接
使用(var conn = new OleDbConnection(connectionString)){
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = String.Format(
@SELECT * FROM [{0} $],
sheetName
);
使用(var rdr = cmd.ExecuteReader()){
// LINQ查询 - 执行时将为每行创建匿名对象
var query =
从DbDataRecord行rdr
选择新的{
name = row [0],
regno = row [1],
description = row [2]
};
//从LINQ查询生成JSON
var json = JsonConvert.SerializeObject(query);
//将文件写入目标路径
File.WriteAllText(destinationPath,json);
}
}
}
}
}
链接:
I have an Excel sheet which has a set of columns and rows with data. I want to read the complete Excel sheet data as JSON, so that later I can write the JSON to a file. How can I do this?
Sample data:
Names RegNo Description
ABCD 12345 DemoInfo
XYZ 67890 DemoInfo2
Connect to the Excel sheet via the ADO.NET OleDb provider. Then, use a JSON library for C# to generate the JSON string, and save the file. (Or use the JavascriptSerialzer, as @boades suggested).
This example uses the JSON.NET library.
using System;
using System.Linq;
using System.Data.OleDb;
using System.Data.Common;
using Newtonsoft.Json;
using System.IO;
namespace ConsoleApplication1 {
class Program {
static void Main(string[] args) {
var pathToExcel = @"C:\path\to\excel\file.xlsx";
var sheetName = "NameOfSheet";
var destinationPath = @"C:\path\to\save\json\file.json";
//Use this connection string if you have Office 2007+ drivers 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],
regno = row[1],
description = row[2]
};
//Generates JSON from the LINQ query
var json = JsonConvert.SerializeObject(query);
//Write the file to the destination path
File.WriteAllText(destinationPath, json);
}
}
}
}
}
Links: