一段用以在导出操作时生成多个sheet页的代码。
1.controller
/**
* 导出方法
**/
@ApiOperation(value = "盘点—导出", notes = "盘点—导出")
@ResponseBody
@GetMapping(value = "/export", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
public String download(@RequestParam(value = "categoryPk") BigInteger inventoryPk,
HttpServletRequest request, HttpServletResponse response
) throws Exception {
String fileId = null;
String extName = ".xls";
HSSFWorkbook workbook = amsFixInventoryService.exportAction(inventoryPk);
Date nowTime = new Date(System.currentTimeMillis());
SimpleDateFormat sdFormatter = new SimpleDateFormat("yyyy-MM-dd HHmmdd");
String currentDate = sdFormatter.format(nowTime);
String fileName = new String(("盘点-" + currentDate).getBytes("UTF-8"), "ISO8859-1") + extName;
response.setContentType("application/octet-stream;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
MongoFile mongoFile = new MongoFile();
// 创建临时文件
OutputStream outputStream = response.getOutputStream();
// 获取生成文件存储至MongoDB中
mongoFile.setExtName(extName);
mongoFile.setOriginalName(fileName);
mongoFile.setContentType("text/html");
String ops = outputStream.toString();
ByteArrayInputStream bais = new ByteArrayInputStream(ops.getBytes());
mongoFile.setFile(bais);
this.mongoKit.saveFile(mongoFile);
// 使用mongoKit保存文件到数据库中,返回一个fileId,然后根据fileId获取到具体的文件。关闭文件流
mongoFile.getFile().close();
fileId = mongoFile.getPk();
Assert.assertNotNull(mongoFile);
return fileId;
}
2.service
HSSFWorkbook exportAction(BigInteger inventoryPk);
3.serviceImpl
@SneakyThrows
@Override
public HSSFWorkbook exportAction(BigInteger inventoryPk) {
String typeName = "";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style_title = workbook.createCellStyle();
style_title.setAlignment(HorizontalAlignment.CENTER);
style_title.setWrapText(true);
/**
* sheet页1 概况
**/
typeName = "概况";
HSSFSheet sheet = workbook.createSheet(typeName);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
Record overview = amsFixInventoryMapper.overview(PCKit.getDomainName(), inventoryPk).get(0);
sheet.setColumnWidth(0, 20 * 256);
sheet.setColumnWidth(1, 20 * 256);
sheet.setColumnWidth(2, 20 * 256);
sheet.setColumnWidth(3, 20 * 256);
//第一行
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell01;
cell01 = row0.createCell(0);
cell01.setCellValue("盘点报告");
cell01.setCellStyle(style_title);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
//第二行
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell11;
cell11 = row1.createCell(0);
cell11.setCellValue("1.基本信息");
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
//第三行
HSSFRow row2 = sheet.createRow(2);
HSSFCell cell21;
cell21 = row2.createCell(0);
cell21.setCellValue("拟定编写人:");
HSSFCell cell22;
cell22 = row2.createCell(1);
cell22.setCellValue(overview.get("insertUserName").toString());
// 第四行
HSSFRow row3 = sheet.createRow(3);
HSSFCell cell31;
cell31 = row3.createCell(0);
cell31.setCellValue("盘点开始时间:");
HSSFCell cell32;
cell32 = row3.createCell(1);
cell32.setCellValue(overview.get("beginDate").toString());
// 第五行
HSSFRow row4 = sheet.createRow(4);
HSSFCell cell41;
cell41 = row4.createCell(0);
cell41.setCellValue("盘点开始时间:");
HSSFCell cell42;
cell42 = row4.createCell(1);
cell42.setCellValue(overview.get("endDate").toString());
// 第六行
HSSFRow row5 = sheet.createRow(5);
HSSFCell cell51;
cell51 = row5.createCell(0);
cell51.setCellValue("2、盘点结果信息汇总");
sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 3));
// 第七行
HSSFRow row6 = sheet.createRow(6);
HSSFCell cell61;
cell61 = row6.createCell(0);
cell61.setCellValue("状态");
HSSFCell cell62;
cell62 = row6.createCell(1);
cell62.setCellValue("数量");
HSSFCell cell63;
cell63 = row6.createCell(2);
cell63.setCellValue("原值金额(元)");
HSSFCell cell64;
cell64 = row6.createCell(3);
cell64.setCellValue("备注");
// 第八行
HSSFRow row7 = sheet.createRow(7);
HSSFCell cell71;
cell71 = row7.createCell(0);
cell71.setCellValue("应盘资产");
HSSFCell cell72;
cell72 = row7.createCell(1);
cell72.setCellValue(overview.get("shouldNumber").toString());
HSSFCell cell73;
cell73 = row7.createCell(2);
cell73.setCellValue(overview.get("shouldMoney").toString());
HSSFCell cell74;
cell74 = row7.createCell(3);
cell74.setCellValue(overview.get("note").toString());
// 第九行
HSSFRow row8 = sheet.createRow(8);
HSSFCell cell81;
cell81 = row8.createCell(0);
cell81.setCellValue("正常资产");
HSSFCell cell82;
cell82 = row8.createCell(1);
cell82.setCellValue(overview.get("normalNumber").toString());
HSSFCell cell83;
cell83 = row8.createCell(2);
cell83.setCellValue(overview.get("normalMoney").toString());
HSSFCell cell84;
cell84 = row8.createCell(3);
cell84.setCellValue(overview.get("note").toString());
// 第十行
HSSFRow row9 = sheet.createRow(9);
HSSFCell cell91;
cell91 = row9.createCell(0);
cell91.setCellValue("盘盈资产");
HSSFCell cell92;
cell92 = row9.createCell(1);
cell92.setCellValue(overview.get("surplusNumber").toString());
HSSFCell cell93;
cell93 = row9.createCell(2);
cell93.setCellValue(overview.get("surplusMoney").toString());
HSSFCell cell94;
cell94 = row9.createCell(3);
cell94.setCellValue(overview.get("note").toString());
// 第十一行
HSSFRow row10 = sheet.createRow(10);
HSSFCell cell101;
cell101 = row10.createCell(0);
cell101.setCellValue("盘亏资产");
HSSFCell cell102;
cell102 = row10.createCell(1);
cell102.setCellValue(overview.get("missingNumber").toString());
HSSFCell cell103;
cell103 = row10.createCell(2);
cell103.setCellValue(overview.get("missingMoney").toString());
HSSFCell cell104;
cell104 = row10.createCell(3);
cell104.setCellValue(overview.get("note").toString());
// 第十二行
HSSFRow row11 = sheet.createRow(11);
HSSFCell cell111;
cell111 = row11.createCell(0);
cell111.setCellValue("实盘资产合计");
HSSFCell cell112;
cell112 = row11.createCell(1);
cell112.setCellValue(overview.get("actualNumber").toString());
HSSFCell cell113;
cell113 = row11.createCell(2);
cell113.setCellValue(overview.get("actualMoney").toString());
HSSFCell cell114;
cell114 = row11.createCell(3);
cell114.setCellValue(overview.get("note").toString());
/**
* sheet页2 资产盘点详情
**/
typeName = "资产盘点详情";
HSSFSheet sheet2 = workbook.createSheet(typeName);
List<Record> assetInventoryDetails = amsFixInventoryMapper.assetInventoryDetails(PCKit.getDomainName(), inventoryPk);
HSSFRow row = sheet2.createRow(0);
sheet2.setColumnWidth(0, 20 * 256);
sheet2.setColumnWidth(1, 20 * 256);
sheet2.setColumnWidth(2, 20 * 256);
sheet2.setColumnWidth(3, 20 * 256);
sheet2.setColumnWidth(4, 20 * 256);
sheet2.setColumnWidth(5, 20 * 256);
sheet2.setColumnWidth(6, 20 * 256);
sheet2.setColumnWidth(7, 20 * 256);
sheet2.setColumnWidth(8, 20 * 256);
sheet2.setColumnWidth(9, 20 * 256);
sheet2.setColumnWidth(10, 20 * 256);
sheet2.setColumnWidth(11, 20 * 256);
sheet2.setColumnWidth(12, 20 * 256);
sheet2.setColumnWidth(13, 20 * 256);
sheet2.setColumnWidth(14, 20 * 256);
sheet2.setColumnWidth(15, 20 * 256);
Cell cell200;
cell200 = row.createCell(0);
cell200.setCellValue("资产编码(assetCode)");
Cell cell201;
cell201 = row.createCell(1);
cell201.setCellValue("资产名称(assetName)");
Cell cell202;
cell202 = row.createCell(2);
cell202.setCellValue("所属分类(category_name)");
Cell cell203;
cell203 = row.createCell(3);
cell203.setCellValue("状态(assetStatus)");
Cell cell204;
cell204 = row.createCell(4);
cell204.setCellValue("所属资产台账编号(financial_no)");
Cell cell205;
cell205 = row.createCell(5);
cell205.setCellValue("描述(describeName)");
Cell cell206;
cell206 = row.createCell(6);
cell206.setCellValue("使用人(name)");
Cell cell207;
cell207 = row.createCell(7);
cell207.setCellValue("使用人工号(username)");
Cell cell208;
cell208 = row.createCell(8);
cell208.setCellValue("使用人手机号(phone)");
Cell cell209;
cell209 = row.createCell(9);
cell209.setCellValue("存放地点(location)");
Cell cell2010;
cell2010 = row.createCell(10);
cell2010.setCellValue("最新发现地点(place_name)");
Cell cell2011;
cell2011 = row.createCell(11);
cell2011.setCellValue("盘点状态(status)");
Cell cell2012;
cell2012 = row.createCell(12);
cell2012.setCellValue("盘点时间(created_at)");
Cell cell2013;
cell2013 = row.createCell(13);
cell2013.setCellValue("盘点负责人(handler_name)");
Cell cell2014;
cell2014 = row.createCell(14);
cell2014.setCellValue("盘点方式(inventory_method)");
Cell cell2015;
cell2015 = row.createCell(15);
cell2015.setCellValue("验证方式(verification_method)");
Cell cell2016;
cell2016 = row.createCell(16);
cell2016.setCellValue("标识码(code)");
Cell cell2017;
cell2017 = row.createCell(17);
cell2017.setCellValue("标签状态(label_status)");
for (int i = 0; i < assetInventoryDetails.size(); i++) {
Record r = assetInventoryDetails.get(i);
Row row21 = sheet2.createRow(i + 1);
cell200 = row21.createCell(0);
cell201 = row21.createCell(1);
cell202 = row21.createCell(2);
cell203 = row21.createCell(3);
cell204 = row21.createCell(4);
cell205 = row21.createCell(5);
cell206 = row21.createCell(6);
cell207 = row21.createCell(7);
cell208 = row21.createCell(8);
cell209 = row21.createCell(9);
cell2010 = row21.createCell(10);
cell2011 = row21.createCell(11);
cell2012 = row21.createCell(12);
cell2013 = row21.createCell(13);
cell2014 = row21.createCell(14);
cell2015 = row21.createCell(15);
cell2016 = row21.createCell(16);
cell2017 = row21.createCell(17);
cell200.setCellValue(r.getStr("amsCode"));
cell201.setCellValue(r.getStr("amsName"));
cell202.setCellValue(r.getStr("type"));
cell203.setCellValue(r.getStr("serviceConditionName"));
cell204.setCellValue(r.getStr("orderNo"));
cell205.setCellValue(r.getStr("note"));
cell206.setCellValue(r.getStr("userName"));
cell207.setCellValue(r.getStr("userName"));
cell208.setCellValue(r.getStr("userPhone"));
cell209.setCellValue(r.getStr("oldSpaceName"));
cell2010.setCellValue(r.getStr("newSpaceName"));
cell2011.setCellValue(r.getStr("checkStatusName"));
cell2012.setCellValue(r.getStr("inventoryDate"));
cell2013.setCellValue(r.getStr("inventoryUserName"));
cell2014.setCellValue(r.getStr("limitUserTypeName"));
cell2015.setCellValue(r.getStr("generateTypeName"));
cell2016.setCellValue(r.getStr("statusName"));
cell2017.setCellValue(r.getStr("tagStatusStatus"));
}
/**
* sheet页3 资产盘点明细表
**/
typeName = "资产盘点明细表";
HSSFSheet sheet3 = workbook.createSheet(typeName);
List<Record> inventoryDetails = amsFixInventoryMapper.inventoryDetails(PCKit.getDomainName(), inventoryPk);
HSSFRow row30 = sheet3.createRow(0);
sheet3.setColumnWidth(0, 20 * 256);
sheet3.setColumnWidth(1, 20 * 256);
sheet3.setColumnWidth(2, 20 * 256);
sheet3.setColumnWidth(3, 20 * 256);
sheet3.setColumnWidth(4, 20 * 256);
sheet3.setColumnWidth(5, 20 * 256);
sheet3.setColumnWidth(6, 20 * 256);
sheet3.setColumnWidth(7, 20 * 256);
Cell cell300;
cell300 = row30.createCell(0);
cell300.setCellValue("序号");
Cell cell301;
cell301 = row30.createCell(1);
cell301.setCellValue("资产台账编号");
Cell cell302;
cell302 = row30.createCell(2);
cell302.setCellValue("总价值");
Cell cell303;
cell303 = row30.createCell(3);
cell303.setCellValue("资产存放地区");
Cell cell304;
cell304 = row30.createCell(4);
cell304.setCellValue("资产存放地点");
Cell cell305;
cell305 = row30.createCell(5);
cell305.setCellValue("录入资产明细数");
Cell cell306;
cell306 = row30.createCell(6);
cell306.setCellValue("实际盘点资产数");
Cell cell307;
cell307 = row30.createCell(7);
cell307.setCellValue("盘亏数量");
for (int i = 0; i < inventoryDetails.size(); i++) {
Record r = inventoryDetails.get(i);
Row row31 = sheet3.createRow(i + 1);
cell300 = row31.createCell(0);
cell301 = row31.createCell(1);
cell302 = row31.createCell(2);
cell303 = row31.createCell(3);
cell304 = row31.createCell(4);
cell305 = row31.createCell(5);
cell306 = row31.createCell(6);
cell307 = row31.createCell(7);
cell300.setCellValue(r.getInt("number"));
cell301.setCellValue(r.getStr("orderNo"));
cell302.setCellValue(r.getStr("orderNo"));
cell303.setCellValue(r.getStr("newSpaceName"));
cell304.setCellValue(r.getStr("newSpaceName"));
cell305.setCellValue(r.getStr("shouldNumber"));
cell306.setCellValue(r.getStr("actualNumber"));
cell307.setCellValue(r.getStr("missingNumber"));
}
return workbook;
}