Excel导出数据量超出Excel承载量上限分sheet写入

public virtual WebApiResult<string> ExpExcelReceiveDetailForm(CustConfirmDetailModel model)
        {
            //记录日志信息
            log.Debug($"应收明细参数：{JsonConvert.SerializeObject(model)}");
            //导出数据量超出excel最大承载量时 每个sheet最多多少行，此处从配置文件中获取此值
            int maxCount = int.Parse(ConfigurationManager.AppSettings["MaxCount"]);
            int index = 0;//索引批次
          
            var result = new WebApiResult<string>();
            try
            {
                //数据集
                DataSet ds = new DataSet();

                //获取数据
                var data = _reportService.ReceiveDetailList(model);
                var list = data.Data;
                //记录日志
                log.Info($"数据拉取完毕：{JsonConvert.SerializeObject(model)}");
                //Skip: 跳过序列中指定数量的元素，然后返回剩余的元素。
                //Take: 从序列的开头返回指定数量的连续元素。
                while (list.Skip(index*maxCount).Take(maxCount).Count() != 0)
                {
                    //每一个pList生成一个datatable 然后装载DataSet
                    var pList = list.Skip(index * maxCount).Take(maxCount);
                    if (pList.Count() == 0)
                    {
                        break;
                    }
                 
                    //数据表
                    DataTable dt = new DataTable();
                    //数据列
                    DataColumn dtColumn;
                    //数据行
                    DataRow dtRow;

                    #region 表结构构建
                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "完成时间";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "订单号";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "类型";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "客户名称";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "门店名称";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "结算单位";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "商品名称";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "服务期限（月）";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "要素名称";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "明细项";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "销售价格";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "复审价格";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "主机类型（到四级分类）";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "实际出库要素";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "编码";
                    dt.Columns.Add(dtColumn);

                    dtColumn = new DataColumn();
                    dtColumn.DataType = Type.GetType("System.String");
                    dtColumn.ColumnName = "更换记录";
                    dt.Columns.Add(dtColumn);

                    #endregion

                    #region 信息的获取

                    foreach (var item in pList)
                    {
                        dtRow = dt.NewRow();
                        dtRow["完成时间"] = item.AuditTime;
                        dtRow["订单号"] = item.OrderNo;
                        switch (item.OrderType)
                        {
                            case 1:
                                dtRow["类型"] = "零售订单";
                                break;
                            case 2:
                                dtRow["类型"] = "批发订单";
                                break;
                            case 3:
                                dtRow["类型"] = "退货订单";
                                break;
                        }
                        dtRow["客户名称"] = item.CustomerName;
                        dtRow["门店名称"] = item.CustStoreName;
                        dtRow["结算单位"] = item.CustSettleName;
                        dtRow["商品名称"] = item.SaleName;
                        dtRow["服务期限（月）"] = item.ServiceDuration;
                        dtRow["要素名称"] = item.MaterialName;
                        dtRow["明细项"] = item.OptionName;
                        dtRow["销售价格"] = item.SalePrice;//下单价格
                        dtRow["复审价格"] = item.RetrialPrice;
                        dtRow["主机类型（到四级分类）"] = item.MaterialType;//物料类型
                        dtRow["实际出库要素"] = item.ActualMaterialName;
                        dtRow["编码"] = item.MaterialNo;
                        switch (item.MaterialStatus)
                        {
                            case 0:
                                dtRow["更换记录"] = "未加装";
                                break;
                            case 1:
                                dtRow["更换记录"] = "已加装";
                                break;
                            case 2:
                                dtRow["更换记录"] = "已拆装";
                                break;
                        }

                        dt.Rows.Add(dtRow);
                    }
                    ds.Merge(dt);
                    index++;
                }
                log.Info("Dataset装载完毕");
                #endregion

                #region Excel 导出
                string filename = "应收明细信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                string filepath = HttpRuntime.AppDomainAppPath + "upload\\" + @filename;
                result.Data = ExcelExport(ds, filename, filepath).Data;
                log.Info($"最终完成");
                #endregion
            }
            catch (Exception e)
            {
                log.Info($"应收明细导出错误:{this.GetInnerException(e)}");
                result.AddError(this.GetInnerException(e));
            }
            return result;
        }