欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#使用Datatable导出Excel

程序员文章站 2023-12-16 13:13:28
本文实例为大家分享了c#使用datatable导出excel的具体代码,供大家参考,具体内容如下 using npoi.ss.usermodel; using...

本文实例为大家分享了c#使用datatable导出excel的具体代码,供大家参考,具体内容如下

using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using system;
using system.collections.generic;
using system.data;
using system.io;
using system.linq;
using newtonsoft.json;
namespace easyframe.common
{
 /// <summary>
 ///title调用信息
 /// </summary>
 public class charttitle
 {
  /// <summary>
  /// 调用名
  /// </summary>
  public string callname { get; set; }
  /// <summary>
  /// 标头信息
  /// </summary>
  public list<chartname> setnamelist { get; set; }
 }
 public class chartname
 {
  /// <summary>
  /// 一级标头
  /// </summary>
  public string name { get; set; }
  /// <summary>
  /// 二级标头
  /// </summary>
  public list<string> chartchildnamelist { get; set; }

 }

 public class excelhelper
 {

  //
  /// <summary>
  /// datatable导出excel
  /// </summary>
  /// <param name="dt">数据</param>
  /// <param name="list">表头</param>
  /// <param name="counts">总条数</param>
  /// <param name="filepath">保存地址</param>
  /// <returns></returns>
  public static memorystream writeexcel(datatable dt, list<charttitle> objlist, string filepath)
  {
   var list = objlist.firstordefault(a => a.callname == "ccc").setnamelist;
   if (!string.isnullorempty(filepath) && null != dt && dt.rows.count > 0)
   {
    xssfworkbook book = new xssfworkbook();
    npoi.ss.usermodel.isheet sheet = book.createsheet(dt.tablename);
    icell cell = null;
    icellstyle style = book.createcellstyle();
    ifont font = book.createfont();//创建字体样式 
    ifont fonts = book.createfont();//创建字体样式 
    ifont fontss = book.createfont();//创建字体样式 
    font.color = npoi.hssf.util.hssfcolor.red.index;//设置字体颜色
    icellstyle styles = book.createcellstyle(); //红色
    fonts.color = npoi.hssf.util.hssfcolor.green.index;
    icellstyle styless = book.createcellstyle(); //绿色
    fontss.color = npoi.hssf.util.hssfcolor.blue.index;
    icellstyle stylesss = book.createcellstyle(); //蓝色
    styless.setfont(fonts);
    styles.setfont(font);
    stylesss.setfont(fontss);
    style.alignment = horizontalalignment.center;
    npoi.ss.usermodel.irow row1 = sheet.createrow(0);
    npoi.ss.usermodel.irow row2 = sheet.createrow(1);
    var row = row1;
    int num = 0;//记录列
    var d = true;//是否第一次加载到二级表单
    #region 创建表头
    //两行单列
    for (int i = 0; i < list.count; i++)
    {
     if (!d)
     {
      row = row1;
      d = true;
     }
     if (list[i].chartchildnamelist == null)
     {
      cell = row.createcell(num);
      sheet.setcolumnwidth(num, 15 * 256);
      cell.setcellvalue(list[i].name);
      cell.cellstyle = style;
      sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 1, num, num));
      num++;
      continue;
     }
     //两行多列
     var count = list[i].chartchildnamelist.count; //列数
     cell = row.createcell(num);
     cell.setcellvalue(list[i].name);
     cell.cellstyle = style;
     sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, num, count + num - 1));
     if (d)
     {
      d = false;
      row = row2;
     }
     var t = 0;
     var rang = sheet.getrow(1);
     for (int j = 0; j < count; j++)
     {
      sheet.setcolumnwidth(num + t, 5 * 256);
      cell = row.createcell(num + t);
      cell.setcellvalue(list[i].chartchildnamelist[j]);
      cell.cellstyle = style;
      t++;
     }
     num = num + count;
    }
    #endregion 创建表头
    #region 写入数据
    int customs = 0;//记录列数
    int clouns = 0;//记录行数



    filepath = string.format(filepath, "xxx");
    #region 导出操作
    var data1 = jsonconvert.deserializeobject<list<类名>>(dt.rows[0][0].tostring());
    foreach (var item in data1)
    {
     npoi.ss.usermodel.irow rows = sheet.createrow(clouns + 2);
     customs = 0;
     rows.createcell(customs).setcellvalue(convert.tostring(item.no)); customs++;
     rows.createcell(customs).setcellvalue(convert.tostring(item.lotteryopenno)); customs++;
     for (int i = 0; i < item.wan.length; i++)
     {
      if (item.lotteryopenno.split(',')[0] == item.wan[i].tostring())
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.wan[i]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.wan[i]); customs++;
      }
     }
     for (int j = 0; j < item.qian.length; j++)
     {
      if (item.lotteryopenno.split(',')[1] == item.qian[j].tostring())
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.qian[j]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.qian[j]); customs++;
      }
     }
     for (int k = 0; k < item.bai.length; k++)
     {
      if (item.lotteryopenno.split(',')[2] == item.bai[k].tostring())
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.bai[k]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.bai[k]); customs++;
      }
     }
     for (int l = 0; l < item.shi.length; l++)
     {
      if (item.lotteryopenno.split(',')[3] == (item.shi[l].tostring()))
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.shi[l]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.shi[l]); customs++;
      }
     }
     for (int m = 0; m < item.ge.length; m++)
     {
      if (item.lotteryopenno.split(',')[4] == (item.ge[m].tostring()))
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.ge[m]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.ge[m]); customs++;
      }
     }
     for (int n = 0; n < item.fen.length; n++)
     {
      if (item.lotteryopenno.contains(item.fen[n].tostring()))
      {
       cell = rows.createcell(customs);
       cell.setcellvalue(item.fen[n]);
       cell.cellstyle = styles;
       customs++;
      }
      else
      {
       rows.createcell(customs).setcellvalue(item.fen[n]); customs++;
      }

     }
     clouns++;
    }

    #endregion


    #endregion 写入数据
    // 写入到客户端 
    using (system.io.memorystream ms = new system.io.memorystream())
    {
     book.write(ms);
     using (filestream fs = new filestream(filepath, filemode.create, fileaccess.write))
     {
      byte[] data = ms.toarray();
      fs.write(data, 0, data.length);
      fs.flush();
     }
     book = null;
     return ms;
    }
   }
   return null;
  }



 }
}

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。

上一篇:

下一篇: