Java利用EasyExcel读取写入Excel详情

目录

• EasyExcel介绍
• 为什么使用EasyExcel？
• 封装使用
• 例子

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();

到此这篇关于Java利用EasyExcel读取写入Excel详情的文章就介绍到这了,更多相关Java 读取Excel内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！