How to Read Excel Files in Java using Apache POI

Apache POI is one of the trusted java package to handle MS office document. Using POI we can read write data in MS Excel using java, Even we can read write in MS word, power point.

In this post, I am discussing some common activities required to do in real life application for MS excel operations.

Some useful classes which are required to handle excel file:

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.

   1.       HSSF is used for Excel 97-2000 file format. E.g. HSSFWorkbook, HSSFSheet.

   2.       XSSF is used for Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.  

   3.       SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.

XSSF (XML SpreadSheet Format) – Used to reading and writing Open Office XML (XLSX) format files.   

HSSF (Horrible SpreadSheet Format) – Use to read and write Microsoft Excel (XLS) format files.

How to Read Excel File (XLSX) in Java

Writing a file using POI is very simple and involve following steps:

a.       Opening workbook

b.      Point to the sheet in workbook

c.       Traversing row in sheet

d.      Read cells in sheet

     a.       Opening Workbook:

In this example I am using xlsx format means I am using XSSF class.

File fileName = new File("C://testprokect/dataSheet.xlsx"); 

FileInputStream fis = new FileInputStream(fileName );

// Finds the workbook instance for XLSX file

 XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);

Now myWorkBook contains all sheets which are available for the workbook.

// Return first sheet from the XLSX

     b.      Point to the sheet in workbook

workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0);

Here I am using only fist sheet of the workbook. Now mySheet pointing only 1^st sheet of the workbook.

// Get iterator to all the rows in current sheet 

      c.       Traversing row in sheet

Iterator<Row> rowIterator = mySheet.iterator();

     d.      Read cells in sheet

   

// Traversing over each row of XLSX file
while (rowIterator.hasNext()) {
Row row = rowIterator.next(); // For each row, iterate through each
// columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
default:
}
}
System.out.println("");
}
}