Large amounts of data to read excel file - 2003 and earlier versions of

I just joined the company, is still in the handyman in the state. The task now is to move customer data to the database, due to customer-supplied data is stored in excel, some amount of file data is still very great, in the regular memory overflow usermodel mode, so can only be used directly through the pl / sql to the database, copy, or read with eventusermodel mode. Direct replication Daoshi simple, but too slow, too much a copy will be memory leak, I'm not that patient, no way, can only use the second solution. Look for in google and found that the relevant example of too little, and fortunately docjar find a sample, but it is changed a bit, changed the original example of an abstract class, providing a optRows () method to operate on the row-level data.

usermodel model before the operation will need to excel all the documents into memory, the memory overhead for large files is great. But it is easy to use.

eventusermodel model using the event model, while reading the file while processing, low memory consumption, high efficiency, because all the documents into memory without waiting for. However, the use of more complex.

The following shows the excel2003 and previous versions of large files to read way.

Abstract class HxlsAbstract:

package com.gaosheng.util.xls;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NoteRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.RKRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public abstract class HxlsAbstract implements HSSFListener {
        private int minColumns;
        private POIFSFileSystem fs;
        private PrintStream output;

        private int lastRowNumber;
        private int lastColumnNumber;

        /** Should we output the formula, or the value it has? */
        private boolean outputFormulaValues = true;

        /** For parsing Formulas */
        private SheetRecordCollectingListener workbookBuildingListener;
        private HSSFWorkbook stubWorkbook;

        // Records we pick up as we process
        private SSTRecord sstRecord;
        private FormatTrackingHSSFListener formatListener;

        /** So we known which sheet we're on */
        private int sheetIndex = -1;
        private BoundSheetRecord[] orderedBSRs;
        private ArrayList boundSheetRecords = new ArrayList();

        // For handling formulas with string results
        private int nextRow;
        private int nextColumn;
        private boolean outputNextStringRecord;

        private int curRow;
        private List<String> rowlist;
        @SuppressWarnings( "unused")
        private String sheetName;

        public HxlsAbstract(POIFSFileSystem fs)
                        throws SQLException {
                this.fs = fs;
                this.output = System.out;
                this.minColumns = -1;
                this.curRow = 0;
                this.rowlist = new ArrayList<String>();

        public HxlsAbstract(String filename) throws IOException,
                        FileNotFoundException, SQLException {
                this(new POIFSFileSystem(new FileInputStream(filename)));

        //excel Record row operation method, to row index and row element list for the parameter  , On the row elements, elements for  String Type
//      public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;

        //excel Record row operation method, to  sheet The index, the row index and row element list for the parameter  , On the sheet of row elements  , Elements of type string
        public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;

         *  Through Excel files
        public void process() throws IOException {
                MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(
                formatListener = new FormatTrackingHSSFListener(listener);

                HSSFEventFactory factory = new HSSFEventFactory();
                HSSFRequest request = new HSSFRequest();

                if (outputFormulaValues) {
                } else {
                        workbookBuildingListener = new SheetRecordCollectingListener(

                factory.processWorkbookEvents(request, fs);

         * HSSFListener  Listen method, treatment   Record
        public void processRecord(Record record) {
                int thisRow = -1;
                int thisColumn = -1;
                String thisStr = null;
                String value = null;

                switch (record.getSid()) {
                case BoundSheetRecord.sid:
                case BOFRecord.sid:
                        BOFRecord br = (BOFRecord) record;
                        if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                                // Create sub workbook if required
                                if (workbookBuildingListener != null && stubWorkbook == null) {
                                        stubWorkbook = workbookBuildingListener

                                // Works by ordering the BSRs by the location of
                                // their BOFRecords, and then knowing that we
                                // process BOFRecords in byte offset order
                                if (orderedBSRs == null) {
                                        orderedBSRs = BoundSheetRecord
                                sheetName = orderedBSRs[sheetIndex].getSheetname();

                case SSTRecord.sid:
                        sstRecord = (SSTRecord) record;

                case BlankRecord.sid:
                        BlankRecord brec = (BlankRecord) record;

                        thisRow = brec.getRow();
                        thisColumn = brec.getColumn();
                        thisStr = "";
                case BoolErrRecord.sid:
                        BoolErrRecord berec = (BoolErrRecord) record;

                        thisRow = berec.getRow();
                        thisColumn = berec.getColumn();
                        thisStr = "";

                case FormulaRecord.sid:
                        FormulaRecord frec = (FormulaRecord) record;

                        thisRow = frec.getRow();
                        thisColumn = frec.getColumn();

                        if (outputFormulaValues) {
                                if (Double.isNaN(frec.getValue())) {
                                        // Formula result is a string
                                        // This is stored in the next record
                                        outputNextStringRecord = true;
                                        nextRow = frec.getRow();
                                        nextColumn = frec.getColumn();
                                } else {
                                        thisStr = formatListener.formatNumberDateCell(frec);
                        } else {
                                thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,
                                                frec.getParsedExpression()) + '"';
                case StringRecord.sid:
                        if (outputNextStringRecord) {
                                // String for formula
                                StringRecord srec = (StringRecord) record;
                                thisStr = srec.getString();
                                thisRow = nextRow;
                                thisColumn = nextColumn;
                                outputNextStringRecord = false;

                case LabelRecord.sid:
                        LabelRecord lrec = (LabelRecord) record;

                        curRow = thisRow = lrec.getRow();
                        thisColumn = lrec.getColumn();
                        value = lrec.getValue().trim();
                        value = value.equals("")?" ":value;
                        this.rowlist.add(thisColumn, value);
                case LabelSSTRecord.sid:
                        LabelSSTRecord lsrec = (LabelSSTRecord) record;

                        curRow = thisRow = lsrec.getRow();
                        thisColumn = lsrec.getColumn();
                        if (sstRecord == null) {
                                rowlist.add(thisColumn, " ");
                        } else {
                                value =  sstRecord
                                value = value.equals("")?" ":value;
                case NoteRecord.sid:
                        NoteRecord nrec = (NoteRecord) record;

                        thisRow = nrec.getRow();
                        thisColumn = nrec.getColumn();
                        // TODO: Find object to match nrec.getShapeId()
                        thisStr = '"' + "(TODO)" + '"';
                case NumberRecord.sid:
                        NumberRecord numrec = (NumberRecord) record;

                        curRow = thisRow = numrec.getRow();
                        thisColumn = numrec.getColumn();
                        value = formatListener.formatNumberDateCell(numrec).trim();
                        value = value.equals("")?" ":value;
                        // Format
                        rowlist.add(thisColumn, value);
                case RKRecord.sid:
                        RKRecord rkrec = (RKRecord) record;

                        thisRow = rkrec.getRow();
                        thisColumn = rkrec.getColumn();
                        thisStr = '"' + "(TODO)" + '"';

                //  Experience the new lines of action
                if (thisRow != -1 && thisRow != lastRowNumber) {
                        lastColumnNumber = -1;

                //  Null values
                if (record instanceof MissingCellDummyRecord) {
                        MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
                        curRow = thisRow = mc.getRow();
                        thisColumn = mc.getColumn();
                        rowlist.add(thisColumn," ");

                //  If you experience printing, print here
                if (thisStr != null) {
                        if (thisColumn > 0) {

                //  Update the value of the rows and columns
                if (thisRow > -1)
                        lastRowNumber = thisRow;
                if (thisColumn > -1)
                        lastColumnNumber = thisColumn;

                //  The operation at the end of the line
                if (record instanceof LastCellOfRowDummyRecord) {
                        if (minColumns > 0) {
                                //  The column value back empty
                                if (lastColumnNumber == -1) {
                                        lastColumnNumber = 0;
                        //  At the end of the line, call   optRows()  Method
                        lastColumnNumber = -1;
                        try {
                                optRows(sheetIndex,curRow, rowlist);
                        } catch (SQLException e) {

Derived class: HxlsBig, role: to excel in the data dump to a database in the temporary table to achieve optRows method

package com.gaosheng.util.examples.xls;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.gaosheng.util.xls.HxlsAbstract;

public class HxlsBig extends HxlsAbstract{

        public static void main(String[] args) throws Exception {
                // XLS2CSVmra xls2csv = new XLS2CSVmra(args[0], minColumns);
                HxlsBig xls2csv = new HxlsBig("E:/up.xls","hxls_temp");

        public HxlsBig(POIFSFileSystem fs, PrintStream output,String tableName)
                        throws SQLException {
                this.conn = getNew_Conn();
                this.statement = conn.createStatement();
                this.tableName = tableName;

        public HxlsBig(String filename,String tableName) throws IOException,
                        FileNotFoundException, SQLException {
                this(new POIFSFileSystem(new FileInputStream(filename)), System.out,tableName);

        private Connection conn = null;
        private Statement statement = null;
        private PreparedStatement newStatement = null;

        private String tableName = "temp_table";
        private boolean create = true;
//      private int sheetIndex = 0;

        public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
                if (curRow == 0 && sheetIndex == 0 ) {
                        StringBuffer preSql = new StringBuffer("insert into " + tableName
                                        + " values(");
                        StringBuffer table = new StringBuffer("create table " + tableName
                                        + "(");
                        int c = rowlist.size();
                        for (int i = 0; i < c; i++) {
                                table.append("  varchar2(100) ,");

                        table.deleteCharAt(table.length() - 1);
                        preSql.deleteCharAt(preSql.length() - 1);
                        if (create) {
                                statement = conn.createStatement();
                                        statement.execute("drop table "+tableName);
                                }catch(Exception e){

                                        System.out.println(" Table "+ tableName +" was successfully deleted  ");
                                if (!statement.execute(table.toString())) {
                                        System.out.println(" Create table tableName + "" + success  ");
                                        // return;
                                } else {
                                        System.out.println(" Create table tableName + "" + failed  ");
                        newStatement = conn.prepareStatement(preSql.toString());

                }else if(curRow > 0) {
                        //  General line
                        int col = rowlist.size();
                        for (int i = 0; i < col; i++) {
                                newStatement.setString(i + 1, rowlist.get(i).toString());
                        if (curRow % 1000 == 0) {

    private static Connection getNew_Conn() {
        Connection conn = null;
        Properties props = new Properties();
        FileInputStream fis = null;

        try {
            fis = new FileInputStream("D:/");
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            StringBuffer jdbcURLString = new StringBuffer();
            conn = DriverManager.getConnection(jdbcURLString.toString(), props
                    .getProperty("user"), props.getProperty("password"));
        } catch (Exception e) {
        } finally {
            try {
            } catch (IOException e) {
        return conn;

        public int close() {
                try {
                        System.out.println(" Data is written to complete  ");
                        return 1;
                } catch (SQLException e) {
                        return 0;


Derived class: HxlsPrint, role: to excel in the data output to the console to achieve optRows method

package com.gaosheng.util.examples.xls;
import java.sql.SQLException;
import java.util.List;

import com.gaosheng.util.xls.HxlsAbstract;

public class HxlsPrint extends HxlsAbstract{

        public HxlsPrint(String filename) throws IOException,
                        FileNotFoundException, SQLException {

        public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
                for (int i = 0 ;i< rowlist.size();i++){

        public static void main(String[] args){
                HxlsPrint xls2csv;
                try {
                        xls2csv = new HxlsPrint("E:/new.xls");
                } catch (FileNotFoundException e) {
                } catch (IOException e) {
                } catch (SQLException e) {


excel2007 a large amount of data to read see Part II, annex contains the 2003 and other earlier versions of the xls file example, but also includes the 2007 version of the sample

分类:Java 时间:2010-03-25 人气:397
blog comments powered by Disqus


  • PL / SQL configuration 2011-03-18

    Transfer PL / SQL configuration: Note: Please download the latest oracle instant client .. some pl / sql Developer does not support the old version of .. There is the original case had installed oracle client ..

  • oracle pl / sql stored procedure (copy) 2010-03-05

    Area of the environment to handle cursor is a SQL statement oracle storage area. Cursor is a pointer to it or handle. Through the cursor, PL / SQL program can control the environment in the region are processed statements. Oracle cursor in two ways:

  • Advanced - control structures (branches, circulation, control) 2010-05-12

    Fun combat tutorial oracle (seventh day) Introduction 1. On the section reviews Advanced √ view (with security, and simplify complex query functions) √ trigger √ Desired goals 1. Master pl / sql Advanced usage (paging proce

  • pl / sql dev's special copy feature 2010-10-13

    In the pl / sql dev written in a hard sql, now trying to stick to the java code sql, connection string needs to be done, change the line, a lot of trouble. Now have a special copy features, everything becomes very easy. special copy can define their

  • PL / SQL programming based learning summary 2010-05-27

    PL / SQL Programming 1. How to write and compile PL / SQL Because PL / SQL is embedded in the Oracle server and Oracle development tool, so Oracle client tools and server can write PL / SQL procedure SQL> edit c: \ plsqlblock1.sql; - to build a docum

  • PL / SQL Developer Plugin for SVN 2010-06-18

    PL / SQL Developer Developer official website provides version control plug-in is the VCS, but the VCS need to rely on other to achieve Microsoft SCC interface version control tool, but the more the project using svn. Therefore, PL / SQL Developer De

  • PL / SQL block syntax: 2010-07-21

    PL / SQL block syntax: [DECLARE] - Declaration statements BEGIN - Executable statements [EXCEPTION] - Exception statements END PL / SQL block of each statement must end with a semicolon, SQL statement is more than one line, but the semicolon indicate

  • Oracle PL / SQL in how to use the% TYPE and% ROWTYPE 2010-08-19

    1. Use% TYPE In many cases, PL / SQL variables can be used to store data in the table in the database. In this case, the variables and table columns should have the same type. For example, students form the first_name column of type VARCHAR2 (20), we

  • SELECT containing a MIN or MAX into a CHAR variable inside a PL / SQL block Fails 2010-09-08

    General descriptions: Host HP 11.31 to upgrade the database ORACLE 9205 to 10 205 Sunday, was upgraded to a library; upgrade completed, application run time, occur ORA-06502: PL / SQL: numeric or value error string buffer too small for the analysis,

  • based oracle pl / sql basics 2011-01-05

    1. Block (Block) is a pl / sql basic program unit, write pl / sql program actually is to write pl / sql departments and regions, by definition part of the operative part and exception handling parts. Definitions used to define constants, variables, c

iOS 开发

Android 开发

Python 开发



PHP 开发

Ruby 开发






Javascript 开发

.NET 开发



Copyright (C), All Rights Reserved. 版权所有 闽ICP备15018612号

processed in 0.154 (s). 12 q(s)