Apache POI 实现 Excel 表格下载

这里以苍穹外卖中数据导出功能为例，记录下 Apache POI 导出 Excel 表格的过程。

首先在 pom.xml 中导入相关依赖

<!-- poi 用于操作 excel 表格-->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></dependency>

controller层

注意方法中的参数 response, service层定义输出流对象要用。

@RestController
@RequestMapping("/admin/report")
@Slf4j
public class ReportController {@GetMapping("/export")@ApiOperation("/导出运营报表")public void exportExcel(HttpServletResponse response){// 注意这里的参数 response, service层定义输出流对象要用。reportService.exportExcel(response);}
}

Service层

定义输出流对象时要用 response 去定义，不是直接 new FileOutputStream()
在得到模板文件时候，注意新建的文件夹在resource下，不要写错，刚开始定义的文件夹名字叫 template， 没加字母 ‘s’，一直获取不到模板文件，正确的应该叫 templates

@Service
public class ReportServiceImpl implements ReportService {@Overridepublic void exportExcel(HttpServletResponse response) {// 得到最近三十天的起止日期LocalDate beginTime = LocalDate.now().minusDays(30);LocalDate endTime = LocalDate.now().minusDays(1);BusinessDataVO businessDataVo = workspaceService.getBusinessData(LocalDateTime.of(beginTime, LocalTime.MIN), LocalDateTime.of(endTime, LocalTime.MAX));// 得到模板文件InputStream in = this.getClass().getClassLoader().getResourceAsStream("templates/运营数据报表模板.xlsx");try {XSSFWorkbook excel = new XSSFWorkbook(in);// 写入时间XSSFSheet sheet = excel.getSheetAt(0);sheet.getRow(1).getCell(1).setCellValue("时间：" + beginTime + "~" + endTime);// 写入概览数据sheet.getRow(3).getCell(2).setCellValue(businessDataVo.getTurnover());sheet.getRow(3).getCell(4).setCellValue(businessDataVo.getOrderCompletionRate());sheet.getRow(3).getCell(6).setCellValue(businessDataVo.getNewUsers());sheet.getRow(4).getCell(2).setCellValue(businessDataVo.getValidOrderCount());sheet.getRow(4).getCell(4).setCellValue(businessDataVo.getUnitPrice());// 填充明细数据for (int i = 0; i < 30; i++) {LocalDate date = beginTime.plusDays(i);BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));XSSFRow row = sheet.getRow(7 + i);row.getCell(1).setCellValue(date.toString());row.getCell(2).setCellValue(businessData.getTurnover());row.getCell(3).setCellValue(businessData.getValidOrderCount());row.getCell(4).setCellValue(businessData.getOrderCompletionRate());row.getCell(5).setCellValue(businessData.getUnitPrice());row.getCell(6).setCellValue(businessData.getNewUsers());}// 输出流下载文件ServletOutputStream out = response.getOutputStream();excel.write(out);// 关闭资源out.flush();out.close();excel.close();} catch (IOException e) {e.printStackTrace();} finally {try {in.close();} catch (IOException e) {e.printStackTrace();}}}}

实现效果