本文实例讲述了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"}]
/// <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"}]}]
/// <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"}]}]
/// <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
/// <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" ); //调用函数
}
希望本文所述对大家C#程序设计有所帮助。
原文链接：https://blog.csdn.net/yelin042/article/details/86551041
dy("nrwz");
查看更多关于C#实现Json转DataTable并导出Excel的方法示例的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did51065