EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。
官方网站:https://easyexcel.opensource.alibaba.com/
Gitee地址:https://gitee.com/easyexcel/easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
一、创建一个监听器
@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {
// 可以通过实例获取该值
private List<T> data = new ArrayList<>();
/**
* 读取excel内容
* 从excel中的第二行开始读取,把每行数据数据都读出来然后封装到T对象中
* 每解析一行数据就会调用一次该方法
*
* @param t
* @param analysisContext
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
// 数据存储到list,供批量处理,或后续自己业务逻辑处理。
data.add(t);
}
/**
* 所有操作都完成之后才会执行本方法
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// excel解析完毕以后需要执行的代码
log.info("This is doAfterAllAnalysed!!!!");
}
public List<T> getData() {
return data;
}
}
二、EasyExcel的读与写
public class EasyExcelTest {
@Test
public void excelReadTest() {
read();
}
@Test
public void excelWriteTest() {
write();
}
// Excel读操作
public static void read() {
// 定义读取Excel文件位置
String fileAddr = "D:\\easyExcel.xlsx";
// 创建一个监听器对象
ExcelListener<User> excelListener = new ExcelListener();
// 解析excel表格
EasyExcel.read(fileAddr, User.class, excelListener)
.sheet().doRead();
// 获取解析到的数据
List<User> data = excelListener.getData();
// 进行遍历操作
//data.forEach(System.out::println);
data.forEach(item -> {
System.out.println(item);
});
}
// Excel写操作
public static void write() {
String fileAddr = "D:\\easyExcel.xlsx";
List<User> data = new ArrayList<>();
data.add(new User(1000L , "小明" , "男"));
data.add(new User(1001L , "小红" , "女"));
data.add(new User(1002L , "李华" , "男"));
EasyExcel.write(fileAddr, User.class).sheet("!111").doWrite(data);
}
}
三、业务中使用EasyExcel
3.1 创建监听器
/**
* 读的监听器
* <p>
* 注意:ExcelListener不能被spring管理,要每次读取excel都要new,
* 然后里面用到spring可以构造方法传进去
*
* @param <T>
*/
@Slf4j
public class ExcelListener<T> implements ReadListener<T> {
/**
* 每隔100条存储数据库,然后清理list,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
// 获取mapper对象
private CategoryMapper categoryMapper;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param categoryMapper
*/
public ExcelListener(CategoryMapper categoryMapper) {
this.categoryMapper = categoryMapper;
}
/**
* 每解析一行数据就会调用一次该方法
*
* @param t 从第二行开始读取,把每行读取内容封装到t对象中
* @param analysisContext
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
CategoryExcelVo data = (CategoryExcelVo) t;
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* excel解析完毕以后才会执行的方法
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
categoryMapper.batchInsert(cachedDataList);
log.info("存储数据库成功!");
}
}
3.2 在实体类上添加注解
@ExcelProperty注解的value属性值匹配Excel中的表头,为全匹配。如果有多行头,只会匹配最后一行表头。
@ExcelProperty注解的index属性值指定到Excel中具体的哪一列。
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CategoryExcelVo implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "id", index = 0)
private Long id;
@ExcelProperty(value = "名称", index = 1)
private String name;
@ExcelProperty(value = "图片url", index = 2)
private String imageUrl;
@ExcelProperty(value = {"负责部门/人", "部门KPI(分)"}, index = 3)
private String dapartKpi;
@ExcelProperty(value = {"负责部门/人", "责任人(元)"}, index = 4)
private String staffKpiFee;
@ExcelIgnore
@ExcelProperty(value = "上级id", index = 5)
private Long parentId;
@ExcelIgnore
@ExcelProperty(value = "状态", index = 6)
private Integer status;
@ExcelIgnore
@ExcelProperty(value = "排序", index = 7)
private Integer orderNum;
}
3.3 写(导出)
/**
* 导出分类数据
*
* @param response
*/
@Override
public void exportData(HttpServletResponse response) {
try {
// 设置响应信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// URLEncoder.encode可以防止中文乱码,和easyexcel没有关系
String fileName = URLEncoder.encode("数据分类", "UTF-8");
// 设置响应头信息
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 查询所有分类
List<Category> categoryList = categoryMapper.selectAllCategory();
ArrayList<CategoryExcelVo> categoryExcelVoList = new ArrayList<>(categoryList.size());
for (Category category : categoryList) {
CategoryExcelVo categoryExcelVo = new CategoryExcelVo();
BeanUtils.copyProperties(category, categoryExcelVo);
categoryExcelVoList.add(categoryExcelVo);
}
// 调用EasyExcel的write方法完成写操作
EasyExcel.write(response.getOutputStream(), CategoryExcelVo.class)
.excelType(ExcelTypeEnum.XLSX) // 当前excel的类型,支持XLS、XLSX、CSV。默认值为空
.inMemory(true) // 是否在内存处理,默认会生成临时文件以节约内存。内存模式效率会更好,但是容易OOM。默认值为false
.sheet("数据")
.doWrite(categoryExcelVoList);
} catch (Exception e) {
e.printStackTrace();
throw new MyselfException(ResultCodeEnum.SYSTEM_ERROR);
}
}
3.4 读(导入)
/**
* 导入分类数据
*
* @param file
*/
@Override
public void importData(MultipartFile file) {
try {
ExcelListener<CategoryExcelVo> excelListener = new ExcelListener<>(categoryMapper);
EasyExcel.read(file.getInputStream(), CategoryExcelVo.class, excelListener).sheet().doRead();
} catch (IOException e) {
log.error("Excel文件导入失败:{}", e.getMessage());
e.printStackTrace();
throw new MyselfException(ResultCodeEnum.SYSTEM_ERROR);
}
}
3.5 下载
@ApiOperation("下载模板")
@GetMapping("/downloadTemplate")
public Result downloadTemplate(HttpServletResponse response) {
DefaultResourceLoader loader = new DefaultResourceLoader();
org.springframework.core.io.Resource resource = loader.getResource("classpath:static/templates/模板下载.xlsx");
try {
InputStream inputStream = resource.getInputStream();
StreamUtils.copy(inputStream, response.getOutputStream());
getResponse("模板.xlsx", response);
} catch (IOException e) {
log.error("模板下载失败:{}", e.getMessage());
e.printStackTrace();
}
return Result.success();
}
四、注解
@ExcelProperty
匹配Excel表头和实体类属性。
- value:用于匹配Excel中的表头,必须全匹配。如果有多行头,只会匹配最后一行表头
- order:优先级高于value,会根据order的顺序来匹配实体和Excel中数据的顺序
- index:优先级高于value和order,会根据index直接指定到Excel中具体的哪一列
- converter:指定当前字段用什么转换器,默认会自动选择。
@ExcelIgnore
默认情况下,所有的字段都会和Excel表头去匹配,加了这个注解会忽略该字段,不会去与Excel表头匹配,输出的Excel中也不会出现这个字段对应的表头。
@ExcelIgnoreUnannotated
默认不管加不加ExcelProperty的注解的所有字段都会参与读写,加了ExcelIgnoreUnannotated注解以后,不加ExcelProperty注解的字段就不会参与读写。
@DateTimeFormat
该注解的作用是做日期转换,用String类型的属性接收Excel数字格式的数据才会调用这个注解。
- value:类似SimpleDateFormat写法
- use1904windowing:使用这个属性可以将时间存储改成1904年开始
@NumberFormat
该注解的作用是做数字转换,用String类型的属性接收Excel数字格式的数据才会调用这个注解。
- value:类似DecimalFormat写法
- roundingMode:格式化的时候设置舍入模式。默认值为RoundingMode.HALF_UP