C#實(shí)現(xiàn)Json轉(zhuǎn)DataTable并導(dǎo)出Excel的方法示例
本文實(shí)例講述了C#實(shí)現(xiàn)Json轉(zhuǎn)DataTable并導(dǎo)出Excel的方法。分享給大家供大家參考，具體如下：
需求：有一個(gè)log文件，需要整理成Excel，日志文件里面的數(shù)據(jù)都是json字符串
思路是，把Json字符串轉(zhuǎn)換成DataTable，然后導(dǎo)出到Excel
在網(wǎng)上找了一些資料，整理了以下三種類型的Json
一、Json轉(zhuǎn)換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"}]
/// <summary>
/// Json 字符串 轉(zhuǎn)換為 DataTable數(shù)據(jù)集合
/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTableTwo(string json)
{
DataTable dataTable = new DataTable(); //實(shí)例化
DataTable result;
try
{
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大數(shù)值
ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);
if (arrayList.Count > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (dictionary.Keys.Count<string>() == 0)
{
result = dataTable;
return result;
}
//Columns
if (dataTable.Columns.Count == 0)
{
foreach (string current in dictionary.Keys)
{
dataTable.Columns.Add(current, dictionary[current].GetType());
}
}
//Rows
DataRow dataRow = dataTable.NewRow();
foreach (string current in dictionary.Keys)
{
dataRow[current] = dictionary[current];
}
dataTable.Rows.Add(dataRow); //循環(huán)添加行到DataTable中
}
}
}
catch
{
}
result = dataTable;
return result;
}
2.處理復(fù)雜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"}]}]
/// <summary>
/// Json 字符串 轉(zhuǎn)換為 DataTable數(shù)據(jù)集合
/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTable(string json)
{
DataTable dataTable = new DataTable(); //實(shí)例化
DataTable result;
try
{
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大數(shù)值
ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);
if (arrayList.Count > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (dictionary.Keys.Count<string>() == 0)
{
result = dataTable;
return result;
}
//Columns
if (dataTable.Columns.Count == 0)
{
foreach (string current in dictionary.Keys)
{
if (current != "data")
dataTable.Columns.Add(current, dictionary[current].GetType());
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
foreach (string key in dic.Keys)
{
dataTable.Columns.Add(key, dic[key].GetType());
}
break;
}
}
}
}
//Rows
string root = "";
foreach (string current in dictionary.Keys)
{
if (current != "data")
root = current;
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
DataRow dataRow = dataTable.NewRow();
dataRow[root] = dictionary[root];
foreach (string key in dic.Keys)
{
dataRow[key] = dic[key];
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
}
catch
{
}
result = dataTable;
return result;
}
3.處理不規(guī)則Json，因?yàn)榱胁⒉淮_定，所以直接定義列，不動(dòng)態(tài)生成
[{"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":"vienna hotel WIFI"}]}]
/// <summary>
/// Json 字符串 轉(zhuǎn)換為 DataTable數(shù)據(jù)集合
/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTable(string json)
{
DataTable dataTable = new DataTable(); //實(shí)例化
DataTable result;
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");
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大數(shù)值
ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);
if (arrayList.Count > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (dictionary.Keys.Count<string>() == 0)
{
result = dataTable;
return result;
}//Rows
string root = "";
foreach (string current in dictionary.Keys)
{
if (current != "data")
root = current;
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
DataRow dataRow = dataTable.NewRow();
dataRow[root] = dictionary[root];
foreach (string key in dic.Keys)
{
dataRow[key] = dic[key];
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
}
catch
{
}
result = dataTable;
return result;
}
二、導(dǎo)出Excel
/// <summary>
/// 導(dǎo)出Excel
/// </summary>
/// <param name="table"></param>
/// <param name="file"></param>
public void dataTableToCsv(DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = 0; i < table.Columns.Count; i++)
{
title += table.Columns[i].ColumnName + "\t"; //欄位：自動(dòng)跳到下一單元格
}
title = title.Substring(0, title.Length - 1) + "\n";
sw.Write(title);
foreach (DataRow row in table.Rows)
{
string line = "";
for (int i = 0; i < table.Columns.Count; i++)
{
line += row[i].ToString().Trim() + "\t"; //內(nèi)容：自動(dòng)跳到下一單元格
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
三、調(diào)用實(shí)現(xiàn)，數(shù)據(jù)導(dǎo)出到Excel
protected void Button1_Click(object sender, EventArgs e)
{
string str = File.ReadAllText(@"C:\Users\Admin\Desktop\json.txt");
DataTable dt = ToDataTable(str);
this.dataTableToCsv(dt, @"E:\json.xls"); //調(diào)用函數(shù)
}
PS：關(guān)于json操作，這里再為大家推薦幾款比較實(shí)用的json在線工具供大家參考使用：
在線JSON代碼檢驗(yàn)、檢驗(yàn)、美化、格式化工具：
http://tools.jb51.net/code/json
JSON在線格式化工具：
http://tools.jb51.net/code/jsonformat
在線XML/JSON互相轉(zhuǎn)換工具：
http://tools.jb51.net/code/xmljson
json代碼在線格式化/美化/壓縮/編輯/轉(zhuǎn)換工具：
http://tools.jb51.net/code/jsoncodeformat
在線json壓縮/轉(zhuǎn)義工具：
http://tools.jb51.net/code/json_yasuo_trans
更多關(guān)于C#相關(guān)內(nèi)容還可查看本站專題：《C#字符串操作技巧總結(jié)》、《C#常見控件用法教程》、《WinForm控件用法總結(jié)》、《C#程序設(shè)計(jì)之線程使用技巧總結(jié)》、《C#中XML文件操作技巧匯總》、《C#數(shù)據(jù)結(jié)構(gòu)與算法教程》、《C#數(shù)組操作技巧總結(jié)》及《C#面向?qū)ο蟪绦蛟O(shè)計(jì)入門教程》
希望本文所述對大家C#程序設(shè)計(jì)有所幫助。
上一篇：Unity實(shí)現(xiàn)輪盤方式的按鈕滾動(dòng)效果
欄 目：C#教程
下一篇：Unity實(shí)現(xiàn)大轉(zhuǎn)盤的簡單筆記
本文標(biāo)題：C#實(shí)現(xiàn)Json轉(zhuǎn)DataTable并導(dǎo)出Excel的方法示例
本文地址：http://mengdiqiu.com.cn/a1/C_jiaocheng/4906.html
您可能感興趣的文章
- 01-10C#實(shí)現(xiàn)txt定位指定行完整實(shí)例
- 01-10WinForm實(shí)現(xiàn)仿視頻播放器左下角滾動(dòng)新聞效果的方法
- 01-10C#實(shí)現(xiàn)清空回收站的方法
- 01-10C#實(shí)現(xiàn)讀取注冊表監(jiān)控當(dāng)前操作系統(tǒng)已安裝軟件變化的方法
- 01-10C#實(shí)現(xiàn)多線程下載文件的方法
- 01-10C#實(shí)現(xiàn)Winform中打開網(wǎng)頁頁面的方法
- 01-10C#實(shí)現(xiàn)遠(yuǎn)程關(guān)閉計(jì)算機(jī)或重啟計(jì)算機(jī)的方法
- 01-10C#自定義簽名章實(shí)現(xiàn)方法
- 01-10C#文件斷點(diǎn)續(xù)傳實(shí)現(xiàn)方法
- 01-10winform實(shí)現(xiàn)創(chuàng)建最前端窗體的方法
閱讀排行
本欄相關(guān)
- 01-10C#通過反射獲取當(dāng)前工程中所有窗體并
- 01-10關(guān)于ASP網(wǎng)頁無法打開的解決方案
- 01-10WinForm限制窗體不能移到屏幕外的方法
- 01-10WinForm繪制圓角的方法
- 01-10C#實(shí)現(xiàn)txt定位指定行完整實(shí)例
- 01-10WinForm實(shí)現(xiàn)仿視頻播放器左下角滾動(dòng)新
- 01-10C#停止線程的方法
- 01-10C#實(shí)現(xiàn)清空回收站的方法
- 01-10C#通過重寫Panel改變邊框顏色與寬度的
- 01-10C#實(shí)現(xiàn)讀取注冊表監(jiān)控當(dāng)前操作系統(tǒng)已
隨機(jī)閱讀
- 01-11ajax實(shí)現(xiàn)頁面的局部加載
- 01-10C#中split用法實(shí)例總結(jié)
- 01-10使用C語言求解撲克牌的順子及n個(gè)骰子
- 08-05織夢dedecms什么時(shí)候用欄目交叉功能？
- 08-05DEDE織夢data目錄下的sessions文件夾有什
- 08-05dedecms(織夢)副欄目數(shù)量限制代碼修改
- 01-10SublimeText編譯C開發(fā)環(huán)境設(shè)置
- 04-02jquery與jsp,用jquery
- 01-11Mac OSX 打開原生自帶讀寫NTFS功能(圖文
- 01-10delphi制作wav文件的方法