SpringBoot整合EasyExcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便
64M内存1分钟内读取75M(46W行25列)的Excel
内存变动
一、准备工作
- 引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
- 实现功能
Excel数据导出，Excel数据导入
二、映射类和工具类
- 映射类
/**
* @Author: Cpz
* @Description: Excel映射类
* @Date: 2021/5/21 17:45
* @Version: 1.0
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExportBean extends BaseRowModel implements Serializable {
private static final long serialVersionUID = 1216307115225329796L;
@ExcelProperty(value = "编号", index = 0)
private Long id;
@ExcelProperty(value = "名称", index = 1)
private String name;
@ExcelProperty(value = "时间", index = 2)
private Date date;
@ExcelProperty(value = "是否禁用", index = 3)
private Boolean isEnabled;
}
作为映射实体类需要继承BaseRowModel ，通过 @ExcelProperty 注解与 value 和 index 变量可以标注成员变量所映射的列，同时不可缺少 setter 方法，本示例使用 lombok 注解代替。
- 工具类
package com.bly.crm.server.util;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.bly.crm.generic.utils.DateUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.Charsets;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
/**
* @Author: Cpz
* @Description: Excel工具类
* @Date: 2021/5/21 17:10
* @Version: 1.0
*/
@Component
public class ExcelUtil {
private static final Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 生成excel文件
*
* @param fileNamePrefix 文件名称前缀
* @param data 导出对象集合
* @throws IOException
*/
public static <T extends BaseRowModel> void write(String fileNamePrefix, List<T> data) {
try (ServletOutputStream out = getResponse().getOutputStream()) {
// 1. 导出excel表名称，格式为：fileName+yyyyMMddHHmmss+.xlsx，
final String excelNameWithExtension = URLEncoder.encode(fileNamePrefix
.concat(DateUtils.getFormatDate(new Date(), DateUtils.DateFormat.PART_DATE_FORMAT_LONG)), Charsets.UTF_8.name())
.concat(ExcelTypeEnum.XLSX.getValue());
// 2. 设置响应类型编码名称
getResponse().setContentType("multipart/form-data");
getResponse().setCharacterEncoding(Charsets.UTF_8.name());
getResponse().setHeader("Content-disposition", "attachment;filename=".concat(excelNameWithExtension));
// 3. 将输入写入excel并输出
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, CollectionUtils.isEmpty(data) ? BaseRowModel.class : data.get(0).getClass());
sheet.setAutoWidth(true);
writer.write(data, sheet);
writer.finish();
} catch (IOException e) {
LOG.error("导出excel发生异常：{}", e.getMessage());
}
}
/**
* 读取excel文件
*
* @param file 文件
* @param clazz 类型
* @return
* @throws IOException
*/
public static <T extends BaseRowModel> List<T> read(MultipartFile file, Class<T> clazz) {
ExcelListener listener = new ExcelListener();
try (InputStream inputStream = new BufferedInputStream(file.getInputStream())) {
ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
excelReader.read(new Sheet(1, 1, clazz));
return listener.getList();
} catch (IOException e) {
LOG.error("读取excel发生异常：{}", e.getMessage());
}
return Collections.emptyList();
}
/**
* 监听读取excel数据并设置到list
*
* @param <T>
*/
private static class ExcelListener<T> extends AnalysisEventListener<T> {
private List<T> list = new ArrayList<>();
public List<T> getList() {
return list;
}
@Override
public void invoke(T data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//list.clear(); //解析结束销毁不用的资源（此处需要用到数据，不能清空）
}
}
/**
* 获取响应对象
*
* @return
*/
public static HttpServletResponse getResponse() {
return ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
}
}
三、Excel数据导出导入测试
package com.bly.crm.server.controller;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.bly.crm.generic.core.R;
import com.bly.crm.server.util.ExcelUtil;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import com.google.common.collect.Lists;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import lombok.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.validation.constraints.NotNull;
import java.util.Date;
import java.util.List;
/**
* @Author: Cpz
* @Description: 测试Excel工具类API
* @Date: 2021/5/21 17:50
* @Version: 1.0
*/
@Api(tags = "导出测试")
@RestController
@RequiredArgsConstructor(onConstructor_ = @Autowired)
public class ExportController {
@ApiOperation("导出")
@ApiOperationSupport(order = 10)
@PostMapping(value = "/export/test", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void exportExcel() {
List<ExportBean> exportBeans = Lists.newArrayList(
new ExportBean(1L, "张三", new Date(), Boolean.FALSE),
new ExportBean(2L, "李四", new Date(), Boolean.TRUE)
);
ExcelUtil.write("导出", exportBeans);
}
@ApiOperation("导入")
@ApiOperationSupport(order = 11)
@PostMapping(value = "/import/test", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@ApiImplicitParam(name = "file", value = "文件", required = true, paramType = "form", dataType = "__File")
public R<ExportBean> importExcel(@NotNull MultipartFile file) {
List<ExportBean> exportBeans = ExcelUtil.read(file, ExportBean.class);
return R.ok(exportBeans);
}
}
四、测试效果图
- 导出
image
- 导入
image
网友评论