Easy Excel 使用总结
1. 概述
官网地址：EasyExcel 官方文档 - 基于 Java 的 Excel 处理工具 | Easy Excel (alibaba.com)
EasyExcel 是一个基于 Java 的、快速、简洁、解决大文件内存溢出的 Excel 处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成 Excel 的读、写等功能
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>最新版本</version>
</dependency>
2. 常用注解
2.2.1 @ExcelProperty
用于匹配 Excel 和实体类的匹配，参数如下：
| 名称 | 默认值 | 描述 |
|---|---|---|
| value | 空 | 用于匹配 Excel 中的头，必须全匹配，如果有多行头，会匹配最后一行头 |
| order | Integer.MAX_VALUE | 优先级高于 value，会根据 order 的顺序来匹配实体和 excel 中数据的顺序 |
| index | -1 | 优先级高于 value 和 order，会根据 index 直接指定到 excel 中具体的哪一列 |
| converter | 自动选择 | 指定当前字段用什么转换器，默认会自动选择。读的情况下只要实现 com.alibaba.excel.converters.Converter#convertToJavaData(com.alibaba.excel.converters.ReadConverterContext<?>) 方法即可 |
2.2.2 @ExcelIgnore
默认所有字段都会和 Excel 去匹配，加了这个注解会忽略该字段
2.2.3 @ExcelIgnoreUnannotated
默认不管加不加 ExcelProperty 的注解的所有字段都会参与读写，加了 ExcelIgnoreUnannotated 注解以后，不加 ExcelProperty 注解的字段就不会参与
2.2.4 @DateTimeFormat
日期转换，用 String 去接收 Excel 日期格式的数据会调用这个注解，参数如下：
| 名称 | 默认值 | 描述 |
|---|---|---|
| value | 空 | 参照 java.text.SimpleDateFormat 书写即可 |
| use1904windowing | 自动选择 | Excel 中时间是存储 1900 年起的一个双精度浮点数，但是有时候默认开始日期是 1904，所以设置这个值改成默认 1904 年开始 |
2.2.5 @NumberFormat
数字转换，用 String 去接收 Excel 数字格式的数据会调用这个注解
| 名称 | 默认值 | 描述 |
|---|---|---|
| value | 空 | 参照 java.text.DecimalFormat 书写即可 |
| roundingMode | RoundingMode.HALF_UP | 格式化的时候设置舍入模式 |
3. 写 Excel（导出）
3.1 常用参数
WriteWorkbook：可以理解成一个 ExcelWriteSheet：理解成一个 Excel 里面的一个表单WriteTable：一个表单里面如果有多个实际用的表格，则可以用WriteTable
3.1.1 通用参数
WriteWorkbook，WriteSheet ，WriteTable 都会有的参数，如果为空，默认使用上级
| 名称 | 默认值 | 描述 |
|---|---|---|
| converter | 空 | 默认加载了很多转换器，这里可以加入不支持的字段 |
| writeHandler | 空 | 写的处理器。可以实现 WorkbookWriteHandler，SheetWriteHandler，RowWriteHandler，CellWriteHandler，在写入 Excel 的不同阶段会调用 |
| relativeHeadRowIndex | 0 | 写入到 Excel 和上面空开几行 |
| head | 空 | 与 clazz 二选一。读取文件头对应的列表，会根据列表匹配数据，建议使用 class |
| clazz | 空 | 与 head 二选一。读取文件的头对应的 class，也可以使用注解。如果两个都不指定，则会读取全部数据 |
| autoTrim | true | 会对头、读取数据等进行自动 trim |
| use1904windowing | false | Excel 中时间是存储 1900 年起的一个双精度浮点数，但是有时候默认开始日期是 1904，所以设置这个值改成默认 1904 年开始 |
| useScientificFormat | false | 数字转文本的时候在较大的数值的是否是否采用科学计数法 |
| needHead | true | 是否需要写入头到 Excel |
| useDefaultStyle | true | 是否使用默认的样式 |
| automaticMergeHead | true | 自动合并头，头中相同的字段上下左右都会去尝试匹配 |
| excludeColumnIndexes | 空 | 需要排除对象中的 index 的数据 |
| excludeColumnFieldNames | 空 | 需要排除对象中的字段的数据 |
| includeColumnIndexes | 空 | 只要导出对象中的 index 的数据 |
| includeColumnFieldNames | 空 | 只要导出对象中的字段的数据 |
3.1.2 WriteWorkbook
EasyExcel.write(fileName, DemoData.class)
// 在 write 方法之后， 在 sheet 方法之前都是设置 WriteWorkbook 的参数
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return data();
});
| 名称 | 默认值 | 描述 |
|---|---|---|
| excelType | 空 | 当前 Excel 的类型,支持 XLS、XLSX、CSV |
| outputStream | 空 | 与 file 二选一。写入文件的流 |
| file | 空 | 与 outputStream 二选一。写入的文件 |
| templateInputStream | 空 | 模板的文件流 |
| templateFile | 空 | 模板文件 |
| charset | Charset#defaultCharset | 只有 csv 文件有用，写入文件的时候使用的编码 |
| autoCloseStream | true | 自动关闭写入的流 |
| password | 空 | 读取文件的密码 |
| inMemory | false | 是否在内存处理，默认会生成临时文件以节约内存。内存模式效率会更好，但是容易 OOM |
| writeExcelOnException | false | 写入过程中抛出异常了，是否尝试把数据写入到 Excel |
3.1.3 WriteSheet
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
// 在 sheet 方法之后，在 doWrite 方法之前都是设置 WriteSheet 的参数
.doWrite(() -> {
// 分页查询数据
return data();
});
| 名称 | 默认值 | 描述 |
|---|---|---|
| sheetNo | 0 | 需要写入的编码 |
| sheetName | 空 | 需要些的 Sheet 名称，默认同 sheetNo |
3.1.4 WriteTable
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.table()
// 在 table 方法之后， 在 doWrite 方法之前都是设置 WriteTable 的参数
.doWrite(() -> {
// 分页查询数据
return data();
});
| 名称 | 默认值 | 描述 |
|---|---|---|
| tableNo | 0 | 需要写入的编码 |
3.2 简单的写
省略其他获取数据和定义接口等实现
3.2.1 定义表格实体类
public class ExcelDO {
@ExcelProperty("员工ID")
private String empId;
@ExcelProperty("员工姓名")
private String empName;
@ExcelProperty("员工工号")
private String empCode;
@ExcelProperty("身份证号")
private String idcardNo;
@ExcelProperty("性别")
private String gender;
@ExcelProperty("电话号码")
private String phone;
@ExcelProperty("地址")
private String address;
@ExcelProperty("邮箱")
private String email;
@ExcelProperty("备注")
private String remark;
@ExcelProperty("有效标志")
private String valiFlag;
@ExcelProperty("创建时间")
private Timestamp createTime;
@ExcelIgnore
private Timestamp updateTime;
}
3.2.2 实现类
@Service
public class ExcelServiceImpl implements ExcelService {
@Resource
private EmployeeService employeeService;
@Override
public void simpleWrite() {
String fileName = "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 写法1
EasyExcel.write(fileName, ExcelDO.class)
.sheet("模板")
.doWrite(() -> getData());
// 写法2
EasyExcel.write(fileName, ExcelDO.class).sheet("模板").doWrite(getData());
// 写法3
try (ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelDO.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(getData(), writeSheet);
}
}
private List<ExcelDO> getData() {
List<ExcelDO> list = ListUtils.newArrayList();
List records = employeeService.getEmployee().getRecords();
list.addAll(records);
return list;
}
}
3.2.3 Controller
@RestController
public class ExcelController {
@Resource
private ExcelService excelService;
@PostMapping("/simpleWrite")
public void simpleWrite() {
excelService.simpleWrite();
}
}
3.2.4 定义特殊格式转换 Convert
当使用 LocalDateTime 或 Timestamp 等格式时，会报 ExcelWriteDataConvertException，此时需要自定义类型转换器
TimestampConvert
public class TimestampConvert implements Converter<Timestamp> {
// 在Java中数据类型
@Override
public Class<Timestamp> supportJavaTypeKey() {
return Timestamp.class;
}
// 在Excel中的数据类型
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
// 将Excel的数据类型转为Java数据类型
@Override
public Timestamp convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
return Timestamp.valueOf(cellData.getStringValue());
}
// 将Java的数据类型转为Excel数据类型
@Override
public WriteCellData<?> convertToExcelData(Timestamp value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<>(value.toString());
}
}
3.2.5 Convert 使用
1、找到需要转换的字段，在 @ExcelProperty 上添加 converter 属性`
2、ExcelWriterBuilders 是支持对单次的操作添加 converter 的，那样就不需要为每个需要转换的字段单独添加 converter 了
3、全局 Convert
即将自定义的 Convert 添加到 EasyExcel 的 Convert Map 转换器 Map 里（Map<ConverterKey, Converter<?>> converterMap()）
详细可见：EasyExcel 自定义 Converter 全局加载器以及加载 Converter
4、第二种方法还能这样写，在单次操作中加入转换器 Map 里
public void simpleWrite() {
String fileName = "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
// EasyExcel.write(fileName, ExcelDO.class)
// .registerConverter(new TimestampConvert())
// .sheet("模板")
// .doWrite(() -> getData());
ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelDO.class).build();
TimestampConvert timestampConvert = new TimestampConvert();
excelWriter.writeContext().currentWriteHolder().converterMap()
.put(ConverterKeyBuild.buildKey(timestampConvert.supportJavaTypeKey()), timestampConvert);
excelWriter.writeContext().currentWriteHolder().converterMap()
.put(ConverterKeyBuild.buildKey(timestampConvert.supportJavaTypeKey(), timestampConvert.supportExcelTypeKey()), timestampConvert);
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(getData(), writeSheet);
}
3.2.6 定义内容转换 Convert
导出后，发现性别和有效标志等字段，显示的是数据库中存储的数字，应该将其转换为对应的含义
GenderConverter
public class GenderConverter implements Converter<String> {
@Override
public Class<String> supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return "男".equals(cellData.getStringValue()) ? "1" : "0";
}
@Override
public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<>(value.equals("1") ? "男" : "女");
}
}
使用 Convert 的第一种使用方法
再导出后，性别已经由数字转换为对应的含义
3.3 Web 中的写（下载）
与简单的写类似
@RestController
public class ExcelController {
@Resource
private ExcelService excelService;
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
// 这里 URLEncoder.encode可以防止中文乱码，和 EasyExcel 没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ExcelDO.class).sheet("模板").doWrite(excelService.getData());
}
}
浏览器地址栏输入接口地址，会弹出文件下载
下载失败的时候返回 JSON
@RestController
public class ExcelController {
@Resource
private ExcelService excelService;
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
try {
// 正确设置 response 的 content-type；即设置正确的 mime type
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
// 这里 URLEncoder.encode可以防止中文乱码 当然和 EasyExcel 没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// int i = 1 / 0;
EasyExcel.write(response.getOutputStream(), ExcelDO.class).sheet("模板").doWrite(excelService.getData());
} catch (Exception e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
response.getWriter().println(JSON.toJSONString(Result.fail("下载文件失败")));
}
}
}
4. 读 Excel（导入）
4.1 常用参数
ReadWorkbook可以理解成一个 excelReadSheet理解成一个 excel 里面的一个表单
4.1.1 通用参数
ReadWorkbook,ReadSheet 都会有的参数，如果为空，默认使用上级
| 名称 | 默认值 | 描述 |
|---|---|---|
| converter | 空 | 默认加载了很多转换器，这里可以加入不支持的字段 |
| readListener | 空 | 可以注册多个监听器，读取 Excel 的时候会不断的回调监听器中的方法 |
| headRowNumber | 1 | excel 中头的行数，默认 1 行 |
| head | 空 | 与 clazz 二选一。读取文件头对应的列表，会根据列表匹配数据，建议使用 class |
| clazz | 空 | 与 head 二选一。读取文件的头对应的 class，也可以使用注解。如果两个都不指定，则会读取全部数据 |
| autoTrim | true | 会对头、读取数据等进行自动 trim |
| use1904windowing | false | Excel 中时间是存储 1900 年起的一个双精度浮点数，但是有时候默认开始日期是 1904，所以设置这个值改成默认 1904 年开始 |
| useScientificFormat | false | 数字转文本的时候在较大的数值的是否是否采用科学计数法 |
4.1.2 ReadWorkbook
EasyExcel.read(fileName, DemoData.class, new DemoDataListener())
// 在 read 方法之后， 在 sheet方法之前都是设置ReadWorkbook的参数
.sheet()
.doRead();
| 名称 | 默认值 | 描述 |
|---|---|---|
| excelType | 空 | 当前 excel 的类型,支持 XLS、XLSX、CSV |
| inputStream | 空 | 与 file 二选一。读取文件的流，如果接收到的是流就只用，不用流建议使用 file 参数。因为使用了 inputStream easyexcel 会帮忙创建临时文件，最终还是 file |
| file | 空 | 与 inputStream 二选一。读取文件的文件。 |
| mandatoryUseInputStream | false | 强制使用 inputStream 来创建对象，性能会变差，但是不会创建临文件。 |
| charset | Charset#defaultCharset | 只有 csv 文件有用，读取文件的时候使用的编码 |
| autoCloseStream | true | 自动关闭读取的流。 |
| readCache | 空 | 默认小于 5M 用 内存，超过 5M 会使用 EhCache,这里不建议使用这个参数。 |
| readCacheSelector | SimpleReadCacheSelector | 用于选择什么时候用内存去存储临时数据，什么时候用磁盘存储临时数据 |
| ignoreEmptyRow | true | 忽略空的行 |
| password | 空 | 读取文件的密码 |
| xlsxSAXParserFactoryName | 空 | 指定 sax 读取使用的 class 的名称，例如：com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl |
| useDefaultListener | true | @since 2.1.4 默认会加入 ModelBuildEventListener 来帮忙转换成传入 class 的对象，设置成 false 后将不会协助转换对象，自定义的监听器会接收到 Map<Integer,CellData> 对象，如果还想继续接听到 class 对象，请调用 readListener 方法，加入自定义的 beforeListener、 ModelBuildEventListener、 自定义的 afterListener 即可。 |
| extraReadSet | 空 | 额外需要读取内容的 set，默认不读取这些数据 |
4.1.3 ReadSheet
EasyExcel.read(fileName, DemoData.class, new DemoDataListener())
.sheet()
// 在 sheet 方法之后， 在 doRead方法之前都是设置ReadSheet的参数
.doRead();
| 名称 | 默认值 | 描述 |
|---|---|---|
| sheetNo | 0 | 需要读取 Sheet 的编码，建议使用这个来指定读取哪个 Sheet |
| sheetName | 空 | 根据名字去匹配 Sheet |
4.2 简单的读
读取的表格就使用上面导出的表格
4.2.1 表格实体类和 Convert
表格实体类和 Convert 就使用写 Excel 的实体类和 Convert
4.2.2 实现类
@Service
public class ExcelServiceImpl implements ExcelService {
@Resource
private EmployeeDAO employeeDAO;
public void simpleRead() {
String fileName = "D:\\simpleWrite1665735316967.xlsx";
// 写法1：不用额外写一个 ExcelListener
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
// 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
EasyExcel.read(fileName, ExcelDO.class, new PageReadListener<ExcelDO>(excelDOS -> {
for (ExcelDO excelDO : excelDOS) {
System.out.println(excelDO);
}
})).sheet().doRead();
// 写法2：匿名内部类 不用额外写一个 ExcelListener
EasyExcel.read(fileName, ExcelDO.class, new ReadListener<ExcelDO>() {
// 单次缓存的数据量
public static final int BATCH_COUNT = 100;
// 临时存储
private List<ExcelDO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(ExcelDO data, AnalysisContext context) {
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
// 所有数据解析完成了 都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
}
// 存储到数据库，批量插入
private void saveData() {
for (ExcelDO excelDO : cachedDataList) {
System.out.println(excelDO);
}
}
}).sheet().doRead();
// 写法3：有个很重要的点 ExcelListener 不能被Spring管理，要每次读取Excel都要new,然后里面用到Spring可以构造方法传进去
EasyExcel.read(fileName, ExcelDO.class, new ExcelListener(employeeDAO)).sheet().doRead();
// 写法4：一个文件一个reader
try (ExcelReader excelReader = EasyExcel.read(fileName, ExcelDO.class, new ExcelListener(employeeDAO)).build()) {
// 构建一个sheet 这里可以指定名字或者no
ReadSheet readSheet = EasyExcel.readSheet(0).build();
// 读取一个sheet
excelReader.read(readSheet);
}
}
}
4.2.3 监听器
即把第二种写法的匿名内部类单独抽出来，监听器不能被 Spring 管理，每次读取 Excel 都要 new，然后里面用到 Spring 可以构造方法传进去
public class ExcelListener implements ReadListener<ExcelDO> {
// 单次缓存的数据量
public static final int BATCH_COUNT = 100;
// 临时存储
private List<ExcelDO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private EmployeeDAO employeeDAO;
/**
* 如果使用了Spring，使用这个构造方法。每次创建Listener的时候需要把Spring管理的类传进来
*
* @param employeeDAO
*/
public ExcelListener(EmployeeDAO employeeDAO) {
this.employeeDAO = employeeDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(ExcelDO data, AnalysisContext context) {
cachedDataList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
}
// 存储到数据库，批量存储
private void saveData() {
// employeeDAO.save(cachedDataList);
for (ExcelDO excelDO : cachedDataList) {
System.out.println(excelDO);
}
}
}
4.2.4 Controller
@RestController
public class ExcelController {
@Resource
private ExcelService excelService;
@PostMapping("/simpleRead")
public void simpleRead() {
excelService.simpleRead();
}
}
4.3 Web 中的读（上传）
与简单的读类似
@RestController
public class ExcelController {
@Resource
private EmployeeDAO employeeDAO;
@PostMapping("upload")
public Result upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), ExcelDO.class, new ExcelListener(employeeDAO)).sheet().doRead();
return Result.success("上传成功");
}
}
更多读写相关操作详见官方文档：关于 Easyexcel | Easy Excel (alibaba.com)
