如何利用POI导出报表

一、报表格式

二、依赖坐标

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.16</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.16</version>
</dependency>

三、 controller层代码

/*
* 导出运营表
* */@GetMapping("/export")@ApiOperation("导出运营数据报表")public void export(HttpServletResponse response) throws Exception {reportService.exportBusinessData(response);}

四、导出报表

@Override
public void exportBusinessData(HttpServletResponse response) {//查询30天的数据LocalDateTime endTime = LocalDateTime.now();LocalDateTime startTime = endTime.minusDays(30).toLocalDate().atStartOfDay();BusinessDataVO businessDataVO = workspaceService.businessData(startTime, endTime);log.info("businessDataVO,{}", businessDataVO.toString());//查询每一天的List<BusinessDataVO> list = new ArrayList<>();while (startTime.isBefore(endTime)) {LocalDateTime end = startTime.plusHours(24).minusSeconds(1);BusinessDataVO dayVO = workspaceService.businessData(startTime, end);list.add(dayVO);log.info("startTime{},dayVO{}", startTime, dayVO);startTime = startTime.plusDays(1);}try {//读取excel模板InputStream in = this.getClass().getClassLoader().getResourceAsStream("templates/运营数据报表模板.xlsx");XSSFWorkbook workbook = new XSSFWorkbook(in);//填充模板XSSFSheet sheet = workbook.getSheetAt(0);//填充总的30天的XSSFRow row3 = sheet.getRow(3);XSSFRow row4 = sheet.getRow(4);row3.getCell(2).setCellValue(businessDataVO.getTurnover() + "");row3.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate() + "");row3.getCell(6).setCellValue(businessDataVO.getNewUsers() + "");row4.getCell(2).setCellValue(businessDataVO.getValidOrderCount() + "");row4.getCell(4).setCellValue(businessDataVO.getUnitPrice() + "");//填充每一天的int i = 0;startTime = endTime.minusDays(30).toLocalDate().atStartOfDay();for (BusinessDataVO dayVO : list) {XSSFRow row = sheet.getRow(7 + i);if (row == null) {row = sheet.createRow(7 + i);}row.getCell(1).setCellValue(startTime.toLocalDate().toString());row.getCell(2).setCellValue(dayVO.getTurnover() + "");row.getCell(3).setCellValue(dayVO.getValidOrderCount() + "");row.getCell(4).setCellValue(dayVO.getOrderCompletionRate() + "");row.getCell(5).setCellValue(dayVO.getUnitPrice() + "");row.getCell(6).setCellValue(dayVO.getNewUsers() + "");i++;startTime = startTime.plusDays(1);}//写入到response的输出流ServletOutputStream outputStream = response.getOutputStream();workbook.write(outputStream);//关流outputStream.close();workbook.close();} catch (Exception e) {log.error(e.getMessage(), e);throw new BaseException("文件下载异常");}
}