Read excel file in java using poi

Read excel file in java using poi

In this tutorial, we will learn to how to read excel file in Java using Apache POI. POI stands for Poor Obfuscation Implementation, is a powerful Java library which works with different Microsoft Office formats such as Excel, Word etc.

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program.

Apache POI can read both Excel formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later).

Although it is not an opened file format, Java applications can still read and write Excel files using the Apache POI – the Java API for Microsoft Documents, because the development team uses reverse-engineering to understand the Excel file format. Hence the name POI stands for Poor Obfuscation Implementation.

Dependencies

First of all, We need to add the required dependencies for including Apache POI in our project. If you use maven, you need to add the following dependencies to your pom.xml

Go to mvn repository for latest version click here

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.15</version>
 </dependency>
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>

To Download Sample Excel file  click here

Program to Read an excel file using Apache POI

package TestNGproject.Practice;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author Admin
 *
 */
public class ReadandWriteExcel {

	public static void main(String[] args) {

		try {
			// Specify the path of file
File src = new File("src/test/java/TestNGproject/Practice/testdata.xlsx");

FileInputStream fis = new FileInputStream(src);

XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet data = wb.getSheetAt(0);

System.out.println(data.getRow(0).getCell(0).getStringCellValue());

System.out.println(data.getRow(0).getCell(1).getStringCellValue());

System.out.println(data.getRow(0).getCell(2).getStringCellValue());

System.out.println(data.getRow(1).getCell(0).getStringCellValue());

System.out.println(data.getRow(1).getCell(1).getStringCellValue());

System.out.println(data.getRow(1).getCell(2).getStringCellValue());

System.out.println(data.getRow(2).getCell(0).getStringCellValue());

System.out.println(data.getRow(2).getCell(1).getStringCellValue());

System.out.println(data.getRow(2).getCell(2).getStringCellValue());

		} catch (Exception e) {

			System.out.println(e.getMessage());

		}

	}

}

Output

username1
password1
url1
username2
password2
url2
username3
password3
url3

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

Author: Saad Mansuri

Leave a Reply

Your e-mail address will not be published. Required fields are marked *