从excel读取数据到C#中的Json对象 [英] Reading data from excel in to Json object in c#
本文介绍了从excel读取数据到C#中的Json对象的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
样本数据:
名称RegNo描述
ABCD 12345 DemoInfo
XYZ 67890 DemoInfo2
解决方案
通过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);
}
}
}
}
}
链接:
- 在ADO.NET中检索和修改数据 - 如何使用连接,命令和读者
- Excel 2007连接字符串
- OLEDB提供程序部分下的旧Excel连接字符串
- C#的JSON库可以可在 JSON 页面上找到
- JSON.NET
- Microsoft Access数据库引擎2010 R可分发的,如果您没有安装Excel 2007+
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:
- Retrieving and Modifying Data in ADO.NET - how to use connections, commands and readers
- Excel 2007 connection strings
- Older Excel connection strings under the 'OLEDB Providers' section
- JSON libraries for C# can be found on the JSON page
- JSON.NET
- Microsoft Access Database Engine 2010 Redistributable, if you don't have Excel 2007+ installed
这篇关于从excel读取数据到C#中的Json对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文