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

1、EasyExce依赖准备

1. EasyExcel依赖

    <!-- easyexcel: excel快速导出-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.0</version>
    </dependency>

2. apache poi依赖

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

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.2.2</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <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博客

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（只需要几行代码）

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 {

        exportDataByAnnotation();
    }

    // 基于注解导出数据
    public static void exportDataByAnnotation() {
        // 文件输出位置
        File outPutFile = new File("D:/temp/Excel-基于注解导出数据.xlsx");
    	// 指定使用哪个注解Bean来生成Excel？
        ExcelWriter excelWriter = EasyExcel.write(outPutFile, FillData.class).excelType(ExcelTypeEnum.XLSX).build();
    	// 生成数据
        excelWriter.write(fillData(5), EasyExcelFactory.writerSheet(0).build());
        // 完成，写数据到本地
        excelWriter.finish();
    }

    public static List<FillData> fillData(int num) {
        List<FillData> mapList = new ArrayList<>();

        for (int i = 0; i < num; i++) {
            FillData fillData = new FillData();
            fillData.setName("姓名" + i)
                    .setAddress("住址" + i)
                    .setWelcome("你好" + i)
                    .setDate(new Date());
            mapList.add(fillData);
        }
        return mapList;
    }
}

2.3、生成结果展示

3、通过Excel模板生成数据

3.1、准备编写Excel模板

注意！

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

如图：Sheet1模板

如图：Sheet2模板

如图：Sheet3模板

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，中间框，加边框

如图：Sheet2，第三列加边框

如图：Sheet3，数据随着占位符的位置向下生成

附录

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

从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);
            }
        }
    }

补充

在读excel文件时，如果文件过大可能会出现错误，需要在读文件前加上一行代码就可以解决。

 // 添加的ZipSecureFile.setMinInflateRatio代码
 ZipSecureFile.setMinInflateRatio(-1.0d);
 XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(new File(filepath));

参考链接

EasyExcel读取模板填充数据_easyexcel填充模板_hello_cmy的博客-CSDN博客
读取excel报错 Zip bomb detected! The file would exceed the max. ratio of compressed file size to the