Java program to read excel file using apache poi

 here is the java function to read the data from excel file using apache poi and testng

First add the following dependencies into maven project as we are using the testNG framework and maven project or if you are not using maven project then add respective JAR files into your project.

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>5.0.0</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>4.1.2</version>

</dependency>

<dependency>

<groupId>org.testng</groupId>

<artifactId>testng</artifactId>

<version>7.4.0</version>

<scope>test</scope>

</dependency>

package readfromexcel;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Collections;

import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

public List<String> ExcelRead(String excelName, String sheetname) throws IOException

{

XSSFWorkbook ws = new XSSFWorkbook("full path to excel file"+excelName+".xlsx");

XSSFSheet sheet = ws.getSheet(sheetname);

int rowcout = sheet.getLastRowNum();

int columncount = sheet.getRow(0).getLastCellNum();

List<String> excel1 = new ArrayList<String>();

for (int i = 0; i <= rowcout; i++) {

for (int j = 0; j < columncount; j++) {

String Data = sheet.getRow(i).getCell(j).getStringCellValue();

excel1.add(Data);

}

}

System.out.println(excelName+"Read Complete");

return excel1;

}

}

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd" >

<suite name="JDBC_Demo">

<test verbose="2" name="Default test">

    <classes>

    

      <class name="Com.ReadExcel_testCase"/>       

</classes>

</test>

</suite>