C#实现Json转DataTable并导出Excel的方法示例
本文实例讲述了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"); //调用函数
}
ps：关于json操作，这里再为大家推荐几款比较实用的json在线工具供大家参考使用：
在线json代码检验、检验、美化、格式化工具：
json在线格式化工具：
在线xml/json互相转换工具：
json代码在线格式化/美化/压缩/编辑/转换工具：
在线json压缩/转义工具：
更多关于c#相关内容还可查看本站专题：《c#字符串操作技巧总结》、《c#常见控件用法教程》、《winform控件用法总结》、《c#程序设计之线程使用技巧总结》、《c#中xml文件操作技巧汇总》、《c#数据结构与算法教程》、《c#数组操作技巧总结》及《c#面向对象程序设计入门教程》
希望本文所述对大家c#程序设计有所帮助。