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.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
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;
        @SuppressWarnings("unchecked")
        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(
                                this);
                formatListener = new FormatTrackingHSSFListener(listener);

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

                if (outputFormulaValues) {
                        request.addListenerForAllRecords(formatListener);
                } else {
                        workbookBuildingListener = new SheetRecordCollectingListener(
                                        formatListener);
                        request.addListenerForAllRecords(workbookBuildingListener);
                }

                factory.processWorkbookEvents(request, fs);
        }
        
        /**
         * HSSFListener  Listen method, treatment   Record
         */
        @SuppressWarnings("unchecked")
        public void processRecord(Record record) {
                int thisRow = -1;
                int thisColumn = -1;
                String thisStr = null;
                String value = null;
                
                switch (record.getSid()) {
                case BoundSheetRecord.sid:
                        boundSheetRecords.add(record);
                        break;
                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
                                                        .getStubHSSFWorkbook();
                                }

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

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

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

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

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

                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()) + '"';
                        }
                        break;
                case StringRecord.sid:
                        if (outputNextStringRecord) {
                                // String for formula
                                StringRecord srec = (StringRecord) record;
                                thisStr = srec.getString();
                                thisRow = nextRow;
                                thisColumn = nextColumn;
                                outputNextStringRecord = false;
                        }
                        break;

                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);
                        break;
                case LabelSSTRecord.sid:
                        LabelSSTRecord lsrec = (LabelSSTRecord) record;

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

                        thisRow = nrec.getRow();
                        thisColumn = nrec.getColumn();
                        // TODO: Find object to match nrec.getShapeId()
                        thisStr = '"' + "(TODO)" + '"';
                        break;
                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);
                        break;
                case RKRecord.sid:
                        RKRecord rkrec = (RKRecord) record;

                        thisRow = rkrec.getRow();
                        thisColumn = rkrec.getColumn();
                        thisStr = '"' + "(TODO)" + '"';
                        break;
                default:
                        break;
                }

                //  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) {
                                output.print(',');
                        }
                        output.print(thisStr);
                }

                //  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) {
                                e.printStackTrace();
                        }
                        rowlist.clear();
                }
        }
}


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.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
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");
                xls2csv.process();
                xls2csv.close();
        }
        
        public HxlsBig(POIFSFileSystem fs, PrintStream output,String tableName)
                        throws SQLException {
                super(fs);
                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++) {
                                preSql.append("?,");
                                table.append(rowlist.get(i));
                                table.append("  varchar2(100) ,");
                        }

                        table.deleteCharAt(table.length() - 1);
                        preSql.deleteCharAt(preSql.length() - 1);
                        table.append(")");
                        preSql.append(")");
                        if (create) {
                                statement = conn.createStatement();
                                try{
                                        statement.execute("drop table "+tableName);
                                }catch(Exception e){
                                        
                                }finally{
                                        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  ");
                                        return;
                                }
                        }
                        conn.setAutoCommit(false);
                        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());
                        }
                        newStatement.addBatch();
                        if (curRow % 1000 == 0) {
                                newStatement.executeBatch();
                                conn.commit();
                        }
                }
        }
        
    private static Connection getNew_Conn() {
        Connection conn = null;
        Properties props = new Properties();
        FileInputStream fis = null;

        try {
            fis = new FileInputStream("D:/database.properties");
            props.load(fis);
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            StringBuffer jdbcURLString = new StringBuffer();
            jdbcURLString.append("jdbc:oracle:thin:@");
            jdbcURLString.append(props.getProperty("host"));
            jdbcURLString.append(":");
            jdbcURLString.append(props.getProperty("port"));
            jdbcURLString.append(":");
            jdbcURLString.append(props.getProperty("database"));
            conn = DriverManager.getConnection(jdbcURLString.toString(), props
                    .getProperty("user"), props.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    
        public int close() {
                try {
                        newStatement.executeBatch();
                        conn.commit();
                        System.out.println(" Data is written to complete  ");
                        this.newStatement.close();
                        this.statement.close();
                        this.conn.close();
                        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.io.FileNotFoundException;
import java.io.IOException;
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 {
                super(filename);
        }

        @Override
        public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
                for (int i = 0 ;i< rowlist.size();i++){
                        System.out.print("'"+rowlist.get(i)+"',");
                }
                System.out.println();
        }
        
        public static void main(String[] args){
                HxlsPrint xls2csv;
                try {
                        xls2csv = new HxlsPrint("E:/new.xls");
                        xls2csv.process();
                } catch (FileNotFoundException e) {
                        e.printStackTrace();
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                
        }
}


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
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

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

  • hibernate generic generic DAO

    package org.lzpeng.dao; import java.io.Serializable; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.Criterion; import org.springside.modules.orm.hibernate.Page; /** * * @version 2009-1-10 *

  • Servlet brief introduction

    Servlet brief introduction: Servlet is a small application server Are used to complete the B / S architecture, the client requests the response to treatment Platform independence, performance, able to run thread Servlet API for Servlet provides the s ...

  • can not be represented as java.sql.Timestamp

    Development of procedures for the use of hibernate when, some time there is no need to fill in the fields, but after the hibernate query time reported "Java.sql.SQLException: Value'0000-00-00 'can not be represented as java.sql.Timestamp ...

  • First Hibernate Example

    Curd a simple example. Source does not contain the dependent libraries, or playing too much of the package. PO object Note: One must have the default constructor 2 non-final modified. Otherwise useless lazy loading. UserDAOImpl category code, and other co

  • Hibernate annotation using notebook

    These are the basic common @Entity --Declared an entity bean @Table(name="promotion_info") --For the entity bean mapping for the specified table (Table name ="promotion_info) @Id --Declare that the identifying attribute of the entity bean @GeneratedValue

  • Struts2 + hibernate + spring problem user log in

    dao layer services layer action jsp <tr> <td align="center"> <b> user name: </ b> </ td> <td> <s: textfield name = "czyNumber" cssClass = "textstyle" theme = "simple" size = &q

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries