.net core 3.1 npoi导出excel文档
实例
[HttpPost]
[Route("Excel/Installer")]
public ActionResult Excel1([FromForm]string name, [FromForm]DateTime? stime, [FromForm] DateTime etime)
{
if (etime == null)
{
etime = DateTime.Now;
}
//取出数据源
var result = _orderRepository.FindByInstaller(name, stime, etime);
var paymonry = _payRepository.FindByName(name);
double allmoney = 0;
foreach (var item in paymonry)
{
allmoney = allmoney + item.money;
}
HSSFWorkbook book = new HSSFWorkbook();
ISheet s1 = book.CreateSheet("安装人订单列表");
//表格样式
IDataFormat dataformat = book.CreateDataFormat();
ICellStyle style0 = book.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日");
if (name != null)
{
IRow r1 = s1.CreateRow(0);
r1.CreateCell(0).SetCellValue("安装人");
r1.CreateCell(1).SetCellValue(name);
}
else
{
return BadRequest("安装人不能为空");
}
IRow r2 = s1.CreateRow(1);
r2.CreateCell(0).SetCellValue("订单号");
r2.CreateCell(1).SetCellValue("业务内容");
r2.CreateCell(2).SetCellValue("安装费");
r2.CreateCell(3).SetCellValue("支付金额");
r2.CreateCell(4).SetCellValue("日期");
int i = 0;
double installation = 0;
for (; i < result.Count; i++)
{
NPOI.SS.UserModel.IRow rt = s1.CreateRow(i + 2);
rt.CreateCell(0).SetCellValue(result[i].order_id);
rt.CreateCell(1).SetCellValue(result[i].info);
rt.CreateCell(2).SetCellValue(result[i].installation);
//合并单元格
CellRangeAddress region = new CellRangeAddress(2, i + 2, 3, 3);
s1.AddMergedRegion(region);
rt.CreateCell(3).SetCellValue(allmoney);
rt.CreateCell(4).SetCellValue(result[i].date.ToString("yyyy/MM/dd"));
installation = installation + result[i].installation;
}
NPOI.SS.UserModel.IRow rn = s1.CreateRow(i + 3);
rn.CreateCell(0).SetCellValue("合计");
rn.CreateCell(2).SetCellValue(installation);
//输出的文件名称
string fileName = "安装人员订单表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
//把Excel转为流,输出
//创建文件流
System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
//将工作薄写入文件流
book.Write(bookStream);
//输出之前调用Seek(偏移量,游标位置) 把0位置指定为开始位置
bookStream.Seek(0, System.IO.SeekOrigin.Begin);
//Stream对象,文件类型,文件名称
return File(bookStream, "application/vnd.ms-excel", fileName);
}