Java操作Excel：POI和EasyExcel

文章与博客园同步，欢迎访问：https://www.cnblogs.com/MessiXiaoMo3334/p/13288568.html
代码地址：https://gitee.com/ilovemo/poi-study

前言

我们经常需要将项目中的表格数据或者文档数据进行导入或者导出操作，这个如果自己从零开始做还比较麻烦。比如我之前就职的公司都是自己做的组件，但是很不好用，BUG 太多。关于表格导入导出，市面上比较知名的开源就是 Apache 的POI 和 阿里巴巴的 EasyExcel了。EasyExcel 也是对 POI 的改进和封装， 更加好用。下面通过一些 demo 学习如何使用这两个开源组件。这两个组件都不难，多看文档就能会，尤其是 EasyExcel 的文档非常详细。这篇博客主要自己在写 demo 的时候整理的笔记，方便以后使用的时候查阅。如果能帮到你那就更好了

日常开发需求

1、将用户的信息导出为 excel 表格。

2、将 Excel 表中的信息录入到网站数据库。

开发中经常会涉及到 excel 的 处理，如导出 Excel ，导入 Excel 到数据库中。

操作 Excel 目前比较流行的就是 Apache POI 和阿里巴巴的 EasyExcel。

Apache POI

Apache POI 官网： http://poi.apache.org/index.html 。

结构：

HSSF － 提供读写Microsoft Excel格式档案的功能。excel 2003 版本（块，但是不能读写超过65536行记录）

XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。excel 207 版本（慢，但是能读写超过65536行记录）

SXSSF － 提供读写Microsoft Excel OOXML格式档案的功能。excel 207 版本(XSSF升级版，比XSSF快，能读写超过65536行记录)

HWPF － 提供读写Microsoft Word格式档案的功能。

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

HDGF － 提供读写Microsoft Visio格式档案的功能。

EasyExcel

GitHub 地址： https://github.com/alibaba/easyexcel

EasyExcel 官网： https://www.yuque.com/easyexcel/doc/easyexcel

EasyExcel 是阿里巴巴开源的一个 excel处理框架，以使用简单、节省内存著称。

EasyExcel 能大大减少内存占用的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

1、POI-Excel 写

1、建立一个空项目，创建普通的 Module 。

2、引入依赖：

<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<!--时间格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.6</version>
</dependency>
<!--junit-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
</dependency>

03 | 07 版本的写操作，就是对象不一样，方法都是一样的。

需要注意：2003 版本和 2007 版本存在兼容性问题， 03 版本最多只有 65535 行。

03 版本：

package com.godfrey;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * description : 测试类
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class ExcelWriteTest {

    String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

    @Test
    public void testWrite03() throws IOException {
        //1.创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("godfrey日常统计表");
        //3.创建一个行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格（1，1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日的新增安排");
        //（1，2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        //第二行（2，1）
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //(2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);

        //生成一张表（IO流） 03版本就是使用xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "godfrey日常统计表03.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("godfrey日常统计表03生成完毕");
    }
}

大文件写 HSSF

缺点：最多只能处理 65536 行，否则会抛出异常

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。

@Test
public void testWrite03BigData() throws IOException {
    //时间
    long begin = System.currentTimeMillis();

    //创建一个工作簿
    Workbook workbook = new HSSFWorkbook();

    //创建表
    Sheet sheet = workbook.createSheet();

    //写入数据
    for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//1.29s
}

大文件写 XSSF

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如 100 万条数据。

优点：可以写较大的数据量，如 20 万条。

@Test
public void testWrite07BigData() throws IOException {
    //时间
    long begin = System.currentTimeMillis();

    //创建一个工作簿
    Workbook workbook = new XSSFWorkbook();

    //创建表
    Sheet sheet = workbook.createSheet();

    //写入数据
    for (int rowNumber = 0; rowNumber < 65537; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//7.34s
}

大文件写 SXSSF

优点： 可以写非常大的数据量，如 100 万条甚至更多，写数据速度快，占用更少的内存。

注意：

过程总会产生临时文件，需要清理临时文件。默认由 100 条记录被保存在内存中，则最前面的数据被写入临时文件。如果想要自定义内存中数据的数量，可以使用 new SXSSFWorkbook (数量)。

@Test
public void testWrite07BigDataS() throws IOException {
    //时间
    long begin = System.currentTimeMillis();

    //创建一个工作簿
    Workbook workbook = new SXSSFWorkbook();

    //创建表
    Sheet sheet = workbook.createSheet();

    //写入数据
    for (int rowNumber = 0; rowNumber < 100000; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();


    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//1.85s
}

SXSSFWorkbook 来自官方的解释：实现“BigGridDemo” 策略的流式 SXSSFWorkbook 版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于你正在使用的功能，例如合并区域，注释。。。仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。

2、POI-Excel 读

03 | 07 版本的读操作

03 版本

String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

@Test
public void testRead03() throws IOException {

    //获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "godfrey日常统计表03.xls");

    //1.创建工作簿,使用excel能操作的这边都看看操作
    Workbook workbook = new HSSFWorkbook(inputStream);
    //2.得到表
    Sheet sheet = workbook.getSheetAt(0);
    //3.得到行
    Row row = sheet.getRow(0);
    //4.得到列
    Cell cell = row.getCell(0);

    //getStringCellValue获取字符串类型
    System.out.println(cell.getStringCellValue());
    inputStream.close();
}

07 版本

@Test
public void testRead07() throws IOException {

    //获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "godfrey日常统计表07.xlsx");

    //1.创建工作簿,使用excel能操作的这边都看看操作
    Workbook workbook = new XSSFWorkbook(inputStream);
    //2.得到表
    Sheet sheet = workbook.getSheetAt(0);
    //3.得到行
    Row row = sheet.getRow(0);
    //4.得到列
    Cell cell = row.getCell(0);

    //getStringCellValue获取字符串类型
    System.out.println(cell.getStringCellValue());
    inputStream.close();
}

注意获取值的类型。

读取不同的数据类型（最麻烦的点）

@Test
public void testCellType() throws IOException {
    //获取文件流
    FileInputStream inputStream = new FileInputStream(PATH + "明细表.xls");

    //创建工作簿,使用excel能操作的这边都看看操作
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    //获取标题内容
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null) {
        //获取有数据的列数
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null) {
                CellType cellType = cell.getCellType();
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + " | ");
            }
        }
        System.out.println();
    }

    //获取表中的内容
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            //读取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");

                Cell cell = rowData.getCell(cellNum);
                //匹配类型数据
                if (cell != null) {
                    CellType cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType) {
                        case STRING: //字符串
                            System.out.print("[String类型]");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN: //布尔类型
                            System.out.print("[boolean类型]");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK: //空
                            System.out.print("[BLANK类型]");
                            break;
                        case NUMERIC: //数字（日期、普通数字）
                            System.out.print("[NUMERIC类型]");
                            if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
                                System.out.print("[日期]");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                //不是日期格式，防止数字过长
                                System.out.print("[转换为字符串输出]");
                                cell.setCellType(CellType.STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case ERROR:
                            System.out.print("[数据类型错误]");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    inputStream.close();
}

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YMGBegQq-1594538757771)(http://imgcloud.duiyi.xyz//data20200712144751.png)]

注意类型转换问题。可以将上面的方法提取成工具类。

计算公式(了解)

.

@Test
public void testFormula() throws IOException {
    FileInputStream inputStream = new FileInputStream(PATH + "公式.xls");
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    //拿到计算公司eval
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    //输出单元格内容
    CellType cellType = cell.getCellType();
    switch (cellType) {
        case FORMULA://公式
            String formula = cell.getCellFormula();
            System.out.println(formula);// SUM(A2:A4)

            //计算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);//SUM(A2:A4)
            break;
    }
}

3、EsayExcel 操作

官方文档很详细，可以根据文档快速入门 https://www.yuque.com/easyexcel/doc/easyexcel 。

导入依赖

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
<!--fastjson-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.72</version>
</dependency>
<!--时间格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.6</version>
</dependency>
<!--junit-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
</dependency>

写入测试

根据官方文档的测试代码： https://www.yuque.com/easyexcel/doc/write

1、DemoData.java

package com.godfrey;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

/**
 * description : 实体类
 *
 * @author godfrey
 * @since 2020-07-11
 */
@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

2、测试写入数据

package com.godfrey;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * description : 测试
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class EasyTest {
    String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    /**
     * 最简单的写
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 写法1
        String fileName = PATH + "EasyTest.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    }
}

最终结果

.3、测试读数据

package com.godfrey;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;

/**
 * Excel工具类
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class ExcelUtil {
    public static void main(String[] args) {
        //创建ExcelReaderBuilder实例
        ExcelReaderBuilder readerBuilder = EasyExcel.read();
        //获取文件对象
        readerBuilder.file("H:\\code\\java\\gaode-poi\\src\\main\\resources\\static\\file\\poicode.xlsx");
        //指定sheet
        readerBuilder.sheet("POI分类编码表");
        //自动关闭输入流
        readerBuilder.autoCloseStream(true);
        //设置Excel格式
        readerBuilder.excelType(ExcelTypeEnum.XLSX);
        //注册监听器进行数据解析
        readerBuilder.registerReadListener(new AnalysisEventListener() {
            @Override
            public void invoke(Object o, AnalysisContext analysisContext) {
                //一行数据读取完后回调
                System.out.println(o);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                //通知文件读取完毕
                System.out.println("数据读取完毕");
            }
        });
        //构建读取器
        ExcelReader reader = readerBuilder.build();
        //读取数据
        reader.readAll();
        //读取完毕
        reader.finish();
    }
}

简单读

package com.godfrey;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.godfrey.poi.pojo.PoiCategory;

import java.util.ArrayList;
import java.util.List;

/**
 * Excel工具类
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class ExcelUtil {
    public static void main(String[] args) {

        List<PoiCategory> list = new ArrayList<>();
        EasyExcel.read(Thread.currentThread().getContextClassLoader().getResource("static/file/poicode.xlsx").getPath(), PoiCategory.class, new AnalysisEventListener<PoiCategory>() {
                    @Override
                    public void invoke(PoiCategory poiCategory, AnalysisContext analysisContext) {
                        list.add(poiCategory);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

                    }
                })
                //读取指定sheet
                .sheet("POI分类编码表")
                .doRead();
                //.readAll(); //读取所有sheet
        System.out.println(list);
    }
}