Wednesday, August 14, 2013

How to Read Excel File in Java using "Apache Poi" API.


Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc.

Let's create a Excel file and insert some data into this file and give this file name as "JavaIsEasy.xls".



Now we need to add library files to execute this program, as follows.


  • poi-3.7-beta1
  • poi-3.8-beta4-20110826
  • poi-ooxml-3.5-beta5


Add above jar in eclipse.





Now create a java class named as "ReadExcelFile.java".


*******************************************************************************


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFile {
 
  public static void main(String[] args) throws IOException {
      String fname = "C:/DATA/Software/LIFERAY/liferay data/Blogs Material/Core Java/JavaIsEasy.xls"; 
     //********************************************************************
      
      
      InputStream inp = new FileInputStream(fname);
      String fileExtn = GetFileExtension(fname);
      Workbook wb_xssf; 
      Workbook wb_hssf; 
      Sheet sheet = null; // sheet can be used as common for XSSF and HSSFWorkBook
      if (fileExtn.equalsIgnoreCase("xlsx"))
      {
        wb_xssf = new XSSFWorkbook(fname);
       log("xlsx="+wb_xssf.getSheetName(0));
       sheet = wb_xssf.getSheetAt(0);
      }
      if (fileExtn.equalsIgnoreCase("xls"))
      {
   POIFSFileSystem fs = new POIFSFileSystem(inp);
       wb_hssf = new HSSFWorkbook(fs);
       log("xls="+wb_hssf.getSheetName(0));
       sheet = wb_hssf.getSheetAt(0);
      }
      Iterator rows = sheet.rowIterator(); // Now we have rows ready fromthe sheet
      
   while (rows.hasNext()) 
       { 
           Row row = (Row) rows.next();
           log("row#="+row.getRowNum()+"");
           log("**********************");
    //log(row.getPhysicalNumberOfCells()+"");
    Iterator cells = row.cellIterator();
    String cellValue=null;
    while (cells.hasNext())
    {
        Cell cell = (Cell) cells.next();
     
     switch ( cell.getCellType() ) 
     {
     case Cell.CELL_TYPE_STRING:
         log(cell.getRichStringCellValue().getString());
         cellValue=cell.getRichStringCellValue().getString();
     break;
     case Cell.CELL_TYPE_NUMERIC:
           if(DateUtil.isCellDateFormatted(cell)) {
             log(cell.getDateCellValue()+"");
             cellValue=cell.getDateCellValue().toString();
           } else {
             System.out.println(cell.getNumericCellValue());
             cellValue=String.valueOf(cell.getNumericCellValue());
           }
           break;
     case Cell.CELL_TYPE_BOOLEAN:
           log(cell.getBooleanCellValue()+"");
           cellValue=cell.getBooleanCellValue()+"";
           break;
         case Cell.CELL_TYPE_FORMULA:
           log(cell.getCellFormula());
           cellValue=cell.getCellFormula();
           break;
     default:
     }
   
    
    }
       }
   inp.close();
  } 
  
  private static void log(String message)
  {
          System.out.println(message);
  }
  private static String GetFileExtension(String fname2)
  {
      String fileName = fname2;
      String fname="";
      String ext="";
      int mid= fileName.lastIndexOf(".");
      fname=fileName.substring(0,mid);
      ext=fileName.substring(mid+1,fileName.length());
      return ext;
  }
}



*******************************************************************************

Now execute this program.

Output:-

*******************************************************************************

xls=Sheet1
row#=0
**********************
Programming Language
Projects Done
Last Used

*
*
*
row#=4
**********************
PHP
0.0
Not Used

*******************************************************************************