读取百万数据Excel Java poi读取百万数据

1.解析Excel的几种方式

　　用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至　OOM异常。

　　事件模式：它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。

2.原理

　　　　我们都知道对于Excel2007的实质是一种特殊的XML存储数据，那就可以使用基于SAX的方式解析XML完成Excel的读取。SAX提供了一种从XML文档中读取数据的机制。它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势

代码实现

自定义处理器　

package com.zhao.common.util;


import com.zhao.domain.User;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

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

    private User user;
    /**
     * 当开始解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void startRow(int i) {
        //实例化对象，开始解析excel的第二行也就是下标为1的行 初始化对象 excel的第一行是标题
        if(i>0) {
            user = new User();
        }
    }

    /**
     * 当结束解析某一行的时候触发
     *      i:行索引
     */
    @Override
    public void endRow(int i) {
        //使用对象进行业务操作， 一般是添加对象到数据库中
        a++;
        System.out.println(a+" "+user);
    }

    /**
     * 对行中的每一个表格进行处理 ，也就是一行中有多少个单元格 这个方法就会被调用几次
     *      cellReference: 单元格名称
     *      value：数据
     *      xssfComment：批注
     */
    @Override
    public void cell(String cellReference, String value, XSSFComment xssfComment) {
        //对对象属性赋值
        if(user != null) {
            String pix = cellReference.substring(0,1);
            switch (pix) {
//                case "A":
//                    user.setName(value);
//                    break;
                case "B":  //excel的第2列
                    user.setName(value);
                    break;
                case "C": //excel的第3列
                    user.setPassword(new Double(value).intValue());
                    break;
//                case "D":
//                    user.setNegative(value);
//                    break;
//                case "E":
//                    user.setStaining(value);
//                    break;
//                case "F":
//                    user.setSupportive(value);
//                    break;
                default:
                    break;
            }
        }
    }
}

View Code

自定义解析

web端　　

/**
     * 基于sax事件模型读取百万Excel数据
     */
    @PostMapping("readMillionExcelData")
    public void  readMillionExcelData(@RequestParam(name = "file") MultipartFile file) throws Exception {

        //1.根据excel报表获取基于事件模型的 OPCPackage对象 其实就是把这个Excel文件以压缩包的形式打开成xml文件 PackageAccess.READ 只读
        OPCPackage opcPackage = OPCPackage.open(file.getInputStream());
        //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.注册自定义的事件处理器
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, new SheetHandler(), false);
        xmlReader.setContentHandler(xmlHandler);
        //7.逐行读取 得到的是所有的 sheet
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next(); //每一个sheet的流数据
            InputSource is = new InputSource(stream);  //每一个sheet对象
            xmlReader.parse(is);
        }

    }

View Code

PC端

package com.zhao.common;

import com.zhao.common.util.SheetHandler;
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.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;

/**
 * 使用事件模型解析百万数据excel报表
 *
 * 基于事件模型，读一行删除一行，  不可逆的读取
 */
public class readMillionRowExcel {
    public static void main(String[] args) throws Exception {
        String path = "D:\\projectCode\\poiUtils\\demoRead.xlsx";

        //1.根据excel报表获取基于事件模型的 OPCPackage对象 其实就是把这个Excel文件以压缩包的形式打开成xml文件 PackageAccess.READ 只读
        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.注册自定义的事件处理器
        XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, new SheetHandler(), false);
        xmlReader.setContentHandler(xmlHandler);
        //7.逐行读取 得到的是所有的 sheet
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next(); //每一个sheet的流数据
            InputSource is = new InputSource(stream);  //每一个sheet对象
            xmlReader.parse(is);
        }
    }
}

View Code

封装的实体类　

package com.zhao.domain;

import com.zhao.common.util.ExcelAttribute;
import com.zhao.common.util.ExcelImportAnnotation;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.text.DecimalFormat;

@Data
@NoArgsConstructor
public class User {
    @ExcelAttribute(sort = 0)
    @ExcelImportAnnotation(sort = 2)
    private  String name;
    @ExcelAttribute(sort = 1)
    @ExcelImportAnnotation(sort = 3)
    private  Integer password;

    public User(Object[] args){
        /** DecimalFormat 用法
         * https://www.jianshu.com/p/b3699d73142e
         * Integer.valueOf 返回的时包装类  Integer.parseInt() 返回的是int
         */
        //因为传进来的args 的赋值是从1开始的
        this.name=args[1].toString();   //new DecimalFormat("#").format(args[2]).toString();
        //因为从Excel中读取的数字是double类型的 所以不能用 Integer.valueOf
        this.password=new Double(args[2].toString()).intValue();
    }

}

View Code

 这种模式读取一行删除一行，过程不可逆，不会造成内存溢出。