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