目录
EasyExcel介绍
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。
为什么使用EasyExcel？
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模式，在上层做了模型转换的封装，让使用者更加简单方便。
封装使用
引入EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>Excel文档的自动列宽设置
public class CustomColumnWidthHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private static final int PADDING_WIDTH = 6;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
if (isHead) {
int columnWidth = cell.getStringCellValue().length() * 2 + PADDING_WIDTH;
columnWidth = Math.min(columnWidth, MAX_COLUMN_WIDTH);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}消费监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;
public class EasyExcelConsumerListener<T> extends AnalysisEventListener<T> {
private int pageSize;
private List<T> list;
private Consumer<List<T>> consumer;
public EasyExcelConsumerListener(int pageSize, Consumer<List<T>> consumer) {
this.pageSize = pageSize;
this.consumer = consumer;
list = new ArrayList<>(pageSize);
}
@Override
public void invoke(T data, AnalysisContext context) {
list.add(data);
if (list.size() >= pageSize) {
consumer.accept(list);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
consumer.accept(list);
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
exception.printStackTrace();
throw exception;
}
}ExcelSheet
public class ExcelSheet<T> {
private String sheetName;
private T clazz;
private List<T> data;
public ExcelSheet() {
}
public ExcelSheet(String sheetName, T clazz, List<T> data) {
this.sheetName = sheetName;
this.clazz = clazz;
this.data = data;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public T getClazz() {
return clazz;
}
public void setClazz(T clazz) {
this.clazz = clazz;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
@Override
public String toString() {
return "CreateExcelSheet{" +
"sheetName='" + sheetName + '\'' +
", clazz=" + clazz +
", data=" + data +
'}';
}
}LocalDateConverter
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
public class LocalDateConverter implements Converter<LocalDate> {
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(ReadConverterContext<?> context) {
Calendar calendar = new GregorianCalendar(1900, 0, -1);
Date gregorianDate = calendar.getTime();
return LocalDate.parse((new SimpleDateFormat("yyyy-MM-dd")).format(
addDay(gregorianDate, context.getReadCellData().getNumberValue().intValue())),
DateTimeFormatter.ofPattern("yyyy-MM-dd")
);
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<LocalDate> context) {
return new WriteCellData<>(context.getValue().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
}
public static Date addDay(Date date, int day) {
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
calendar.add(5, day);
date = calendar.getTime();
return date;
}
}LocalDateTimeConverter
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(ReadConverterContext<?> context) {
return LocalDateTime.parse(context.getReadCellData().getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<LocalDateTime> context) {
return new WriteCellData<>(context.getValue().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}ByteArrayConverter
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.nio.charset.StandardCharsets;
public class ByteArrayConverter implements Converter<byte[]> {
public ByteArrayConverter() {
}
@Override
public Class<byte[]> supportJavaTypeKey() {
return byte[].class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public byte[] convertToJavaData(ReadConverterContext<?> context) {
String stringValue = context.getReadCellData().getStringValue();
return stringValue.getBytes(StandardCharsets.UTF_8);
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<byte[]> context) {
return new WriteCellData((byte[])context.getValue());
}
}EasyExcel工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
public class ExcelUtil extends EasyExcel {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
private ExcelUtil() {}
/**
* 分批读取
*/
public static <T> ExcelReaderBuilder read(String pathName, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(pathName, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
/**
* 分批读取
*/
public static <T> ExcelReaderBuilder read(File file, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(file, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
/**
* 分批读取
*/
public static <T> ExcelReaderBuilder read(InputStream inputStream, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(inputStream, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
/**
* 根据实体生成Excel模版（用于数据导入的模版下载）
*/
public static ExcelWriterBuilder write(String pathName, Class head) {
return EasyExcel.write(pathName, head)
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler());
}
/**
* 写入
*/
public static void write(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class clazz) throws Exception {
EasyExcel.write(getOutputStream(fileName, response), clazz)
.excelType(ExcelTypeEnum.XLSX)
.sheet(sheetName)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler())
.doWrite(data);
}
/**
* 写入
*/
public static void write(OutputStream outputStream, List<?> data, String sheetName, Class clazz) {
EasyExcel.write(outputStream, clazz)
.excelType(ExcelTypeEnum.XLSX)
.sheet(sheetName)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler())
.doWrite(data);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
return response.getOutputStream();
}
/**
* 获取表头
*/
public static Map<String, String> getHeadMap(Class<?> aClass) {
Map<String, String> HeadMap = new LinkedHashMap<>();
Field[] declaredFields = aClass.getDeclaredFields();
ExcelProperty excelProperty;
for (Field field : declaredFields) {
if (field != null) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelProperty.class)) {
excelProperty = field.getAnnotation(ExcelProperty.class);
HeadMap.put(field.getName(), StringUtils.join(Arrays.asList(excelProperty.value()), ","));
}
}
}
return HeadMap;
}
/**
* 生成通用表格样式
*/
public static HorizontalCellStyleStrategy buildCellStyle(){
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont font = new WriteFont();
font.setFontName("Microsoft YaHei Light");
font.setColor(IndexedColors.WHITE.getIndex());
font.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(font);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 创建一个Excel文件多个Sheet
* @param sheetList
*/
public static void createExcel(List<ExcelSheet> sheetList, OutputStream os){
ExcelWriter excelWriter = null;
WriteSheet writeSheet = null;
int count = 0;
try {
excelWriter = EasyExcel.write(os)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler())
.build();
for (ExcelSheet sheet : sheetList) {
writeSheet = EasyExcel.writerSheet(count++, sheet.getSheetName()).head((Class) sheet.getClazz()).build();
excelWriter.write(sheet.getData(),writeSheet);
}
} catch (Exception e) {
LOGGER.error("创建一个Excel文件多个Sheet失败", e);
}finally {
if (null != excelWriter){
excelWriter.finish();
}
}
}
}
举个简单的例子
UserVo实体
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.time.LocalDate;
@Data
public class UserVo {
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "年龄")
private int age;
@ExcelProperty(value = "出生日期", converter = LocalDateConverter.class)
private LocalDate birthdate;
}导出用户信息
ExcelUtil.write(httpServletResponse, list, "用户信息.xlsx", "用户信息", UserVo.class);读取用户信息
ExcelUtil.read(filePath, UserVo.class, 1000, pageList -> {
pageList.forEach(user -> {
// 业务逻辑
});
}).sheet().doRead();更多使用方法参考官方文档：EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
