EasyExcel导出包含多个sheet的Excel

亚林瓜子

已于 2022-08-19 10:20:52 修改

阅读量2.5k

点赞数 1

文章标签： EasyExcel

于 2022-08-13 16:04:51 首次发布

欢迎流通功德无量

本文链接：https://blog.csdn.net/fxtxz2/article/details/126320775

版权

Java

  @Override
  public void exportPostOverview(HttpServletResponse response) {
    // 获取所有数据
    List<HeadcountOverviewAreaCodeDTO> headcountViewList =
        headcountViewMapper.getAllHeadcountView();
    List<HeadcountOverviewBuCodeDTO> buCodeList = getBuCodeExportList();
    List<HeadcountOverviewRegionCodeDTO> regionCodeList =
        getRegionCodeExportList(headcountViewList);
    List<HeadcountOverviewAreaCodeDTO> areaCodeList = getAreaCodeExportList(headcountViewList);
    // 设置输入流，设置响应域
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("编制总览.xlsx", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
    // 需要合并的列
    List<Integer> ignoreColumn = new ArrayList<>();
    ignoreColumn.add(6);
    ignoreColumn.add(7);
    ignoreColumn.add(8);
    ignoreColumn.add(10);
    ignoreColumn.add(11);
    // 需要从第一行开始，列头第一行,到第12列
    int mergeRowIndex = 1;
    int mergeColumnRegion = 11;

    try (ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).autoCloseStream(Boolean.TRUE).build()){
	    WriteSheet buSheet =
	            EasyExcelFactory.writerSheet(0, "BU")
	                    .head(HeadcountOverviewBuCodeDTO.class)
	                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnRegion, ignoreColumn))
	                    .build();
	    WriteSheet regionSheet =
	            EasyExcelFactory.writerSheet(1, "Region")
	                    .head(HeadcountOverviewRegionCodeDTO.class)
	                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnRegion, ignoreColumn))
	                    .build();
	    WriteSheet areaSheet =
	            EasyExcelFactory.writerSheet(2, "Area")
	                    .head(HeadcountOverviewAreaCodeDTO.class)
	                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnRegion, ignoreColumn))
	                    .build();
	    excelWriter
	        .write(buCodeList, buSheet)
	        .write(regionCodeList, regionSheet)
	        .write(areaCodeList, areaSheet);
	        excelWriter.finish();
     } catch (IOException e) {
         log.error("导出异常:{}", e);
     }
  }

这里是导出包含3个sheet的excel文件。