百万数据excel报表读取

package com.*.poi.test;

import cn.*.poi.handler.SheetHandler;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

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

/**
 * 使用事件模型解析百万数据excel报表
 */
public class PoiTest {

    public static void main(String[] args) throws Exception {
        String path = "C:\\Users\\\Administrator\\Desktop\\demo.xlsx";
        //1.根据excel报表获取OPCPackage
        OPCPackage opcPackage = OPCPackage.open(path, PackageAccess.READ);
        //2.创建XSSFReader
        XSSFReader reader = new XSSFReader(opcPackage);
        //3.获取SharedStringTable对象
        SharedStringsTable table = reader.getSharedStringsTable();
        //4.获取styleTable对象
        StylesTable stylesTable = reader.getStylesTable();
        //5.创建Sax的xmlReader对象
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        //6.注册事件处理器(测试时这里是poi4.0.1的构造方法,3.14时还没有此构造方法)
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable,table,new SheetHandler(),false);
        xmlReader.setContentHandler(xmlHandler);
        //7.逐行读取
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next(); //每一个sheet的流数据
            InputSource is = new InputSource(stream);
            xmlReader.parse(is);
        }
    }
}

 

package com.*.poi.handler;

import cn.*.poi.entity.Book;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

/**
 * 自定义的事件处理器
 *  处理每一行数据读取
 *      实现接口
 */
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    private Book entity;
    /**
     * 当开始解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void startRow(int i) {
        //实例化对象
        if(i>0) {
            entity = new Book();
        }
    }

    /**
     * 当结束解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void endRow(int i) {
        //TODO 进行业务操作保存对象
        System.out.println(entity);
    }

    /**
     * 对行中的每一个表格进行处理
     *      cellReference: 单元格名称
     *      value：数据
     *      xssfComment：批注
     */
    @Override
    public void cell(String cellReference, String value, XSSFComment xssfComment) {
        //对象属性赋值
        if(entity != null) {
            String pix = cellReference.substring(0,1);
            switch (pix) {
                case "A":
                    entity.setName(value);
                    break;
                case "B":
                    entity.setNote(value);
                    break;
                default:
                    break;
            }
        }
    }
}

 

package com.*.poi.entity;

public class Book {
    
    private String name;
    private String note;
    
    public String getName(){
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
    
    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }
}