CODEWITHSUNDEEP
javaexcelloopsapache-poi
hgpl
hgpl

Reputation: 869

Iteration through multiple sheet of excel in java

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.

Upvotes: 10

Views: 40530

Answers (3)

Chad Crowe
Chad Crowe

Reputation: 1338

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) }

Upvotes: 1

Mathias G.
Mathias G.

Reputation: 5105

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();

Upvotes: 3

billerby
billerby

Reputation: 1978

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();
    }
}

Upvotes: 22

Related Questions