Iteration through multiple sheet of excel in java

Question

I am very new to java. I am creating pList from excel. My excel file contains multiple sheets. I want to iterate through all the sheets of the excel file. How to to this? please help.

Answer 1

public static void main( String [] args ) {
    try {

        InputStream input = POIExample.class.getResourceAsStream( "qa.xls" );
        POIFSFileSystem fs = new POIFSFileSystem( input );
        HSSFWorkbook wb = new HSSFWorkbook(fs);


        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);

            // Do your stuff        
        }

    } catch ( IOException ex ) {
        ex.printStackTrace();
    }
}

Answer 2

Java:

Workbook workbook = WorkbookFactory.create(file);
Iterator<Sheet> sheetIterator;
sheetIterator = workbook.sheetIterator();
while(sheetIterator.hasNext()){
    Sheet sheet = sheetIterator.next();
    out.println(sheet.getSheetName());
}

Scala:

var iterator = workbook.sheetIterator(); while(iterator.hasNext){ var sheet = iterator.next println(sheet) }

Answer 3

From the apache poi documentation we see there is also an iterator available, which is in my opinion a cleaner solution:

Iterator<Sheet> sheetIterator = workbook.iterator();
while (sheetIterator.hasNext()) {
    Sheet sheet = sheetIterator.next();
}

Depending on the type of workbook you use (HSSF or XSSF) you might need to execute an additional cast operation:

• HSSF: POI Project's pure Java implementation of the Excel '97(-2007) file format.

  HSSFSheet sheet = (HSSFSheet) sheetIterator.next();
  

• XSSF: POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

  XSSFSheet sheet = (XSSFSheet) sheetIterator.next();