EasyExcel读模板生成excel文件&注解Bean生成文件

旧城等待， 2023-10-15 13:02 68阅读 0赞

#### 文章目录 ####

*  1、EasyExce依赖准备
 *  2、通过注解Bean的方式生成Excel
 *   *  2.1、注解Bean准备
     *  2.2、封装数据，生成Excel（只需要几行代码）
     *   *  2.3、生成结果展示
 *  3、通过Excel模板生成数据
 *   *  3.1、准备编写Excel模板
     *  3.2、封装数据，生成excel
     *  3.3、模板导出数据结果展示
 *  附录
 *   *  占位符为什么是\`\{.字段名\}\`？源码分析
     *  参考链接

## 1、EasyExce依赖准备 ##

1.  EasyExcel依赖

<dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>3.1.0</version>
        </dependency>

1.  apache poi依赖

EasyExcel同时依赖Apache POI库，同时还需要注意下两个库的版本对应关系。EasyExcel可能升级对poi的依赖，导致功能不兼容。

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>5.2.2</version>
        </dependency>
        
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>5.2.2</version>
        </dependency>

## 2、通过注解Bean的方式生成Excel ##

### 2.1、注解Bean准备 ###

EasyExcel注解部分参考：[EasyExcel注解大全\_起一个让人印象深刻的名字的博客-CSDN博客][EasyExcel_-CSDN]

package com.dongzi.utils.excel.bean;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.format.DateTimeFormat;
    import com.alibaba.excel.annotation.write.style.*;
    import com.alibaba.excel.enums.BooleanEnum;
    import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
    import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    import java.util.Date;
    
    @Data // lombok提供getter\setter等
    @Accessors(chain = true) // lombok提供链式操作
    @ContentRowHeight(25) // 行高
    public class FillData {
        
    
        @HeadStyle(fillBackgroundColor = 4) // 表头样式设置，背景颜色填充，颜色类型4
        @HeadFontStyle(fontName = "宋体", fontHeightInPoints = 25, color = 3) // 表头字体设置，宋体，颜色3
        @ExcelProperty(value = "姓名") // 列名
        @ColumnWidth(20) // 列宽
        @ContentFontStyle(fontName = "宋体", color = 4, bold = BooleanEnum.TRUE) // 数据部分单元格字体样式
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
        // 数据部分单元格样式
        private String name; // 字段
    
        @HeadStyle(fillBackgroundColor = 4)
        @HeadFontStyle(fontName = "楷体", fontHeightInPoints = 25, color = 3)
        @ExcelProperty(value = "地址")
        @ColumnWidth(20)
        @ContentFontStyle(fontName = "楷体")
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
        private String address;
    
        @HeadStyle(fillBackgroundColor = 4)
        @HeadFontStyle(fontName = "微软雅黑", fontHeightInPoints = 25, color = 3)
        @ExcelProperty(value = "时间")
        @ColumnWidth(50)
        @ContentFontStyle(fontName = "微软雅黑")
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
        @DateTimeFormat(value = "yyyy-MM-dd") // 时间格式化
        private Date date;
    
        // 忽略生成的字段
        @ExcelIgnore
        private String welcome;
    
    }

### 2.2、封装数据，生成Excel（只需要几行代码） ###

#### 2.3、生成结果展示 ####

![image.png][]

## 3、通过Excel模板生成数据 ##

### 3.1、准备编写Excel模板 ###

注意！

1.  占位符的书写格式为`{.字段名}`（为什么占位符是这样的，详见附录）
2.  一个单元格个可以写多个占位符
3.  模板需要按照预定的**格式/样式**调整好，之后会按照相同格式输出单元格
4.  数据输出视占位符所在位置依次向下输出

如图：Sheet1模板  
![image.png][image.png 1]  
如图：Sheet2模板  
![image.png][image.png 2]  
如图：Sheet3模板  
![image.png][image.png 3]

### 3.2、封装数据，生成excel ###

package com.dongzi.utils.excel;
    
    import cn.hutool.core.date.DateUtil;
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.dongzi.utils.excel.bean.FillData;
    import org.springframework.core.io.ClassPathResource;
    
    import java.io.File;
    import java.io.IOException;
    import java.util.*;
    
    public class ReadeExcelTemplate {
        
    
        public static void main(String[] args) throws IOException {
        
    
           exportDataByTemplate();
    
        }
    
        // 基于excel模板导出数据
        public static void exportDataByTemplate() throws IOException {
        
            File outPutFile = new File("D:/temp/Excel-基于模板导出数据.xlsx");
            ClassPathResource pathResource = new ClassPathResource("template/info.xlsx");
    
            // 基于excel模板导出数据
            ExcelWriter excelWriter = EasyExcel.write(outPutFile).withTemplate(pathResource.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX).build();
    
    //        excelWriter.write(fillData(5), EasyExcel.writerSheet(0).build());
    //        excelWriter.write(fillData(10), EasyExcel.writerSheet(1).build());
    //        excelWriter.write(fillData(15), EasyExcel.writerSheet(2).build());
    
            // {.name}-{.welcome}, {.address}, {.date} 占位符位置数据补充,一个单元格可以写多个{.字段名}
            // 数据的位置从当前sheet页的的占位符位置开始，无论title在什么位置
            // 注意这里必须使用excelWriter.fill()进行数据填充
            excelWriter.fill(fillData(5), EasyExcel.writerSheet(0).build());
            excelWriter.fill(fillData(10), EasyExcel.writerSheet(1).build());
            excelWriter.fill(fillData(15), EasyExcelFactory.writerSheet(2).build());
    
            // {.name}, {.address}, {.date} 占位符位置数据补充
    //        excelWriter.fill(fillData2(5), EasyExcel.writerSheet(0).build());
    //        excelWriter.fill(fillData2(10), EasyExcel.writerSheet(1).build());
    //        excelWriter.fill(fillData2(15), EasyExcelFactory.writerSheet(2).build());
    
            excelWriter.finish();
    
        }
    
        public static List<Map<String, Object>> fillData2(int num) {
        
            List<Map<String, Object>> mapList = new ArrayList<>();
    
            for (int i = 0; i < num; i++) {
        
                Map<String, Object> beanMap = new HashMap<>();
                beanMap.put("name", "姓名" + i);
                beanMap.put("welcome", "你好" + i);
                beanMap.put("address", "地址" + i);
                beanMap.put("date", DateUtil.format(new Date(), "yyyy-MM-dd"));
                mapList.add(beanMap);
            }
            return mapList;
        }
    }

### 3.3、模板导出数据结果展示 ###

如图：Sheet1，中间框，加边框  
![image.png][image.png 4]  
如图：Sheet2，第三列加边框  
![image.png][image.png 5]  
如图：Sheet3，数据随着占位符的位置向下生成  
![image.png][image.png 6]

## 附录 ##

### 占位符为什么是`{.字段名}`？源码分析 ###

从`excelWriter.fill()`逐行调试，发现：在读excel模板的时候，会先进行占位符的解析。看源码：  
包路径: `package com.alibaba.excel.write.executor;`

private List<AnalysisCell> readTemplateData(Map<ExcelWriteFillExecutor.UniqueDataFlagKey, List<AnalysisCell>> analysisCache) {
        
            List<AnalysisCell> analysisCellList = (List)analysisCache.get(this.currentUniqueDataFlag);
            if (analysisCellList != null) {
        
                return analysisCellList;
            } else {
        
                Sheet sheet = this.writeContext.writeSheetHolder().getCachedSheet();
                Map<ExcelWriteFillExecutor.UniqueDataFlagKey, Set<Integer>> firstRowCache = MapUtils.newHashMapWithExpectedSize(8);
    
                for(int i = 0; i <= sheet.getLastRowNum(); ++i) {
        
                    Row row = sheet.getRow(i);
                    if (row != null) {
        
                        for(int j = 0; j < row.getLastCellNum(); ++j) {
        
                            Cell cell = row.getCell(j);
                            if (cell != null) {
        
                                // 解析单元格占位符
                                String preparedData = this.prepareData(cell, i, j, firstRowCache);
                                if (preparedData != null) {
        
                                    cell.setCellValue(preparedData);
                                }
                            }
                        }
                    }
                }
    
                return (List)analysisCache.get(this.currentUniqueDataFlag);
            }
        }
    
        private String prepareData(Cell cell, int rowIndex, int columnIndex, Map<ExcelWriteFillExecutor.UniqueDataFlagKey, Set<Integer>> firstRowCache) {
        
            if (!CellType.STRING.equals(cell.getCellType())) {
        
                return null;
            } else {
        
                String value = cell.getStringCellValue();
                if (StringUtils.isEmpty(value)) {
        
                    return null;
                } else {
        
                    StringBuilder preparedData = new StringBuilder();
                    AnalysisCell analysisCell = null;
                    int startIndex = 0;
                    int length = value.length();
                    int lastPrepareDataIndex = 0;
    
                    label81:
                    while(startIndex < length) {
        
                        // 对单元格占位符进行分割，取出字段名，之后按字段名去取值
                        // 有兴趣的可以逐行调试
                        int prefixIndex = value.indexOf("{", startIndex);
                        if (prefixIndex < 0) {
        
                            break;
                        }
    
                        if (prefixIndex != 0) {
        
                            char prefixPrefixChar = value.charAt(prefixIndex - 1);
                            if (prefixPrefixChar == '\\') {
        
                                startIndex = prefixIndex + 1;
                                continue;
                            }
                        }
    
                        int suffixIndex = -1;
    
                        while(suffixIndex == -1 && startIndex < length) {
        
                            suffixIndex = value.indexOf("}", startIndex + 1);
                            if (suffixIndex < 0) {
        
                                break label81;
                            }
    
                            startIndex = suffixIndex + 1;
                            char prefixSuffixChar = value.charAt(suffixIndex - 1);
                            if (prefixSuffixChar == '\\') {
        
                                suffixIndex = -1;
                            }
                        }
    
                        if (analysisCell == null) {
        
                            analysisCell = this.initAnalysisCell(rowIndex, columnIndex);
                        }
    
                        String variable = value.substring(prefixIndex + 1, suffixIndex);
                        if (!StringUtils.isEmpty(variable)) {
        
                            int collectPrefixIndex = variable.indexOf(".");
                            if (collectPrefixIndex > -1) {
        
                                if (collectPrefixIndex != 0) {
        
                                    analysisCell.setPrefix(variable.substring(0, collectPrefixIndex));
                                }
    
                                variable = variable.substring(collectPrefixIndex + 1);
                                if (StringUtils.isEmpty(variable)) {
        
                                    continue;
                                }
    
                                analysisCell.setCellType(WriteTemplateAnalysisCellTypeEnum.COLLECTION);
                            }
    
                            analysisCell.getVariableList().add(variable);
                            if (lastPrepareDataIndex == prefixIndex) {
        
                                analysisCell.getPrepareDataList().add("");
                                if (lastPrepareDataIndex != 0) {
        
                                    analysisCell.setOnlyOneVariable(Boolean.FALSE);
                                }
                            } else {
        
                                String data = this.convertPrepareData(value.substring(lastPrepareDataIndex, prefixIndex));
                                preparedData.append(data);
                                analysisCell.getPrepareDataList().add(data);
                                analysisCell.setOnlyOneVariable(Boolean.FALSE);
                            }
    
                            lastPrepareDataIndex = suffixIndex + 1;
                        }
                    }
    
                    if (analysisCell != null && CollectionUtils.isNotEmpty(analysisCell.getVariableList())) {
        
                        cell.setBlank();
                    }
    
                    return this.dealAnalysisCell(analysisCell, value, rowIndex, lastPrepareDataIndex, length, firstRowCache, preparedData);
                }
            }
        }

### 参考链接 ###

[EasyExcel读取模板填充数据\_easyexcel填充模板\_hello\_cmy的博客-CSDN博客][EasyExcel_easyexcel_hello_cmy_-CSDN]

[EasyExcel_-CSDN]: https://blog.csdn.net/y449739776/article/details/123879540
[image.png]: https://img-blog.csdnimg.cn/img_convert/a0a6d0cfe93e9eda2bae286e8ddc30ec.png
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/48f3573e3def605c07991191a0663f9d.png
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/666026a7a05c37423b87213c48b91a72.png
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/2e72498310cd419936004250ef756284.png
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/2151f0ed462aef581821b187fb61bfd6.png
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/b960df6f7629eb69e3bb5ddc661cbe44.png
[image.png 6]: https://img-blog.csdnimg.cn/img_convert/7d5be57a345e124a189c63b37f2dbbc2.png
[EasyExcel_easyexcel_hello_cmy_-CSDN]: https://blog.csdn.net/hello_cmy/article/details/131128846