Java处理大数据量EXCEL

Java处理大数据量EXCEL

1.MAVEN依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

2.创建抽象类BigExcelReader

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
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;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

public abstract class BigExcelReader {

    enum xssfDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
    }

    public static final int ERROR = 1;
    public static final int BOOLEAN = 1;
    public static final int NUMBER = 2;
    public static final int STRING = 3;
    public static final int DATE = 4;
    public static final String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";


    //	private DataFormatter formatter = new DataFormatter();
    private InputStream sheet;
    private XMLReader parser;
    private InputSource sheetSource;
    private int index = 0;

    /**
     * 读大数据量Excel
     *
     * @param file Excel文件
     * @throws IOException
     * @throws OpenXML4JException
     * @throws SAXException
     */
    public BigExcelReader(File file) throws IOException, OpenXML4JException, SAXException {
        OPCPackage pkg = OPCPackage.open(file);
        init(pkg);
    }

    /**
     * 读大数据量Excel
     *
     * @param in Excel