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.處理簡(jiǎn)單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ì)入門教程》
希望本文所述對(duì)大家C#程序設(shè)計(jì)有所幫助。
- C#中把DataTable、Dataset轉(zhuǎn)Json數(shù)據(jù)
- C#中DataTable 轉(zhuǎn)換為 Json的方法匯總(三種方法)
- C#實(shí)現(xiàn)DataTable,List和Json轉(zhuǎn)換的方法
- C#實(shí)現(xiàn)將json轉(zhuǎn)換為DataTable的方法
- C#中的DataSet、string、DataTable、對(duì)象轉(zhuǎn)換成Json的實(shí)現(xiàn)代碼
- c#擴(kuò)展datatable轉(zhuǎn)json示例
- C#中把Datatable轉(zhuǎn)換為Json的5個(gè)代碼實(shí)例
- C#中把Json數(shù)據(jù)轉(zhuǎn)為DataTable
相關(guān)文章
Visual Studio連接unity編輯器的實(shí)現(xiàn)步驟
unity編輯器中打開C#腳本的時(shí)候發(fā)現(xiàn)Visual Studio沒有連接unity編輯器,本文主要介紹了Visual Studio連接unity編輯器的實(shí)現(xiàn)步驟,感興趣的可以了解一下2023-11-11C#實(shí)現(xiàn)簡(jiǎn)單播放mp3的方法
這篇文章主要介紹了C#實(shí)現(xiàn)簡(jiǎn)單播放mp3的方法,涉及C#播放多媒體文件的技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-03-03C#實(shí)現(xiàn)String字符串轉(zhuǎn)化為SQL語句中的In后接的參數(shù)詳解
在本篇文章中小編給大家分享的是一篇關(guān)于C#實(shí)現(xiàn)String字符串轉(zhuǎn)化為SQL語句中的In后接的實(shí)例內(nèi)容和代碼,需要的朋友們參考下。2020-01-01利用C#實(shí)現(xiàn)獲取當(dāng)前設(shè)備硬件信息
這篇文章主要為大家詳細(xì)介紹了如何利用C#實(shí)現(xiàn)獲取當(dāng)前設(shè)備硬件信息的功能,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起了解一下2023-03-03C# winform打開Excel文檔的方法總結(jié)(必看篇)
下面小編就為大家?guī)硪黄狢# winform打開Excel文檔的方法總結(jié)(必看篇)。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-01-01C#中IEnumerable、ICollection、IList、List之間的區(qū)別
這篇文章主要介紹了C#中IEnumerable、ICollection、IList、List之間的區(qū)別,本文分別分析了它的實(shí)現(xiàn)源碼,從而總結(jié)出了它們之間的關(guān)系和不同之處,需要的朋友可以參考下2015-06-06