SpringBoot集成EasyExcel
EasyExcel简介
官网地址:https://www.yuque.com/easyexcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel
特点:
1、Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是 非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或 者JVM频繁的full gc。
2、EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减 少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一 行行读取数据,逐个解析。
3、EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理 (AnalysisEventListener)。
应用场景
1、数据导入:减轻录入工作量
2、数据导出:统计信息归档
3、数据传输:异构系统之间数据传输
导入应用
依赖
<!--EasyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
监听类
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.chif.goingplus.dao.FileMapper;
import com.chif.goingplus.pojo.OrderData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
// 有个很重要的点 OrderDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class OrderDataListener extends AnalysisEventListener<OrderData> {
private static final Logger LOGGER = LoggerFactory.getLogger(OrderDataListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<OrderData> list = new ArrayList<OrderData>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
//private DemoDAO demoDAO;
private FileMapper fileMapper;
/* public OrderDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}*/
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param fileMapper
*/
public OrderDataListener(FileMapper fileMapper) {
this.fileMapper = fileMapper;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(OrderData data, AnalysisContext context) {
//System.out.println(JSON.toJSONString(data));
//data.setCreateTime(new Date());
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* DemoData 类型
* AnalysisContext 分析上下文
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 数据存储到数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
if (!list.isEmpty()){
//防止syntax error, expect ')' 报错
fileMapper.addOrdersByList(list);
/*for (OrderData orderData : list) {
try {
Thread.sleep(10);
} catch (InterruptedException e) {
e.printStackTrace();
}
fileMapper.addOrder(orderData);
}*/
}
LOGGER.info("存储数据库成功!");
}
}
controller类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.chif.goingplus.dao.FileMapper;
import com.chif.goingplus.excel.OrderDataListener;
import com.chif.goingplus.pojo.OrderData;
import com.chif.goingplus.utils.ExcelDataUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.IOException;
import java.util.List;
@CrossOrigin
@RestController
@RequestMapping("/file")
public class FileController {
@Autowired
private FileMapper fileMapper;
/**
* 1.获取传递过来的CommonsMultipartFile文件
* 2.保存文件到(上传路径)服务器
* 3.使用EasyExcel读取Excel文件,并调用Listenter进行数据库数据的导入
* @param file
* @return
* @throws IOException
*/
@RequestMapping("/upload")
public String uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
//获取文件名 : file.getOriginalFilename();
String uploadFileName = file.getOriginalFilename();
/**
* 使用springmvc中的CommonsMultipartFile进行文件的保存
*/
//上传路径保存设置(项目上线请修改这里---!!!)
String path = "D:\\谷歌下载\\GO+订单\\";
File realPath = new File(path);
if (!realPath.exists()){
realPath.mkdir();
}
//上传文件地址
// System.out.println("上传文件保存地址:"+realPath);
//通过CommonsMultipartFile的方法直接写文件(注意这个时候)
String[] split = uploadFileName.split("\\.");
String fileString = ExcelDataUtils.getDateString(split[0]);
String fileName=fileString+"."+split[1];
file.transferTo(new File(realPath +"/"+ fileName));
/**
* 使用EasyExcel读取Excel文件 进行数据的导入
*/
EasyExcel.read(path+fileName, OrderData.class, new OrderDataListener(fileMapper)).sheet().doRead();
//返回文件名
return uploadFileName;
}
String PATH="D:\\谷歌下载\\Go+导出\\";
/**
* 将传递过来的JSON字符串转换成OrderData类型的List集合使用在Excel doWrite()导出中
* @param orderDatas
* @return
*/
@RequestMapping("/download")
public String downloadExcel(String orderDatas) {
// 写法1
String fileName = PATH+ ExcelDataUtils.getDateString("go+订单模板") +".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// write(fileName, 格式类)
//sheet (表名)
//doWrite(数据)
List orderList = JSON.parseArray(orderDatas,OrderData.class);
EasyExcel.write(fileName, OrderData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(orderList);
return PATH;
}
}
导出
工具类
import com.alibaba.excel.EasyExcel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* EasyExcel 工具 传入Excel必要的参数
*/
@Data
@Slf4j
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class EasyExcelUtil {
/**
* Excel下载文件名
*/
private String excelName;
/**
* 工作表 ps:如果需要支持多表 请自定义添加对应的sheetName
*/
private String sheetName;
/**
* Excel数据
*/
private List<?> resultList;
/**
* EasyExcel 写出
*
* @param easyExcelUtil 自定义参数对象
* @param clazz 指定返回表格对象 ps:用注解定义行和列
* @throws UnsupportedEncodingException 编码异常 解决字符编码问题。
*/
public static void create(EasyExcelUtil easyExcelUtil, Class<?> clazz) throws UnsupportedEncodingException {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder
.getRequestAttributes();
//避免response报错空指针异常
assert requestAttributes != null;
HttpServletResponse httpServletResponse = requestAttributes.getResponse();
//下载Excel名称
String fileName = new String(
(easyExcelUtil.getExcelName() + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xlsx")
.getBytes(), StandardCharsets.UTF_8);
//处理中文乱码
fileName = new String(fileName.getBytes(), "ISO_8859_1");
//导出Excel 设置两个头 响应内容格式
//避免response报错空指针异常
assert httpServletResponse != null;
httpServletResponse.setContentType("application/vnd.ms-excel;charset=UTF-8");
//设置前端下载文件名
httpServletResponse.setHeader("Content-disposition", "attachment;filename=" + fileName);
try {
//向前端写入文件流流
EasyExcel.write(httpServletResponse.getOutputStream(), clazz)
.sheet(easyExcelUtil.getSheetName())
//此步骤是开启EasyEXCEL自适应列宽
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(easyExcelUtil.getResultList());
} catch (IOException e) {
log.info("[EasyExcelUtil-create] Excel下载出错");
}
}
}
EasyEXCEL自适应列宽:
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* EasyEXCEL自适应列宽
* 在导出时注册registerWriteHandler(new CustomCellWriteHandler())
*/
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
自定义类型转换器: ps:一个类型转换器只支持一个类型 不能在一个转换器里写多个方法实现
因为 implements Converter 我们自定义的都是 CellData 方法
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* EasyExcel 自定义转换器
*/
public class TypeConverter implements Converter<Integer> {
/**
* 类型(1-新增 2-扩科 3-续费 4-新增连报 5-新增体验)
*/
private static final String NEW = "新增";
private static final String EXTENSION = "扩科";
private static final String RENEWAL = "续费";
private static final String NEW_EVEN = "新增连报";
private static final String NEW_EXPERIENCE = "新增体验";
private static final String NULL_CHARACTER = " ";
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
@Override
public CellData convertToExcelData(Integer num, ExcelContentProperty excelContentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
switch (num) {
case 1:
return new CellData(NEW);
case 2:
return new CellData(EXTENSION);
case 3:
return new CellData(RENEWAL);
case 4:
return new CellData(NEW_EVEN);
case 5:
return new CellData(NEW_EXPERIENCE);
default:
return new CellData(NULL_CHARACTER);
}
}
}
实体类
import cn.njcool.backend.edu.common.utils.TypeConverter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Builder;
import lombok.Data;
@Data
@Builder
@ExcelIgnoreUnannotated
/**
* @ExcelIgnoreUnannotated
* 默认不加ExcelProperty 的注解的都会参与读写,加了不会参与
* 所以我们在某些场景下不需要ID 用Excel的自动排序 就开启该注解
*/
public class StudentDO {
/**
* 默认所有字段都会和excel去匹配,加了这个注解会忽略该字段
* 但是在类上需要开启@ExcelIgnoreUnannotated
*/
@ExcelIgnore
private Integer id;
//第一列序号为0
@ExcelProperty(value = "学员名称", index = 0)
private String name;
/**
* @ExcelProperty(value = "学员名称", index = 0)
* 指定当前字段对应excel中的那一列。可以根据名字或者Index去匹配。当然也可以不写,默认第一个字段就是index=0,
* 以此类推。千万注意,要么全部不写,要么全部用index,要么全部用名字去匹配。千万别三个混着用,除非你非常了解源代码中三个混着用怎么去排序的。
*/
@ExcelProperty(value = "学员爱好", index = 1)
private String hobby;
/**
* 类型(1-新增 2-扩科 3-续费 4-新增连报 5-新增体验
* 转换器,默认加载了很多转换器。也可以自定义 指向你自定义的转换器
*/
@ExcelProperty(value = "类型", index = 2, converter = TypeConverter.class)
private Integer type;
/**
* DateTimeFormat 日期转换,
* 用String去接收excel日期格式的数据会调用这个注解。
* 里面的value参照java.text.SimpleDateFormat
*/
// @ExcelProperty(value = "时间", index = 3)
// @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
// @DateTimeFormat(pattern = "yyyy-MM-dd")
// private String date;
}
控制层方法调用:
@ApiOperation(value = "Excel下载测试")
@GetMapping("test")
public void Test() throws UnsupportedEncodingException {
//就不查数据库了 一层撸到底 大家一下就看懂了
//准备数据
List<StudentDO> resultList = new ArrayList<>();
StudentDO StudentDO1 = StudentDO.builder().id(1).name("哈拉少").hobby("洗澡的干活").type(1).build();
StudentDO StudentDO2 = StudentDO.builder().id(2).name("龚大人").hobby("摸鱼").type(2).build();
StudentDO StudentDO3 = StudentDO.builder().id(3).name("秋桑").hobby("钓鱼").type(3).build();
StudentDO StudentDO4 = StudentDO.builder().id(4).name("文总").hobby("分析一波").type(4).build();
StudentDO StudentDO5 = StudentDO.builder().id(5).name("为老总").hobby("18禁").type(5).build();
resultList.add(StudentDO1);
resultList.add(StudentDO2);
resultList.add(StudentDO3);
resultList.add(StudentDO4);
resultList.add(StudentDO5);
//构建生成Excel对象
EasyExcelUtil excel = EasyExcelUtil.builder()
.excelName("应天打工人")
.sheetName("个人介绍")
.resultList(resultList)
.build();
//Run
EasyExcelUtil.create(excel,StudentDO.class);
}