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;  } }}