C#实现Json转DataTable并导出Excel的方法示例
本文实例讲述了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(Dictionary dictionaryinarrayList) { 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(Dictionary dictionaryinarrayList) { 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(Dictionary dicinlist) { 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(Dictionary dicinlist) { 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(Dictionary dictionaryinarrayList) { if(dictionary.Keys.Count ()==0) { result=dataTable; returnresult; }//Rows stringroot=""; foreach(stringcurrentindictionary.Keys) { if(current!="data") root=current; else { ArrayListlist=dictionary[current]asArrayList; foreach(Dictionary dicinlist) { 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互相转换工具: