本文實例講述了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"}]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
/// <summary>/// Json 字符串 轉換為 DataTable數據集合/// </summary>/// <param name="json"></param>/// <returns></returns>public static DataTable ToDataTableTwo(string json){ DataTable dataTable = new DataTable(); //實例化 DataTable result; try { JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer(); javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大數值 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); //循環添加行到DataTable中 } } } catch { } result = dataTable; return result;} |
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"}]}]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
/// <summary>/// Json 字符串 轉換為 DataTable數據集合/// </summary>/// <param name="json"></param>/// <returns></returns>public static DataTable ToDataTable(string json){ DataTable dataTable = new DataTable(); //實例化 DataTable result; try { JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer(); javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大數值 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.處理不規則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":"vienna hotel WIFI"}]}]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
/// <summary>/// Json 字符串 轉換為 DataTable數據集合/// </summary>/// <param name="json"></param>/// <returns></returns>public static DataTable ToDataTable(string json){ DataTable dataTable = new DataTable(); //實例化 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; //取得最大數值 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;} |
二、導出Excel
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
/// <summary>/// 導出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"; //欄位：自動跳到下一單元格 } 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"; //內容：自動跳到下一單元格 } line = line.Substring(0, line.Length - 1) + "\n"; sw.Write(line); } sw.Close(); fs.Close();} |
三、調用實現，數據導出到Excel
|
1
2
3
4
5
6
|
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"); //調用函數} |
希望本文所述對大家C#程序設計有所幫助。
原文鏈接：https://blog.csdn.net/yelin042/article/details/86551041
