Read Excel With Apache POI UserModel

If we try to make a framework we need to read and write a file,it may be excel may be xml or may be CSV or even it may be a text file.Here we try to read and write excel using Apache POI.

Q.What is Apache POI ?

Ans:-  Apache POI(Poor Obfuscation Implementation) is a open source API   to read .xls(Excel 97) and .xlsx(Excel 2007) using java.

Q.What type of model should I use to read or write excel file using Apache POI ?

Ans:- Basically Apache POI have  two types of model

1. UserModel: It can read and write file but need more memory and reading speed is not like EventModel. 
2. EventModel: It can only read file with better reading speed but hard to implement and it need less memory .

 So we can draw a conclusion that if we have less time and small file we use UserModel to read and write if we have a big size of excel for only read  and have some time to code then choose EventModel.

Q.How to implement UserModel ?

Ans:-

• At first we need to download Apache POI API. We can download(Apache POI 3.10) this from here.
• We extract the files from downloaded compressed folder . It looks like below.
  

 

•  Open our IDE and create a project to read excel.
• Here we will read .xls and .xlsx file So we need to import jar from Apache POI which we extracted previously to read both type of file.
• We need to add this .jar with our projects

                1. poi-3.10-FINAL-20140208.jar if we try to read only .xls file then it is enough but if we try to read .xlsx file also then we need to add this .jar as given below.
                2.poi-ooxml-3.10-FINAL-20140208.jar
                3.poi-ooxml-schemas-3.10-FINAL-20140208.jar
                4.xmlbeans-2.3.0.jar
                5.dom4j-1.6.1.jar

• Finally it should be shown like that
  
  
  
  
  

Total code is given below


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelFileReadApachePOI {

    /**
     * @param Excel2003FileToRead
     * @throws java.io.IOException
     */
    public static void readXLSFile(FileInputStream Excel2003FileToRead) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook(Excel2003FileToRead);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            row = (HSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                cell = (HSSFCell) cells.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    System.out.print(cell.getStringCellValue() + " ");
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue() + " ");
                } else {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println();
        }
    }

    public static void readXLSXFile(FileInputStream Excel2007FileToRead) throws IOException{
        XSSFWorkbook xwb = new XSSFWorkbook(Excel2007FileToRead);
        XSSFSheet xsheet = xwb.getSheetAt(0);
        XSSFRow xrow;
        XSSFCell xcell;
        Iterator xrows = xsheet.rowIterator();
        while (xrows.hasNext()) {
            xrow = (XSSFRow) xrows.next();
            Iterator xcells = xrow.cellIterator();
            while (xcells.hasNext()) {
                xcell = (XSSFCell) xcells.next();
                if (xcell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    System.out.print(xcell.getStringCellValue() + " ");
                } else if (xcell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    System.out.print(xcell.getNumericCellValue() + " ");
                } else {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println("");
        }
    }

    public static void main(String[] args) {
        try {
            File fileToRead = new File("resources/Test.xlsx");
            String fileToReadname = fileToRead.getName();
            String extension = fileToReadname.substring(fileToReadname.lastIndexOf(".")
                    + 1, fileToReadname.length());
            String excel2003 = "xls";
            String excel2007 = "xlsx";
            if (excel2003.equalsIgnoreCase(extension)) {
                FileInputStream Excel2003FileToRead = new FileInputStream(fileToRead);
                readXLSFile(Excel2003FileToRead);
            } else if (excel2007.equalsIgnoreCase(extension)) {
                FileInputStream Excel2007FileToRead = new FileInputStream(fileToRead);
                readXLSXFile(Excel2007FileToRead);
            }
        } catch (IOException ex) {
            System.out.println(ex.getMessage());
        }
    }
}