Print

Read Excel File In Java Using Apache POI

Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc.In this tutorial we will use Apache POI library to read an Excel spreadsheet. We will read the excel file "workbook.xls" we created in the article "Write Excel File In Java Using Apache POI".  

 

Create a Java maven-based project and add the following Apache POI dependency in the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>

Write the following code in the class "ReadExcelFileWithJavaPOI.java".

package com.example.excel;

import jxl.write.WriteException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import javax.swing.text.html.HTMLDocument;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

public class ReadExcelFileWithJavaPOI {
    public static void main(String[] args) throws WriteException {
        try {
            //Open an excel file as input stream
            FileInputStream fileInputStream=new FileInputStream(new File("workbook.xls"));
            //Get the workbook instance for XLS file
            //define a workbook
            HSSFWorkbook hssfWorkbook=new HSSFWorkbook(fileInputStream);
            //Get first sheet from the workbook
            HSSFSheet sheet=hssfWorkbook.getSheetAt(0);
            //Get iterator to all the rows in the current sheet
            Iterator rowIterator=sheet.iterator();
            while (rowIterator.hasNext())
            {
                Row row=rowIterator.next();
                System.out.println("Row Number  "+row.getRowNum());
                //For each row, iterate through each columns
                Iterator cellIterator=row.cellIterator();
                while (cellIterator.hasNext())
                {
                    Cell cell=cellIterator.next();
                    System.out.print(cell.getStringCellValue() + ",");
                }
                System.out.println("");
            }

            fileInputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Run the above code and see the result. The content of the excel file "workbook.xls" is displayed as below.

Row Number  0
TestId,TestName,TestModule,TestType,TestSteps,Action,TestResult,Note,
Row Number  1
1,Login,Dashboard,Regression,1,Open Browser,Browser Should Open,This is a note,