Handling Excel spreadsheets

Apache POI (http://poi.apache.org/index.html) is a set of APIs providing access to many Microsoft products including Excel and Word. It consists of a series of components designed to access a specific Microsoft product. An overview of these components is found at http://poi.apache.org/overview.html.

In this section we will demonstrate how to read a simple Excel spreadsheet using the XSSF component to access Excel 2007+ spreadsheets. The Javadocs for the Apache POI API is found at https://poi.apache.org/apidocs/index.html.

We will use a simple Excel spreadsheet consisting of a series of rows containing an ID along with minimum, maximum, and average values. These numbers are not intended to represent any specific type of data. The spreadsheet follows:

We start with a try-with-resources block to handle any IOExceptions that may occur:

try (FileInputStream file = new FileInputStream( 
        new File("Sample.xlsx"))) { 
    ... 
    } 
} catch (IOException e) { 
    // Handle exceptions 
} 

An instance of a XSSFWorkbook class is created using the spreadsheet. Since a workbook may consists of multiple spreadsheets, we select the first one using the getSheetAt method.

XSSFWorkbook workbook = new XSSFWorkbook(file); 
XSSFSheet sheet = workbook.getSheetAt(0); 

The next step is to iterate through the rows, and then each column, of the spreadsheet:

for(Row row : sheet) { 
    for (Cell cell : row) { 
        ... 
    } 
out.println(); 

Each cell of the spreadsheet may use a different format. We use the getCellType method to determine its type and then use the appropriate method to extract the data in the cell. In this example we are only working with numeric and text data.

switch (cell.getCellType()) { 
    case Cell.CELL_TYPE_NUMERIC: 
        out.print(cell.getNumericCellValue() + "\t"); 
        break; 
    case Cell.CELL_TYPE_STRING: 
        out.print(cell.getStringCellValue() + "\t"); 
        break; 
   } 

When executed we get the following output:

ID Minimum Maximum Average 
12345.0 45.0 89.0 65.55
23456.0 78.0 96.0 86.75
34567.0 56.0 89.0 67.44
45678.0 86.0 99.0 95.67

POI supports other more sophisticated classes and methods to extract data.