Java利用EasyExcel讀取寫入Excel詳情

2022-09-26 14:06:11

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內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

Java利用EasyExcel讀取寫入Excel詳情

目錄

EasyExcel介紹

為什麼使用EasyExcel？

封裝使用

例子

熱門文章