POI、Easy Excel操作Excel

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:

3.Excel在Java中是一个对象

在这里插入图片描述

    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

  1. 导入依赖
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.0-beta2</version>
</dependency>
  1. 数据来源:
  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;
    }
  1. 一行代码导出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 简单的读

参考:

  1. 先写一个类继承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("存储数据库成功!");
    }
}

  1. 测试:
    /**
     * 最简单的读
     * <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();



    }