做网站学的是代码吗seo快速排名软件品牌

需求：有一个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"}]

        /// <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;}//Columnsif (dataTable.Columns.Count == 0){foreach (string current in dictionary.Keys){dataTable.Columns.Add(current, dictionary[current].GetType());}}//RowsDataRow 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"}]}]

        /// <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;}//Columnsif (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;}}}}//Rowsstring 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"}]}]

       /// <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;}//Rowsstring 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

        /// <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

        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"); //调用函数 }