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



    }
}