官网地址: EasyExcel
本文代码: EasyExcelDemo
在开发中Excel的导入导出是非常常见的需求,最近在项目中用到了阿里的EasyExcel,简单学习下,还是比较容易上手的。下面记录下EasyExcel的基本使用
EasyExcel介绍
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.2</version>
</dependency>导出
例如现在要导出如下Excel:
使用模型导出
使用模型导出时,EasyExcel会根据导出实体中 @ExcelProperty注解生成表头
- 建立模型
@Data
@NoArgsConstructor
@AllArgsConstructor
@ColumnWidth(15) // 设置列宽
public class ExcelDemoDto {
@ExcelProperty("字符串测试1")
private String column1;
@ExcelProperty("字符串测试2")
private String column2;
@ExcelProperty("数字测试")
private BigDecimal number;
@ExcelProperty("日期测试")
@DateTimeFormat(value = "yyyy-MM-dd")
private Date date;
}- 导出实现
public void easyWrite(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode防止中文乱码
String fileName = URLEncoder.encode("导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
// 获取导出数据
List<ExcelDemoDto> exportData = new ArrayList<>();
ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date());
exportData.add(excelDemoDto);
// 初始化ExcelWriter
ExcelWriter writer = EasyExcel.write(outputStream).build();
// 初始化一个sheet
WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1")
.head(ExcelDemoDto.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build();// EasyExcel自带的自动列宽拦截器
// 写入数据 生成Excel文件
try {
writer.write(exportData, sheet1);
} catch (Exception e) {
log.info("导出失败: {}",e.getCause());
throw e;
} finally {
writer.finish();
}
}不使用用模型导出
如果不使用模型,可以手动设置表头
public void easyWriteNoModel(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode防止中文乱码
String fileName = URLEncoder.encode("导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
// 获取导出数据
List<ExcelDemoDto> exportData = new ArrayList<>();
ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date());
exportData.add(excelDemoDto);
// head
List<List<String>> head = new ArrayList<>(); // 每一列都需要设置一个List
List<String> headColumn1 = Arrays.asList("字符串测试1");
List<String> headColumn2 = Arrays.asList("字符串测试2");
List<String> headColumn3 = Arrays.asList("数字测试");
List<String> headColumn4 = Arrays.asList("日期测试");
head.add(headColumn1);
head.add(headColumn2);
head.add(headColumn3);
head.add(headColumn4);
// 初始化ExcelWriter
ExcelWriter writer = EasyExcel.write(outputStream).build();
// 初始化一个sheet
WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1")
.head(head)
.build();
// 写入数据 生成Excel文件
try {
writer.write(exportData, sheet1);
} catch (Exception e) {
log.info("导出失败: {}",e.getCause());
throw e;
} finally {
writer.finish();
}
}导入
监听器实现
/**
* 自定义监听器实现
* @param <E>
*/
@Data
@EqualsAndHashCode(callSuper = true)
public class AnalysisEventListenerImpl<E> extends AnalysisEventListener<E> {
private List<E> datas = new ArrayList<E>();
@Override
public void invoke(E data, AnalysisContext context) {
datas.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}读取工具类 EasyExcelUtil
public static <T> List<T> readExcelByModel(MultipartFile file,Class<T> clazz,int sheetNo,int headRowNumber) throws IOException {
List<T> list = new ArrayList<>();
InputStream inputStream = null;
try {
// 获取输入流
inputStream = file.getInputStream();
// 数据收集器
AnalysisEventListenerImpl<T> listener = new AnalysisEventListenerImpl<>();
ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
// 第一个sheet读取类型
ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(clazz).headRowNumber(headRowNumber).build();
// 开始读取第一个sheet
excelReader.read(readSheet);
list = listener.getDatas();
} catch (IOException e) {
// throw new RuntimeException("excel解析出错");
log.info("解析出错: " + e.getCause());
} finally {
// 记得关流
if (inputStream != null) {
inputStream.close();
}
}
return list;
}导入实现
public List<ExcelDemoDto> easyRead(MultipartFile file) throws Exception {
try {
List<ExcelDemoDto> list = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class);
return list;
} catch (IOException e) {
throw new Exception("Excel解析出错");
}
}多sheet导出
public void multiSheetWrite(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode防止中文乱码
String fileName = URLEncoder.encode("导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
// 获取导出数据
List<ExcelDemoDto> exportData1 = new ArrayList<>();
ExcelDemoDto excelDemoDto = new ExcelDemoDto("zjm","520", BigDecimal.valueOf(20),new Date());
exportData1.add(excelDemoDto);
// 初始化ExcelWriter
ExcelWriter writer = EasyExcel.write(outputStream).build();
// 初始化一个sheet1
WriteSheet sheet1 = EasyExcel.writerSheet(0, "sheet1")
.head(ExcelDemoDto.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build();// EasyExcel自带的自动列宽拦截器
WriteSheet sheet2 = EasyExcel.writerSheet(1, "sheet2")
.head(ExcelDemoDto.class)
.build();
// 写入数据 生成Excel文件
try {
writer.write(exportData1, sheet1);
writer.write(exportData1, sheet2);
} catch (Exception e) {
log.info("导出失败: {}",e.getCause());
throw e;
} finally {
writer.finish();
}
}多sheet导入
public Map<Integer, List<ExcelDemoDto>> multiSheetRead(MultipartFile file) throws IOException {
List<ExcelDemoDto> list1 = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class, 0, 1);
List<ExcelDemoDto> list2 = EasyExcelUtil.readExcelByModel(file, ExcelDemoDto.class, 1, 1);
Map<Integer,List<ExcelDemoDto>> map = new HashMap<>();
map.put(0,list1);
map.put(1,list2);
return map;
}复杂表头导出
在实际业务中,往往导入导出的Excel表头十分复杂,例如下面这样的(数据已做脱敏处理)
使用模型设置
@Data
public class ExcelMultiHeadDemoDto {
@ExcelProperty({"序号","序号","序号","序号"})
private String no;
@ExcelProperty({"字段1","字段1","字段1","字段1"})
private String column1;
@ExcelProperty({"大标题1","大标题1-1","大标题1-1","大标题1-1"})
private String column2;
@ExcelProperty({"大标题1","大标题1-2","大标题1-2","大标题1-2"})
private String column3;
@ExcelProperty({"大标题1","大标题1-3","大标题1-3","大标题1-4"})
private BigDecimal column4;
}效果如下
复杂表头导入
EasyExcel在解析数据时,默认表头为1,如多表头是多行,需要设置 rowHeadNumber参数,若不设置,EasyExcel会将表头也解析,在使用模型读取时极有可能出现类型转换错误
public List<ExcelMultiHeadDemoDto> multiHeadRead(MultipartFile file) {
try {
List<ExcelMultiHeadDemoDto> list = EasyExcelUtil.readExcelByModel(file, ExcelMultiHeadDemoDto.class,0,4);
return list;
} catch (IOException e) {
log.info("Excel解析出错");
}
return null;
}