POI、Easy Excel操作Excel

news/2024/7/21 7:33:15 标签: excel

文章目录

  • 1.常用的场景
  • 2.基本功能
  • 3.Excel在Java中是一个对象
  • 4. 简单的写（07版本（.xlsx）Excel）
    • 大文件写HSSF
    • 大文件写XSSF
    • 大文件写SXSSF
  • 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

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



    }

http://www.niftyadmin.cn/n/5094423.html

相关文章

c# 用非递归的写法实现递归

c# 用非递归的写法实现递归

最近写代码碰到了一个bug&#xff0c;就是递归次数太多爆堆栈了&#xff0c;然后就写了一个递归工具来解决这个问题。 using System; using System.Collections.Generic;/// <summary> /// 递归工具 /// </summary> public static class RecursionTool {//递归方式…
阅读更多...
PTA： 队列的实现及基本操作

PTA： 队列的实现及基本操作

队列的实现及基本操作 题目输入格式输出格式输入样例输出样例 思路代码 题目 给定一个初始为空的队列和一系列入队、出队操作&#xff0c;请编写程序输出每次出队的元素。队列的元素值均为整数。 输入格式 输入第1行为1个正整数n&#xff0c;表示操作个数&#xff1b;接下来…
阅读更多...
可阅读随机字符串与随机字符串

可阅读随机字符串与随机字符串

1、将创建一个可阅读的字符串&#xff0c;使其更接近词典中的单词&#xff0c;实用且具有密码验证功能。 /** * param length - length of random string (must be a multiple of 2) * 参数length-随机字符串的长度&#xff08;必须是2的倍数&#xff09; */ function re…
阅读更多...
注册大量短视频矩阵账号很简单，这个方法教会你，还有这个批量剪辑神器帮你完成矩阵分发

注册大量短视频矩阵账号很简单，这个方法教会你，还有这个批量剪辑神器帮你完成矩阵分发

一个人一天就能注册上百个抖音实名账号&#xff0c;那么是如何做到的呢&#xff1f;其实很简单&#xff0c;只需要一个营业执照就能办到。 一个营业执照可以点亮两个蓝v号&#xff0c;每个蓝v账号可以注册50个员工账号。 员工账号和个人号的实名是不冲突的&#xff0c;所以之前…
阅读更多...
解决table表格td中文字过长自动换行问题

解决table表格td中文字过长自动换行问题

网上有很多说可以解决的&#xff0c;大致有以下几种。 1、在table中加table-layout:fixed; 2、在td中加word-wrap:break-word;word-break:break-all; 对我遇到的问题来说&#xff0c;没用&#xff0c;可能是因为框架样式冲突的原因 解决方法如下&#xff1a; 在显示长内容…
阅读更多...
Disney验厂要求、验厂流程及审核注意事项

Disney验厂要求、验厂流程及审核注意事项

【Disney验厂要求、验厂流程及审核注意事项】 Disney验厂是迪士尼公司对供应商工厂的审核和评估&#xff0c;以确保其符合迪士尼公司的社会责任标准和道德要求。Disney验厂是迪士尼供应链管理中的重要环节&#xff0c;旨在确保供应商遵守道德和法律规定&#xff0c;以及迪士尼公…
阅读更多...
如何进行开关电源能效（效率）测试？纳米软件电源测试系统如何助力？

如何进行开关电源能效（效率）测试？纳米软件电源测试系统如何助力？

开关电源在实际工作中输入与输出转换是有一定的能量损耗的&#xff0c;并不会完全无损耗转换&#xff0c;电源效率就是用来衡量能量转换损耗的一个重要指标。那么开关电源效率要如何测试呢? 电源效率是指输出功率与输入功率之比&#xff0c;计算公式如下&#xff1a; ​​​​…
阅读更多...
【考研数学】概率论与数理统计 —— 第五章 | 大数定律与中心极限定理

【考研数学】概率论与数理统计 —— 第五章 | 大数定律与中心极限定理

文章目录 一、切比雪夫不等式二、大数定律2.1 依概率收敛的定义2.2 常见的大数定律 三、中心极限定理写在最后 一、切比雪夫不等式 定理 1 —— &#xff08;切比雪夫不等式&#xff09;设随机变量 X X X 的方差存在&#xff0c;则对任意的 ε > 0 \varepsilon>0 ε&g…
阅读更多...
最新文章

Copyright @ 2022~2023