摘要
本文介绍EasyExcel的作用、优点、注解使用、自定义样式和分批次导出，并模拟10万条数据1s级导出的场景。
认识EasyExcel
EasyExcel是一个基于ApachePOI的Java库，阿里巴巴开源，专门用于简化Excel文件的读写操作，支持xls和xlsx。目前处在维护模式，最近更新在8个月前。
优点：
- 优化了大文件处理，将内存使用降低到几MB，避免内存溢出。
- 简洁的API接口，使得读写Excel的操作更为简便，使用者无需深入了解Excel内部结构即可快速上手。
- 支持多线程读写，大数据场景导出效率高。
- SAX（Simple API for XML）流式解析Excel文件，按需逐行读取数据，避免一次性加载整个文件到内存中。
- 自带表格注解映射表头和属性，比如@ExcelIgnore、@ExcelProperty、@DateTimeFormat、@ColumnWidth等。
- 自带样式策略，方便使用。
注意：
若项目已使用Apache POI，需检查依赖冲突（如POI 5+需手动排除poi-ooxml-schemas）
官网：
easyexcel.opensource.alibaba.com/
gitee地址：
Api文档：
easyexcel.opensource.alibaba.com/docs/curren…
使用示例
1）导入依赖pom.xml
<!-- excel工具 easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.3</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
2）设置表头属性映射
package org.coffeebeans.easyexcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Date;
/**
* <li>ClassName: EasyUserInfo </li>
* <li>Author: OakWang </li>
*/
@HeadRowHeight(20)//整体表头行高
@ContentRowHeight(18)//整体表体行高
@ColumnWidth(20)//整体列宽
@Data
public class EasyUserInfo {
@ExcelIgnore//不导出
private String id;
@ExcelProperty(index = 0, value = {"基本信息", "姓名"})//复杂表头写法
private String name;
@ExcelProperty(index = 1, value = {"基本信息", "年龄"})
private Integer age;
@ExcelProperty(index = 2, value = {"基本信息", "性别"})
private String sexStr;
@ExcelProperty(index = 3, value = {"基本信息", "手机"})
private String phone;
@ExcelProperty(index = 4, value = "创建时间")//单表头写法
@DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Date createTime;
@ExcelProperty(index = 5, value = "生日")
@DateTimeFormat(value = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
private Date brithday;
@ExcelProperty(index = 6, value = "小数")
@NumberFormat(value = "#.##", roundingMode = RoundingMode.HALF_UP)// 保留两位小数
private BigDecimal decimalNum;
public EasyUserInfo() {
}
public EasyUserInfo(String name, Integer age, String sexStr, String phone, Date createTime, Date brithday, BigDecimal decimalNum) {
this.name = name;
this.age = age;
this.sexStr = sexStr;
this.phone = phone;
this.createTime = createTime;
this.brithday = brithday;
this.decimalNum = decimalNum;
}
}
3）定义导出逻辑
package org.coffeebeans.easyexcel;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
/**
* <li>ClassName: EasyUserInfo </li>
* <li>Author: OakWang </li>
*/
@Slf4j
@Service
public class EasyExportService {
/**
* 导出excel(适用于小列表导出)
*/
public void exportDataBySimple1(List<EasyUserInfo> userInfos) {
//指定存放文件的路径
String fileName = "D:\用户信息" + System.currentTimeMillis() + ".xlsx";
// 根据用户传入字段 假设我们要忽略dicimalNum列
Set<String> excludeColumnFiledNames = new HashSet<>();
excludeColumnFiledNames.add("decimalNum");
// 执行写入
EasyExcel.write(fileName, EasyUserInfo.class)
.excludeColumnFieldNames(excludeColumnFiledNames) // 忽略字段
.sheet("信息")
.registerWriteHandler(customCellStyleStrategy()) // 自定义单元格样式
.doWrite(userInfos);
}
/**
* 分批次 写到同一个sheet里
*/
public void exportDataByBatchMethod1(List<EasyUserInfo> dataList, HttpServletResponse response) {
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), EasyUserInfo.class)
.registerWriteHandler(customCellStyleStrategy()).build()) {
// 设置响应头
String fileName = "用户信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 定义每一批次处理的数据量
int batchSize = 20000;
// 分批次写入数据
long totalRows = dataList.size();
WriteSheet writeSheet = EasyExcel.writerSheet("信息").build();
for (int beginRowNumber = 0; beginRowNumber < totalRows; beginRowNumber += batchSize) {
int endRowNumber = (int) Math.min(beginRowNumber + batchSize, totalRows);
// 分页写入
List<EasyUserInfo> subList = dataList.subList(beginRowNumber, endRowNumber);
excelWriter.write(subList, writeSheet);
}
} catch (IOException e) {
log.error("导出用户表失败：" + e.getMessage());
response.setStatus(HttpStatus.INTERNAL_SERVER_ERROR.value());
}
}
/**
* 分批次 写到不同的sheet里 同一个对象（区别在于指定实体对象的时机）
*/
public void exportDataByBatchMethod2(List<EasyUserInfo> dataList, HttpServletResponse response) {
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), EasyUserInfo.class)
.registerWriteHandler(customCellStyleStrategy()).build()) {
// 设置响应头
String fileName = "用户信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 定义每一批次处理的数据量
int batchSize = 20000;
// 分批次写入数据
long totalRows = dataList.size();
for (int beginRowNumber = 0; beginRowNumber < totalRows; beginRowNumber += batchSize) {
int endRowNumber = (int) Math.min(beginRowNumber + batchSize, totalRows);
WriteSheet writeSheet = EasyExcel.writerSheet("信息" + endRowNumber).build();
// 分页写入
List<EasyUserInfo> subList = dataList.subList(beginRowNumber, endRowNumber);
excelWriter.write(subList, writeSheet);
}
} catch (IOException e) {
log.error("导出用户表失败：" + e.getMessage());
response.setStatus(HttpStatus.INTERNAL_SERVER_ERROR.value());
}
}
/**
* 分批次 写到不同的sheet里 不同的对象（区别在于指定实体对象的时机）
*/
public void exportDataByBatchMethod3(List<EasyUserInfo> dataList, HttpServletResponse response) {
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.registerWriteHandler(customCellStyleStrategy()).build()) {
// 设置响应头
String fileName = "用户信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 定义每一批次处理的数据量
int batchSize = 20000;
// 分批次写入数据
long totalRows = dataList.size();
for (int beginRowNumber = 0; beginRowNumber < totalRows; beginRowNumber += batchSize) {
int endRowNumber = (int) Math.min(beginRowNumber + batchSize, totalRows);
WriteSheet writeSheet = EasyExcel.writerSheet("信息" + endRowNumber).head(EasyUserInfo.class).build();
// 分页写入
List<EasyUserInfo> subList = dataList.subList(beginRowNumber, endRowNumber);
excelWriter.write(subList, writeSheet);
}
} catch (IOException e) {
log.error("导出用户表失败：" + e.getMessage());
response.setStatus(HttpStatus.INTERNAL_SERVER_ERROR.value());
}
}
/**
* 自定义单元格样式 （除了注解 还能这样单独定义）
*/
public HorizontalCellStyleStrategy customCellStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//todo 定义其他样式
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//todo 定义其他样式
// 内容居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
4）开放api导出
package org.coffeebeans.easyexcel;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/easyexcel")
@Slf4j
public class EasyController {
@Autowired
private EasyExportService easyExportService;
@GetMapping("/exportDataBySimple1")
public void exportDataBySimple1() {
long startTime = System.currentTimeMillis();
easyExportService.exportDataBySimple1(userInfos());
long endTime = System.currentTimeMillis();
log.info("===========exportDataBySimple1 导出耗时：" + (double) (endTime - startTime) / 1000 + " 秒=============");
}
@GetMapping("/exportDataByBatchMethod1")
public void exportDataByBatchMethod1(HttpServletResponse response) {
long startTime = System.currentTimeMillis();
easyExportService.exportDataByBatchMethod1(userInfos(), response);
long endTime = System.currentTimeMillis();
log.info("===========exportDataByBatchMethod1 导出耗时：" + (double) (endTime - startTime) / 1000 + " 秒=============");
}
@GetMapping("/exportDataByBatchMethod2")
public void exportDataByBatchMethod2(HttpServletResponse response) {
long startTime = System.currentTimeMillis();
easyExportService.exportDataByBatchMethod2(userInfos(), response);
long endTime = System.currentTimeMillis();
log.info("===========exportDataByBatchMethod2 导出耗时：" + (double) (endTime - startTime) / 1000 + " 秒=============");
}
@GetMapping("/exportDataByBatchMethod3")
public void exportDataByBatchMethod3(HttpServletResponse response) {
long startTime = System.currentTimeMillis();
easyExportService.exportDataByBatchMethod3(userInfos(), response);
long endTime = System.currentTimeMillis();
log.info("===========exportDataByBatchMethod3 导出耗时：" + (double) (endTime - startTime) / 1000 + " 秒=============");
}
//临时模拟数据用
private List<EasyUserInfo> userInfos() {
long startTime = System.currentTimeMillis();
List<EasyUserInfo> userInfos = new ArrayList<>();
DateTime dateTime = DateUtil.parseDateTime("2020-01-01 12:34:56");
BigDecimal number = new BigDecimal("123.456");
for (int i = 0; i < 100000; i++) {
EasyUserInfo userInfo = new EasyUserInfo("张三" + i, i, "男", "13888888888", dateTime, dateTime, number);
userInfos.add(userInfo);
}
long endTime = System.currentTimeMillis();
log.info("===========数据产生耗时：" + (double) (endTime - startTime) / 1000 + " 秒=============");
return userInfos;
}
}
5）测试导出10w条数据
简单导出
分批次 写到同一个sheet里
分批次 写到不同的sheet里 同一个对象
分批次 写到不同的sheet里 不同的对象
总结
以上我们了解了EasyExcel的作用、优点、注解使用、自定义样式和分批次导出，并模拟了10万条数据1s级导出效果。无论是api封装易用性和导出内存占用率都比ApachePOI好，大数据环境下导出更推荐使用EasyExcel。但对于百万级数据的导出，效率上还可以考虑其他方案再优化一下。
关注公众号：咖啡Beans
在这里，我们专注于软件技术的交流与成长，分享开发心得与笔记，涵盖编程、AI、资讯、面试等多个领域。无论是前沿科技的探索，还是实用技巧的总结，我们都致力于为大家呈现有价值的内容。期待与你共同进步，开启技术之旅。
