Spring boot easyexcel 实现复合数据导出、按模块导出

场景：

导出数据为1对多的复合数据
一个模块是一条数据，直接填充数据无法实现

如图：

红框内为一条数据(1对多)，下方箭头指向为第二条数据
如果直接填充，只能填充第一条，第二条就没办法了。
由于多行都包含许多，固定表头，只能走填充路线，怎么实现呢

在这里插入图片描述

实现思路流程：

准备一个导出基础填充模板，默认填充key

在这里插入图片描述

计算，复制起始行、复制结束行、复制行数；用poi的复制行方式生成新模块，也就是一条新的 1对多数据。
sheet.copyRows(startRows.get(i), endRows.get(i), copyStartRows.get(i), policy);

复制后

在这里插入图片描述

根据填充fillKey 规律，生成填充key集合；然后进行填充key替换

在这里插入图片描述

并返回待填充的 fillKeys，与数据对齐，进行数据填充。

如果数据过大，经测试一般一个 sheet 最好 100个复合数据，多的再进行sheet复制
xssfWorkbook.cloneSheet(0,"sheet" + (i+1));

参考代码：

    @ApiOperation(value = "数据-excel导出",notes = "首次调用会返回一个processId标识,查询进度携带标识")@GetMapping("/export")public ResultData exportHtMeta(String processId){HtMetaExcelProcessVo htMetaExcelProcessVo;if (!StringUtils.hasLength(processId)){try {htMetaExcelProcessVo=htMetaInfoService.exportHtMetaCopyModule(processId);} catch (Exception e) {throw new ExcelHandlerException("导入失败:"+e.getMessage());}}else {Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);htMetaExcelProcessVo=cache.get(processId,HtMetaExcelProcessVo.class);if (htMetaExcelProcessVo==null){return new ResultData(ErrorCodeEnum.NOT_FOUND_DATA.getCode(),"该导入uid,没有对应数据");}if (htMetaExcelProcessVo.getCurProcess().equals(htMetaExcelProcessVo.getTotalProcess())){htMetaExcelProcessVo.setImportStatus(HtMetaConstants.EXCEL_PROCESS_SUCCESS);htMetaExcelProcessVo.setMsg("导出成功");}}return new ResultData(htMetaExcelProcessVo);}

/*** 导出批次大小，每个sheet导出模块大小*/private static final Integer SPLIT_SIZE=100;@Overridepublic HtMetaExcelProcessVo exportHtMetaCopyModule(String exportKey) throws Exception{HtMetaExcelProcessVo excelProcessVo;Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);if (cache==null){throw new ExcelHandlerException("ehcahe 缓存配置异常");}if (StringUtils.hasLength(exportKey)){//检查是否存在已导出if (cache.get(exportKey)!=null){return cache.get(exportKey,HtMetaExcelProcessVo.class);}}else {exportKey = UUID.randomUUID().toString().replace("-", "");}ClassPathResource resource = new ClassPathResource("excel-template/导入模板.xlsx");String exportPath = new File("").getAbsolutePath() + File.separator + "ht-meta-export";if (!new File(exportPath).exists()){boolean mkdir = new File(exportPath).mkdir();log.info("导出目录创建：{}",mkdir);}File exportFile = new File(exportPath+File.separator+exportKey+".xlsx");log.info("华泰-元数据,导出文件：{}",exportFile.getAbsolutePath());//按数据生成-临时导入模板File tmpExportTemplate = null;ExcelWriter excelWriter =null;try {tmpExportTemplate = File.createTempFile("temp", ".xlsx");List<HtMetaClusterInfoVo> list = htMetaClusterInfoMapper.clusterList(new HtMetaClusterQo());log.info("导出数据条数：{}",list.size());int sheetSize = (list.size() / SPLIT_SIZE);if (sheetSize==1){excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4, "正在导出");}else {excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4+sheetSize, "正在导出");}excelProcessVo.setProcessId(exportKey);cache.put(exportKey,excelProcessVo);//阶段1refreshProcess(cache,exportKey);//单条导出if (list.size()==1){FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter = EasyExcel.write(exportFile).withTemplate(resource.getInputStream()).build();WriteSheet writeSheet = EasyExcel.writerSheet(0).build();HtMetaClusterInfoVo e = list.get(0);excelWriter.fill(new FillWrapper("data0", Collections.singletonList(e)), fillConfig, writeSheet);List<HtMetaNodeInfoVo> nodeInfoVos = e.getNodeInfoVos();List<HtMetaBsInfoVo> bsInfoVos = e.getBsInfoVos();excelWriter.fill(new FillWrapper("data1", nodeInfoVos), fillConfig, writeSheet);excelWriter.fill(new FillWrapper("data2", bsInfoVos), fillConfig, writeSheet);excelWriter.finish();excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_SUCCESS, 4, 4, "导出成功");excelProcessVo.setProcessId(exportKey);cache.put(exportKey,excelProcessVo);return excelProcessVo;}int overSize;if (sheetSize>1){//剩余数量overSize = list.size() - (sheetSize * SPLIT_SIZE);log.info("剩余数据条数：{}",overSize);} else {overSize = 0;}log.info("开始生成数据导出模板");List<List<String>> fillKeys = HtMetaExcelUtil.copyMultiRow(6, 17,12,list.size() - 1,resource.getInputStream(), tmpExportTemplate);log.info("生成结束");//阶段2refreshProcess(cache,exportKey);FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter = EasyExcel.write(exportFile).withTemplate(tmpExportTemplate).build();//阶段3refreshProcess(cache,exportKey);String finalExportKey = exportKey;ExcelWriter finalExcelWriter = excelWriter;File finalTmpExportTemplate = tmpExportTemplate;CompletableFuture.runAsync(()->{try {fiilTemplateExcel(finalExportKey, cache, finalExcelWriter, list, sheetSize, overSize, fillKeys, fillConfig);log.info("填充结束");}finally {if (finalExcelWriter!=null){finalExcelWriter.finish();}boolean delete = finalTmpExportTemplate.delete();log.info("临时导入模板删除: {}",delete);}});} catch (IOException e) {log.info("导出失败");throw e;}return excelProcessVo;}

HtMetaExcelUtil

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Arrays;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;/*** @author xiaoshu* @description* @date 2023年09月01日 23:34*/
@Slf4j
public class HtMetaExcelUtil {/*** 导出批次大小，每个sheet导出模块大小*/private static final Integer SPLIT_SIZE=100;/*** poi对excel进行 多行模块复制,并替换 Fill填充前缀** @param startRowIndex    模块-起始行index，excel标记行号-1* @param endRowIndex      模块-结束行* @param moduleRowSize    模块行数* @param copyCount        复制次数* @param sourceFileStream 源文件流* @param outFile          输出文件* @return List<List<String>> 填充fillKey列表*/public static List<List<String>> copyMultiRow(int startRowIndex, int endRowIndex,int moduleRowSize, int copyCount,InputStream sourceFileStream,File outFile) {File tempFile =null;int sheetSize=0;if (copyCount>SPLIT_SIZE){sheetSize= (copyCount + 1) / SPLIT_SIZE;log.info("复制sheet数量：{}",sheetSize);copyCount=SPLIT_SIZE-1;}//填充key列表List<List<String>> fillKeys = new LinkedList<>();//添加填充模板，默认keyfillKeys.add(Arrays.asList("data0","data0","data1","data2"));//复制起始行int startRow = startRowIndex;//复制结束行int endRow = endRowIndex;//目标起始行int targetRow = endRow + 1;List<Integer> startRows = new LinkedList<>();startRows.add(startRow);List<Integer> endRows = new LinkedList<>();endRows.add(endRow);List<Integer> copyStartRows = new LinkedList<>();copyStartRows.add(targetRow);XSSFWorkbook workbook = null;XSSFWorkbook xssfWorkbook = null;try {workbook = new XSSFWorkbook(sourceFileStream);for (int i = 1; i < copyCount; i++) {startRow = startRow + moduleRowSize;startRows.add(startRow);endRow = endRow + moduleRowSize;endRows.add(endRow);targetRow = endRow + 1;copyStartRows.add(targetRow);}XSSFSheet sheet = workbook.getSheetAt(0);CellCopyPolicy policy = new CellCopyPolicy();policy.setCopyCellFormula(false);policy.setMergeHyperlink(false);policy.setMergeHyperlink(false);for (int i = 0; i < copyCount; i++) {sheet.copyRows(startRows.get(i), endRows.get(i), copyStartRows.get(i), policy);setRowsBorder(workbook,sheet,copyStartRows.get(i)+5,copyStartRows.get(i)+7);setRowsBorder(workbook,sheet,copyStartRows.get(i)+9,copyStartRows.get(i)+12);}//生成临时模板文件tempFile = File.createTempFile("temp", ".xlsx");//写入复制模块后的文件workbook.write(Files.newOutputStream(tempFile.toPath()));//移除模板本身索引startRows.remove(0);//添加最后一列索引if (copyCount!=1){Integer lastRow = startRows.get(startRows.size() - 1);startRows.add(lastRow + moduleRowSize);}//替换填充前缀xssfWorkbook = new XSSFWorkbook(tempFile);XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);int initIndex = 3;if (copyCount!=1){for (Integer row : startRows) {//每行对应填充keyList<String> fillKey = new LinkedList<>();XSSFRow row1 = xssfSheet.getRow(row);replaceRowValue(row1, "data0", "data" + initIndex);fillKey.add("data" + initIndex);XSSFRow row2 = xssfSheet.getRow(row + 2);replaceRowValue(row2, "data0", "data" + initIndex);fillKey.add("data" + initIndex);XSSFRow row3 = xssfSheet.getRow(row + 4);replaceRowValue(row3, "data1", "data" + (initIndex + 1));fillKey.add("data" + (initIndex + 1));XSSFRow row4 = xssfSheet.getRow(row + 8);replaceRowValue(row4, "data2", "data" + (initIndex + 2));fillKey.add("data" + (initIndex + 2));initIndex = initIndex + 3;fillKeys.add(fillKey);}}else {//每行对应填充keyList<String> fillKey = new LinkedList<>();int row=endRowIndex+1;XSSFRow row1 = xssfSheet.getRow(row);replaceRowValue(row1, "data0", "data" + initIndex);fillKey.add("data" + initIndex);XSSFRow row2 = xssfSheet.getRow(row + 2);replaceRowValue(row2, "data0", "data" + initIndex);fillKey.add("data" + initIndex);XSSFRow row3 = xssfSheet.getRow(row + 4);replaceRowValue(row3, "data1", "data" + (initIndex + 1));fillKey.add("data" + (initIndex + 1));XSSFRow row4 = xssfSheet.getRow(row + 8);replaceRowValue(row4, "data2", "data" + (initIndex + 2));fillKey.add("data" + (initIndex + 2));fillKeys.add(fillKey);}if (sheetSize>=1){for (int i = 0; i < sheetSize; i++) {xssfWorkbook.cloneSheet(0,"sheet" + (i+1));}}//替换填充前缀->输出文件xssfWorkbook.write(Files.newOutputStream(outFile.toPath()));return fillKeys;} catch (IOException | InvalidFormatException e) {throw new RuntimeException(e);} finally {try {if (xssfWorkbook != null) {xssfWorkbook.close();}if (workbook != null) {workbook.close();}if (sourceFileStream != null) {sourceFileStream.close();}} catch (IOException e) {throw new RuntimeException(e);}if (tempFile!=null){boolean delete = tempFile.delete();log.info("临时模板删除: {}",delete);}}}//添加边框public static void setRowsBorder(XSSFWorkbook xssfWorkbook,XSSFSheet sheet,int startRow,int endRow){// 创建单元格样式XSSFCellStyle style = xssfWorkbook.createCellStyle();//上下左右边框// 设置边框样式为实线style.setBorderTop(BorderStyle.THIN);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);// 设置边框颜色为黑色style.setTopBorderColor(IndexedColors.BLACK.getIndex());style.setBottomBorderColor(IndexedColors.BLACK.getIndex());style.setLeftBorderColor(IndexedColors.BLACK.getIndex());style.setRightBorderColor(IndexedColors.BLACK.getIndex());for (int i = startRow; i < endRow; i++) {XSSFRow row = sheet.getRow(i);row.setRowStyle(style);}}/*** 行值替换* @param row      替换行* @param oldValue 过去值* @param newValue 替换值*/public static void replaceRowValue(XSSFRow row, String oldValue, String newValue) {Iterator<Cell> cellIterator = row.cellIterator();cellIterator.forEachRemaining(e -> {if (StringUtils.hasLength(e.getStringCellValue())) {String cellValue = e.getStringCellValue();cellValue = cellValue.replace(oldValue, newValue);e.setCellValue(cellValue);}});}/*** 获取导出文件* @param processId 进度id* @return String - 文件路径*/public static String getHtExportFile(String processId) {File file = new File("");return  file.getAbsolutePath() + File.separator + "ht-meta-export" + File.separator + processId + ".xlsx";}/*** 浏览器文件下载* @param targetFile  目标文件* @param response    response*/public static void browserDownLoad(File targetFile, String downLoadName, HttpServletResponse response){OutputStream out = null;InputStream in = null;try {response.reset();response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downLoadName, "UTF-8"));response.addHeader("Content-Length", "" + targetFile.length());response.setContentType("application/vnd.ms-excel");out = new BufferedOutputStream(response.getOutputStream());in = new BufferedInputStream(new FileInputStream(targetFile));IOUtils.copy(in, out);out.flush();} catch (Exception e) {} finally {IOUtils.closeQuietly(in);IOUtils.closeQuietly(out);}}}