Saturday, August 11, 2018

Read excel using java API

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 Projects pure Java implementation of the Excel 97(-2007) file format. XSSF is the POI Projects 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());
            }
        }