POI、Easy Excel操作Excel

已于 2023-10-18 14:10:17 修改
阅读量1k 收藏 8
点赞数 6
文章标签： excel
于 2023-10-13 15:59:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fghjhdrf/article/details/133803437
版权

文章目录

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

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

大文件写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();



    }
爱折磨键盘的大鹏
关注
  • 6
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Excel导入导出利器——easy-poi
qq_45337431的博客
12-14 2115
主要介绍了easy-poi的使用
POI与EasyExcle
jackroo12138的博客
03-30 248
POI及EasyExcel 所用场景： 1.将用户信息导出为excel表格（导出数据) 2.将Excel表中的信息录入到网站数据库（习题上传）大大减轻网站录入量 开发中会经常设计到excel的处理，如导出Excel，导入Excel到数据库中 POI简介 Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对[Microsoft Office](https://baike.baidu.com/item/Microsoft Office)格式
后端常用技能：基于easy-poi实现excel一对多、多对多导入导出【附带源码】
最新发布
55555的博客
05-08 1777
easy-poi是一个基于Apache POI的Java端Excel 操作工具库，目的是为了简化java程序对excel文件的操作。该库提供了简单的API接口，支持excel的读写、格式化等，以及excel数据导出到pdf、word等文件。easypoi-base 是 easypoi的核心模块，提供了基本的 Excel 处理功能，如读取、写入、转换等。它不依赖于 Spring Boot，可以在任何 Java 项目中使用。
快速学习-EasyExcel背景介绍
逍遥云恋
10-23 804
easyexcel要去解决的问题 Excel读写时候内存溢出 虽然POI是目前使用最多的用来做excel解析的框架，但这个框架并不那么完美。大部分使用POI都是使用他的userModel模式。userModel的好处是上手容易使用简单，随便拷贝个代码跑一下，剩下就是写业务转换了，虽然转换也要写上百行代码，相对比较好理解。然而userModel模式最大的问题是在于非常大的内存消耗，一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式，之所以还正常在跑一定是并发不大，并发上来后一定会OOM或者频繁的f
EasyPoi、EasyExcel
没有简介
01-24 1332
EasyPoi、EasyExcel
apache POI ，easy excel详细教程
weixin_46255402的博客
10-14 306
apachepoi easyexcel
使用POI筛选字段导出Excel，以及条件查询和指定数据导出
05-27
使用POI筛选字段导出Excel，以及条件查询和指定数据导出
poi导出excel生成下拉列表
08-20
poi作为导出excel常用的工具，方便快捷。对于excel指定下拉列表的列，如何生成呢？本文提供如何生成下拉列表的excel列
POI读取Excel合并单元格并储存
01-22
Java操纵POI解析Excel表，根据表头将内容储存。通过实体类set、get方便其它方法引用
利用poi把excel数据批量导入mysql
10-23
利用poi获取excel中大量数据，然后利用jdbc批量插入到mysql，直接运行main方法
POI导出带图片的excel
05-18
利用POI技术将图片插入到excel中，希望对大家有用，直接运行ExportExcel类，即可看到效果！！！
POI和EasyExcel
HGW的博客
08-30 420
操作 Excel 目前比较流行的就是 Apache POI 和 阿里巴巴的 easyExcel！
阿里EasyExcel让你彻底告别easypoi
码眼的博客
10-27 4604
为什么说EasyExcel可以让你告别easypoi呢？在说这个问题之前我们先来了解下easypoi easypoi easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法 这是easypoi官方给出的定义，使用这个工具后发现在进行excel的导入导出时，的确很方便。特别是一些简单的excel 比如这种简单的.
Poi、EasyExcel、EasyPOI导入EXCEL文档的实现方案有何区别
热门推荐
AllenJoe666的博客
06-05 1万+
EasyExcel、EasyPOI导入EXCEL文档的实现方案有何区别 一.easyPoi的excel导出 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("日程主表", "日程"), AgendaExcelVO.class, agendaExcels); 其中ExportParams第一个参数是表名,第二个参数是...
POI及EasyExcel
Jin的随手笔记
03-27 383
将用户信息导出为excel表格 讲Excel表中的信息录入到网站数据库，大大减小网站数据的录入量！ 开发中经常会涉及到excel的处理，如导出Excel到数据库中！ 操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel
JAVA Excel导入导出技术选型（Apache POI、EasyPoi、EasyExcel）
chang_mao的博客
01-31 1748
是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。Apache POI是java原生的，可以精细到对每个表单，每个单元格进行操作，封装度较低，使用起来需写大量的代码，并且性能较低，可以入门使用，这里不详细介绍。EasyPoi和EasyExcel都是基于Apache POI进行二次开发的，都做了封装处理使用起来很方便。
JAVA操作Excel（POI、easyPOI、easyExcel）
我认不到你的博客
07-27 4697
一、支持文档类型不同1、Excel03：只支持xls类型的文档。2、Excel07：除了支持xls类型文档，还支持xlsx类型的文档。二、功能不同1、Excel03：将智能标记操作与部分电子表格的特定内容关联，并使适当的智能标记操作仅在用户将鼠标悬停在关联的单元格区域上时出现。2、Excel07：菜单、工具条已经成为历史，取而代之的是功能区，大量图标和命令组织到多个选项卡中，形成带状区域，还有快速访问工具栏、画廊等。三、特点不同1、Excel03：使用 Excel 2003 在任何客户定义的 XML 架构中
poi + esayExcel 学习笔记
paleatta的博客
08-21 304
学习视频 随看随记 poi和easyExcel的主要区别在于poi之大量数据存入内存写入excel，easyExcel是将数据一条一条的存到表格中。 写入操作 少量数据 03版本使用HSSFWorkbook，07版本使用XSSFWorkbook 03版本文件格式是xls，07文件格式xlsx PATH地址最后要注意加\，否则和文件名直接拼接字符串导致输出位置错误。 填充数据是从工作簿->工作表->行->列->数据 行和列在java中都是从0开始的，而不是像表格中的从1开始 p
easy excel事务
12-24
Easy Excel是一种简单易用的Excel事务模板，它可以帮助用户更加高效地进行数据分析和管理。用户可以通过Easy Excel事务模板来创建图表、制作报表、进行数据筛选和排序等操作，从而更好地理解和利用所需的数据信息。 Easy Excel事务模板具有简单直观的界面和丰富实用的功能，用户可以通过简单的拖拽和点击操作就能完成各种数据处理任务，无需深入学习复杂的Excel公式和函数。同时，Easy Excel还提供了丰富的数据分析工具，用户可以通过这些工具来进行数据透视、多维分析、数据关联等操作，帮助用户更加深入地挖掘数据背后的价值。 除此之外，Easy Excel还提供了丰富的模板和样式库，用户可以通过选择模板和应用样式来快速创建符合要求的报表和图表，极大地提升了数据展示的效率和美观度。 总的来说，Easy Excel事务模板是一种简单易用的数据处理工具，它能够帮助用户高效地进行数据分析和管理，提升数据处理效率和结果展示效果。不论是数据分析新手还是资深分析师，都能够通过Easy Excel轻松处理各种数据事务。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱折磨键盘的大鹏

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值