This article describes the method of C# to implement Json to DataTable and export Excel. Share it for your reference, as follows:
Requirements: There is a log file that needs to be sorted into Excel, and the data in the log file is all json strings
The idea is to convert the Json string into a DataTable and then export it to Excel
I found some information online and sorted out the following three types of Json
1. Json converts DataTable
1. Simple processing of Json:
[{"mac":"20:f1:7c:c5:cd:80","rssi":"-86","ch":"9"},{"mac":"20:f1:7c:c5:cd:85","rssi":"-91","ch":"9"}]
/// <summary>
/// Convert Json string to DataTable data collection/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTableTwo(string json)
{
DataTable dataTable = new DataTable(); //Instantiation DataTable result;
try
{
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
= ; //Get the maximum value ArrayList arrayList = <ArrayList>(json);
if ( > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (<string>() == 0)
{
result = dataTable;
return result;
}
//Columns
if ( == 0)
{
foreach (string current in )
{
(current, dictionary[current].GetType());
}
}
//Rows
DataRow dataRow = ();
foreach (string current in )
{
dataRow[current] = dictionary[current];
}
(dataRow); //Loop add rows to DataTable }
}
}
catch
{
}
result = dataTable;
return result;
}
2. Handle complex 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>
/// Convert Json string to DataTable data collection/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTable(string json)
{
DataTable dataTable = new DataTable(); //Instantiation DataTable result;
try
{
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
= ; //Get the maximum value ArrayList arrayList = <ArrayList>(json);
if ( > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (<string>() == 0)
{
result = dataTable;
return result;
}
//Columns
if ( == 0)
{
foreach (string current in )
{
if (current != "data")
(current, dictionary[current].GetType());
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
foreach (string key in )
{
(key, dic[key].GetType());
}
break;
}
}
}
}
//Rows
string root = "";
foreach (string current in )
{
if (current != "data")
root = current;
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
DataRow dataRow = ();
dataRow[root] = dictionary[root];
foreach (string key in )
{
dataRow[key] = dic[key];
}
(dataRow);
}
}
}
}
}
}
catch
{
}
result = dataTable;
return result;
}
3. Handle irregular Json, because the column is not sure, the column is directly defined and not dynamically generated
[{"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>
/// Convert Json string to DataTable data collection/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTable(string json)
{
DataTable dataTable = new DataTable(); //Instantiation DataTable result;
try
{
("id");
("mac");
("rssi");
("ch");
("ts");
("tmc");
("tc");
("ds");
("essid");
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
= ; //Get the maximum value ArrayList arrayList = <ArrayList>(json);
if ( > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (<string>() == 0)
{
result = dataTable;
return result;
}//Rows
string root = "";
foreach (string current in )
{
if (current != "data")
root = current;
else
{
ArrayList list = dictionary[current] as ArrayList;
foreach (Dictionary<string, object> dic in list)
{
DataRow dataRow = ();
dataRow[root] = dictionary[root];
foreach (string key in )
{
dataRow[key] = dic[key];
}
(dataRow);
}
}
}
}
}
}
catch
{
}
result = dataTable;
return result;
}
2. Export Excel
/// <summary>
/// Export Excel/// </summary>
/// <param name="table"></param>
/// <param name="file"></param>
public void dataTableToCsv(DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, );
StreamWriter sw = new StreamWriter(new BufferedStream(fs), );
for (int i = 0; i < ; i++)
{
title += [i].ColumnName + "\t"; //Bar: automatically jump to the next cell }
title = (0, - 1) + "\n";
(title);
foreach (DataRow row in )
{
string line = "";
for (int i = 0; i < ; i++)
{
line += row[i].ToString().Trim() + "\t"; //Content: Automatically jump to the next cell }
line = (0, - 1) + "\n";
(line);
}
();
();
}
3. Call to implement and export data to Excel
protected void Button1_Click(object sender, EventArgs e)
{
string str = (@"C:\Users\Admin\Desktop\");
DataTable dt = ToDataTable(str);
(dt, @"E:\"); //Calling the function}
PS: Regarding json operation, here are some more practical json online tools for your reference:
OnlineJSONCode verification, inspection, beautification and formatting tools:
http://tools./code/json
JSONOnline formatting tools:
http://tools./code/jsonformat
Online XML/JSONConvert each other tools:
http://tools./code/xmljson
jsonCode online formatting/beautification/compression/editing/converting tools:
http://tools./code/jsoncodeformat
OnlinejsonCompression/escaping tools:
http://tools./code/json_yasuo_trans
For more information about C#, you can also view the special topic of this site: "Summary of C# string operation skills》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《Summary of thread usage techniques for C# programming》、《Summary of XML file operation skills in C#》、《C# data structure and algorithm tutorial》、《Summary of C# array operation skills"and"Introduction to C# object-oriented programming tutorial》
I hope this article will be helpful to everyone's C# programming.