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#程序设计有所帮助。