Showing posts with label Apache POI. Show all posts
Showing posts with label Apache POI. Show all posts

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

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

How can I add the Apache POI Library in Eclipse


How can I add the Apache POI Library in Eclipse


Many times application requires excel to generate report or some time application expect excel file to read required data.  Example to design a testing framework we need several  excel file to read input data and expected output for validation purpose.

installation of POI in javaAny programs who wants to access MS office files like excel, word , power point as a output or input must be used predefined APIs.  Apache POI is one of them and it is very powerful and popular java API to access MS office files.

For better understanding I am going write down one program which will read one excel file using poi and print the value, I will explain each line .


Download & Installation process 


Before the code we need to download the latest version on the POI from http://poi.apache.org/download.html and unzip its contains to a folder.  After extracting the folder we need to add all jars in eclipse project build path.

Required jars in POI




Required jars which we need to add in our project build path




Required jars for POI





Required jars which we need to add in our project build path
Required jars for POI





Required jars which we need to add in our project build path






After adding jars in project build path

Add poi jars in eclipse


After adding all jars in project build path project is read to use Apache POI.