SpringBoot中使用poi导出Excel

环境

• IntelliJ IDEA 2023.2.5
• JDK15
• SpringBoot 2.3.1.RELEASE
• POI 3.9

既上一篇JOOQ递归分类查询 将得到的结果excel导出

代码实现

@Slf4j
@RestController
@RequestMapping("/api")
//@OpenApi(name = "excel", intro = "导出Excel")
public class ContentArticleExcelController {

    @Resource
    private ContentArticleService contentArticleService;


    @GetMapping("/excel/download/article")
    public void download(@RequestParam(value = "published", required = false) Byte published, HttpServletResponse response) {
        try {
			// 查询的数据
            List<ContentArticleVO> data = contentArticleService.fetchContentArticle(published);
            // 创建工作簿
            Workbook workbook = new XSSFWorkbook();
            // 创建工作表
            Sheet sheet = workbook.createSheet("文章");
            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("文章编号");
            headerRow.createCell(1).setCellValue("文章标题");
            headerRow.createCell(2).setCellValue("文章分类");
            headerRow.createCell(3).setCellValue("文章作者");
            headerRow.createCell(4).setCellValue("文章图片");
            headerRow.createCell(5).setCellValue("文章内容");

            // 填充数据
            int rowNum = 1;
            for (ContentArticleVO article : data) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(article.getId());
                row.createCell(1).setCellValue(article.getTitle());
                row.createCell(2).setCellValue(article.getCategory());
                row.createCell(3).setCellValue(article.getAuthor());
                row.createCell(4).setCellValue(article.getImages());
                row.createCell(5).setCellValue(article.getContent());
            }

            // 生成Excel文件
            String fileName = "文章内容_" + System.currentTimeMillis()+ ".xlsx";
            Path filePath = Path.of(System.getProperty("app.home") + "\\storage\\excel", fileName);
            try (FileOutputStream outputStream = new FileOutputStream(filePath.toFile())) {
                workbook.write(outputStream);
            }

            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode("文章内容.xlsx", StandardCharsets.UTF_8) + "\"");

//            Files.copy(filePath, response.getOutputStream());

            // 将Excel文件写入响应输出流
            ResponseHelper.download(filePath, response);
        } catch (IOException ex) {
            log.warn("exportToExcel", ex);
        }
    }
}

ResponseHelper工具类

@Slf4j
public final class ResponseHelper {

  public static void download(Path filePath, HttpServletResponse response) {
    try {
      CacheControl cacheControl = CacheControl.maxAge(365, TimeUnit.DAYS).cachePublic();
      response.setHeader(HttpHeaders.CACHE_CONTROL, cacheControl.getHeaderValue());

      @Cleanup InputStream inputStream = Files.newInputStream(filePath);

      IOUtils.copy(inputStream, response.getOutputStream());

      response.flushBuffer();
    } catch (IOException ex) {
      log.warn("download", ex);
    }
  }
}

导出结果