C#实现Json转DataTable并导出Excel的方法示例

2023-08-29 03:57:04 68

本文实例讲述了C#实现Json转DataTable并导出Excel的方法。分享给大家供大家参考，具体如下：

需求：有一个log文件，需要整理成Excel，日志文件里面的数据都是json字符串

思路是，把Json字符串转换成DataTable，然后导出到Excel

在网上找了一些资料，整理了以下三种类型的Json

一、Json转换DataTable

1.处理简单Json：

[{"mac":"20:f1:7c:c5:cd:80","rssi":"-86","ch":"9"},{"mac":"20:f1:7c:c5:cd:85","rssi":"-91","ch":"9"}]

///
///Json字符串转换为DataTable数据集合
///
///
///
publicstaticDataTableToDataTableTwo(stringjson)
{
DataTabledataTable=newDataTable();//实例化
DataTableresult;
try
{
JavaScriptSerializerjavaScriptSerializer=newJavaScriptSerializer();
javaScriptSerializer.MaxJsonLength=Int32.MaxValue;//取得最大数值
ArrayListarrayList=javaScriptSerializer.Deserialize(json);
if(arrayList.Count>0)
{
foreach(DictionarydictionaryinarrayList)
{
if(dictionary.Keys.Count()==0)
{
result=dataTable;
returnresult;
}
//Columns
if(dataTable.Columns.Count==0)
{
foreach(stringcurrentindictionary.Keys)
{
dataTable.Columns.Add(current,dictionary[current].GetType());
}
}
//Rows
DataRowdataRow=dataTable.NewRow();
foreach(stringcurrentindictionary.Keys)
{
dataRow[current]=dictionary[current];
}
dataTable.Rows.Add(dataRow);//循环添加行到DataTable中
}
}
}
catch
{
}
result=dataTable;
returnresult;
}

2.处理复杂Json

[{"id":"00e58d51","data":[{"mac":"20:f1:7c:c5:cd:80","rssi":"-86","ch":"9"},{"mac":"20:f1:7c:c5:cd:85","rssi":"-91","ch":"9"}]},
{"id":"00e58d53","data":[{"mac":"bc:d1:77:8e:26:78","rssi":"-94","ch":"11"},{"mac":"14:d1:1f:3e:bb:ac","rssi":"-76","ch":"11"},{"mac":"20:f1:7c:d4:05:41","rssi":"-86","ch":"12"}]}]

///
///Json字符串转换为DataTable数据集合
///
///
///
publicstaticDataTableToDataTable(stringjson)
{
DataTabledataTable=newDataTable();//实例化
DataTableresult;
try
{
JavaScriptSerializerjavaScriptSerializer=newJavaScriptSerializer();
javaScriptSerializer.MaxJsonLength=Int32.MaxValue;//取得最大数值
ArrayListarrayList=javaScriptSerializer.Deserialize(json);
if(arrayList.Count>0)
{
foreach(DictionarydictionaryinarrayList)
{
if(dictionary.Keys.Count()==0)
{
result=dataTable;
returnresult;
}
//Columns
if(dataTable.Columns.Count==0)
{
foreach(stringcurrentindictionary.Keys)
{
if(current!="data")
dataTable.Columns.Add(current,dictionary[current].GetType());
else
{
ArrayListlist=dictionary[current]asArrayList;
foreach(Dictionarydicinlist)
{
foreach(stringkeyindic.Keys)
{
dataTable.Columns.Add(key,dic[key].GetType());
}
break;
}
}
}
}
//Rows
stringroot="";
foreach(stringcurrentindictionary.Keys)
{
if(current!="data")
root=current;
else
{
ArrayListlist=dictionary[current]asArrayList;
foreach(Dictionarydicinlist)
{
DataRowdataRow=dataTable.NewRow();
dataRow[root]=dictionary[root];
foreach(stringkeyindic.Keys)
{
dataRow[key]=dic[key];
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
}
catch
{
}
result=dataTable;
returnresult;
}

3.处理不规则Json，因为列并不确定，所以直接定义列，不动态生成

[{"id":"00e58d53","data":[{"mac":"34:b3:54:89:86:64","rssi":"-86","ch":"13"},{"mac":"50:bd:5f:02:80:44","rssi":"-90","ch":"1"}]},
{"id":"00ccda81","data":[{"mac":"bc:46:99:4e:96:c8","rssi":"-92","ch":"1"},{"mac":"bc:3a:ea:fc:77:6c","rssi":"-93","ch":"6","ds":"Y","essid":"viennahotelWIFI"}]}]

///
///Json字符串转换为DataTable数据集合
///
///
///
publicstaticDataTableToDataTable(stringjson)
{
DataTabledataTable=newDataTable();//实例化
DataTableresult;
try
{
dataTable.Columns.Add("id");
dataTable.Columns.Add("mac");
dataTable.Columns.Add("rssi");
dataTable.Columns.Add("ch");
dataTable.Columns.Add("ts");
dataTable.Columns.Add("tmc");
dataTable.Columns.Add("tc");
dataTable.Columns.Add("ds");
dataTable.Columns.Add("essid");
JavaScriptSerializerjavaScriptSerializer=newJavaScriptSerializer();
javaScriptSerializer.MaxJsonLength=Int32.MaxValue;//取得最大数值
ArrayListarrayList=javaScriptSerializer.Deserialize(json);
if(arrayList.Count>0)
{
foreach(DictionarydictionaryinarrayList)
{
if(dictionary.Keys.Count()==0)
{
result=dataTable;
returnresult;
}//Rows
stringroot="";
foreach(stringcurrentindictionary.Keys)
{
if(current!="data")
root=current;
else
{
ArrayListlist=dictionary[current]asArrayList;
foreach(Dictionarydicinlist)
{
DataRowdataRow=dataTable.NewRow();
dataRow[root]=dictionary[root];
foreach(stringkeyindic.Keys)
{
dataRow[key]=dic[key];
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
}
catch
{
}
result=dataTable;
returnresult;
}

二、导出Excel

///
///导出Excel
///
///
///
publicvoiddataTableToCsv(DataTabletable,stringfile)
{
stringtitle="";
FileStreamfs=newFileStream(file,FileMode.OpenOrCreate);
StreamWritersw=newStreamWriter(newBufferedStream(fs),System.Text.Encoding.Default);
for(inti=0;i
三、调用实现，数据导出到Excel
protectedvoidButton1_Click(objectsender,EventArgse)
{
stringstr=File.ReadAllText(@"C:\Users\Admin\Desktop\json.txt");
DataTabledt=ToDataTable(str);
this.dataTableToCsv(dt,@"E:\json.xls");//调用函数
}


PS：关于json操作，这里再为大家推荐几款比较实用的json在线工具供大家参考使用：
在线JSON代码检验、检验、美化、格式化工具：

http://tools.jb51.net/code/json
JSON在线格式化工具：

http://tools.jb51.net/code/jsonformat
在线XML/JSON互相转换工具：

C#实现Json转DataTable并导出Excel的方法示例

热门推荐

随机推荐