前言
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便
1 概述
EasyExcel 是一款基于 Java 的简单易用的 Excel 文件操作工具。它提供了丰富的 API,可以方便地读取、写入和操作 Excel 文件,支持常见的 Excel 操作,如读取/写入单元格数据、合并单元格、设置样式、处理大数据量等。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
2 读取 Excel 文件
使用 EasyExcel 可以读取 Excel 文件的内容并将其转化为 Java 对象,或者按行进行处理。例如:
导出功能实现
package com.pumch.financeselfservice.common.utils;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.pumch.financeselfservice.common.CommonConsts;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author clzhang
* @Date 2023/8/10 10:14
* @Description: Excel导入导出工具类
*/
@Slf4j
public class ExcelUtils {
/**
* 读取文件解析监听类,此类供外部实例化使用需要设置为静态类
* @param <T>
*/
public static class ExcelListener<T> extends AnalysisEventListener<T> {
/**
* 存放读取后的数据
*/
public List<T> datas = new ArrayList<>();
/**
* 读取数据,一条一条读取
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
datas.add(t);
}
/**
* 解析完毕之后执行
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取数据条数:{}条!", datas.size());
}
public List<T> getDatas(){
return this.datas;
}
}
//-------------------------------------------------------------- 读取文件解析监听类 end ----------------------------------------------------
//-------------------------------------------------------------- 导出excel表格,设置自适应列宽配置类 start ----------------------------------------------------
/**
* ClassName:Custemhandler
* Description:设置自适应列宽配置类
*/
public static class Custemhandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
//因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 20;
private Map<Integer, Map<Integer, Integer>> cache = new HashMap(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)cache.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
if(columnWidth<COLUMN_WIDTH){
columnWidth =columnWidth*2;
}
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth* 256);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData 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;
}
}
}
}
}
//-------------------------------------------------------------- 导出excel表格,设置自适应列宽配置类 end -----------------------------------------------------
/**
* 读取Excel文件返回数据集合,不包含表头,默认读取第一个sheet数据
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtil.isNull(inputStream) || ObjectUtil.isNull(tClass) || ObjectUtil.isNull(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
read.sheet().doRead();
return excelListener.getDatas();
}
/**
* 读取Excel文件返回数据集合,不包含表头,读取第x个sheet数据,不设置sheet就读取全部
* @date 2021/9/2 0:17
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtil.isNull(inputStream) || ObjectUtil.isNull(tClass) || ObjectUtil.isNull(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
if(ObjectUtil.isNotNull(sheetNo)){
read.sheet(sheetNo).doRead();
}else{
ExcelReader excelReader = read.build();
excelReader.readAll();
excelReader.finish();
}
return excelListener.getDatas();
}
/**
* 不带模板输出数据到Excel,数据传输类属性用 @ExcelProperty("") 标注
* @param response 响应对象
* @param tClass 输出格式
* @param datas 输出的数据
* @return:
*/
public static <T> void writeEasyExcel(HttpServletResponse response, Class<T> tClass, List<T> datas, String fileName) throws IOException {
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(response) || ObjectUtil.isNull(tClass)){
return;
}
if(StrUtil.isBlank(fileName)){
fileName = CommonConsts.EXCEL_DEFAULT_PREFIX+CommonConsts.EXCEL_FILE_EXTENSION;
}else{
if(!fileName.contains(CommonConsts.EXCEL_FILE_EXTENSION)){
fileName = fileName+CommonConsts.EXCEL_FILE_EXTENSION;
}
}
response.setStatus(HttpServletResponse.SC_OK);
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName, "utf-8"));
EasyExcel.write(response.getOutputStream(), tClass)
.autoCloseStream(true)
.registerWriteHandler(new Custemhandler())
.excelType(ExcelTypeEnum.XLSX)
.sheet("sheet")
.doWrite(datas);
log.info("导出excel数据:{}条,耗时:{}秒!", datas.size(), timer.intervalSecond());
}
/**
* 带模板输出数据到Excel,数据传输类属性用 @ExcelProperty("") 标注
* @param outputStream 输出流
* @param tClass 输出格式
* @param datas 输出的数据
* @return:
*/
public static <T> void writeExcel(InputStream inputStream ,OutputStream outputStream, Class<T> tClass, List<T> datas){
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(outputStream) || ObjectUtil.isNull(tClass)){
return;
}
EasyExcel.write(outputStream, tClass).excelType(ExcelTypeEnum.XLSX).withTemplate(inputStream).sheet("sheet").doFill(datas);
log.info("导出excel数据:{}条,耗时:{}秒!", datas.size(), timer.intervalSecond());
}
/**
* 不带模板输出数据到Excel,数据传输类属性用 @ExcelProperty("") 标注
* @param response 响应对象
* @param tClass 输出格式
* @param datas 输出的数据
* @return:
*/
public static <T> void writeExcel(HttpServletResponse response, Class<T> tClass, List<T> datas, String fileName) throws IOException, IllegalAccessException, NoSuchFieldException {
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(response) || ObjectUtil.isNull(tClass)){
return;
}
if(StrUtil.isBlank(fileName)){
fileName = CommonConsts.EXCEL_DEFAULT_PREFIX+CommonConsts.EXCEL_FILE_EXTENSION;
}else{
if(!fileName.contains(CommonConsts.EXCEL_FILE_EXTENSION)){
fileName = fileName+CommonConsts.EXCEL_FILE_EXTENSION;
}
}
//编码设置成UTF-8,excel文件格式为.xlsx
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// 这里URLEncoder.encode可以防止中文乱码 和easyexcel本身没有关系
fileName = URLEncoder.encode("test", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), tClass).build();
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("sheet");
excelWriter.write(datas, writeSheet);
excelWriter.finish();
log.info("导出excel数据:{}条,耗时:{}秒!", datas.size(), timer.intervalSecond());
}
}