Apache POI & EasyExcel

常用场景

  • 将用户信息导出为excel表格(导出数据。。。)
  • 将Excel表中的信息录入到网站数据库(习题上传。。)
  • 开发中经常会涉及到Excel的处理,如导出Excel,导入Excel到数据库中

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

Apache POI

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

image-20201028145941594

image-20201028150255858

EasyExcel

EasyExcel官网:https://github.com/alibaba/easyexcel

image-20201028151032836

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

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

内存问题:

  • POI - 数据先全部加载到内存OOM,再写入到文件。
  • EasyExcel - 一行一行写入文件

POI和EasyExcel对比:

img

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


1. POI-Excel写

1.1 创建项目

建立一个空项目poi-study,创建普通maven的Moudle poi-study-01

引入依赖

<?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>cn.codewei</groupId>
    <artifactId>poi-study-01</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>

03 | 07版本的写,就是对象不同,方法一样的!

==需要注意:2003和2007版本存在兼容性的问题!03最多只有65535行!==

image-20201028153830816

工作簿/工作表/行/列

image-20201028154426269

1.2 03版本写

/**
     * 03版测试
     * @throws Exception
     */
@Test
public void testWrite03() throws Exception {
    String path = "F:\\";
    // 创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 创建一个工作表
    Sheet sheet = workbook.createSheet("工作表1");
    // 创建一行
    Row row1 = sheet.createRow(0);

    // 创建一个单元格
    Cell cell11 = row1.createCell(0);
    // 设置单元格内容
    cell11.setCellValue("用户ID");

    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("用户名");

    // 创建第二行
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue(1);

    Cell cell22 = row2.createCell(1);
    cell22.setCellValue("codewei");

    // 生成一张表(IO流)
    FileOutputStream fileOutputStream = new FileOutputStream(path + "poitest.xls");
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    System.out.println("文件生成完毕");
}

1.3 07版本写

/**
     * 07版测试
     * @throws Exception
     */
@Test
public void testWrite07() throws Exception {
    String path = "F:\\";
    // 创建一个工作簿
    Workbook workbook = new XSSFWorkbook();
    // 创建一个工作表
    Sheet sheet = workbook.createSheet("工作表1");
    // 创建一行
    Row row1 = sheet.createRow(0);

    // 创建一个单元格
    Cell cell11 = row1.createCell(0);
    // 设置单元格内容
    cell11.setCellValue("用户ID");

    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("用户名");

    // 创建第二行
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue(1);

    Cell cell22 = row2.createCell(1);
    cell22.setCellValue("codewei");

    // 生成一张表(IO流)
    FileOutputStream fileOutputStream = new FileOutputStream(path + "poitest.xlsx");
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    System.out.println("文件生成完毕");
}

注意对象区别,文件后缀!

1.4 HSSF大文件写

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

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

image-20201029102137458

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

/**
 *  03版大文件测试
*/
@Test
public void testWrite03BigData() throws Exception {

    long start = 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(rowNum+","+cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream("f://testWrite03BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println("共花费了:"+(end-start)/1000+"s");
}

1.5 大文件写XSSF

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

优点:可以写较大的数据量,如20万条

/**
 * 07版大文件测试
*/
@Test
public void testWrite07BigData() throws Exception {

    long start = System.currentTimeMillis();
    // 创建工作簿
    Workbook workbook = new XSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0;rowNum<65537;rowNum++){
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0;cellNum<10;cellNum++){
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(rowNum+","+cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream("f://testWrite07BigData.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println("共花费了:"+(end-start)/1000+"s");
}

1.6 大文件写SXSSF

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

注意:

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

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

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

/**
 * 07版大文件升级版测试
*/
@Test
public void testWrite07SuperBigData() throws Exception {
    long start = 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(rowNum+","+cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream("f://testWrite07SuperBigData.xlsx");
    workbook.write(fileOutputStream);
    // 清除临时文件
    ((SXSSFWorkbook) workbook).dispose();
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println("共花费了:"+(end-start)/1000+"s");
}

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

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

再使用 POI的时候!内存问题 Jprofile!


2. POI-Excel读

2.1 03版本

@Test
public void testRead03() throws Exception{
    String path = "f://test03.xls";
    // 获取文件流
    FileInputStream in = new FileInputStream(path);
    // 读取到工作簿
    Workbook workbook = new HSSFWorkbook(in);
    // 根据索引得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 获取行
    Row row = sheet.getRow(0);
    // 获取单元格
    Cell cell = row.getCell(0);
    // 获取单元格的值
    // getStringCellValue:获取字符串
    // getNumericCellValue:获取数值
    // 读取值的时候一定要注意类型
    String str = cell.getStringCellValue();
    System.out.println(str);
    in.close();
}

2.2 07版本

@Test
public void testRead07() throws Exception {
    String path = "f://test07.xlsx";
    // 获取文件流
    FileInputStream in = new FileInputStream(path);
    // 读取到工作簿
    Workbook workbook = new XSSFWorkbook(in);
    // 根据索引得到工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 获取行
    Row row = sheet.getRow(0);
    // 获取单元格
    Cell cell = row.getCell(0);
    // 获取单元格的值
    // getStringCellValue:获取字符串
    // getNumericCellValue:获取数值
    // 读取值的时候一定要注意类型
    String str = cell.getStringCellValue();
    System.out.println(str);
    in.close();
}

2.3 读取不同的数据类型

@Test
public void testCellType() throws Exception{
    String path = "f://test03.xls";
    // 获取文件流
    FileInputStream in = new FileInputStream(path);
    // 读取到工作簿
    Workbook workbook = new HSSFWorkbook(in);
    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 physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    for(int rowNum = 1;rowNum<physicalNumberOfRows;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();
                    Object cellValue = "";
                    switch (cellType){
                        case STRING:
                            System.out.print("【String】");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            System.out.print("【Boolean】");
                            cellValue = cell.getBooleanCellValue();
                            break;
                        case BLANK:
                            System.out.print("【Blank】");
                            break;
                        case NUMERIC:
                            System.out.print("【Numeric】");
                            if (HSSFDateUtil.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);
                                cellValue = cell.toString();
                            }
                            break;
                        case ERROR:
                            System.out.print("【Error】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
            System.out.println();
        }
    }
    in.close();
}

测试

image-20201029115253127

2.4 计算公式

@Test
public void testFormula() throws Exception{
    FileInputStream in = new FileInputStream("f://公式.xlsx");
    Workbook workbook = new XSSFWorkbook(in);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);
    // 拿到计算公式
    FormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook)workbook);
    // 输出单元格的内容
    CellType cellType = cell.getCellType();
    switch (cellType){
        case FORMULA:
            // 公式
            String cellFormula = cell.getCellFormula();
            System.out.println(cellFormula);

            // 计算
            CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);
            break;
    }
    in.close();
}

测试

image-20201029115219780


3. EasyExcel操作

3.1 导入依赖

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

3.2 写入测试

https://www.yuque.com/easyexcel/doc/write

DemoData.java

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

测试写入数据

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 = "f://EasyExcelTest.xlsx";
    // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

3.3 读取测试

https://www.yuque.com/easyexcel/doc/read

DemoDataListener

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);

    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();

    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 读取数据会执行invoke方法
     * DemoData 类型
     * AnalysisContext 分析上下文
     * @param data
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(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("存储数据库成功!");
    }
}

DemoDAO

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

测试

/**
* 最简单的读
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
* <p>3. 直接读即可
*/
@Test
public void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    String fileName = "f://EasyExcelTest.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

}

固定套路:

  • 写入:固定类格式写入
  • 读取:根据监听器设置的规则进行读取
本文作者: Author:     文章标题: Apache POI & EasyExcel 快速使用
本文地址: https://codewei.cn/archives/214/      
版权说明:若无注明,本文皆为“阿伟的小屋”原创,转载请保留文章出处
Last modification:October 29th, 2020 at 02:51 pm
贫困山区儿童,谢谢打赏