POI HSSFListener - determine sheet

Thi sis the output of Event example - ttt, mmm, gg are on the first sjheet
and trattat is on the second sheet - you see it enountered sheet reference and then prints the sceond sheet

Encountered workbook
New sheet named: Sheet1
New sheet named: Sheet2
New sheet named: Sheet3
String table value 0 = ttt
String table value 1 = mmm
String table value 2 = ggg
String table value 3 = trattata
Encountered sheet reference
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
String cell found with value ttt
String cell found with value mmm
String cell found with value ggg
Encountered sheet reference
Row found, first column at 0 last column at 1
String cell found with value trattata
Encountered sheet reference
done.

Select allOpen in new window

you get that from the BoundSheetRecord

http://poi.apache.org/apidocs/org/apache/poi/hssf/record/BoundSheetRecord.html

When you get a BSR store the sheet for later use

This will print you sheet names:

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.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

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

/**
 * This example shows how to use the event API for reading a file.
 */
public class EventExample
        implements HSSFListener
{
    private SSTRecord sstrec;
    String sheetName = null;
    BoundSheetRecord bos;

   static  ArrayList ar ;
    int count;

    /**
     * This method listens for incoming records and handles them as required.
     * @param record    The record that was found while reading.
     */
    public void processRecord(Record record)
    {


        switch (record.getSid())
        {

            // the BOFRecord can represent either the beginning of a sheet or the workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == bof.TYPE_WORKBOOK)
                {
                    System.out.println("Encountered workbook");
                    // assigned to the class level member
                } else if (bof.getType() == bof.TYPE_WORKSHEET)
                {
                   // System.out.println(bof.toString());
                    if(record instanceof BoundSheetRecord)  {
                        System.out.println("here");
                        bos = (BoundSheetRecord) record;
                         sheetName = bos.getSheetname();
                    }
                    System.out.println("here");

                    System.out.println("Encountered sheet reference " + (String)ar.get(count));
                    count++;
                }
                break;
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                System.out.println("New sheet named: " + bsr.getSheetname());
               System.out.println(  bsr.getSheetname());
                ar.add(bsr.getSheetname());
                System.out.println(ar.size());
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                System.out.println("Row found, first column at "
                        + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
                 // System.out.println(sheetName);
                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());
              //  System.out.println(sheetName);
                break;
                // SSTRecords store a array of unique strings used in Excel.
            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;
            case LabelSSTRecord.sid:
                LabelSSTRecord lrec = (LabelSSTRecord) record;
                System.out.println("String cell found with value "
                        + sstrec.getString(lrec.getSSTIndex()));
                break;
        }
    }

    /**
     * Read an excel file and spit out what we find.
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException
    {
          ar = new ArrayList();
        // create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = new FileInputStream(args[0]);
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = poifs.createDocumentInputStream("Workbook");
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        req.addListenerForAllRecords(new EventExample());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        fin.close();
        // and our document input stream (don't want to leak these!)
        din.close();
        System.out.println("done.");

    }
}

Select allOpen in new window

Encountered workbook
New sheet named: Sheet1
Sheet1
1
New sheet named: Sheet2
Sheet2
2
New sheet named: Sheet3
Sheet3
3
String table value 0 = ttt
String table value 1 = mmm
String table value 2 = ggg
String table value 3 = trattata
here
Encountered sheet reference Sheet1
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
String cell found with value ttt
String cell found with value mmm
String cell found with value ggg
here
Encountered sheet reference Sheet2
Row found, first column at 0 last column at 1
String cell found with value trattata
here
Encountered sheet reference Sheet3
done.

Select allOpen in new window

Removed extra printing and organized better output:

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.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

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

/**
 * This example shows how to use the event API for reading a file.
 */
public class EventExample
        implements HSSFListener
{
    private SSTRecord sstrec;
    String sheetName = null;
    BoundSheetRecord bos;

   static  ArrayList ar ;
    int count;


    /**
     * This method listens for incoming records and handles them as required.
     * @param record    The record that was found while reading.
     */
    public void processRecord(Record record)
    {


        switch (record.getSid())
        {

            // the BOFRecord can represent either the beginning of a sheet or the workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == bof.TYPE_WORKBOOK)
                {
                    System.out.println("Encountered workbook");
                    // assigned to the class level member
                } else if (bof.getType() == bof.TYPE_WORKSHEET)
                {

                                sheetName =    (String)ar.get(count);
                    System.out.println("Encountered sheet reference " + sheetName);

                    count++;
                }
                break;
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                System.out.println("New sheet named: " + bsr.getSheetname());
             //  System.out.println(  bsr.getSheetname());
                ar.add(bsr.getSheetname());
           //     System.out.println(ar.size());
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                System.out.println("Row found, first column at "
                        + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
                 // System.out.println(sheetName);
                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());
              //  System.out.println(sheetName);
                break;
                // SSTRecords store a array of unique strings used in Excel.
            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;
            case LabelSSTRecord.sid:
                LabelSSTRecord lrec = (LabelSSTRecord) record;
                System.out.println("String cell found with value "
                        + sstrec.getString(lrec.getSSTIndex()) + " sheet " +  sheetName);
                break;
        }
    }

    /**
     * Read an excel file and spit out what we find.
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException
    {
          ar = new ArrayList();
        // create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = new FileInputStream(args[0]);
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = poifs.createDocumentInputStream("Workbook");
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        req.addListenerForAllRecords(new EventExample());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        fin.close();
        // and our document input stream (don't want to leak these!)
        din.close();
        System.out.println("done.");

    }
}

Select allOpen in new window

Encountered workbook
New sheet named: Sheet1
New sheet named: Sheet2
New sheet named: Sheet3
String table value 0 = ttt
String table value 1 = mmm
String table value 2 = ggg
String table value 3 = trattata
Encountered sheet reference Sheet1
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
Row found, first column at 0 last column at 1
String cell found with value ttt sheet Sheet1
String cell found with value mmm sheet Sheet1
String cell found with value ggg sheet Sheet1
Encountered sheet reference Sheet2
Row found, first column at 0 last column at 1
String cell found with value trattata sheet Sheet2
Encountered sheet reference Sheet3
done.

Select allOpen in new window

This is an interesting thread with folks sufferring with the same issues with this POIFs:
http://www.thatsjava.com/java-core-apis/42255/

Not much of any better solution though in this thread, though there are some good explanations like this one:
----------------------------------------------------------------------
Well, lets say you have the standard three worksheets, with some cells.

you will get:

BoundSheetRecord.sid (sheet1)

BoundSheetRecord.sid (sheet2)

BoundSheetRecord.sid (sheet3)

BOFRecord (bof.TYPE_WORKSHEET) - index 1

RowRecord

NumberRecord

SSTRecord

LabelSSTRecord

The BOFRecord, type Worksheet is an event to say worksheet found.

The RowRecord says that a row has been found

The NumberRecord says that a cell with a number value has been found

The SSTRecord is a touch more complex, its an index to the string

table. You get the SSTRecord before you get the LabelSSTRecord. I'm

not sure if you have a 1 to many relationship, but you will see that I

get the sstrec in the SSTRecord event and then refer to it with the

LabelSSTRecord. So I suspect its a one to one relationship.

The problem is that the events are based on the structure of the xls

file, not on a logical order of how the table is represented.

If you want, just change processRecord function to:

public void processRecord(Record record) {

System.out.println(record.toString());

}

That will output a nice structure of what happens. Not sure if I

got the above 100% correct, as i'm at home now. can check tomorrow for

you if you wish.
----------------------------------------------------------------------------------



Strange that we cannot link them directly -say, there is a method

getPositionOfBof() (begginning offset of the recodr) of the BoundSheetRecord (corresponding the Workssheet and having worksheet name)

but there is no such method either at BOFRecord
or at RowRecord level - which would allow to link them  together,


So as of now I could not find methods which will link them directly

so still looks like the order is
the  way to link them

It certainly is some work to access the sheets. As you show, you have to "inventory" them with BoundSheetRecord and then actually access them in with  BOFRecord.TYPE_WORKSHEET.

I should add, I don't want to, nor do I think it is possible to 'store the sheet for later use'. Rather, I used a HashMap remembering the position and name of the sheet.