• 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. 工作表
      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();
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53

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

    大文件写HSSF

    • 优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
    • 缺点：最多只能处理65535行，否则会抛出异常：
    java.lang.IlleaglArgumentException:Invalid row number(65535) outside allowable rang (0..65535)

    • 1
    • 代码截图：(没有自己写–自己没有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 + "秒");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    大文件写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 + "秒");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    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();

    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    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();

    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80

    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;


        }
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    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
    • 2
    • 3
    • 4
    • 5
    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    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());
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    1. 测试：
        /**
     * 最简单的读
     * 
    
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * 
    
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * 
    
     * 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();



    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
  • 相关阅读:
    如何用AR Engine开发一个虚拟形象表情包？
    机器人操作系统ROS（22）ROS安装opencv
    输运方程的推导
    SLAM从入门到精通（rviz自定义形状）
    人力资源行业HR从业现状，这份报告了解下
    前端用户体验设计：创造卓越的用户界面和交互
    ruoyi-vue前后端分离版本验证码实现思路
    数组的API
    23个react常见问题
    安全测试之w3af 安装
  • 原文地址：https://blog.csdn.net/fghjhdrf/article/details/133803437