POI & easyExcel快速使用

常用信息

将用户信息导出为Excel表格(导出数据)
将Excel表中的信息录入到网站数据库(习题上传...),可以大大减轻网站录入量

开发中经常涉及到Excel的处理，如导出Excel,导入数据到Excel中

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

Apache POI

POI（Poor Obfuscation Implementation），直译为“可怜的模糊实现”，利用POI接口可以通过 Java 操作 Microsoft office 套件工具的读写功能。POI支持office的所有版本。

基本功能

结构：

HSSF －提供读写Microsoft Excel格式档案的功能。（03版本，行数最多支持65536行）

XSSF －提供读写Microsoft Excel OOXML格式档案的功能。(07版本，行数无限制)

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

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

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

POI 较为原生和复杂，操作起来比较麻烦，POI 会将数据先加载到内存中，然后再写入到文件中，处理大量数据时可能出现OOM(Out Of Memory，内存溢出)问题。

easyExcel

easyExcel 官网： https://github.com/alibaba/easyexcel

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

介绍

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

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

easyExcel 和 POI 在解析Excel时的对比图

POI - Excel 写

创建一个项目，创建普通Maven的Module

导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.shiguang</groupId>
    <artifactId>poi-easyExcel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--导入依赖-->
    <dependencies>
        <!-- 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.8</version>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

注意：2003和2007版本存在兼容性的问题！03版本最多只有65535行！

可以将Excle 抽象出工作簿，工作表，行，列这几个对象

Workbook接口下的实现类

示例代码

package com.shiguang;


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;

public class ExcelWriteTest {
    String PATH = "D:\Desktop\"; //表输出路径
    @Test
    public void testWrite03() throws IOException {
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("時光收支统计表");
        //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("统计时间");

        //第二行
        Row row2 = sheet.createRow(1);
        //第二行第一列（2,1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("888");
        //第二行第二列(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 + "時光收支情况统计表03.xls");
        //输出
        workbook.write(fileOutputStream);

        //关闭流
        fileOutputStream.close();
        System.out.println("Excle文件生成完毕ヾ(•ω•`)o");

    }



}

效果

07版本只需要将对象该为 XSSFWorkbook() ,并将表名称后缀改为 xlsx 即可

数据批量导入

大文件写HSSF

示例代码

@Test
    public void testWrite03BigData() throws IOException {
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for(int rowNum = 0; rowNum<65536;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("工作表创建成功ヾ(^▽^*)))");
        FileOutputStream OutputStream = new FileOutputStream(PATH + "大数据测试03.xls");
        workbook.write(OutputStream);
        OutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

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

优点: 先写入缓存，最后一次性写入磁盘，速度快

大文件写XSSF

示例代码

@Test
    public void testWrite07BigData() throws IOException {
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for(int rowNum = 0; rowNum<100000;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("工作表创建成功ヾ(^▽^*)))");
        FileOutputStream OutputStream = new FileOutputStream(PATH + "大数据测试07.xlsx");
        workbook.write(OutputStream);
        OutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

缺点：写数据时速度非常慢，非常小号内存，并且会发生内存溢出，如处理100万条数据时

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

大文件写SXSSF

示例代码

 @Test
    public void testWrite07BigDataS() throws IOException {
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for(int rowNum = 0; rowNum<100000;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("工作表创建成功ヾ(^▽^*)))");
        FileOutputStream OutputStream = new FileOutputStream(PATH + "大数据测试07S.xlsx");
        workbook.write(OutputStream);
        OutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

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

注意：

过程中会产生临时文件，需要清理临时文件

默认有100条记录被保存在内存中，如果超出这个数量，则最前面的数据会被写入临时文件中。

如果想自定义内存中数据的数量，可以使用 new SXSSFWorkbook(数量)

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

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

POI-Excle 读

03版本

表数据

示例代码

package com.shiguang;

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.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

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

public class ExcelReadTest {
    String PATH = "D:\Desktop\"; //Excel表所在路径
    @Test
    public void testRead03() throws IOException {
        //1.创建一个工作簿 03

        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "時光收支情况统计表03.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        //2.获取一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row row = sheet.getRow(1);
        //获取第一行第一列
        Cell cell = row.getCell(0);

        //读取值时一定要注意类型
        //getStringCellValue() 字符串类型
        //System.out.println(cell.getStringCellValue());
        //getNumericCellValue() 数值类型
        System.out.println(cell.getNumericCellValue());
        //关闭流
        inputStream.close();
    }

}

07 版本

示例代码

@Test
    public void testRead07() throws IOException {
        //1.创建一个工作簿 07

        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "時光收支情况统计表07.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        //2.获取一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row row = sheet.getRow(1);
        //获取第一行第一列
        Cell cell = row.getCell(0);

        //读取值时一定要注意类型
        //getStringCellValue() 字符串类型
        //System.out.println(cell.getStringCellValue());
        //getNumericCellValue() 数值类型
        System.out.println(cell.getNumericCellValue());
        //关闭流
        inputStream.close();
    }

注意：读取值时一定要注意类型

读取不同数据类型

示例代码

@Test
    public void testRead07demo() throws IOException {
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(PATH + "销售信息统计表.xls");
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        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 stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "|");
                }
            }
            System.out.println();

        }

        //获取表中的内容

        //获取所有行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        //由于第0行是表头信息,所以从第一行开始遍历
        for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null){
                //读取行中的列信息

                //获取总列数
                //int cellCount = rowTitle.getPhysicalNumberOfCells();
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int celNum = 0; celNum <cellCount ; celNum++) {
                    System.out.print("[" + (rowNum+1) + "-" + (celNum+1) + "]");
                    Cell cell = rowData.getCell(celNum);
                    //匹配列的数据类型
                    if(cell != null){
                        //获取当前列的数据类型
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case BLANK: // 空
                                System.out.println("[BLANK]");
                                break;
                            case STRING: //字符串
                                System.out.println("[STRING]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN: //布尔类型
                                System.out.println("[BOOLEAN]");
                                //强制转换为字符串类型
                                cellValue= String.valueOf(cell.getBooleanCellValue());
                                break;
                            case NUMERIC: //数字（日期,普通数字）
                                System.out.print("[Numeric]");
                                //新版本HSSFDateUtil方法过时,使用DataUtil
                                if (DateUtil.isCellDateFormatted(cell)){
                                    // 日期
                                    System.out.print("[日期]");
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                }else {
                                    System.out.print("[转换为字符串输出]");
                                    // 不是日期格式，防止数字过长,转换为字符串类型
//                                    cell.setCellType(CellType.STRING); 5.0 版本后过时,使用如下方法
                                    HSSFDataFormatter hssfDataFormat = new HSSFDataFormatter();
                                    cellValue = hssfDataFormat.formatCellValue(cell).toString();
                                }
                                break;
                            case ERROR: //错误
                                System.out.println("[数据类型错误！！]");
                                break;

                        }
                        System.out.println(cellValue);

                    }
                }

            }
        }
        //关闭流
        fileInputStream.close();
    }

公式计算

表数据

示例代码

@Test
    public void testFormula() throws IOException {
        FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(5);
        Cell cell = row.getCell(0);
        //拿到计算公式
        FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        //输出单元格的内容
        CellType cellType = cell.getCellType();
        switch (cellType){
            case FORMULA: //公式
                //获取当前单元格的计算公式
                String cellFormula = cell.getCellFormula();
                System.out.println("使用公式:	" + cellFormula);
                //计算得到计算结果
                CellValue evaluate = FormulaEvaluator.evaluate(cell);
                //将计算结果转换为字符串类型
                String cellValue = evaluate.formatAsString();
                System.out.println("计算结果为:	" + cellValue);
                break;
        }
    }

EasyExcel基本使用

easyExcel 官网： https://github.com/alibaba/easyexcel

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

导入依赖

<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>

写入测试

实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

测试类

package com.shiguang.easy;

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

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

public class easyTest {
    String PATH = "D:\Desktop\";
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        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;
    }

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

    }
}

此处运行时遇到了一个错误

官方解释：

此错误在组织slf4j.inf.strestcoperbinder类无法装入内存时报告。当在类路径上找不到合适的slf4j绑定时，就会发生这种情况。slf4j-nop.jar放置一个(且只有一个), slf4j-simple.jar, slf4j-log4j12.jar, slf4j-jdk14.jar 或 logback-classic.jar 的类路径应该解决这个问题。

解决方法：

在Maven工程的pom文件中新增如下依赖

<dependency>  
  <groupId>org.slf4j</groupId> 
  <artifactId>slf4j-nop</artifactId> 
  <version>1.7.2</version> 
</dependency>

重新刷新依赖就可以了

运行 simpleWrite() 方法后生成的Excel表如下所示

读测试

实体类

参考上文

监听器

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

持久层

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

注意：

如果 DemoDataListener 的 @Override 错误，将Modules该为8版本即可

另外，还需要导入fastjson 依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.75</version>
</dependency>

测试代码

@Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = PATH + "EasyTest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

执行效果如下：

总结

写入：根据固定类格式写入

读取：根据监听器设置的规则进行读取

相关阅读:
python函数对象
 生成器表达式，列表推导式
 python转换excel成py文件
 Python处理excel表
 Go基础:接口相关
 JAVA03-输入和输出
 python6-while循环
 python5-字典
 自动化8-xpath
网络学习day1-计算机网络基础
原文地址：https://www.cnblogs.com/an-shiguang/p/14828674.html