亲宝软件园·资讯

展开

Java 读取Excel

一只小羊啊 人气:0

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

加载全部内容
相关教程
猜你喜欢
用户评论