IWorkbook 生成Excel 可有多个sheet

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace CubejoySyncAppId.BLL
{
public class orderLoadApplyBLL
{
private static readonly object oLock_CreateSheet = new object();
private readonly DAL.orderLoadApplyDAL dal = null;
public orderLoadApplyBLL()
{
dal = new DAL.orderLoadApplyDAL(“ConPlatformDB”);
}

    public void OrderDetailHandler(int pageSize)
    {
        try
        {
            IWorkbook workbook = new XSSFWorkbook();
            int totalSheet = 0;//分页总数量
            Models.orderLoadApply mod = dal.GetorderLoadApply();//申请列表
            EnumCommon.ExportState state = EnumCommon.ExportState.Error;
            bool flag = false;
            if (mod != null && mod.id > 0)
            {
                CommonDLL.LogHelper.Info("mod.id=" + mod.id);
                //检查文件是否存在
                CheckFileAndReturnFullpath(mod);
                //导出数据的总数量
                DateTime sDate = CommonDLL.Utils.ObjectToDateTime(mod.endTime);
                string endTime = sDate.AddDays(1).ToString("yyyy-MM-dd");
                int totalnum = dal.GetOrderDetailsTotalCount(mod.startTime, endTime);
                CommonDLL.LogHelper.Info("totalnum=" + totalnum);
                if (totalnum > 0)
                {
                    try
                    {
                        List<Task> taskList = new List<Task>();
                        Action<int, int, int> createsheet = ((pindex, psize, totcount) =>
                        {
                            lock (oLock_CreateSheet)
                            {
                                DataTable dt = dal.GetOrderDetail(psize, pindex, totcount, mod.startTime, endTime);
                                CreateSheet(workbook, $"Sheet{pindex}", dt);
                            }
                        });
                        //需要几个sheet
                        if (totalnum % pageSize == 0)
                        { totalSheet = totalnum / pageSize; }
                        else
                        { totalSheet = totalnum / pageSize + 1; }

                        for (int indexSheet = 1; indexSheet <= totalSheet; indexSheet++)
                        {
                            int k = indexSheet;
                            taskList.Add(Task.Run(() => createsheet.Invoke(k, pageSize, totalnum)));
                            if (taskList.Count > 5)
                            {
                                Task.WaitAny(taskList.ToArray());
                                taskList = taskList.Where(t => t.Status != TaskStatus.RanToCompletion).ToList();
                            }
                        }
                        Task.WaitAll(taskList.ToArray());
                        //FileMode.Create 不存在创建，存在则覆盖                            
                        string fullPath = $"{mod.orderWebPath}\\{mod.fileName}";
                        CommonDLL.LogHelper.Info("fullPath2=" + fullPath);
                        using (FileStream file = new FileStream(fullPath, FileMode.Create))
                        {
                            workbook.Write(file);  //创建Excel文件。
                            file.Close();
                        }
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        CommonDLL.LogHelper.Error("订单流水 生成Excel报错:", ex);
                        flag = false;
                    }
                    if (flag)
                    { state = EnumCommon.ExportState.Success; }
                    //更新请求状态
                    if (!dal.Update(mod.id))
                    {
                        throw new Exception($"导出订单流水 更新请求状态失败。请求id={mod.id},状态={(int)state}");
                    }
                }
                else
                {
                    //创建空白sheet
                    CreateOrderDetailBlankSheet(workbook);
                }
            }
        }
        catch (Exception ex)
        {
            CommonDLL.LogHelper.Error(ex.Message);
        }
    }

    private void CheckFileAndReturnFullpath(Models.orderLoadApply request)
    {
        try
        {
            string fullPath = string.Empty;
            string physicsPaht = string.Empty;
            physicsPaht = request.orderWebPath;
            if (!string.IsNullOrEmpty(physicsPaht))
            {
                //判断路径是否存在，不存在创建
                if (!Directory.Exists(physicsPaht))
                { Directory.CreateDirectory(physicsPaht); }
                fullPath = $"{physicsPaht}";
                //判断文件是否存在，存在则删除
                if (File.Exists(fullPath)) { File.Delete(fullPath); }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private ISheet CreateOrderDetailBlankSheet(IWorkbook workBook)
    {
        ISheet sheet = workBook.CreateSheet("没有相关数据");
        //表头行
        //IRow RowHead = sheet.CreateRow(0);
        //RowHead.CreateCell(0).SetCellValue("说明");
        //数据行
        //IRow row_0 = sheet.CreateRow(0);
        //row_0.CreateCell(0).SetCellValue("币种--1:人民币; 2:美元; 3:台币");
        //IRow row_1 = sheet.CreateRow(1);
        //row_1.CreateCell(0).SetCellValue("销售单价/支付单价/平台抽成 单位是\"分\"");
        //IRow row_2 = sheet.CreateRow(2);
        //row_2.CreateCell(0).SetCellValue("状态-- 0:收款; 1:退款");
        return sheet;
    }

    private ISheet CreateSheet(IWorkbook workBook, string sheetName, DataTable dt)
    {
        try
        {
            ISheet sheet = workBook.CreateSheet(sheetName);
            IRow RowHead = sheet.CreateRow(0);
            //表头行
            int columnsCount = dt.Columns.Count;
            for (int iColumnIndex = 0; iColumnIndex < columnsCount; iColumnIndex++)
            {
                DataColumn dc = dt.Columns[iColumnIndex];
                RowHead.CreateCell(iColumnIndex).SetCellValue(dc.ColumnName);
            }
            //数据行
            //表头已占用一行，所以数据行下标从1开始
            int sheetRowIndex = 1;
            int dtRowCount = dt.Rows.Count;//
            for (int iDtRowIndex = 0; iDtRowIndex < dtRowCount; iDtRowIndex++)
            {
                DataRow dr = dt.Rows[iDtRowIndex];
                IRow row = sheet.CreateRow(sheetRowIndex);
                for (int iColumnIndex = 0; iColumnIndex < columnsCount; iColumnIndex++)
                { row.CreateCell(iColumnIndex).SetCellValue(dr[iColumnIndex].ToString()); }
                sheetRowIndex++;
            }
            return sheet;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

}