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);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

/**
     * 导出批次大小，每个sheet导出模块大小
     */
    private static final Integer SPLIT_SIZE=100;

    @Override
    public 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);
            //阶段1
            refreshProcess(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("生成结束");
            //阶段2
            refreshProcess(cache,exportKey);
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter = EasyExcel.write(exportFile).withTemplate(tmpExportTemplate).build();
            //阶段3
            refreshProcess(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;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

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> 填充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<>();
        //添加填充模板，默认key
        fillKeys.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) {
                    //每行对应填充key
                    List<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 {
                //每行对应填充key
                List<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);
        }
    }
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250