文档章节

POI工具--POI读写大数据量EXCEL

spinachgit
 spinachgit
发布于 06/25 17:19
字数 1694
阅读 137
收藏 0

另一篇文章http://www.cnblogs.com/tootwo2/p/8120053.html里面有xml的一些解释。

大数据量的excel一般都是.xlsx格式的,网上使用POI读写的例子比较多,但是很少提到读写非常大数据量的excel的例子,POI官网上提到XSSF有三种读写excel,POI地址:http://poi.apache.org/spreadsheet/index.html。官网的图片:

可以看到有三种模式:

1、eventmodel方式,基于事件驱动,SAX的方式解析excel(.xlsx是基于OOXML的),CPU和内存消耗非常低,但是只能读不能写

2、usermodel,就是我们一般使用的方式,这种方式可以读可以写,但是CPU和内存消耗非常大

3、SXSSF,POI3.8以后开始支持,这种方式只能写excel

下面介绍下使用方式(官网地址:http://poi.apache.org/spreadsheet/how-to.html):

第一种方式:

pom文件需要添加依赖:

 

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>xerces</groupId>
    <artifactId>xerces</artifactId>
    <version>2.4.0</version>
</dependency>

 

java官网示例代码:

package excel;

import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

public class ExampleEventUserModel {
    
    
    public void processOneSheet(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        // To look up the Sheet Name / Sheet Order / rID,
        //  you need to process the core Workbook stream.
        // Normally it's of the form rId# or rSheet#
        InputStream sheet2 = r.getSheet("rId2");
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    }

    public void processAllSheets(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();
        
        XMLReader parser = fetchSheetParser(sst);

        Iterator<InputStream> sheets = r.getSheetsData();
        while(sheets.hasNext()) {
            System.out.println("Processing new sheet:\n");
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
            System.out.println("");
        }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
            XMLReaderFactory.createXMLReader(
                    "com.sun.org.apache.xerces.internal.parsers.SAXParser"
            );
        ContentHandler handler = new SheetHandler(sst);
        parser.setContentHandler(handler);
        return parser;
    }

    /** 
     * See org.xml.sax.helpers.DefaultHandler javadocs 
     */
    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;
        
        private SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }
        
        public void startElement(String uri, String localName, String name,
                Attributes attributes) throws SAXException {
            // c => cell
            if(name.equals("c")) {
                // Print the cell reference
                System.out.print(attributes.getValue("r") + " - ");
                // Figure out if the value is an index in the SST
                String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
            }
            // Clear contents cache
            lastContents = "";
        }
        
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            // Process the last contents as required.
            // Do now, as characters() may be called more than once
            if(nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                nextIsString = false;
            }

            // v => contents of a cell
            // Output after we've seen the string contents
            if(name.equals("v")) {
                System.out.println(lastContents);
            }
        }

        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }
    
    public static void main(String[] args) throws Exception {
        ExampleEventUserModel example = new ExampleEventUserModel();
        System.out.println("11");
        example.processOneSheet(args[0]);
        example.processAllSheets(args[0]);
    }
}

 

运行的时候使用本地的文件地址替代main函数里面的参数就可以运行(亲测可以)。

第三种方式:

其核心是减少存储在内存当中的数据,达到一定行数就存储到硬盘的临时文件中。

pom文件需要增加依赖:

<dependency>
    <groupId>xerces</groupId>
    <artifactId>xercesImpl</artifactId>
    <version>2.11.0</version>
</dependency>

java代码如下:

 

package excel;


//import junit.framework.Assert;
import java.io.FileOutputStream;

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.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class SXSSFDemo {
    public static void main(String[] args) throws Throwable {
        
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // 在内存当中保持 100 行 , 超过的数据放到硬盘中
        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < 10000; rownum++){
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 10; cellnum++){
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }     
        
        FileOutputStream out = new FileOutputStream("/Users/tootwo2/Documents/sxssf.xlsx");
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }

}

 

HSSFworkbook,XSSFworkbook,SXSSFworkbook区别总结

用JavaPOI导出Excel时,我们需要考虑到Excel版本及数据量的问题。针对不同的Excel版本,要采用不同的工具类,如果使用错了,会出现错误信息。JavaPOI导出Excel有三种形式,他们分别是1.HSSFWorkbook  2.XSSFWorkbook  3.SXSSFWorkbook。

HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls;

XSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;

SXSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;

第一种:HSSFWorkbook

poi导出excel最常用的方式;但是此种方式的局限就是导出的行数至多为65535行,超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况(OOM)。

对于不同版本的EXCEL文档要使用不同的工具类,如果使用错了,会提示如下错误信息。

org.apache.poi.openxml4j.exceptions.InvalidOperationException org.apache.poi.poifs.filesystem.OfficeXmlFileException


 

第二种:XSSFWorkbook

这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行,16384列)扩展名为“.xlsx”,最多可以导出104万行,不过这样就伴随着一个问题---OOM内存溢出,原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。

 

第三种:SXSSFWorkbook

从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建,一个关键问题就是,要确保不会内存溢出。其实,就算生成很小的excel(比如几Mb),它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式(比如,加粗,背景标红之类的),那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的,就只有SXSSFWorkbook了。它的原理很简单,用硬盘空间换内存(就像hash map用空间换时间一样)。

SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看,在此之前的excel rows都会被写入到硬盘里(Windows电脑的话,是写入到C盘根目录下的temp文件夹)。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

SXSSF与XSSF的对比:

a. 在一个时间点上,只可以访问一定数量的数据

b. 不再支持Sheet.clone()

c. 不再支持公式的求值

d. 在使用Excel模板下载数据时将不能动态改变表头,因为这种方式已经提前把excel写到硬盘的了就不能再改了

当数据量超出65536条后,在使用HSSFWorkbook或XSSFWorkbook,程序会报OutOfMemoryError:Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。

 

注意:针对 SXSSF Beta 3.8下,会有临时文件产生,比如:
poi-sxssf-sheet4654655121378979321.xml
文件位置:java.io.tmpdir这个环境变量下的位置
Windows 7下是C:\Users\xxxxxAppData\Local\Temp
Linux下是 /var/tmp/
要根据实际情况,看是否删除这些临时文件


参考链接:

本文转载自:https://www.cnblogs.com/tootwo2/p/6683143.html

spinachgit
粉丝 10
博文 268
码字总数 269029
作品 0
亳州
架构师
私信 提问
Excel解析与数据生成(支持JXL,POI)

最近项目中需要用到Excel解析与生成Excel,但现有系统中没有进行解析工具封装于是便封装一个Excel工具包,支持JXL工具包与POI工具包解析与生成数据,POI支持2003与2007。 Excel解析目前网上有...

皮蛋猫
2013/02/01
893
0
[转]Java大数据量导出Excel的问题

试共同条件: 数据总数为110011条,每条数据条数为19个字段。 电脑配置为:P4 2.67GHz,1G内存。 一、POI、JXL、FastExcel比较 POI、JXL、FastExcel均为java第三方开源导出Excel的开源项目。...

大冬
2011/07/28
4.4K
2
Java程序员从笨鸟到菜鸟之(一百零四)java操作office和pdf文件(二)利用POI实现数据导出excel报表

在上一篇博客中,我们简单介绍了java读取word,excel和pdf文档内容 ,但在实际开发中,我们用到最多的是把数据库中数据导出excel报表形式。不仅仅简单的读取office中的数据.尤其是在生产管理...

长平狐
2012/11/12
1K
0
java读写excel之POI篇—001

POI 简单介绍 Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 Apache POI 是创建和维护操作各种符合Office Open XML(OOXML...

Mysoft
2015/09/21
83
0
使用Apache POI读取Excel文件

网上关于介绍Apache POI操作Excel的文章已经很多了,但都讲得比较复杂。poi的API 与实际使用中的Excel很类似,可以说是POI把Excel中的workbook、sheet、cell等对象化了,在实际使用中极易理解...

zlikun
2013/05/13
4.8K
4

没有更多内容

加载失败,请刷新页面

加载更多

一套基于SpringBoot+Vue+Shiro 前后端分离 开发的代码生成器

一、前言 最近花了一个月时间完成了一套基于Spring Boot+Vue+Shiro前后端分离的代码生成器,目前项目代码已基本完成 止步传统CRUD,进阶代码优化: 该项目可根据数据库字段动态生成 controll...

郑清
今天
7
0
javascript-十六进制随机颜色

<script> // 编写一个函数,获得一个十六进制的随机颜色的字符串(如#20CD4F) // function randomColor(){ // var r = random(0,255).toString(16); // var g = random(0,255).toString(16......

ACKo
今天
3
0
springBoot +mybatis 出现sql 语句在数据库可以查询到,但是赋值到实体类上就没有的情况?

1.不要老是反复查看自己是否写错了,为啥有的能出来有的出不来? 可以查看配置文件中是否配置全: 如果在application.yml 文件中是如下配置: mybatis: mapper-locations: classpath:mapp...

kuchawyz
今天
3
0
正则表达式

一、RegExp对象 进行验证和查找的API 1、创建对象: (1)用/创建(直接量):var reg=/正则/ig,表达式固定不变时使用 (2)用new创建:var reg=new RegExp(‘正则’,‘ig’),表达式需要...

wytao1995
今天
3
0
实战限流(guava的RateLimiter)

关于限流 常用的限流算法有漏桶算法和令牌桶算法,guava的RateLimiter使用的是令牌桶算法,也就是以固定的频率向桶中放入令牌,例如一秒钟10枚令牌,实际业务在每次响应请求之前都从桶中获取...

程序员欣宸
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部