使用EasyExcel 根据模板导出多个Sheet格式相同内容不同的文件_easyexcel 导出多个sheet

作者：2023面试高手 | 2024-05-18 23:52:21

踩

easyexcel 导出多个sheet

文件格式为：
在这里插入图片描述

最后成型：
循环多个会生成多个一样的结构，数据不一样的文件
在这里插入图片描述

我们可以使用easyExcel 的模板填充写入数据
在这里插入图片描述
使用poi复制多个Sheet模板

 /**
     * 生成多sheet模板
     * @param taskNoList
     * @param outModelFile
     * @param templateFileName
     */
    private static void createModel(List<String> taskNoList, File outModelFile, String templateFileName) {
        try (FileOutputStream fileOutputStream = new FileOutputStream(outModelFile)) {
            //读取excel模板
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templateFileName));
            workbook.cloneSheet(0);

            // 获取要复制的工作表索引
            int sourceSheetIndex = 0;
            Sheet sourceSheet = workbook.getSheetAt(sourceSheetIndex);

            Workbook targetWorkbook = new XSSFWorkbook();
            for (int i = 0; i < taskNoList.size(); i++) {
                String sheetName = taskNoList.get(i);
                Sheet targetSheet = targetWorkbook.createSheet(sheetName);
                // 复制原始工作表的内容到目标工作表
                copySheet(sourceSheet, targetSheet);
            }
            // 保存目标文件
            targetWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
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

获取模板格式：

/**
     * 获取模板内容和格式
     *
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
        Workbook targetWorkbook = targetSheet.getWorkbook();
        CellStyle targetCellStyle;

        // 复制合并的单元格区域
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(mergedRegion);
        }

        for (Row sourceRow : sourceSheet) {
            Row targetRow = targetSheet.createRow(sourceRow.getRowNum());

            for (Cell sourceCell : sourceRow) {
                Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                targetCell.setCellType(sourceCell.getCellType());

                // 复制单元格的值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                }

                // 复制单元格的样式
                CellStyle sourceCellStyle = sourceCell.getCellStyle();
                targetCellStyle = targetWorkbook.createCellStyle();
                targetCellStyle.cloneStyleFrom(sourceCellStyle);
                targetCell.setCellStyle(targetCellStyle);
            }
        }
    }
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

完整代码如下：

package com.vanmilk.wms.stocktake.service.impl;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 〈〉
 *
 * @author Mr.Wang
 * @create 2023/6/26
 * @since 1.0.0
 */
@Slf4j
@Service
public class DownloadStockTakeServiceImpl implements DownloadStockTakeService {

    @Autowired
    private WmsStocktakeTaskMapper wmsStocktakeTaskMapper;

    @Autowired
    private WmsStocktakeTaskFilterMapper wmsStocktakeTaskFilterMapper;

    @Autowired
    private WmsStocktakeTaskDetailMapper wmsStocktakeTaskDetailMapper;


    @Override
    protected void process(DownloadStockTakeDoc actionContext) throws BusinessException {
        List<String> taskNoList = actionContext.getTaskNoList();
        String s3Url = null;
        String path = "D:\\AFile\\";
        final String modelFileName = "Stock_Task_Model.xlsx";
        final String outsFileName = "Stock_Task_" + new SimpleDateFormat("yyyyMMddHH").format(new Date()) + ".xlsx";
        final File outModelFile = new File(path, modelFileName);
        final File outsFile = new File(path, outsFileName);
        String templateFileName = path + "Inventory_Task_List_Template.xlsx";
        createModel(taskNoList, outModelFile, templateFileName);
        ExcelWriter excelWriter = setStyle(outModelFile, outsFile);
        for (String taskNo : taskNoList) {
            WriteSheet writeSheet = EasyExcel.writerSheet(taskNo).build();
            StocktakeTaskVo stocktakeTaskVo = this.wmsStocktakeTaskMapper.queryVoByTaskNo(taskNo);
            if (stocktakeTaskVo != null) {
                WmsStocktakeTaskFilter wmsStocktakeTaskFilter = this.wmsStocktakeTaskFilterMapper.selectByTaskNo(taskNo);
                if (wmsStocktakeTaskFilter != null) {
                    stocktakeTaskVo.setWarehouseCode(wmsStocktakeTaskFilter.getWarehouseCode());
                    stocktakeTaskVo.setLocationCodes(StrUtil.split(wmsStocktakeTaskFilter.getLocationCode(), ','));
                    stocktakeTaskVo.setRackCodes(StrUtil.split(wmsStocktakeTaskFilter.getRackCode(), ','));
                    stocktakeTaskVo.setGoodsPositionCodes(StrUtil.split(wmsStocktakeTaskFilter.getGoodsPositionCode(), ','));
                }
                List<StocktakeTaskDetailVo> wmsStockTakeTaskDetails = this.wmsStocktakeTaskDetailMapper.selectVoByTaskNo(taskNo);
                stocktakeTaskVo.setStocktakeTaskDetails(wmsStockTakeTaskDetails);

                FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.TRUE).build();

                if (stocktakeTaskVo.getStocktakeTaskDetails().size() > 0) {
                    excelWriter.fill(new FillWrapper("list", stocktakeTaskVo.getStocktakeTaskDetails()), fillConfig, writeSheet);
                }

                Map<String, Object> map = getStringObjectMap(stocktakeTaskVo);
                excelWriter.fill(map, writeSheet);
            }
        }
        excelWriter.finish();
        outModelFile.delete();
    }

    private static ExcelWriter setStyle(File outModelFile, File outsFile) {
        // 内容
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 标题
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setFontName("等线");
        contentWriteFont.setBold(false);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        ExcelWriter excelWriter = EasyExcel.write(outsFile)
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .registerWriteHandler(new ExcelWidthStyleStrategy()) // 自动设置列宽
                .withTemplate(outModelFile)
                .build();
        return excelWriter;
    }

    /**
     * 生成多sheet模板
     * @param taskNoList
     * @param outModelFile
     * @param templateFileName
     */
    private static void createModel(List<String> taskNoList, File outModelFile, String templateFileName) {
        try (FileOutputStream fileOutputStream = new FileOutputStream(outModelFile)) {
            //读取excel模板
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templateFileName));
            workbook.cloneSheet(0);

            // 获取要复制的工作表索引
            int sourceSheetIndex = 0;
            Sheet sourceSheet = workbook.getSheetAt(sourceSheetIndex);

            Workbook targetWorkbook = new XSSFWorkbook();
            for (int i = 0; i < taskNoList.size(); i++) {
                String sheetName = taskNoList.get(i);
                Sheet targetSheet = targetWorkbook.createSheet(sheetName);
                // 复制原始工作表的内容到目标工作表
                copySheet(sourceSheet, targetSheet);
            }
            // 保存目标文件
            targetWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 写入数据
     *
     * @param stocktakeTaskVo
     * @return
     */
    private Map<String, Object> getStringObjectMap(StocktakeTaskVo stocktakeTaskVo) {
        Map<String, Object> map = new HashMap<>();
        map.put("taskNo", stocktakeTaskVo.getTaskNo());
        map.put("scope", stocktakeTaskVo.getScope());
        map.put("countType", stocktakeTaskVo.getCountType());
        map.put("warehouseCode", stocktakeTaskVo.getWarehouseCode());
        map.put("locationCodes", stocktakeTaskVo.getLocationCodes() != null || !stocktakeTaskVo.getLocationCodes().isEmpty()
                ? String.join(",", stocktakeTaskVo.getLocationCodes()) : stocktakeTaskVo.getLocationCodes());
        map.put("rackCodes", stocktakeTaskVo.getRackCodes() != null || !stocktakeTaskVo.getRackCodes().isEmpty()
                ? String.join(",", stocktakeTaskVo.getRackCodes()) : stocktakeTaskVo.getRackCodes());
        map.put("goodsPositionCodes", stocktakeTaskVo.getGoodsPositionCodes() != null || !stocktakeTaskVo.getGoodsPositionCodes().isEmpty()
                ? String.join(",", stocktakeTaskVo.getGoodsPositionCodes()) : stocktakeTaskVo.getGoodsPositionCodes());
        map.put("description", stocktakeTaskVo.getDescription());
        return map;
    }

    /**
     * 获取模板内容和格式
     *
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
        Workbook targetWorkbook = targetSheet.getWorkbook();
        CellStyle targetCellStyle;

        // 复制合并的单元格区域
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(mergedRegion);
        }

        for (Row sourceRow : sourceSheet) {
            Row targetRow = targetSheet.createRow(sourceRow.getRowNum());

            for (Cell sourceCell : sourceRow) {
                Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                targetCell.setCellType(sourceCell.getCellType());

                // 复制单元格的值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                }

                // 复制单元格的样式
                CellStyle sourceCellStyle = sourceCell.getCellStyle();
                targetCellStyle = targetWorkbook.createCellStyle();
                targetCellStyle.cloneStyleFrom(sourceCellStyle);
                targetCell.setCellStyle(targetCellStyle);
            }
        }
    }
}
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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/590475