POI、Easy Excel操作Excel

文章目录

1.常用的场景
2.基本功能
3.Excel在Java中是一个对象
4. 简单的写（07版本（.xlsx）Excel）
5. Excel读
- 5.1 读取遇到类型转化问题该怎么解决
- 5.2 遇到Excel公式怎么办
6. Easy Excel
- 6.1简单的写
- 6.2 简单的读

1.常用的场景

1. 将用户信息导出为Excel表格（导出数据）
1. 将Excel表中的信息录入到网站数据库中（习题上传）大大减轻网站录入量。开发中经常会用到设计处理Excel，如导入Excel和导出Excel
1. 目前操作Excel比较流行的就是Apach的POI和阿里巴巴的Easy Excel。
1. POI官网：https://poi.apache.org/
1. Easy Excel官网：https://github.com/alibaba/easyexcel

2.基本功能

1.POI的基本功能：（会比较麻烦，可能会出现OOM（Out Of Memory）异常）

其中：第一个HSSF和XSSF的区别（前者用03版本的Excel，后者用07年的Excel）
- 1.1 内存问题：
  POI 当数据为100w的时候，先将100w的数据先加载到内存当中（OOM），再写入文件
1. Easy Excel:
- 2.1 简介，官方文档：(已经迁移到)https://easyexcel.opensource.alibaba.com/docs/current/
- 2.2 内存问题：
  相对于POI来说，上图反映了POI会把所有内容加载到内存当中，而Easy Excel会一行一行通过磁盘返回。

3.Excel在Java中是一个对象

在这里插入图片描述

1. 工作簿
1. 工作表
1. 行
1. 列

4. 简单的写（07版本（.xlsx）Excel）

03版本代码中有标注，生成文件时候，需要改后缀为：(.xls)

package com.dapeng;

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.streaming.SXSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

/**
 * @Description
 * @Author zhaopeng
 * @Date 2023/10/13 9:49
 */
public class ExcelWriteTest {
    @Test
    public void testWrite() throws Exception {
        String PATH = "D:\ExcelTest\";
        // 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)
        // Workbook workbook = new HSSFWorkbook();03版本
        Workbook workbook = new SXSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet = workbook.createSheet("我的工作表");
        // 3. 创建一行(0代表从第一行开始创建)
        Row row1 = sheet.createRow(0);
        // 4. 创建一列(0代表从第一列开始创建),Cell代表一个单元格
        Cell cell11 = row1.createCell(0);
        Cell cell12 = row1.createCell(1);
        // 5. 给单元格赋值
        cell11.setCellValue("测试数据1-1");
        cell12.setCellValue("Cell12数据");

        // 来个第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        // 来个时间
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell21.setCellValue(time);
        cell22.setCellValue("第二行第二个数据");

        // 生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "test.xlsx");
        workbook.write(fileOutputStream);

		fileOutputStream.close();
    }
}

效果：
在这里插入图片描述

大文件写HSSF

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
缺点：最多只能处理65535行，否则会抛出异常：

java.lang.IlleaglArgumentException:Invalid row number(65535) outside allowable rang (0..65535)

代码截图：(没有自己写–自己没有03版本的excel)
当超过65536 的时候会报错

大文件写XSSF

优点：可以写较大的数据量，如20万条。
缺点：写数据时非常慢，非常耗内存，也会发生内存溢出，如100万条。

  @Test
    public void testWrite07BigData() throws Exception {
        long startTime = System.currentTimeMillis();
        // 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)
        Workbook workbook = new XSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet = workbook.createSheet("我的工作表");
        // 3.循环写入大数据
        for (int i = 0; i < 100000; i++) {
            // 创建行
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                // 创建列j
                Cell cell = row.createCell(j);
                // 给列赋值
                cell.setCellValue(j);
            }
        }
        System.out.println("完事了");

        // 生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        long end = System.currentTimeMillis();
        System.out.println("花费了：" + (double)(end - startTime)/1000 + "秒");
    }

大文件写SXSSF

优点：可以写非常大的数据量，如100万条甚至更多条，写数据快，占用更少的内存。
Demo（没有多大变化，只用了SXSSFWorkbook类）

 @Test
    public void testWrite07BigData() throws Exception {
        long startTime = System.currentTimeMillis();
        // 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)
        Workbook workbook = new SXSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet = workbook.createSheet("我的工作表");
        // 3.循环写入大数据
        for (int i = 0; i < 1000000; i++) {
            // 创建行
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                // 创建列j
                Cell cell = row.createCell(j);
                // 给列赋值
                cell.setCellValue(j);
            }
        }
        System.out.println("完事了");

        // 生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
  		 // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println("花费了：" + (double)(end - startTime)/1000 + "秒");
    }

5. Excel读

Demo:

package com.dapeng;

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.junit.Test;

import java.io.FileInputStream;

/**
 * @Description
 * @Author zhaopeng
 * @Date 2023/10/13 10:43
 */
public class ExcelReadTest {
    String PATH = "D:\ExcelTest\";

    @Test
    public void testRead() throws Exception{
        // 1. 获取文件流读取文件
        FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");
        // 2. 创建一个工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3. 得到表(通过下标);
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 得到行(第一行)
        Row row = sheet.getRow(0);
        // 5. 获取第一个数据
        Cell cell = row.getCell(0);
        // 6.输出数据
        System.out.println(cell.getStringCellValue());

        inputStream.close();

    }
}

5.1 读取遇到类型转化问题该怎么解决


    @Test
    public void testReadTeyp() throws Exception {
        // 1. 获取文件流读取文件
        FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");
        // 2. 创建一个工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3. 得到表(通过下标);
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取第一行标题
        Row rowTitle = sheet.getRow(0);
        //      4.1 获取标题的数量
        if (rowTitle != null) {
            // 必须掌握，拿到所有的列
            int cells = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i < cells; i++) {
                Cell cell = rowTitle.getCell(i);
                if (cell != null) {
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
        }
        // 获取行的总数
        int rowCounts = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCounts; rowNum++) {
            // 获取每一行
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                // 读取列
                int columns = rowTitle.getPhysicalNumberOfCells();
                for (int cellColumn = 0; cellColumn < columns; cellColumn++) {
                    System.out.print("[" + rowNum + "-" + cellColumn + "]");
                    // 获取每一个数据
                    Cell cell = row.getCell(cellColumn);
                    // 匹配列数据类型
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";

                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔类型
                                System.out.print("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                System.out.print("[BLANK]");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字(日期和数字)
                                System.out.print("[NUMERIC]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是日期类型
                                    System.out.print("[日期]");
                                    Date time = cell.getDateCellValue();
                                    // 使用工具类把日期进行转换
                                    cellValue = new DateTime(time).toString("yyyy-MM-dd HH:mm:ss");
                                } else {
                                    // 如果不是日期格式，防止数字过长
                                    System.out.print("[转换为字符串输出]");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 错误
                                System.out.print("[数据类型错误]");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }

        inputStream.close();

    }

5.2 遇到Excel公式怎么办

   @Test
    public void readMath() throws Exception{
        FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");

        // 读取
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 这个单元格前提有公式比如=SUM(A2:A4);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        // 1.拿到该工作簿的公式
        XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);


        // 输出单元格内容,先获取类型
        int cellType = cell.getCellType();
        switch (cellType){
            case HSSFCell.CELL_TYPE_FORMULA: //公式
                // 先获取公式
                String formula = cell.getCellFormula();
                System.out.println(formula); // 输出公式

                // 计算得到值
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                System.out.println(evaluate);// 输出计算好的值
                String value = evaluate.formatAsString();// 把值转化为String输出
                System.out.println(value);
                break;


        }
    }

6. Easy Excel

6.1简单的写

参考官方文档：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write

导入依赖

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.0-beta2</version>
</dependency>

数据来源：

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

一行代码导出Excel

 // 根据List写入值
    @Test
    public void simpleWrite(){
        // 1. 当前的位置
        String PATH = "D:\ExcelTest\";
        String fileName= PATH + "esayExcel.xlsx";
        EasyExcel.write(fileName,Entity.class).sheet("easyExcel").doWrite(data());
    }

6.2 简单的读

参考：

先写一个类继承AnalysisEventListener并重写invoke方法，数据的处理逻辑全在这里。

package com.dapeng.DemoEntity;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

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

/**
 * @Description
 * @Author zhaopeng
 * @Date 2023/10/13 15:31
 */
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener extends AnalysisEventListener<DemoData> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<>();

    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new 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));
        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();
        log.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        log.info("存储数据库成功！");
    }
}

测试：

    /**
     * 最简单的读
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>
     * 3. 直接读即可
     */
    @Test
    public void simpleRead() {
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String PATH = "D:\ExcelTest\";
        String fileName= PATH + "esayExcel.xlsx";
        // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
        // 具体需要返回多少行可以在`PageReadListener`的构造函数设置
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();



    }