public ActionResult ExportReport(string y, string m, int t)
{
List <Published> data = null;
FormType formType = new BaseServices <FormType>().LoadEntities(c => c.FormId == t).First();
if (!string.IsNullOrEmpty(y))
{
data = new BaseServices <Published>().LoadEntities(c => c.PublishTime.Year.ToString() == y).ToList();
}
if (!string.IsNullOrEmpty(m))
{
data = new BaseServices <Published>().LoadEntities(c => c.PublishTime.Year.Equals(DateTime.Now.Year) && c.PublishTime.Month.ToString() == m).ToList();
}
if (data != null)
{
data = data.Where(d => d.PublishType == t).ToList();
//将发行记录改为发行实体
var formnoBuilder = new StringBuilder("(");
data.Where(d => d.PublishType == t).ForEach(k => formnoBuilder.Append("'" + k.FormNo + "',"));
string whereString = formnoBuilder.ToString();
DataTable dt = DataFactory.Database().FindTableBySql($"SELECT * FROM Form{formType.Method} " + "WHERE FormNo in " + whereString.Substring(0, whereString.Length - 1) + ")");
//下载
HttpContext.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode($"{formType.FormName}.xls");
HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
//var ds = new DataSet();
//ds.Tables.Add(dt);
var workbook = new ExcepNpoi().DataTableToStreamExcel(dt, "Data");
workbook.Write(HttpContext.Response.OutputStream);
return(null);
}
return(Link.ErrorBy(new Exception("内部异常，请联系管理员"), this.GetType()));
}
public ActionResult OldImport(Guid fileGroup, string sheet)
{
List <FilesFileGroup> files = new FilesFileGroupBll().GetFileListByGroupGuid(fileGroup);
if (files.Count == 1)
{
var phyPath = Path.GetFullPath(BaseHelper.UpPath) + files[0].Md5;
DataTable dt = ExcepNpoi.ExcelToDataTable(phyPath, sheet, 0);
var ds = new DataSet();
ds.Tables.Add(dt);
int count = _pdm.OldImport(ds);
return(Json(count > 0 ? "上传成功(" + count + "/" + dt.Rows.Count + ")！！！！" : "系统错误"));
}
return(Json("文件个数错误", JsonRequestBehavior.AllowGet));
}
public void ErpWip()
{
IWorkbook wb = new HSSFWorkbook();
Dictionary <string, ICellStyle> cellStyles = new Dictionary <string, ICellStyle>
{
{ "默认", ExcepNpoi.Getcellstyle(wb, ExcepNpoi.Stylexls.默认) },
{ "数字", ExcepNpoi.Getcellstyle(wb, ExcepNpoi.Stylexls.数字) }
};
WriteSheet("WIP",
DataFactory.Database("CysoftContext").FindTableBySql(System.IO.File.ReadAllText(Server.MapPath("/Resource/Sql/erpwip.txt"))),
wb,
new List <CellSetting>
{
new CellSetting()
{
Name = "品名", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
new CellSetting()
{
Name = "品号", CellStyle = cellStyles["默认"], CellWidth = 25 * 256 * 2,
},
new CellSetting()
{
Name = "规格", CellStyle = cellStyles["默认"], CellWidth = 35 * 256 * 2,
},
new CellSetting()
{
Name = "总WIP", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "厂内WIP", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "厂外WIP", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "待入库良品", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "待入库不良品", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "库存", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
},
cellStyles);
DataTable dt =
DataFactory.Database("CysoftContext")
.FindTableBySql(System.IO.File.ReadAllText(Server.MapPath("/Resource/Sql/Stock.txt")));
var cells = new List <CellSetting>
{
new CellSetting()
{
Name = "品号大类", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "品号大类说明", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
new CellSetting()
{
Name = "品号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
new CellSetting()
{
Name = "品名", CellStyle = cellStyles["默认"], CellWidth = 25 * 256 * 2,
},
new CellSetting()
{
Name = "规格", CellStyle = cellStyles["默认"], CellWidth = 35 * 256 * 2,
},
new CellSetting()
{
Name = "仓库代号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "仓库名称", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "数量一", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "单位一", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "数量二", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "单位二", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
};
WriteSheet("仓库全部", dt, wb, cells, cellStyles);
string[] stocks = new string[] { "成品不良仓", "半成品仓", "成品不良仓(客退）", "呆滞料库" };
foreach (var stock in stocks)
{
if (string.IsNullOrEmpty(stock))
{
continue;
}
var dts = dt.Clone();
foreach (var dataRow in dt.Select($"仓库名称 LIKE '%{stock}%'"))
{
dts.ImportRow(dataRow);
}
WriteSheet(stock, dts, wb, cells, cellStyles);
}
WriteSheet("未交订单明细",
DataFactory.Database("CysoftContext").FindTableBySql(System.IO.File.ReadAllText(Server.MapPath("/Resource/Sql/Order.txt"))),
wb,
new List <CellSetting>
{
new CellSetting()
{
Name = "客户代号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "客户名称", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "订单编号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "品号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
new CellSetting()
{
Name = "品名", CellStyle = cellStyles["默认"], CellWidth = 25 * 256 * 2,
},
new CellSetting()
{
Name = "规格", CellStyle = cellStyles["默认"], CellWidth = 35 * 256 * 2,
},
new CellSetting()
{
Name = "业务员代号", CellStyle = cellStyles["默认"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "预计交期", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
new CellSetting()
{
Name = "订单数量1", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "累计出货1", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "未交数量1", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "订单数量2", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "累计出货2", CellStyle = cellStyles["数字"], CellWidth = 15 * 256,
},
new CellSetting()
{
Name = "客户采购单号", CellStyle = cellStyles["默认"], CellWidth = 20 * 256,
},
new CellSetting()
{
Name = "接单日期", CellStyle = cellStyles["默认"], CellWidth = 15 * 256 * 2,
},
},
cellStyles);
//下载
HttpContext.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode($"ERPSTATUS{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls");
HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
wb.Write(HttpContext.Response.OutputStream);
}
private void WriteSheet(string sheetName, DataTable dt, IWorkbook wb, List <CellSetting> cellSettings, Dictionary <string, ICellStyle> cellStyles)
{
//创建表
ISheet sh = wb.CreateSheet(sheetName);
for (int j = 0; j < cellSettings.Count; j++)
{
sh.SetColumnWidth(j, cellSettings[j].CellWidth);//设置单元的宽度
}
#region 合并单元格
sh.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellSettings.Count - 1));
IRow row0 = sh.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1Top0 = row0.CreateCell(0);
icell1Top0.CellStyle = ExcepNpoi.Getcellstyle(wb, ExcepNpoi.Stylexls.头);
icell1Top0.SetCellValue($"SZMJ-{sheetName}:{DateTime.Now.ToString(CultureInfo.InvariantCulture)}");
#endregion
#region 设置表头
IRow row1 = sh.CreateRow(1);
row1.Height = 20 * 20;
for (int j = 0; j < cellSettings.Count; j++)
{
ICell icellTop = row1.CreateCell(j);
icellTop.CellStyle = ExcepNpoi.Getcellstyle(wb, ExcepNpoi.Stylexls.头);
icellTop.SetCellValue(cellSettings[j].Name);
}
#endregion
#region 写入数据
int i = 1;
NumberFormatInfo provider = new NumberFormatInfo
{
NumberDecimalDigits = 1
};
foreach (var dtRow in dt.Rows)
{
//创建行
IRow row = sh.CreateRow(++i);
row.Height = 18 * 20;
for (int j = 0; j < cellSettings.Count; j++)
{
ICell iCells = row.CreateCell(j);
iCells.CellStyle = cellSettings[j].CellStyle;
if (Equals(iCells.CellStyle, cellStyles["数字"]))
{
iCells.SetCellValue(double.Parse(((DataRow)dtRow).ItemArray[j].ToString(), provider));
}
else
{
iCells.SetCellValue(((DataRow)dtRow).ItemArray[j].ToString());
}
}
}
#endregion
}
