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"}]

/// 

/// Json 字符串 转换为 DataTable数据集合
/// 
/// 
/// 
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(json);
    if (arrayList.Count > 0)
    {
      foreach (Dictionary dictionary in arrayList)
      {
 if (dictionary.Keys.Count() == 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"}]}]

/// 

/// Json 字符串 转换为 DataTable数据集合
/// 
/// 
/// 
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(json);
    if (arrayList.Count > 0)
    {
      foreach (Dictionary dictionary in arrayList)
      {
 if (dictionary.Keys.Count() == 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 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 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"}]}]

/// 

/// Json 字符串 转换为 DataTable数据集合
/// 
/// 
/// 
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(json);
    if (arrayList.Count > 0)
    {
      foreach (Dictionary dictionary in arrayList)
      {
 if (dictionary.Keys.Count() == 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 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

/// 

/// 导出Excel
/// 
/// 
/// 
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:UsersAdminDesktopjson.txt");
  DataTable dt = ToDataTable(str);
  this.dataTableToCsv(dt, @"E:json.xls"); //调用函数
}

PS：关于json操作，这里再为大家推荐几款比较实用的json在线工具供大家参考使用：

在线JSON代码检验、检验、美化、格式化工具：
http://tools.jb51.net/code/json

JSON在线格式化工具：
http://tools.jb51.net/code/jsonformat

在线XML/JSON互相转换工具：
http://tools.jb51.net/code/xmljson

json代码在线格式化/美化/压缩/编辑/转换工具：
http://tools.jb51.net/code/jsoncodeformat

在线json压缩/转义工具：
http://tools.jb51.net/code/json_yasuo_trans

更多关于C#相关内容还可查看本站专题：《C#字符串操作技巧总结》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#程序设计之线程使用技巧总结》、《C#中XML文件操作技巧汇总》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》

希望本文所述对大家C#程序设计有所帮助。