How to extract data from one of the sheets in XLS file using POIFS API?

Question

I understand how to use POI API (User Model). However, presently, this is not helpful to me as I'm dealing with XLS files larger than 200 MB in size.

In one of the questions here on stack overflow, I learned that I need to use an Event-Driven Approach using POIFS API.

POIFS API barely has any detailed explanations available on internet or YouTube videos as well or rather no videos at all. I've struggling to understand this approach, although I have a very good understanding of 'Event-Driven' programming otherwise.

I did manage to find a piece of code somewhere on internet using the newer approach however, I have not been successful to modify the code to do what I want it to.

Below is the code and what I want it to do is extract all the data from first column & second row onwards from sheets 18, 19, 20, 21 & 22.

Any help will he highly appreciated or at least any guidance on how to understand/learn this newer approach. Any sources?

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

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.record.BOFRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class EventExample implements HSSFListener{

    private SSTRecord sstrec;

    @SuppressWarnings("static-access")
    public void processRecord(Record record) {


        switch (record.getSid()) {

        case BOFRecord.sid:

            BOFRecord bof = (BOFRecord) record;
            if (bof.getType() == bof.TYPE_WORKBOOK) {
                System.out.println("Encountered workbook");
            } else if (bof.getType() == bof.TYPE_WORKSHEET) {
                System.out.println("Encountered sheet reference");
            }
            break;

        case BoundSheetRecord.sid:

            BoundSheetRecord bsr = (BoundSheetRecord) record;
            System.out.println("New sheet named: " + bsr.getSheetname());
            break;

        case RowRecord.sid:

            RowRecord rowrec = (RowRecord) record;
            System.out.println("Row found, first column at " + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
            break;

        case NumberRecord.sid:

            NumberRecord numrec = (NumberRecord) record;
            System.out.println("Cell found with value " + numrec.getValue() + " at row " + numrec.getRow() + " and column " + numrec.getColumn());
            break;

        case LabelSSTRecord.sid:

            LabelSSTRecord lrec = (LabelSSTRecord) record;
            System.out.println("String cell found with value " + sstrec.getString(lrec.getSSTIndex()));
            break;

        case SSTRecord.sid:

            sstrec = (SSTRecord) record;
            for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
                System.out.println("String table value " + k + " = " + sstrec.getString(k));
            }
            break;

        } 


    }

    public static void main(String[] args) throws IOException {

        FileInputStream fin = new FileInputStream("C:\\Users\\bharat.nanwani\\desktop\\POI_Test.xls");
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        InputStream din = poifs.createDocumentInputStream("Workbook");
        HSSFRequest req = new HSSFRequest();
        req.addListenerForAllRecords(new EventExample());
        HSSFEventFactory factory = new HSSFEventFactory();
        factory.processEvents(req, din);
        fin.close();
        din.close();
        System.out.println("done.");

    }

}

Answer 1

Thank you guys for your little help. I appreciate it.

User Model throws OOM error with large excel files. However, I just learned about the new SS.usermodel with which you can handle both older & newer excel files at the same time (XLS & XLSX), and with this User Model I am also able to process excel files of any size.