Monday, January 10, 2011

Read excel using java API

Read Spreadsheet








In this post, we are going to learn how to read spreadsheet content using java API.

A brief Idea
I am going to use Apache POI HSSF for reading excel data.
Apache POI HSSF(Horrible SpreadSheet Format) is a java API to read and write Microsoft Excel Format files. It can read files written by Excel 97 onwards;

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:
  • low level structures for those with special needs
  • an eventmodel api for efficient read-only access
  • a full usermodel api for creating, reading and modifying XLS files
Here We Go









Let us consider a file test.xls having following data













I have written a sample java class to read this file.


package com.sarf.excel;
import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ReadExcelFile {

      private HSSFRow row;
      private HSSFCell cell;
      private POIFSFileSystem fs;
      private  HSSFWorkbook wb;
      private HSSFSheet sheet;
     


      protected void readExcelData(String pstrFileName)
      {
          // No of rows
          int nRows;
          // No of column
          int cols = 0;

          try
          {
              fs = new POIFSFileSystem(new FileInputStream(pstrFileName));
              wb = new HSSFWorkbook(fs);
              sheet = wb.getSheetAt(0);

              //Get row count
              nRows = sheet.getPhysicalNumberOfRows();
                 
              //Loop for traversing each row in the spreadsheet
              for(int r = 0;r < nRows;r++)
              {
                row = sheet.getRow(r);
                if(row != null)
                {
                  //Column count in the current row
                  cols = sheet.getRow(r).getPhysicalNumberOfCells();
                  //Loop for traversing each column in each row in the spreadsheet
                  for(int c = 0; c < cols; c++)
                  {
                    cell = row.getCell((short)c);
                    // If cell contains String value
                    if(cell != null && cell.getCellType() 
                       == HSSFCell.CELL_TYPE_STRING){
                       System.out.print(cell.getStringCellValue()+"|");
                     }
                    else 
                    if(cell != null && cell.getCellType() 
                       == HSSFCell.CELL_TYPE_NUMERIC){
                      System.out.print(cell.getNumericCellValue());
                     }
                   }
                  System.out.println();
                 }
                }
            }catch (Exception ex) {
                // TODO: handle exception
                System.out.println(ex.getMessage());
            }
        }
}

We can use other methods available in this API to enhance this class. For example, the row iterator can be used in place of loop  like Iterator itrRow = sheet.rowIterator();
Similarly , we can use row.cellIterator(); for cell.




Here is our main class file.

package com.sarf.excel;

public class ReadExcelMain {
      public static void main(String[] args) {
         String strFilePath = "K:/R&D/readExcelFile/src/com/sarf/excel/test.xls";
         ReadExcelFile objREF = new ReadExcelFile();
         objREF.readExcelData(strFilePath);
      }
 }









Empl No|Name|Salary|
E001|Sarfaraz Khan|1000.0
E002|Rahul Khanna|2000.0
E003|Amit Verma|3000.0
E004|Ramesh|4000.0
E005|Rakesh Sharma|5000.0
E006|Viod S.|6000.0
E007|Tom Seth|7000.0