Java使用easyexcel操作Excel

easyexcel简介

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

easyexcel项目主页：https://github.com/alibaba/easyexcel

easyexcel使用

Maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
</dependency>

写入Excel

无表头实现

通过com.alibaba.excel.ExcelWriter类实现，每行数据是List

@Test
public void writeWithoutHead() throws IOException {
    OutputStream out = new FileOutputStream("withoutHead.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, false);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("sheet1");
    List<List<String>> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        List<String> item = new ArrayList<>();
        item.add("item0" + i);
        item.add("item1" + i);
        item.add("item2" + i);
        data.add(item);
    }
    writer.write0(data, sheet1);
    writer.finish();
}

有表头实现

很多时候，我们在生成Excel的时候都是需要添加表头的，使用easyexcel可以很容易的实现，我们可以对上面的例子进行简单的改造，为其添加表头

@Test
public void writeWithHead() throws IOException {
    try (OutputStream out = new FileOutputStream("withHead.xlsx");) {
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        Sheet sheet1 = new Sheet(1, 0);
        sheet1.setSheetName("sheet1");
        List<List<String>> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<String> item = new ArrayList<>();
            item.add("item0" + i);
            item.add("item1" + i);
            item.add("item2" + i);
            data.add(item);
        }
        List<List<String>> head = new ArrayList<List<String>>();
        List<String> headCoulumn1 = new ArrayList<String>();
        List<String> headCoulumn2 = new ArrayList<String>();
        List<String> headCoulumn3 = new ArrayList<String>();
        headCoulumn1.add("第一列");
        headCoulumn2.add("第二列");
        headCoulumn3.add("第三列");
        head.add(headCoulumn1);
        head.add(headCoulumn2);
        head.add(headCoulumn3);
        Table table = new Table(1);
        table.setHead(head);
        writer.write0(data, sheet1, table);
        writer.finish();
    }
}

ExcelProperty注解生成表头

除了上面添加表头的方式，我们还可以使用实体类，为其添加com.alibaba.excel.annotation.ExcelProperty注解来生成表头，实体类数据作为Excel数据

@Test
public void writeWithModel() throws IOException {
    OutputStream out = new FileOutputStream("withModel.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet1 = new Sheet(1, 0, ExcelPropertyIndexModel.class);
    sheet1.setSheetName("sheet1");
    List<ExcelPropertyIndexModel> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        ExcelPropertyIndexModel item = new ExcelPropertyIndexModel();
        item.name = "name" + i;
        item.age = "age" + i;
        item.email = "email" + i;
        item.address = "address" + i;
        item.sax = "sax" + i;
        item.height = "height" + i;
        item.last = "last" + i;
        data.add(item);
    }
    writer.write(data, sheet1);
    writer.finish();
}

@Data
public static class ExcelPropertyIndexModel extends BaseRowModel {
    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private String age;

    @ExcelProperty(value = "邮箱", index = 2)
    private String email;

    @ExcelProperty(value = "地址", index = 3)
    private String address;

    @ExcelProperty(value = "性别", index = 4)
    private String sax;

    @ExcelProperty(value = "身高", index = 5)
    private String height;

    @ExcelProperty(value = "备注", index = 6)
    private String last;
}

多行复杂表头

如果单行表头表头还不满足需求，没关系，还可以使用多行复杂的表头

@Test
public void writeWithMultiHead() throws IOException {
    OutputStream out = new FileOutputStream("withMultiHead.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet1 = new Sheet(1, 0, MultiLineHeadExcelModel.class);
    sheet1.setSheetName("sheet1");
    List<MultiLineHeadExcelModel> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        MultiLineHeadExcelModel item = new MultiLineHeadExcelModel();
        item.p1 = "p1" + i;
        item.p2 = "p2" + i;
        item.p3 = "p3" + i;
        item.p4 = "p4" + i;
        item.p5 = "p5" + i;
        item.p6 = "p6" + i;
        item.p7 = "p7" + i;
        item.p8 = "p8" + i;
        item.p9 = "p9" + i;
        data.add(item);
    }
    writer.write(data, sheet1);
    writer.finish();
}

@Data
public static class MultiLineHeadExcelModel extends BaseRowModel {
    @ExcelProperty(value = { "表头1", "表头1", "表头31" }, index = 0)
    private String p1;

    @ExcelProperty(value = { "表头1", "表头1", "表头32" }, index = 1)
    private String p2;

    @ExcelProperty(value = { "表头3", "表头3", "表头3" }, index = 2)
    private String p3;

    @ExcelProperty(value = { "表头4", "表头4", "表头4" }, index = 3)
    private String p4;

    @ExcelProperty(value = { "表头5", "表头51", "表头52" }, index = 4)
    private String p5;

    @ExcelProperty(value = { "表头6", "表头61", "表头611" }, index = 5)
    private String p6;

    @ExcelProperty(value = { "表头6", "表头61", "表头612" }, index = 6)
    private String p7;

    @ExcelProperty(value = { "表头6", "表头62", "表头621" }, index = 7)
    private String p8;

    @ExcelProperty(value = { "表头6", "表头62", "表头622" }, index = 8)
    private String p9;
}

添加多个表

easyexcel还支持在一个sheet中添加多个表

@Test
public void writeWithMultiTable() throws IOException {
    OutputStream out = new FileOutputStream("withMultiTable.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("sheet1");

    // 数据全是List<String> 无模型映射关系
    Table table1 = new Table(1);
    List<List<String>> data1 = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        List<String> item = new ArrayList<>();
        item.add("item0" + i);
        item.add("item1" + i);
        item.add("item2" + i);
        data1.add(item);
    }
    writer.write0(data1, sheet1, table1);

    // 模型上有表头的注解
    Table table2 = new Table(2);
    table2.setClazz(MultiLineHeadExcelModel.class);
    List<MultiLineHeadExcelModel> data2 = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        MultiLineHeadExcelModel item = new MultiLineHeadExcelModel();
        item.p1 = "p1" + i;
        item.p2 = "p2" + i;
        item.p3 = "p3" + i;
        item.p4 = "p4" + i;
        item.p5 = "p5" + i;
        item.p6 = "p6" + i;
        item.p7 = "p7" + i;
        item.p8 = "p8" + i;
        item.p9 = "p9" + i;
        data2.add(item);
    }
    writer.write(data2, sheet1, table2);

    // 模型上没有注解，表头数据动态传入，此情况下模型field顺序与excel现实顺序一致
    List<List<String>> head = new ArrayList<List<String>>();
    List<String> headCoulumn1 = new ArrayList<String>();
    List<String> headCoulumn2 = new ArrayList<String>();
    List<String> headCoulumn3 = new ArrayList<String>();
    headCoulumn1.add("第一列");
    headCoulumn2.add("第二列");
    headCoulumn3.add("第三列");
    head.add(headCoulumn1);
    head.add(headCoulumn2);
    head.add(headCoulumn3);
    Table table3 = new Table(3);
    table3.setHead(head);
    writer.write0(data1, sheet1, table3);

    writer.finish();
}

表头样式

如果表头的样式不满足我们的需求，需要调整，我们可以使用com.alibaba.excel.metadata.TableStyle定义我们需要的样式，然后调用table对象的setTableStyle方法进行设置。

读取excel

ExcelReader工具类

使用easyexcel读取excel时需要设置excel的版本，但有时我们无法预知excel的版本，所以模仿poi写了一个用于获取com.alibaba.excel.ExcelReader对象的工具类

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.apache.poi.EmptyFileException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.util.IOUtils;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

/**
 * @Author: yezhou
 * @Date: 2019/5/13 18:47
 * @Version 1.0
 */
public class ExcelReaderFactory {

    /**
     * @param in
     *           文件输入流
     * @param customContent
     *           自定义模型可以在
     *           {@link AnalysisEventListener#invoke(Object, AnalysisContext) }
     *           AnalysisContext中获取用于监听者回调使用
     * @param eventListener
     *           用户监听
     * @throws IOException
     * @throws EmptyFileException
     * @throws InvalidFormatException
     */
    public static ExcelReader getExcelReader(InputStream in, Object customContent,
                                             AnalysisEventListener<?> eventListener) throws EmptyFileException, IOException, InvalidFormatException {
        // 如果输入流不支持mark/reset，需要对其进行包裹
        if (!in.markSupported()) {
            //in = new PushbackInputStream(in, 8);
            in = new BufferedInputStream(in, 8);
        }

        // 确保至少有一些数据
        byte[] header8 = IOUtils.peekFirst8Bytes(in);
        ExcelTypeEnum excelTypeEnum = null;
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            excelTypeEnum = ExcelTypeEnum.XLS;
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
            excelTypeEnum = ExcelTypeEnum.XLSX;
        }
        if (excelTypeEnum != null) {
            return new ExcelReader(in, excelTypeEnum, customContent, eventListener);
        }
        throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }

    /**
     * @param in
     *           文件输入流
     * @param customContent
     *           自定义模型可以在
     *           {@link AnalysisEventListener#invoke(Object, AnalysisContext) }
     *           AnalysisContext中获取用于监听者回调使用
     * @param eventListener
     *           用户监听
     * @param trim
     *           是否对解析的String做trim()默认true,用于防止 excel中空格引起的装换报错。
     * @throws IOException
     * @throws EmptyFileException
     * @throws InvalidFormatException
     */
    public static ExcelReader getExcelReader(InputStream in, Object customContent,
                                             AnalysisEventListener<?> eventListener, boolean trim)
            throws EmptyFileException, IOException, InvalidFormatException {
        // 如果输入流不支持mark/reset，需要对其进行包裹
        if (!in.markSupported()) {
            //in = new PushbackInputStream(in, 8);
            in = new BufferedInputStream(in, 8);
        }

        // 确保至少有一些数据
        byte[] header8 = IOUtils.peekFirst8Bytes(in);
        ExcelTypeEnum excelTypeEnum = null;
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            excelTypeEnum = ExcelTypeEnum.XLS;
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
            excelTypeEnum = ExcelTypeEnum.XLSX;
        }
        if (excelTypeEnum != null) {
            return new ExcelReader(in, excelTypeEnum, customContent, eventListener, trim);
        }
        throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }
}

按行读取Excel

@Test
public void readRow() throws Exception {
    InputStream in = new FileInputStream("withoutHead.xlsx");
    AnalysisEventListener<List<String>> listener = new AnalysisEventListener<List<String>>() {

        @Override
        public void invoke(List<String> object, AnalysisContext context) {
            System.err.println("Row:" + context.getCurrentRowNum() + " Data:" + object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            System.err.println("doAfterAllAnalysed...");
        }
    };
    ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, null, listener);
    excelReader.read();
}

读取Excel到数据模型

正如写入Excel的时候可以使用数据模型一样，在读取Excel的时候也可以直接将数据映射为模型对象，区别在于要使用ExcelReader #read的重载方法。

@Test
public void readModel() throws Exception {
    InputStream in = new FileInputStream("withModel.xlsx");
    AnalysisEventListener<ExcelPropertyIndexModel> listener = new AnalysisEventListener<ExcelPropertyIndexModel>() {

        @Override
        public void invoke(ExcelPropertyIndexModel object, AnalysisContext context) {
            System.err.println("Row:" + context.getCurrentRowNum() + " Data:" + object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            System.err.println("doAfterAllAnalysed...");
        }
    };
    ExcelReader excelReader = ExcelReaderFactory.getExcelReader(in, null, listener);
    // 第二个参数为表头行数，按照实际设置
    excelReader.read(new Sheet(1, 1, ExcelPropertyIndexModel.class));
}

@Data
public static class ExcelPropertyIndexModel extends BaseRowModel {
    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private String age;

    @ExcelProperty(value = "邮箱", index = 2)
    private String email;

    @ExcelProperty(value = "地址", index = 3)
    private String address;

    @ExcelProperty(value = "性别", index = 4)
    private String sax;

    @ExcelProperty(value = "高度", index = 5)
    private String height;

    @ExcelProperty(value = "备注", index = 6)
    private String last;
}