导出多个文件到一个Excel中的不同sheets
@ApiOperation(value = "导出多个文件到一个Excel中的不同sheets", produces = "application/octet-stream")
@PostMapping("/exportMultipleExcel")
public void exportMultipleExcel(@RequestBody DataCountVO dataCountVO, HttpServletResponse response) throws Exception {
// 设置响应的ContentType和Header,告诉浏览器下载文件
response.setContentType("application/vnd.ms-excel");
response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("数据统计表.xls", "UTF-8"));
// 创建Excel工作簿
Workbook workbook = new HSSFWorkbook();
// 获取各个数据集
List<goodsStatusVO> goodsStatusList = snShipmentManageService.goodsStatus(dataCountVO);
List<goodsPartVO> goodsPartList = snShipmentManageService.goodsPart(dataCountVO);
List<shipCountVO> shipCountList = snShipmentManageService.shipCount(dataCountVO);
List<loadingStatusVO> loadingStatusList = snShipmentManageService.loadingStatus(dataCountVO);
// 创建参数对象(用来设定excel得sheet得内容等信息)
ExportParams goodsExport = new ExportParams();
// 设置sheet得名称
goodsExport.setSheetName("品种销售情况");
// 创建sheet1使用得map
Map<String, Object> goodsExportMap = new HashMap<>();
// title的参数为ExportParams类型,目前仅仅在ExportParams中设置了sheetName
goodsExportMap.put("title", goodsExport);
// 模版导出对应得实体类型
goodsExportMap.put("entity", goodsStatusVO.class);
// sheet中要填充得数据
goodsExportMap.put("data", goodsStatusList);
ExportParams partExport = new ExportParams();
partExport.setSheetName("品种销量占比");
// 创建sheet2使用得map
Map<String, Object> partExportMap = new HashMap<>();
partExportMap.put("title", partExport);
partExportMap.put("entity", goodsPartVO.class);
partExportMap.put("data", goodsPartList);
ExportParams shipExport = new ExportParams();
shipExport.setSheetName("客户发运量统计");
// 创建sheet3使用得map
Map<String, Object> shipExportMap = new HashMap<>();
shipExportMap.put("title", shipExport);
shipExportMap.put("entity", shipCountVO.class);
shipExportMap.put("data", shipCountList);
ExportParams loadingExport = new ExportParams();
loadingExport.setSheetName("当日装车情况");
// 创建sheet4使用得map
Map<String, Object> loadingExportMap = new HashMap<>();
loadingExportMap.put("title", loadingExport);
loadingExportMap.put("entity", loadingStatusVO.class);
loadingExportMap.put("data", loadingStatusList);
// 将sheet1、sheet2、sheet3、sheet4使用得map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(goodsExportMap);
sheetsList.add(partExportMap);
sheetsList.add(shipExportMap);
sheetsList.add(loadingExportMap);
workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
// 将工作簿写入到响应输出流中
try (ServletOutputStream out = response.getOutputStream()) {
workbook.write(out);
}
// 关闭工作簿
workbook.close();
}
还有就是实体类:::::::VO
序号是一个虚拟展示序号 导出文件的时候有几个数据就有几个序号 在查询的时候可以进行设置
记得加判空哦!!!!!!!!