AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

POI EasyExcel

2022-10-23 558
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
全局流量管理 GTM，标准版 1个月
公共DNS（含HTTPDNS解析），每月1000万次HTTP解析
云解析 DNS，旗舰版 1个月
简介： EasyExcel 是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

POI EasyExcel

常用信息

1、将用户信息导出为excel表格（导出数据…）

2、将Excel表汇总的信息导入到网站数据库（习题上传…）

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

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

Apache POI

Apache POI 官网：https://poi.apache.org/

image.png

image.png

easyExcel

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

image.png

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

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

内存问题：POI = 100W数据 会先加载到内存 OOM，在写入文件！ es = 1

下面是 EasyExcel 和 POI 在解析Excel时的对比分析图。

image.png

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

POI - Excel写

创建项目

1、建立一个空项目 ，创建普通maven的Module kuang-poi

2、导入pom依赖

<!--导入依赖-->
<dependencies>
    <!--xls（03版本）-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <!--xlsx（07版本）-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <!-- 日期格式化工具 -->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.14</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>
03版本 | 07版本的写，只是对象的不同，方法是一样的！

==需要注意：03 版本 和 07 版本存在兼容行问题！03最多就是65535行！==

03版本：

@Test
public void testWrite03() throws Exception {
    //1、创建一个工作蒲
    Workbook workbook = new HSSFWorkbook();
    //2、创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
    //3、创建一个行（1，1）
    Row row1 = sheet.createRow(0);
    //4、创建一个单元格
    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 outputStream = new FileOutputStream(PATH+"\\统计表.xls");
    workbook.write(outputStream);

    //关闭流
    outputStream.close();
    System.out.println("03版本生成完毕");
}

07 版本：

@Test
public void testWrite07() throws Exception {
    //1、创建一个工作蒲
    Workbook workbook = new XSSFWorkbook();
    //2、创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
    //3、创建一个行（1，1）
    Row row1 = sheet.createRow(0);
    //4、创建一个单元格
    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流）07版本就是xlsx结尾！
    FileOutputStream outputStream = new FileOutputStream(PATH+"\\统计表.xlsx");
    workbook.write(outputStream);

    //关闭流
    outputStream.close();
    System.out.println("07版本生成完毕");
}

注意对象的一个区别：文件后缀！

大文件写 HSSF

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

java.lang.IllegalArgumentException:Invalidrownumber(65536)outside allowable range (0..65535)

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

@Test
public void testWrite03BigData() throws Exception {
    //时间
    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("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);
}
大文件写XSSF

缺点：写数据时速度非常慢，非常消耗内存，也会发生内存溢出~

优点：可以写较大的数据量

@Test
public void testWrite07BigData() throws Exception {
    //时间
    long begin = System.currentTimeMillis();
    // 创建一个蒲
    Workbook workbook = new XSSFWorkbook();
    // 创建表
    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("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);
}
大文件写SXSSF

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

注意：

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

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

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

@Test
public void testWrite07BigDataS() throws Exception {
    //时间
    long begin = System.currentTimeMillis();
    // 创建一个蒲
    Workbook workbook = new SXSSFWorkbook();
    // 创建表
    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("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();

    //清除临时文件
    ((SXSSFWorkbook)workbook).dispose();
    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);
}

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

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

POI-Excel - 读

03 | 07

03版本

@Test
public void testRead03() throws Exception {

    //获得文件流
    FileInputStream inputStream = new FileInputStream(PATH + "kuang-poi统计表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(1);

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

07版本

@Test
public void testRead07() throws Exception {

    //获得文件流
    FileInputStream inputStream = new FileInputStream(PATH + "kuang-poi统计表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(1);

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

注意：获取值的类型即可！

读取不同的数据了类型（最麻烦的就是这里！）

可以抽取文工具类utils，==在这里要注意版本的问题，以下为3.x版本！==

@Test
public void testCellType() throws Exception {
    //获得文件流
    FileInputStream is = new FileInputStream(PATH + "kuang-poi统计表03.xls");
    //1、创建一个工作蒲。使用excel能操作的这边他都可以操作！
    Workbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    //读取标题的所有内容
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null){ // 行不为空
        // 读取cell
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null){
                int cellType = cell.getCellType();
                String cellValue = cell.getStringCellValue();
                System.out.println(cellValue+ "|");
            }
        }
        System.out.println();
    }
    // 读取商品列表的数据
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {

        Row rowData = sheet.getRow(rowNum);
        if (rowData != null){ // 行不为空

             // 读取cell
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.println("["+(rowNum+1)+"-"+(cellNum+1)+"]");

                Cell cell = rowData.getCell(cellNum);
                if (cell != null){
                    int cellType = cell.getCellType();

                    // 判断单元格数据类型
                    String cellValue = "";
                    switch (cellType) {
                        case HSSFCell.CELL_TYPE_STRING: // 字符串
                            System.out.println("[String]");
                            cellValue = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔值
                            System.out.println("[Boolean]");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: // null
                            System.out.println("[Blank]");
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                            System.out.println("[NUMBER]");
                            //cellValue = cell.getStringCellValue();

                            if (HSSFDateUtil.isCellDateFormatted(cell)) { //  日期
                                System.out.println("[日期]");
                                Date data = cell.getDateCellValue();
                                cellValue = new DateTime(data).toString("yyyy-MM-dd");
                            } else {
                                // 不是日期格式，则防止当数字过长时以科学计数法显示
                                System.out.println("转换为日期格式！");
                                cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: // error
                            System.out.println("[数据类型异常!]");
                            break;

                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    is.close();
}
计算公式 
@Test
public void testFormula() throws Exception {
    FileInputStream is = new FileInputStream("表名.xls");
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.getSheetAt(0);

    //读取第五行的第一列
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    //公式计算器
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    // 输出单元内容
    int cellType = cell.getCellType();
    switch (cellType) {
        case HSSFCell.CELL_TYPE_FORMULA: //2

            //得到公式
            String formula = cell.getCellFormula();
            System.out.println(formula);

            CellValue evaluate = formulaEvaluator.evaluate(cell);
            //String cellValue = String.valueOf(evaluate.getNumberValue());
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);

            break;
    }
}

EasyExcel 操作

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

Github：https://github.com/alibaba/easyexcel

1、导入依赖

注意：easyExcel依赖本身就依赖了 poi 和 poi-ooxml 两个包，注意避免冲突。

亲测：当我们排除 easyExcel 中的poi 和 poi-ooxml 两个包，会导致版本的冲突，找不到一些方法，这可能是依赖版本升级以后，有些方法被弃用导致的。最稳妥的做法就是将我们自己导入的 poi 那两个依赖去掉。

     <!-- slf4j -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <scope>compile</scope>
    </dependency>
    <!--fastJson-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.75</version>
    </dependency>
    <!-- easyExcel-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.0-beta2</version>
    </dependency>
    <!-- xls 03 -->
   <!-- <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>-->
    <!-- xlsx 07 -->
    <!--<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>-->

2、写入测试

demo：简单的写

image.png

  1. 创建实体
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

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

如果是在日常开发中，数据的来源往往是来自数据库、缓存等。

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;
}
  1. 写入文件

easyExcel 官方文档中给出了3种写入的操作方式，我这里选用的是最简单的。

@Test
public void testEasyExcelWrite(){
    
    String fileName = PATH + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    // fileName 文件写出的目录和工作簿名称
    // DemoData.class 写出对象的字节码文件
    // sheet("模板") 工作表名称
    // data() 写入到工作表的数据
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
  1. 生成结果

image.png

image.png

3、读取测试

demo：最简单的读

image.png

  1. 创建实体

可以继续使用刚刚写入时的 DemoData 实体。

  1. 配置监听
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个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) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}
  1. 持久层
/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}
  1. 简单读取
/**
     * 最简单的读
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>
     * 3. 直接读即可
     */
@Test
public void testSimpleRead(){
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法3：
    String fileName = PATH + "simpleWrite1632457706893.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
  1. 执行结果

image.png

小结：

固定套路:

1、写入，固定类格式进行写入

2、读取，根据监听器设置的规则进行读取!

3、了解面向对象的思想，学会面向接口编程！

文章标签：
云解析DNS
Java
easyexcel
Apache
Maven
数据库
缓存
存储
游客wazjug5ieii4a
目录
相关文章
VipSoft
|
3月前
|
easyexcel Java API
Apache POI、EasyPoi、EasyExcel 三种区别，如何选择
Apache POI、EasyPoi、EasyExcel 三种区别，如何选择
VipSoft
602 0
游客repv2zmhyosc2
|
6月前
|
easyexcel 计算机视觉
easyexcel
asd
游客repv2zmhyosc2
85 1
极客李华
|
6月前
|
easyexcel Java
EasyExcel的使用
EasyExcel的使用
极客李华
124 0
杨不易呀
|
前端开发 Java 索引
从零玩转EasyPoi
从零玩转EasyPoi
杨不易呀
522 0
是谢添啊
|
easyexcel API 数据安全/隐私保护
EasyExcel使用与详细说明，EasyExcel工具类（三）
EasyExcel使用与详细说明，EasyExcel工具类
是谢添啊
8860 0
是谢添啊
|
存储 JavaScript easyexcel
EasyExcel使用与详细说明，EasyExcel工具类（一）
EasyExcel使用与详细说明，EasyExcel工具类
是谢添啊
1257 0
是谢添啊
|
easyexcel
EasyExcel使用与详细说明，EasyExcel工具类（二）
EasyExcel使用与详细说明，EasyExcel工具类
是谢添啊
951 0
是洋不是阳
|
存储 easyexcel Java
EasyExcel
EasyExcel
是洋不是阳
346 0
日星月云
|
easyexcel Java API
POI及EasyExcel【Java提高】
POI及EasyExcel【Java提高】
日星月云
537 0
坚持学习永不言弃
POI学习笔记
POI学习笔记
坚持学习永不言弃
198 1