EasyExcel实现文件上传下载（百万级数据、单元格自定义样式）

一、EasyExcel介绍

  Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
  EasyExcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

官方网站：https://easyexcel.opensource.alibaba.com/docs/current

maven依赖

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

特别说明：Excel不同版本最多的行和列

• Excel2003(xls) 最多65536（六万多）行，256 列，超过就会报错
• Excel2007(xlsx) 最多1048576（一百万多）行，16384列（采取的是xml存储），超过就会报错

二、写Excel

1、最简单的写

实体类

@Data
public class Employee {
    @ExcelProperty(value = "用户编号")
    private Integer id;

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String gender;

    @ExcelProperty(value = "工资")
    private Double salary;

    @ExcelProperty(value = "入职时间")
    private Date hireDate;
}

通用数据生成 后面不会重复写

private List<Employee> employeeData() {
    List<Employee> employeeList = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        Employee employee = new Employee();
        employee.setId(i);
        employee.setName("emp" + i);
        employee.setGender(i % 2 == 0 ? "男" : "女");
        employee.setSalary(i * 1000.00);
        employee.setHireDate(new Date());
        employeeList.add(employee);
    }
    return employeeList;
}

✅最简单的写代码

@Test
public void simpleWrite() {
    String fileName = "/Users/xuchang/Documents/simpleWrite.xlsx";
    EasyExcel.write(fileName, Employee.class)
            .sheet("Sheet名称")
            .doWrite(employeeData());
}

🌈效果

注意⚠️在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照重复多次写入

2、列宽、行高、背景颜色、字体大小颜色、对齐方式

2.1、编码方式

✅代码

@Test
public void customWrite() {
    String fileName = "/Users/xuchang/Documents/customWrite.xlsx";
    EasyExcel.write(fileName, Employee.class)
            .sheet("Sheet名称")
            // 简单的列宽策略，列宽20
            .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
            // 简单的行高策略：头行高30，内容行高20
            .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30,(short)20))
            // 自定义样式
            .registerWriteHandler(getStyleStrategy())
            .doWrite(employeeData());
}


// 设置样式
public static HorizontalCellStyleStrategy getStyleStrategy() {
    // 内容样式策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // 垂直居中,水平居中
    contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    // 上下左右边框设置
    contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
    contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
    contentWriteCellStyle.setBorderBottom(BorderStyle.THICK);
    // 设置 自动换行
    contentWriteCellStyle.setWrapped(true);
    // 字体策略
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short) 12);
    contentWriteFont.setColor(IndexedColors.GREEN.getIndex());
    contentWriteCellStyle.setWriteFont(contentWriteFont);

    // 头策略使用默认
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}

🌈效果

2.2、注解方式

✅代码

@ContentRowHeight(20) // 设置每个表格的高度
@HeadRowHeight(50) // 设置每个表头的高度
@ColumnWidth(20) // 设置每个表格的宽度

// 头背景设置成红色
// fillForegroundColor(填充颜色)：枚举类IndexedColors 10-红色 12-蓝色 17-绿色。。。
// fillPatternType(填充方式)：枚举类FillPatternTypeEnum SOLID_FOREGROUND-实心填充 还有其他填充方式，则不需要填写颜色了
// horizontalAlignment(水平对齐类型）：枚举类HorizontalAlignmentEnum LEFT-左对齐 CENTER-居中 RIGHT-右对齐
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
// 头字体设置成20，表头字体颜色设置为蓝色
@HeadFontStyle(fontHeightInPoints = 30, color = 12)

// 内容的背景设置成绿色（与HeadStyle注解一致）
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17, horizontalAlignment = HorizontalAlignmentEnum.LEFT)
// 内容字体设置成20，内容字体颜色设置为红色
@ContentFontStyle(fontHeightInPoints = 10, color = 13)

@Data
public class Employee {
    @ColumnWidth(30)
    @ExcelProperty(value = "用户编号")
    private Integer id;

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String gender;

    @ExcelProperty(value = "工资")
    private Double salary;

    @ColumnWidth(40)
    @ExcelProperty(value = "入职时间")
    private Date hireDate;
}

🌈效果

3、复杂头与修改列顺序

✅代码（只需要改下数据实体注解）

@Data
public class Employee {
    @ExcelProperty(value = {"主标题", "用户编号"}, index = 1)
    private Integer id;

    @ExcelProperty(value = {"主标题", "姓名"}, index = 0)
    private String name;

    @ExcelProperty(value = {"主标题", "性别"}, index = 4)
    private String gender;

    @ExcelProperty(value = {"主标题", "工资"}, index = 2)
    private Double salary;

    @ExcelProperty(value = {"主标题", "入职时间"}, index = 3)
    private Date hireDate;
}

🌈效果

4、日期、数字类型和自定义格式化

✅代码

@Test
public void converterWrite() {
    List<DataInfo> list = Lists.newArrayList();
    list.add(new DataInfo("张三", 1, new Date(), new BigDecimal("0.5")));
    list.add(new DataInfo("李四", 2, new Date(), new BigDecimal("0.123")));
    list.add(new DataInfo("王五", 3, new Date(), new BigDecimal("0.4567")));
    list.add(new DataInfo("赵六", 4, new Date(), new BigDecimal("0.55789")));

    String fileName = "/Users/xuchang/Documents/converterWrite.xlsx";
    EasyExcel.write(fileName, DataInfo.class)
            .sheet("Sheet名称")
            .doWrite(list);
}

// 实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataInfo {
    @ExcelProperty(value = "名称")
    private String name;
	
	// 自定义格式化，java的数字类型，导出excel为字符串类型
    @ExcelProperty(value = "季节", converter = IntegerToStringConverter.class)
    private Integer season;

    @ExcelProperty(value = "日期")
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private Date date;

    // 最后一位默认四舍五入
    @ExcelProperty(value = "百分比")
    @NumberFormat(value = "0.00%")
    private BigDecimal percent;
}

// 自定义格式化转换类
public class IntegerToStringConverter implements Converter<Integer> {

    // 设置Java类型
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    // 设置Excel类型
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }


    // 读操作时候 将 excel 对象转换为 Java 对象
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        switch (cellData.getStringValue()) {
            case "春天":
                return 1;
            case "夏天":
                return 2;
            case "秋天":
                return 3;
            case "冬天":
                return 4;
            default:
                break;
        }
        throw new Exception("未知季节类型");
    }

    // 写操作时 将 Java 对象转换为 excel 对象
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        switch (value) {
            case 1:
                return new WriteCellData<>("春天");
            case 2:
                return new WriteCellData<>("夏天");
            case 3:
                return new WriteCellData<>("秋天");
            case 4:
                return new WriteCellData<>("冬天");
            default:
                break;
        }
        throw new Exception("未知季节类型");
    }
}

🌈效果

5、设置单元格下拉

✅代码

@Test
public void selectWrite() {
    String fileName = "/Users/xuchang/Documents/selectWrite.xlsx";
    EasyExcel.write(fileName, Employee.class)
            .registerWriteHandler(new SelectWriteHandler())
            .sheet("Sheet名称")
            .doWrite(employeeData());
}

// 设置下拉的实现
public class SelectWriteHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 定义一个map key是需要添加下拉框的列的index value是下拉框数据
        Map<Integer, String[]> mapDropDown = new HashMap<>(1);
        // 设置性别的码值
        String[] sex = {"男", "女"};

        // 下拉选在Excel中对应的列
        mapDropDown.put(2, sex);

        // 获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            // 起始行、终止行、起始列、终止列  起始行为1即表示表头不设置
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
    }
}

🌈效果

6、重复多次写入(百万数据)

✅代码（16g内存mac耗时6秒多）真快🚀

@Test
public void repeatedWrite() {
    long start = System.currentTimeMillis();
    String fileName = "/Users/xuchang/Documents/repeatedWrite.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
        // 如果写入同一个Sheet，创建WriteSheet就写在这里
        // 如果想要写入多个Sheet，创建WriteSheet就放入for循环中，每组10万个数据放入同一个sheet中
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet名称").build();
        // 每次写入10万数据，循环写入10次，一共写入100万数据
        for (int i = 1; i <= 10; i++) {
            excelWriter.write(employeeMoreData(100000), writeSheet);
        }
    }
    System.out.println("耗时：" + (System.currentTimeMillis() - start));// 耗时：6732
}

🌈效果

7、导出指定列

✅代码（可以选择忽略和只导出的字段）

@Test
public void excludeOrIncludeWrite() {
    String excludeFileName = "/Users/xuchang/Documents/testExclude.xlsx";
    // 我们要忽略 “工资” 这一列不导出
    EasyExcel.write(excludeFileName, Employee.class)
            .excludeColumnFieldNames(Arrays.asList("salary"))
            .sheet("Sheet名称")
            .doWrite(employeeData());

    String includeFileName = "/Users/xuchang/Documents/testInclude.xlsx";
    // 我们只要导出 "编号和名称" 这两列，其他列不导出
    EasyExcel.write(includeFileName, Employee.class)
            .includeColumnFieldNames(Arrays.asList("id", "name"))
            .sheet("Sheet名称")
            .doWrite(employeeData());
}

🌈效果

8、动态生成表头

✅代码

@Test
public void excludeOrIncludeWrite() {
    String excludeFileName = "/Users/xuchang/Documents/testExclude.xlsx";
    // 我们要忽略 “工资” 这一列不导出
    EasyExcel.write(excludeFileName, Employee.class)
            .excludeColumnFieldNames(Arrays.asList("salary"))
            .sheet("Sheet名称")
            .doWrite(employeeData());

    String includeFileName = "/Users/xuchang/Documents/testInclude.xlsx";
    // 我们只要导出 "编号和名称" 这两列，其他列不导出
    EasyExcel.write(includeFileName, Employee.class)
            .includeColumnFieldNames(Arrays.asList("id", "name"))
            .sheet("Sheet名称")
            .doWrite(employeeData());
}

🌈效果

9、模板填充Excel

• 向预设样式的表格中直接写入数据，写入数据的时候会保持原有样式

📊准备模板

✅代码

@Test
public void templateFill() {
    // 模板路径
    String templateFileName = "/Users/xuchang/Documents/templateFill.xlsx";

    // 生成文件excel路径
    String fileName = "/Users/xuchang/Documents/simpleFill.xlsx";

    // 基础信息
    HashMap<String, Object> map1 = new HashMap<>();
    map1.put("recipient", "张无忌");
    HashMap<String, Object> map2 = new HashMap<>();
    map2.put("addresser", "赵敏");
    HashMap<String, Object> map3 = new HashMap<>();
    map3.put("phone", "13552454101");
    HashMap<String, Object> map4 = new HashMap<>();
    map4.put("date", "2020-01-01");

    // 报价单
    List<QuotationInfo> quotationInfoList = Lists.newArrayList();
    quotationInfoList.add(new QuotationInfo(1,"电饭锅", 100D, 2, 200D, "备注1"));
    quotationInfoList.add(new QuotationInfo(2,"手电筒", 25D, 4, 100D, "备注2"));
    quotationInfoList.add(new QuotationInfo(3,"台灯", 40D, 10, 400D, "备注3"));
    quotationInfoList.add(new QuotationInfo(4,"手机", 3000D, 2, 6000D, "备注4"));
    quotationInfoList.add(new QuotationInfo(5,"电脑", 5000D, 10, 50000D, "备注5"));
    quotationInfoList.add(new QuotationInfo(6,"微波炉", 100D, 2, 200D, "备注6"));

    try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(map1, writeSheet);
        excelWriter.fill(map2, writeSheet);
        excelWriter.fill(map3, writeSheet);
        excelWriter.fill(map4, writeSheet);
        excelWriter.fill(quotationInfoList, writeSheet);
    }
}

@Data
@AllArgsConstructor
public class QuotationInfo {
    private Integer no;
    private String name;
    private Double price;
    private Integer number;
    private Double total;
    private String notes;
}

🌈效果

10、springboot项目中写法

✅代码

@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("测试文件名.xlsx", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    EasyExcel.write(response.getOutputStream(), Employee.class).sheet().doWrite(employeeData());
}

三、读Excel

1、最简单的读

✅代码（自带监听PageReadListener，每次读取100条）

@Test
public void simpleRead() {
    // 使用最简单的写法生成的simpleWrite.xlsx来读取
    String fileName = "/Users/xuchang/Documents/simpleWrite.xlsx";
    // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
    // PageReadListener里面定义了每次返回100行数据
    EasyExcel.read(fileName, Employee.class, new PageReadListener<Employee>(dataList -> {
        for (Employee employee : dataList) {
            System.out.println("读取到一条数据" + JSON.toJSONString(employee));
        }
    })).sheet().doRead();
}

• @ExcelIgnoreUnannotated：没有注解的字段都不转换（全局控制）
• @ExcelIgnore：不将该字段转换成Excel（单个控制）
• 当读取的表头与@ExcelProperty注解的数量和顺序不一致的情况下，如果两个注解都不加，则读取数量错乱

@Data
@ExcelIgnoreUnannotated
public class Employee {
    // @ColumnWidth(30)
    @ExcelProperty(value = "用户编号")
    private Integer id;

    @ExcelProperty(value = "姓名")
    private String name;

    // @ExcelProperty(value = "性别")
    @ExcelIgnore
    private String gender;

    // @ExcelProperty(value = "工资")
    @ExcelIgnore
    private Double salary;

    // @ColumnWidth(40)
    @ExcelProperty(value = "入职时间")
    private Date hireDate;
}

🌈效果

2、自定义监听器

✅代码

@Test
public void customRead() {
    // 使用最简单的写法生成的simpleWrite.xlsx来读取
    String fileName = "/Users/xuchang/Documents/simpleWrite.xlsx";
    // 每次读取500条数据入库
    EasyExcel.read(fileName, DataInfo.class, new DataInfoListener()).sheet().doRead();
}

• 自定义监听器（每次读取500条数据）
• 不能被spring管理，要每次读取excel都要new，需要通过构造函数方式注入xxxService
• 或者也可以使用匿名内部类的方式，也就不需要注入xxxService了

@Slf4j
public class DataInfoListener implements ReadListener<DataInfo> {
    /**
     * 设置多少条数据入库
     */
    private static final int BATCH_COUNT = 500;

    /**
     * 缓存的数据
     */
    private List<DataInfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * DataInfoListener不能被spring管理，要每次读取excel都要new
     * 需要使用构造方法注入方式将dataInfoService注入进来
     */
    private DataInfoService dataInfoService;

    public DemoDataListener(DataInfoService dataInfoService) {
        this.dataInfoService = dataInfoService;
    }

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(DataInfo data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理，实际就是新建一个空集合
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 插入数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        dataInfoService.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}

3、日期、数字类型和自定义格式化

✅代码（实体类注解及自定义转化器与写Excel里面一样，写和读配置一套即可）

@Test
public void converterRead() {
    String fileName = "/Users/xuchang/Documents/converterWrite.xlsx";
    EasyExcel.read(fileName, DataInfo.class, new PageReadListener<DataInfo>(dataList -> {
        for (DataInfo dataInfo : dataList) {
            System.out.println("读取到一条数据" + JSON.toJSONString(dataInfo));
        }
    })).sheet().doRead();
}

// 实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataInfo {
    @ExcelProperty(value = "名称")
    private String name;
	
	// 自定义格式化，java的数字类型，导出excel为字符串类型
    @ExcelProperty(value = "季节", converter = IntegerToStringConverter.class)
    private Integer season;

    @ExcelProperty(value = "日期")
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private Date date;

    // 最后一位默认四舍五入
    @ExcelProperty(value = "百分比")
    @NumberFormat(value = "0.00%")
    private BigDecimal percent;
}

// 自定义格式化转换类
public class IntegerToStringConverter implements Converter<Integer> {

    // 设置Java类型
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    // 设置Excel类型
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }


    // 读操作时候 将 excel 对象转换为 Java 对象
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        switch (cellData.getStringValue()) {
            case "春天":
                return 1;
            case "夏天":
                return 2;
            case "秋天":
                return 3;
            case "冬天":
                return 4;
            default:
                break;
        }
        throw new Exception("未知季节类型");
    }

    // 写操作时 将 Java 对象转换为 excel 对象
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        switch (value) {
            case 1:
                return new WriteCellData<>("春天");
            case 2:
                return new WriteCellData<>("夏天");
            case 3:
                return new WriteCellData<>("秋天");
            case 4:
                return new WriteCellData<>("冬天");
            default:
                break;
        }
        throw new Exception("未知季节类型");
    }
}

🌈效果

4、springboot项目中读法

✅代码

@PostMapping("/upload")
@ResponseBody
public void upload(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), DataInfo.class, new PageReadListener<DataInfo>(list -> {
        for (DataInfo dataInfo : list) {
            System.out.println("读取到一条数据" + JSON.toJSONString(dataInfo));
        }
    })).sheet().doRead();
}