Reading Excel File in Java using POI
In the previous tutorial, we learned the basics of Apache POI API and steps to download and install POI Jar files.
If you don’t know the basics of Apache POI, I recommend that first, you go to Apache POI tutorial.
In this tutorial, we will learn reading excel file in Java using Apache POI API in a very simple way.
This topic is very important for interview purposes for beginners and experienced testers. The interviewer may ask you one or two questions related to the coding.
How to read Excel File in Java using Apache POI API
To maintain test cases and test data, Microsoft Excel is the favorite tool used by testers. It gives us many features and a well-structured way to store and analyze data, send user inputs, and store the test execution outputs.
A tester can create and maintain the tables of test data in an Excel spreadsheet very easily.
To read data from the excel file 2007 (.xlsx), we will use an Excel spreadsheet as a data source using Apache POI API, which is developed by Apache foundation and XSSF (XML Spreadsheet Format).
We will use the below classes to work with excel 2007 files by importing the below statements.
import java.io.File; import java.io.FileInputStream; import org.apache.poi.ss.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
Create the XSSF workbook instance for XLSX file using the below syntax. It takes a parameter of the FileInputStream class object.
XSSFWorkbook workbook = new XSSFWorkbook(fis); // fis is an parameter of the FileInputStream class object.
We will read the sheet name inside the workbook by using getSheet() method. The getSheet method returns sheet name. Use the below code.
XSSFSheet sheet = workbook.getSheet("Sheet1");
or
XSSFSheet sheet = wb.getSheetAt(0);Iterate all the rows in the sheet using the following code.
Iterator rows = sheet.rowIterator(); ----or--- Iterator rows=sheet.iteratore();
Use the below code to iterate all the cells of the current row.
Iterator cells = row.cellIterator();
A Cell can be strings, numeric, formula, or a blank cell. A string cell cannot hold the numeric value and a numeric cell cannot hold the string value.
[adinserter block=”5″]
To get the cell type, use the getCellType() method where CellType represents the type of cell. We can use this method to compare the value of the cell type.
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
// Write your code here
}
For example:
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue() + " ");
}Follow all the steps for reading Excel sheet using Apache POI API
There are the following steps for reading excel file in Java using POI API. They are shown in the below figure.
Let’s take a simple example program to open the existing workbook. First, you download the POI Jar files and install them to the Eclipse IDE. You can see the previous tutorial for this.
Now create an excel sheet using MS Office Excel and write required test data in the excel file for your test. You can see sample text below the screenshot.
Once the excel sheet is ready, you save it by giving a name as Datasheet and close the created excel file before executing the test script.
Open Existing Workbook
Let’s create a program in which we will open the existing workbook only. We will not read the excel spreadsheet inside the workbook. Use the following code to open an existing Datasheet workbook.
Program source code 1:
package excelsheet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class OpenWorkbook
{
public static void main(String[] args) throws IOException
{
// Declare a variable 'filePath' with data type String to store path of file.
String filePath = "C:\ExcelSheet\DataSheet.xlsx";
// Create an object of the file class and pass "filePath" as a parameter to open xlsx based data file.
File file = new File(filePath);
// Create an object of the FileInputStream class and pass the reference variable "file" as a parameter to read excel data file.
FileInputStream fis = new FileInputStream(file);
// Declare a variable "fileName" with data type String and store the "DataSheet.xlsx" as a file name.
String fileName = "DataSheet.xlsx";
// Get the index position of first occurrence of specified substring.
int index = fileName.indexOf("."); // Return type of this method is an Integer.
System.out.println(index);
// Call substring() method to get the file extension name by splitting file name into substring.
String extType = fileName.substring(index);
// The above three lines of code can be written in one step like this:
// String extType = fileName.substring(fileName.indexOf("."));
// Use if statement and equals() method to check the condition whether the file is xlsx file.
if(extType.equals(".xlsx"))
{
// If it is xlsx file, create the object of XSSFWorkbook class and pass the reference variable "fis" as a parameter.
XSSFWorkbook wb = new XSSFWorkbook(fis);
System.out.println("OpenFile is .xlsx file");
System.out.println("DataSheet.xlsx file opened successfully");
}
// Similarly, check the condition whether the file is xls file.
if(extType.equals(".xls"))
{
// If the file is xls file, create the object of the HSSFWorkbook class and pass the reference variable "fis" as a parameter.
HSSFWorkbook wb = new HSSFWorkbook(fis);
System.out.println("OpenFile is .xls file");
System.out.println("DataSheet.xls file opened successfully");
}
}
}Output:
OpenFile is .xlsx file
DataSheet.xlsx file opened successfullyHow it works……
1. When the test is executed, the file class opens the ‘xlsx’ based data file by using the file path. The FileInputStream class reads the excel data file.
2. indexOf(String str) method returns the index position of the first occurrence of the specified substring in the string. The return type of this method is an integer. If there is no such occurrence, it will return -1.
3. substring(int index) method returns the file extension name by splitting file name into substring.
For example:
"unhappy".substring(2) returns "happy". "Datasheet.xlsx".substring(9) returns ".xlsx"
4. equals(Object o) method compares the string to the specified object. It returns true if the given object represents a string equivalent to this string.
[adinserter block=”2″]
5. If the Open file is an xlsx file, it will create an object of the XSSFWorkbook class and Datasheet.xlsx file opened successfully.
How to read Data of Cells in Excel spreadsheet without Iterator?
Let’s make a program where we will read the contents (data) of the cells in the Excel spreadsheet row by row without using Iterator. We will use the above excel sheet to read the data.
Program source code 2:
package excelsheet;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel
{
public static void main(String[] args) throws Exception
{
// Declare a variable 'filePath' with data type String to store path of the file.
String filePath = "C:\ExcelSheet\DataSheet.xlsx";
// Create an object of the file class and pass 'filePath' as a parameter to open xlsx based data file.
File file = new File(filePath);
// Create an object of the FileInputStream class and pass the reference variable 'file' as a parameter to read excel data file.
FileInputStream fis = new FileInputStream(file);
// Create the object of XSSFWorkbook class and pass reference variable 'fis' as a parameter.
XSSFWorkbook wb = new XSSFWorkbook(fis);
System.out.println("DataSheet.xlsx file opened successfully");
// Call getSheet() method to read sheet name inside the workbook. The getSheet() method will return sheet name.
// Since the return type of getSheet method is an XSSFSheet. Therefore, we will store the returning value by using variable 'sheet' with type XSSFSheet.
XSSFSheet sheet = wb.getSheet("Sheet1");
// XSSFSheet Sheet = wb.getSheetAt(0);
// Call getStringCellValue() method to read data from first row and first column. Since getStringCellValue() will return string data. Therefore, we will store it using a variable 'data00' with Datatype string.
String data00 = sheet.getRow(0).getCell(0).getStringCellValue();
System.out.println("Data from first row and first cell: "+data00);
// Similarly, Read the data from the first row and second column.
String data01 = sheet.getRow(0).getCell(1).getStringCellValue();
System.out.println("Data from first row and second cell: "+data01);
XSSFCell data = sheet.getRow(1).getCell(0);
String data10 = data.getStringCellValue();
// This method reads the string data from the cell one and second row.
System.out.println("Data from the second row and first cell: " +data10);
XSSFCell data1 = sheet.getRow(1).getCell(1);
// Second cell and second row.
String data11 = data1.getStringCellValue();
System.out.println("Data from second row and second cell: " +data11);
}
}Output:
DataSheet.xlsx file opened successfully
Data from first row and first cell: Username
Data from first row and second cell: Password
Data from second row and first cell: Username1
Data from second row and second cell: Password1In this example program, we did not iterate rows and cells of the spreadsheet. We just read the data.
How to read Data from Cells using Iterator method?
Let’s create a program where we will read the data from the cells by iterating the rows and cells using rowIterator() and cellIterator() method. The excel sheet is given below in the screenshot.
Use the following code to open an existing ExcelData workbook.
Program source code 3:
package excelsheet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
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 ReadExcelSheet
{
public static void main(String[] args) throws IOException
{
String filePath = "C:\ExcelSheet\ExcelData.xlsx";
File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
System.out.println("DataSheet.xlsx file opened successfully");
// Call getSheet() method to read the sheet name inside the workbook. The getSheet() method will return the sheet name.
// Since the return type of getSheet method is an XSSFSheet. Therefore, we will store the returning value by using variable 'sheet' with type XSSFSheet.
XSSFSheet sheet = wb.getSheet("Sheet1");
// Call iterator method to iterate row of the sheet.
Iterator<Row> rows = sheet.rowIterator();
rows.next(); // It will start to iterate from the second row.
while (rows.hasNext())
{
Row r = rows.next();
XSSFRow row = (XSSFRow)r; // Typecasting.
// OR In one line:
XSSFRow row = (XSSFRow)rows.next();
// Iterating all cells of the current row.
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext())
{
Cell c = cells.next();
XSSFCell cell = (XSSFCell)c;
// OR In one line of code:
XSSFCell cell = (XSSFCell)cells.next();
// Call getCellType() method to compare value of cell type using if-else statement.
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
String stringData = cell.getStringCellValue();
System.out.print(stringData + " ");
// OR In one line code:
System.out.println(cell.getStringCellValue() +" ");
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
double numericData = cell.getNumericCellValue(); // Returns numeric value.
System.out.print(numericData + " ");
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue() + " ");
} else {
// Here if require, we can also add below methods to
// Read the cell content
// XSSFCell.CELL_TYPE_BLANK
// XSSFCell.CELL_TYPE_FORMULA
// XSSFCell.CELL_TYPE_ERROR
}
}
System.out.println();
try {
// Close the fileinputstream.
fis.close();
}
catch (IOException e) {
e.printStackTrace();
}
} }}Output:
DataSheet.xlsx file opened successfully
Deepak 1234.0
Rashmi 12345.0
Amit 64576.0If you have any difficulty with the Iterator concept in Java, you can go to this tutorial Iterators in Java.
How to send multiple sets of Data from Excel Sheet using Apache POI API?
Let us make a program where we will send multiple sets of data such as ‘username’ and ‘password’ from the Excel sheet to the login page using POI API.
1. Open the URL: www.pixabay.com
2. Sign up with two different email id and password. You can also use other websites like Facebook, Gmail, etc. to login webpage for your convenience but the concepts will be the same. The only change will happen in the XPath of username, password, and URL of website.
3. Create an excel sheet like this:
Use the following code to read username and password from the Excel sheet to log in the page using POI API.</div
Program source code 4:
package excelsheet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.concurrent.TimeUnit;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
public class ReadExcelSheet2
{
public void loginData() throws IOException
{
// Create Firefox driver object and launch Firefox browser.
WebDriver driver = new FirefoxDriver();
// Declaration of variable URL with datatype String and store the URL of the webpage.
String url = "https://pixabay.com/en/accounts/login/?next=/en/accounts/register/";
// Call get() command to open the URL of the webpage.
driver.get(url);
driver.manage().window().maximize();
// Wait for page to load completely.
driver.manage().timeouts().implicitlyWait(60, TimeUnit.SECONDS);
// Call ArrayList readExcelData() method by passing the integer value.
ArrayList userName = readExcelData(0);
ArrayList passWord = readExcelData(1);
for(int i=0; i < userName.size(); i++)
{
// Find XPath of username.
WebElement username = driver.findElement(By.xpath("//input[@name='username']"));
String str = userName.get(i);
// Get username from the excel sheet one by one.
// Send username using sendKeys() method.
username.sendKeys(str);
// Or One line code:
username.sendKeys(userName.get(i));
// Find XPath of the password.
WebElement password = driver.findElement(By.xpath("//input[@name='password']"));
password.sendKeys(passWord.get(i));
// Find XPath of Log in button and click using variable signIn.
WebElement signIn = driver.findElement(By.xpath("//input[@value='Log in']"));
signIn.click();
driver.findElement(By.xpath("//img[@class='profile_image']")).click();
driver.findElement(By.linkText("Log out")).click();
driver.findElement(By.linkText("Log in")).click();
}
System.out.println("Test is successful.");
driver.close();
}
// Create a readExcelData() method with return type ArrayList and parameter colNo.
ArrayList readExcelData(int colNo) throws IOException
{
String filePath = "C:\ExcelSheet\Excelsheet.xlsx";
File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheet("Sheet1");
Iterator<Row> row = sheet.rowIterator();
row.next();
// Create an ArrayList object of String type. It will accept String value only.
ArrayList<String> ar = new ArrayList<String>();
// Checking the next element availability using reference variable row.
while(row.hasNext())
{
// Moving cursor to next Row using reference variable row.
Row r = row.next();
// Moving cursor to the cell by getting cell number.
Cell c = r.getCell(colNo);
// Read value of cell using getStringCellValue() method.
String data = c.getStringCellValue();
// Adding the value of the cells in the array list by passing 'data' as a parameter.
ar.add(data);
// OR One line code:
ar.add(row.next().getCell(colNo).getStringCellValue());
}
System.out.println("List: " +ar);
// Return the data to the arraylist method.
return ar;
}
public static void main(String[] args) throws IOException
{
// Create an object of the class ReadExcelSheet2.
ReadExcelSheet2 read = new ReadExcelSheet2();
// Call loginData() method using reference variable read.
read.loginData();
}
}Output:
List: [xyz@gmail.com, pqr@yahoo.com]
List: [xyz986, pqr986]
Test is successfull.Follow all the above steps to run the program because this program is very important for the company’s project. So practice step by step.
Hope that this tutorial has covered almost all important key points related to reading Excel file using POI API in Java. I hope that you will have understood the basic concepts.
Thanks for reading!!!
Next ⇒ How to write Excel File in Java using Apache POI⇐ PrevNext ⇒
