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