Scientech Easy Reading Excel File in Java using POI | Scientech Easy

Wednesday, December 26, 2018

Reading Excel File in Java using POI

In the previous tutorial, we learned the basic of Apache POI API and steps to download and install the POI Jar files. If you don't know the basic of Apache POI, We will recommend that first, you go to the previous POI tutorial. Now In this tutorial, we will learn another very important topic on how to read an Excel(.xlsx) file in Java using Apache POI API in a very simple way and step by step. This topic is very important for interview purpose for beginners and experienced. The interviewer can ask you one or two questions related to the coding. 

Reading Excel File in Java using POI


To maintain the test cases and test data, Microsoft Excel is the favorite tool used by testers. It gives us a lot of features and a well-structured way to store and analyze data, to 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 an 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 the 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 below 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. 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 to read Excel sheet using POI API showing in the below picture:
How to read excel sheet using poi in Java, Read data from excel file in selenium xssf webdriver using POI API.

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 the below sample text.
Datasheet
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
Program source code 1: In this example program, 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.
    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 Datatype String to store the path of the file. String filePath="C:\\ExcelSheet\\DataSheet.xlsx"; // Create an object of the file class and pass the '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 Datatype String and store the 'DataSheet.xlsx' as a file name. String fileName="DataSheet.xlsx"; // Get the index position of the first occurrence of the specified substring. int index=fileName.indexOf("."); //The 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 successfully
How 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 given object represents a string equivalent to this string.
5. If the Open file is an xlsx file, it will create an object of the XSSFWorkbook class and Datasheet.xlsx file opened successfully.

Program source code 2: In this example program, we will read the contents of the cells in the Excel spreadsheet row by row without using Iterator. We will use the above excel sheet to read the data.
    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 Datatype String to store the path of the file. String filePath="C:\\ExcelSheet\\DataSheet.xlsx"; //Create an object of the file class and pass the '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 the reference variable 'fis' as a parameter. 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"); // XSSFSheet Sheet=wb.getSheetAt(0); // Call getStringCellValue() method to read the data from first row and first column. Since the 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: Password1
In this example program, we did not iterate rows and cells of the spreadsheet. We just read the data. 

Program source code 3: In this program, 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 picture.
ExcelData
Use the following code to open an existing ExcelData workbook.

    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 { // Declare a variable 'filePath' with Datatype String to store the path of the file. String filePath="C:\\ExcelSheet\\ExcelData.xlsx"; //Create an object of the file class and pass the '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 the reference variable 'fis' as a parameter. 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 the 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 the 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 the getCellType() method to compare the value of the 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.0
If you have any difficulty in the Iterator concept in Java, you can get the tutorial "Iterators in Java" in a very simple way and step by step. 

How to send multiple sets of Data from the Excel Sheet using POI API. 

Program source code 4: In this example program, 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 and sign up with two different email id and password. You can also use other websites like facebook, Gmail etc. to log in the webpage but the concepts will be the same. The only change will happen in the XPath of username, password, and URL of the website.
2. Create an Excel sheet like this:
Sheet
 Use the following code to read username and password from the Excel sheet to log in the page using POI API.
    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 the 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/"; // Now call the get() command to open the URL of the webpage. driver.get(url); // 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 the xpath of the username. WebElement username=driver.findElement(By.xpath("//input[@name='username']")); String str=userName.get(i); // Get the username from the excel sheet one by one. // Send the username using sendKeys() method. username.sendKeys(str); // Or One line code: username.sendKeys(userName.get(i)); // Find the XPath of the password. WebElement password=driver.findElement(By.xpath("//input[@name='password']")); password.sendKeys(passWord.get(i)); // Find the 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 successfull."); 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 the value of the 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.
Note: If you use this URL "www.pixabay.com" then during the execution process, you maximize the webpage otherwise you will get the exception.

Final words 
We hope that this article will help to learn how to read Excel sheet using POI API in Java. You follow all the above important steps and practice two or three times in Eclipse.