waynelone / EasyExcel 使用模板导出

import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.ArrayList; import java.util.List; /** * 根据单元格区域合并单元格 **/ public class MergeCellRangeWriteHandler implements SheetWriteHandler { private final List<CellRangeAddress> rangeCellList; public MergeCellRangeWriteHandler(List<CellRangeAddress> rangeCellList) { this.rangeCellList = (rangeCellList == null) ? new ArrayList<>() : rangeCellList; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Sheet sheet = writeSheetHolder.getSheet(); for (CellRangeAddress cellRangeAddress : rangeCellList) { sheet.addMergedRegionUnsafe(cellRangeAddress); } } }

import com.alibaba.excel.EasyExcelFactory; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.core.io.ClassPathResource; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.net.URLEncoder; import java.util.*; import java.util.stream.Collectors; @RestController @RequestMapping("person") public class DemoController { @GetMapping("export") public ResponseEntity<Object> export() throws Exception { List<Person> list = personService.queryList(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); InputStream templateInputStream = new ClassPathResource("templates/PersonListTemplate.xlsx").getInputStream(); List<CellRangeAddress> cellRangeAddressList = buildMergeCellRangeList(list, 1, 4); MergeCellRangeWriteHandler mergeStrategy = new MergeCellRangeWriteHandler(cellRangeAddressList); ExcelWriter writer = EasyExcelFactory.write(outputStream).withTemplate(templateInputStream).build(); WriteSheet sheet = EasyExcelFactory.writerSheet().registerWriteHandler(mergeStrategy).sheetName("公司员工表").build(); Map<String, String> headerMap = new HashMap<>(); headerMap.put("companyName", "xxx 公司"); writer.fill(headerMap, sheet); FillConfig fillConfig = FillConfig.builder() .forceNewRow(true) .build(); writer.fill(list, fillConfig, sheet); writer.finish(); String encFileName = URLEncoder.encode("公司员工表", "UTF-8"); HttpHeaders headers = new HttpHeaders(); headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + encFileName + ".xlsx"); headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); return new ResponseEntity<>(outputStream.toByteArray(), headers, HttpStatus.OK); } private List<CellRangeAddress> buildMergeCellRangeList( List<Person> statsList, int columnIndex, int startRowIndex ) { List<CellRangeAddress> rangeCellList = new ArrayList<>(); LinkedHashMap<String, Long> groupMap = statsList.stream() .collect(Collectors.groupingBy(Person::getDepartmentName, LinkedHashMap::new, Collectors.counting())); for (Map.Entry<String, Long> entry : groupMap.entrySet()) { int count = entry.getValue().intValue(); if (count > 1) { int endRowIndex = startRowIndex + count - 1; rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex)); } startRowIndex += count; } return rangeCellList; } }