What is Apache POI

Team, Today we will discuss what is Apache POI and how can read/write data from excel sheet using it.

Apache POI, a project run by the Apache Software Foundation, and previously known as a sub-project of the Jakarta Project, it provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

POI stands for "Poor Obfuscation Implementation".

POI contains several sub components but we will discuss two(HSSF and XSSF) and see how we can read write data from MS Office excel.

  • HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
  • XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.
  • HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files
  • HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.

Before we start let’s make a list of required files and prepare environment. We need...

a) Apache POI v3.8 or above
b) JDK v1.5 or above
c) eclipse (optional)

Team it is very important to make a block/raw diagram before implementation in real, for example in this case let’s make it first as

Section 1 : will contain all import section
Section 2 : Declare a class
Section 3 : create a method which will read data from excel
Section 4 : Make user define function which will convert cell data into string and call this method in section 3.

So above is my raw material and lets implement it in a very very simple ways...

For Example

//Section 1: Import Section
import java.io.File;
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;

//Section 2 : Declare a class
public class readingXL {

//Section 3:This method will read data from excel
public void readXL() throws Exception{
        String sPath = "D:\\Selenium_Projects\\abc.xls"; //This is excel file path
        File myFile = new File(sPath);  //Pass this file path to myFile
        FileInputStream myStream = new FileInputStream(myFile);  //Declare a input stream and pass myFile in it
        HSSFWorkbook myWB = new HSSFWorkbook(myStream); //Declare work book and pass myStream in it
        HSSFSheet mySheet = myWB.getSheetAt(0); //Go to the sheet at index 0 i.e. First One
        int xRows = mySheet.getLastRowNum() + 1; //Get rows number
        int xCols = mySheet.getRow(0).getLastCellNum(); //Get column number
        System.out.println("Number of rows are : " + xRows);
        System.out.println("Number of columns are : " + xCols);
        String[][] xData = new String[xRows][xCols]; //Declare an array of string type which will hold values
        for(int i=0; i<xRows; i++){
            HSSFRow row = mySheet.getRow(i); //Pointing to the row from we need to read data; it will be first row at first iteration
            for(int j=0; j<xCols; j++){
                HSSFCell cell = row.getCell(j);  //Pointing to the cell of the row
                String value = cellToString(cell); //Getting value of the cell and put it into a variable "value"; Here cellToString() is a user define function which will convert cell value in string.  
                xData[i][j] = value;   //Store that value in to array
                System.out.print(value); //Print data in console
//Section 4: This method will convert object of type cell into String
public String cellToString(HSSFCell cell){//Declare a method "cellToString()" which will convert an object of type cell into string
        int type = cell.getCellType();// This method "getCellType()" will return an integer which is 0-5 and on the basis of this integer value we will use SWITCH
        Object result; // Its an temporary object which will hold value in switch statement
        case HSSFCell.CELL_TYPE_NUMERIC: // If a cell contain numeric value then it will return 0
            result = cell.getNumericCellValue();
            System.out.println("Value of type is : " +type);
        case HSSFCell.CELL_TYPE_STRING: // If a cell string value then it will return 1
            result = cell.getStringCellValue();
        case HSSFCell.CELL_TYPE_FORMULA:  // If a cell contain formula then it will return 2
            System.out.println("Can not eveulate formila in JAVA");
            throw new RuntimeException("Can not eveulate formila in JAVA");
        case HSSFCell.CELL_TYPE_BLANK: // If a cell contain blank value then it will return 3
            result = "";
        case HSSFCell.CELL_TYPE_BOOLEAN: // If a cell contain boolean value then it will return 4
            result = cell.getBooleanCellValue();
        case HSSFCell.CELL_TYPE_ERROR:// If a cell contain error then it will return 5
            System.out.println("Can not eveulate formila in JAVA");
            throw new RuntimeException("This cell has an error");
            throw new RuntimeException("We dont support this cell type : " +type);
        return result.toString();   //Here "toString()" method will convert result object to string; Here question may arise i.e why convert value into string...? This is so because we already have an array[xData] in which we will put this value and that is declared as STRING.