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

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