POI -- 01 -- Excel大数据读取

最新推荐文章于 2024-05-20 12:59:12 发布
最新推荐文章于 2024-05-20 12:59:12 发布
阅读量954 收藏 3
点赞数
分类专栏： POI 文章标签： poi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Goodbye_Youth/article/details/90759157
版权

原文链接：POI – 01 – Excel大数据读取

相关文章：

在日常的开发过程中，当涉及到 Excel 报表导出的时候，我们通常会使用 POI 来进行处理

POI 是 Apache 软件基金会下一个开放源码的函式库，提供了相关 API 供 Java 程序对 Microsoft Office 文档进行读写操作，POI 提供了两种读写 API 模型，分别是：用户模型 (usermodel) 和事件模型 (eventmodel)

在这里插入图片描述

  • 用户模型

    • 基于内存树 (memory tree) 的方式实现，使用 DOM 对 Excel 进行解析

    • 对 CPU 和内存的消耗较大，但可以通过面向对象的方式来进行操作，使用简单，可读可写

  • 事件模型

    • 基于流 (stream) 的方式实现，使用 SAX (Simple API for XML) 模型对 Excel 进行解析

    • 对 CPU 和内存的消耗较小，但使用复杂，且无法进行写操作

  • 在对大数据 Excel 的处理上，如果使用用户模型的话，操作起来的话就比较耗费时间了，甚至可能会导致内存溢出，因此针对大数据的读取，我们最好采用事件模型来处理

官方文档：POI-HSSF and POI-XSSF/SXSSF

官方文档：Event API (HSSF Only)

一、Excel 工具类

  • 工作表处理器

    public class SheetHandler extends DefaultHandler {

    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;
    /**
     * 单元格内容
     */
    private String lastContents;
    /**
     * 字符串标识
     */
    private boolean nextIsString;
    /**
     * 单元格位置
     */
    private String cellPosition;
    /**
     * 单元格数据（单元格位置 + 单元格内容）
     */
    private LinkedHashMap<String, String> cellContents = new LinkedHashMap<>();

    public LinkedHashMap<String, String> getCellContents() {
        return cellContents;
    }

    public void setCellContents(LinkedHashMap<String, String> cellContents) {
        this.cellContents = cellContents;
    }

    public void createCellContents() {
        this.cellContents = new LinkedHashMap<>();
    }

    public SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // 判断标签名是否为c (c表示单元格)
        if ("c".equals(name)) {
            // 获取单元格的r属性 (r表示单元格位置)
            cellPosition = attributes.getValue("r");
            // 获取单元格的t属性 (t表示单元格类型；注意：类型为数字、日期的单元格没有t属性)
            String cellType = attributes.getValue("t");
            // 判断单元格类型是否为字符串 (s表示字符串)
            if (cellType != null && "s".equals(cellType)) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // 清除缓存内容
        lastContents = "";
    }

    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        if (nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        // 判断标签名是否为v (v表示单元格内容)
        if ("v".equals(name)) {
            // 通常情况下，第一行为表头，我们可以将其去掉
            boolean headLine = cellPosition.length() == 2 && !"1".equals(cellPosition.substring(1));
            boolean dataLine = cellPosition.length() != 2;
            if (headLine || dataLine){
                cellContents.put(cellPosition, lastContents);
            }
        }
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

  • Excel 导入工具类

    public class LargeExcelImportUtil {

    /**
     * sheet处理器
     */
    private SheetHandler sheetHandler;
    /**
     * 单元格数据（单个sheet）
     */
    private LinkedHashMap<String, String> cellContents = new LinkedHashMap<>();
    /**
     * 单元格数据（多个sheet）
     */
    private LinkedHashMap<String, LinkedHashMap<String, String>> allCellContents = new LinkedHashMap<>();

    public SheetHandler getSheetHandler() {
        return sheetHandler;
    }

    public void setSheetHandler(SheetHandler sheetHandler) {
        this.sheetHandler = sheetHandler;
    }

    public LinkedHashMap<String, String> getCellContents() {
        return cellContents;
    }

    public void setCellContents(LinkedHashMap<String, String> cellContents) {
        this.cellContents = cellContents;
    }

    public LinkedHashMap<String, LinkedHashMap<String, String>> getAllCellContents() {
        return allCellContents;
    }

    public void setAllCellContents(LinkedHashMap<String, LinkedHashMap<String, String>> allCellContents) {
        this.allCellContents = allCellContents;
    }

    /**
     * 处理单个sheet
     *
     * @param filePath
     * @throws Exception
     */
    public void processOneSheet(String filePath) {
        InputStream sheet = null;
        OPCPackage pkg = null;
        try {
            pkg = OPCPackage.open(filePath);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            XMLReader parser = fetchSheetParser(sst);
            // rId1表示当前Excel的第一个sheet
            sheet = r.getSheet("rId1");
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            setCellContents(sheetHandler.getCellContents());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pkg != null) {
                    pkg.close();
                }
                if (sheet != null) {
                    sheet.close();
                }
            } catch (Exception e) {  
                e.printStackTrace();  
            } 
        }
    }

    /**
     * 处理多个sheet
     * 
     * @param filePath
     * @throws Exception
     */
    public void processAllSheets(String filePath) {
        OPCPackage pkg = null;
        InputStream sheet = null;
        try{
            pkg = OPCPackage.open(filePath);
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            XMLReader parser = fetchSheetParser(sst);
            Iterator<InputStream> sheets = r.getSheetsData();
            int count = 1;
            LinkedHashMap<String, LinkedHashMap<String, String>> allCellContents = new LinkedHashMap<>();
            while (sheets.hasNext()) {
                sheet = sheets.next();
                InputSource sheetSource = new InputSource(sheet);
                sheetHandler.createCellContents();
                parser.parse(sheetSource);
                setCellContents(sheetHandler.getCellContents());
                allCellContents.put("sheet" + count, cellContents);
                count++;
            }
            setAllCellContents(allCellContents);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pkg != null) {
                    pkg.close();
                }
                if (sheet != null) {
                    sheet.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取sheet解析器
     *
     * @param sst
     * @return
     * @throws SAXException
     */
    private XMLReader fetchSheetParser(SharedStringsTable sst) {
        XMLReader parser = new SAXParser();
        setSheetHandler(new SheetHandler(sst));
        parser.setContentHandler(sheetHandler);
        return parser;
    }
}

二、导入测试

  • 需要自行准备一个有 10w 条数据的 Excel

  • 处理单个 sheet

    public static void main(String[] args) {
    long start = System.currentTimeMillis();
    LargeExcelImportUtil example = new LargeExcelImportUtil();
    example.processAllSheets("C:\\Users\\Qi\\Desktop\\Test.xlsx");
    LinkedHashMap<String, String> cellContents = example.getCellContents();
    int count = 0;
    for (Map.Entry<String, String> entry : cellContents.entrySet()) {
        System.out.println(entry.getKey() + " ===> " + entry.getValue());
        count++;
    }
    long end = System.currentTimeMillis();
    System.out.println("共处理【" + count + "】条数据，共耗时【" + (end - start) + "】毫秒" );
}

// 共处理【100000】条数据，共耗时【3060】毫秒

  • 处理多个 sheet

    public static void main(String[] args) {
    long start = System.currentTimeMillis();
    LargeExcelImportUtil example = new LargeExcelImportUtil();
    example.processAllSheets("C:\\Users\\Qi\\Desktop\\Test.xlsx");
    LinkedHashMap<String, LinkedHashMap<String, String>> allCellContents = example.getAllCellContents();
    int count = 0;
    for (Map.Entry<String, LinkedHashMap<String, String>> mapEntry : allCellContents.entrySet()) {
        System.out.println(mapEntry.getKey());
        for (Map.Entry<String, String> entry : mapEntry.getValue().entrySet()) {
            System.out.println(entry.getKey() + " ===> " + entry.getValue());
            count++;
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("共处理【"+count+"】条数据，共耗时【"+(end - start)+"】毫秒" );
}

// 共处理【300000】条数据，共耗时【4997】毫秒

  • 从上面的测试中，我们可以看出，采用事件模型来读取大数据的 Excel 时，速度是相当之快的

三、解析

  • 在使用事件模型的过程中我们会发现出现了许多莫名其妙的标签，不知道是从哪里来的，刚开始看官方文档的时候一脸茫然，后来了解到其实是将 Excel 转换成了一个 xml 文件，那些标签都是 xml 文件中的节点，下面我们来讲讲如何查看这个 xml 文件

    • 将 Excel 文件后缀改为 .zip

    • 双击打开压缩文件，里面会有一个 worksheets 文件夹

      在这里插入图片描述

    • 进入 worksheets 文件夹后会有一个 sheet1.xml 文件 (即当前工作表)

      在这里插入图片描述

    • sheet1.xml 里存放了 Excel 中的所有数据，格式如下

      <c r="A2" s="3" t="s"><v>1</v></c>

      • c 标签表示单元格，r 属性表示是哪个单元格

      • s 属性暂时不清楚什么含义，t 属性表示单元格类型，v 标签表示单元格内容

  • 通过上面几个步骤，我们就可以很清楚的知道了这些标签的由来，使用起来也就更加方便了

  • PS：通常情况下，我们会通过 web 页面上传 Excel 进行解析，那么问题来了，上面用到的方法解析的是 Excel 文件的绝对地址，那我们该怎么办呢？

  • 不用着急，OPCPackage.open() 这个方法还有个重载体可以接收一个 File 对象

    public void processOneSheet(File file) {
    InputStream sheet = null;
    OPCPackage pkg = null;
    try {
        pkg = OPCPackage.open(file);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        // rId1表示当前Excel的第一个sheet
        sheet = r.getSheet("rId1");
        InputSource sheetSource = new InputSource(sheet);
        parser.parse(sheetSource);
        setCellContents(sheetHandler.getCellContents());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (pkg != null) {
                pkg.close();
            }
            if (sheet != null) {
                sheet.close();
            }
        } catch (Exception e) {  
            e.printStackTrace();  
        } 
    }
}

  • 此外，由于后端接收到的文件格式为 MultipartFile 类型的，不是 File 类型，所以需要进行转换，步骤如下

    @PostMapping(value = "/import")
public void importUserAndEle(@RequestParam("excel") MultipartFile excel) {
    try {
        // 用uuid作为文件名，防止生成的临时文件重复
        final File excelFile = File.createTempFile(UUID.randomUUID().toString(), ".xlsx");
        // MultipartFile to File
        excel.transferTo(excelFile);
        LargeExcelParseUtil largeExcelParseUtil = new LargeExcelParseUtil();
        largeExcelParseUtil.processOneSheet(excelFile);
        deleteFile(excelFile);
        LinkedHashMap<String, String> cellContents = largeExcelParseUtil.getCellContents();
        
        // ......
        
    } catch (Exception e) {
       e.printStackTrace();
    }
}

private void deleteFile(File... files) {
    for (File file : files) {
        if (file.exists()) {
            file.delete();
        }
    }
}
    • 如上所示，将 MultipartFile 转换为 File 后，再调用 processOneSheet()方法，然后通过 getCellContents() 方法就可以获得数据源了，剩下的就是处理自身的业务逻辑了
Gene Xu
关注
专栏目录
通用的POI导入Excel解决方案-ExcelUtilTest
03-27
在项目中，经常免不了要导入、导出Excel，导出Excel稍微简单点，制作一张模板，然后将查询之后的数据写入到模板中即可，导出程序可以做到一次写好，基本上就可以通用。 但导入Excel相对麻烦点，一般情况下，导入的程序无法通用的主要原因是：对于不同的导入模板，字段field与单元格cellRef的映射关系是不相同的，这就导致对每一个导入模板都要去写一套相似的代码；我们应该想办法解决这个问题，减少每次导入Excel时都去拷贝粘贴相同的代码。
apache-poi-4.1.2的一些必要库
02-20
- **使用SXSSF处理大数据**：当处理大量数据时，使用SXSSF可以减少内存占用，因为它采用滑动窗口模型，只保留最近的数据在内存中。 - **合理使用样式对象**：创建样式对象后应重复使用，避免频繁创建导致性能下降...
一个使用POI读取excel（.xlsx）文件的 Util
qq_37759376的博客
09-20 236
【代码】一个使用POI读取excel（.xlsx）文件的 Util。
poi的使用导入Excel表格的工具类封装到JavaBean对象之中
sinat_38259539的博客
11-26 2777
经过几天的不眠不夜的奋战（哈哈，开个玩笑），咳咳正经点，这几天自己封装了一个Excel表格的导入（本人是菜鸟所以是几天，经过大量测试，和自己的需求，如果有其它的表格形式需要自己改写），我在这里做一个笔记，仅供自己以后忘了，没有别的意图。偶对了，是用泛型+反射进行封装的、下面活不多说上代码。 1、先编写工具类 package com.huayi.dayan.util; import org....
POI-Excel导入导出开发文档
奔腾游子
04-14 661
POI-Excel导入导出开发文档 此处导入导出功能为集成Jeecg-boot开源项目中的功能，在此基础上移植到项目中。 1、在pom.xml中引入添加依赖 <dependency> <groupId>org.jeecgframework</groupId> <artifactId>autopoi-web</artifactId> <version>1.0.1</version> </depe
Excel快速导入工具类
最新发布
嗯哼？是可乐啊~
05-20 1850
本本介绍了ExcelImportUtils工具类通过ExcelImportField注解进行Excel数据解析、数据填充到Bean中，简化了数据解析过程。通过ExcelImportFieldFormat接口，灵活转换数据格式。
poi-4.0.1.jar
12-04
4. **公式支持**：POI支持读取和写入Excel公式。对于写入，你需要使用`FormulaEvaluator`来计算公式的结果并将其设置为单元格值。对于读取，POI会自动处理公式并返回计算结果。 5. **样式和格式**：POI提供了`...
poi-bin-5.1.0.zip（只要2积分）
11-18
Apache POI是一个流行的开源Java库，它允许开发者创建、修改和读取Microsoft Office格式的文件，如Excel（.xlsx和.xls）、Word（.docx）和PowerPoint（.pptx）。"poi-bin-5.1.0.zip"是Apache POI项目的5.1.0版本的二...
poi-util.zip
12-16
尽管 poi-util 工具类可能默认使用了常规的 HSSFWorkbook 或 XSSFWorkbook，但在处理大数据时，你可以根据需要切换到 SXSSF。 总结，"poi-util.zip" 提供了一个便捷的接口，让 Java 开发者能够快速地实现 Excel ...
poi-3.8-20120326.jar 和 poi-excelant-3.8-20120326.jar
04-28
4. **数据流处理**：为了处理大数据，POI提供了SXSSF（Streaming Usermodel API），它可以在内存有限的情况下处理大型Excel文件，通过写入磁盘进行临时存储。 5. **公式处理**：POI能够解析和计算Excel中的公式，这...
ImportExcelUtil.java文件支持所有Excel文件解析导入的方法
04-15
本文件内含有支持所有Excel文件解析导入的方法
POI实现excel的导入导出
weixin_34366546的博客
06-13 1256
一。导入到excel 注：本文是在之前ssm框架基础上引入的POI 1.引入依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</vers...
Java之POI操作，封装ExcelUtil实现Excel导入导出
04-02 1万+
最近老师布置了一个任务，实现 Excel 和数据库的导入导出。这个问题起初看起来是不复杂，实现简陋的导入导出比较容易。但是，后来老师要求写一个包装类，让其他同学能够直接使用。这就涉及到了泛型和反射的内容，其实也不复杂。最终，在网上找到一段代码，写得挺好的，然后一顿调试和修改，就能直接拿来用了。现在也分享给大家。 基本功能* 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列.* 2....
POIUtil Excel导入 POI 3.7-1
qq_42570684的博客
08-11 250
package com.hxzy.xazb.util; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.D
利用poi完成excel导入功能
qq_40111437的博客
09-30 349
之前分享了一个导出excel的功能，导出都有了，那自然需要导入了，不多说，先上代码。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ /** * 获取文件后缀名 * @param p
在java中： poi导入Excel通用工具类示例详解
weixin_41595101的博客
07-05 929
第一步：导入jar包： Maven项目只需要在pom.xml文件里做如下配置： <org.apache.poi.version>3.15</org.apache.poi.version> <dependency> <groupId>org.apache.poi</groupId> <artifactId>p...
JAVA使用POI工具生成动态生成excel文件
qq_42021843的博客
06-10 1145
JAVA使用POI工具生成动态生成excel文件
Java操作Excel进行导入导出
SaoLife的博客
10-14 2430
Java操作办公软件 poi和jxl的比较 一、jxl 优点： Jxl对中文支持非常好，操作简单，方法看名知意。 Jxl是纯javaAPI，在跨平台上表现的非常完美，代码可以再windows或者Linux上运行而无需重新编写 支持Excel 95-2000的所有版本（网上说目前可以支持Excel2007了，还没有尝试过） 生成Excel 2000标准格式 支...
解决easypoi多行表头ExcelImportUtil.importExcel，无法获取值
热门推荐
愤怒的苹果ext的博客
12-15 1万+
目录背景解决方案 背景 需要读取excel数据，我使用的是 easypoi的ExcelImportUtil.importExcel方法。 读取的表格格式如下图所示，注意：多行的表头。 代码如下所示。 package com.springboot.sample.service; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ImportParams; import
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值