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