EasyExcel简介
-
Java领域解析,生成Excel比较有名的框架有Apache poi,jxl等,但他们都存在一个严重的问题就是非常的耗内存,如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc.
-
EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单,节省内存著称,EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
-
EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
导入EXCEL(读取)
监听器(关键)
public class ExcelListener extends AnalysisEventListener<Object(excel对应的实体类)> {
//模拟excel存储数据
@Getter
private List<Object> excels = Lists.newArrayList();
//处理excel每一行数据
@Override
public void invoke(Object data, AnalysisContext analysisContext) {
Integer rowIndex = analysisContext.readRowHolder().getRowIndex();
try {
ValidationUtils.validate(data);
} catch (InvalidFormatException e) {
log.error("格式校验出错, data={}, row={}, errMessage={}", JSONObject.toJSONString(data), rowIndex + 1, e.getMessage());
throw new GenericBusinessException(String.format("第%s行导入数据非法,请重新编辑", rowIndex + 1));
}
excels.add(data);
// 防止OOM
Integer limit = getLimit();
if (excels.size() > limit) {
throw new GenericBusinessException("导入数据不能超过" + limit + "行");
}
}
//整个表读取完成之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
读取代码
//...获取到InputStream之后调用下面
public List<Object> excelImport(InputStream inputStream) {
try {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = EasyExcel.read(inputStream, Object.class, excelListener).build();
ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(1).autoTrim(true).build();
reader.read(readSheet);
return excelListener.getExcels();
} catch (ExcelAnalysisException e) {
log.error("导入excel失败, message = {}", e.getMessage(), e);
throw new GenericBusinessException(e.getMessage());
} finally {
if(reader!=null)
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
reader.finish();
}
}
下载EXCEL(写入)
public OutputStream downloadOutputStream(HttpServletResponse response) throws Exception {
try{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//此处指定了文件类型为xls,如果是xlsx的,请自行替换修改。文件名字获取方式自己补足
response.setHeader("Content-Disposition", "attachment; filename=" + getFileName() + ".xls");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e){
throw new Exception("导出文件失败!");
}
写
public void writeExcel() HttpServletResponse response, List<?> list, String fileName,String sheetName, Class clazz) throws Exception{
ExcelWriter writer = new ExcelWriter(getOutputStream(response), ExcelTypeEnum.XLS);
Sheet sheet = new Sheet(1, 0, clazz);
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}