C#使用Datatable导出Excel 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;
  }



 }
}