Reading the Data From Excel in Selenium

Selenium WebDriver is the about used automation tool for the automation of web applications. Now, nosotros know that these web applications are used by multiple users, and each 1 of those uses the applications as per their own information. And so, considering the usage, it becomes the primary responsibleness of the QAs also to exam the spider web applications with varying data sets. Now the user journeys will be the same, but the information ready will be different. Therefore, it makes more sense to execute the same test instance with different data, instead of writing a separate examination case for each user journey with each information set. This is where Microsoft Excel comes in handy, which is ane of the favorite tools for storing test data. Excel in Selenium is 1 of the well-nigh used combinations for storing test data and then running the same test instance confronting various data sets.

There are various libraries in JAVA which helps in reading/writing information from Excel files. Just,Apache POI is ane of the most used libraries, which provides diverse classes and methods to read/write data from diverse formats of Excel files (xls, xlsx etc). Subsequently, in this commodity, we volition understand the details of Apache POI and how we can utilise the aforementioned to read/write data from Excel files, by covering the details nether the following topics:

• What is Apache POI?
  • How to manage Excel workbooks?
  • How to manage Excel sheets?
  • Likewise, how to manage Excel rows?
  • How to manage Excel cells?
• How to read data from Excel in Selenium tests using Apache POI?
  • Additionally, how to read a specific cell value?
  • How to read the entire Excel canvas?
• How to write data in Excel in Selenium tests using Apache POI?
  • Moreover, how to write to a new cell in an existing row?
  • And, how to write to a new cell in a new row?

What is Apache POI?

Apache POI,  where POI stands for (Poor Obfuscation Implementation) is an API that offers a collection of Java libraries that helps us to read, write, and dispense different Microsoft files such as excel sheets, ability-point, and word files.

Apache POI uses certain terms to work with Microsoft Excel. Permit'southward get familiar with these before nosotros go into the details of the code.

Term	Details
Workbook	A workbook represents a Microsoft Excel file. It tin be used for creating and maintaining the spreadsheet. A workbook may contain many sheets.
Canvass	A canvass refers to a page in a Microsoft Excel file that contains the number of rows and columns.
Row	A row represents a collection of cells, which is used to stand for a row in the spreadsheet.
Cell	A cell is indicated past a row and column combination. Data entered by a user is stored in a prison cell. Data can be of the type such as string, numeric value, or formula.

Earlier we start, the commencement step is to download the jar files required to use the library. You lot can download the Apache POI library by referring to Steps to Download Apache POI.

The below image conspicuously depicts the construction and how the classes and interfaces are aligned in Apache POI.

Allow'southward at present understand how nosotros tin access and manage diverse components in an Excel file using Apache POI ?

How to manage Excel workbooks pragmatically?

Apache POI provides various interfaces and classes that assist u.s. to work with Excel. Information technology provides a "Workbook " interface to maintain Excel Workbooks. There are certain classes that implement this interface and we use these classes to create, change, read, and write data in Excel files. The two mainly used  classes for managing Excel Workbooks are:

• HSSFWorkbook- These class methods are used to read/write information to Microsoft Excel file in .xls format. It is uniform with MS-Office versions 97–2003.
• XSSFWorkbook- These class methods are used to read-write data to Microsoft Excel in .xls or .xlsx format. It is compatible with MS-Part versions 2007 or later.

How to manage Excel sheets programmatically?

At that place is another interface, "Sheet " , which nosotros use to create a sheet in the Workbook. There are two classes that used to work with sheets, same as we have for Workbook Interface:

• HSSFSheet - This class is used to create a new sheet in the HSSFWorkbook, ie, the older format of Excel.
• XSSFSheet - This class is used to create a new sheet in the XSSFWorkbook., ie, the new format of Excel

How to manage Excel rows pragmatically?

The Row interface provides u.s. with the ability to work with rows in the Excel sheet. Below two classes implement this interface:

• HSSFRow - This represents a row in the HSSFSheet.
• XSSFRow - This represents a row in the XSSFSheet.

How to manage Excel cells?

The Cell interface helps united states in accessing the cells of a particular row. There are two classes that implement this interface and we tin utilise for reading/writing data into the prison cell:

• HSSFCell - Nosotros utilize information technology to work with cells of HSSFRow.
• XSSFCell - We use it to work with cells of XSSFRow.

Now that we have gone through the details of the Apache POI library, permit'southward effort to use it to read and write into Excel Files using Selenium WebDriver.

How to read data from Excel in Selenium tests using Apache POI?

Suppose, for a Selenium test instance, nosotros need to read the student data from the Excel Canvas, having a sample information every bit shown below:

While reading the Excel file, Apache POI tin read data in two ways:

• You want to read the value of a item jail cell, for instance, y'all want to become the address of the student present in the second row.
• You lot can read the entire excel in 1 get. It is based upon the need for your test script and the data needed for examination execution.

We will empathize both the ways of reading the excel in Selenium, but earlier that, there are some common steps to follow:

1. The first step is to obtain the Excel Workbook based upon its location on the reckoner. You can create an object of the workbook by referring to the FileInputStream object that points to the excel file. Nosotros tin can do it as shown below using the HSSFWorkbook Class.  If you lot are using MS-Office versions 97–2003 or XSSFWorkbook Class if MS-Office versions 2007 or later. In the below line, we use HSSFWorkbook  as an Excel version is 97-2003.

                      File            file            =            new            File("Due east:\\TestData\\TestData.xls");            FileInputStream            inputStream            =            new            FileInputStream(file); HSSFWorkbook wb=new            HSSFWorkbook(inputStream);                  

2. Once we create the Workbook, the adjacent step is to create a Canvas in the Workbook. Additionally, we tin practice it equally below using the proper name of the sheet in the getSheet (String sheetName) method. Hither, "STUDENT_DATA" is the name of the sheet in the Excel Workbook.

          HSSFSheet sheet=wb.getSheet("STUDENT_DATA");                  

Y'all can also create a sail based upon the index using the getSheetAt (int alphabetize) method equally shown below -

                      HSSFSheet sheet1=wb.getSheetAt(ane);                  

3. After the canvas creation, we take to obtain the row of the sheet, which we can retrieve using the getRow (int rowIndex) method of the canvass object:

                      HSSFRow row1=sheet.getRow(ane);                  

4. Once you take got the row, you can get the cell of the row using the getCell (int index) method of the HSSFRow form:

          sheet.getRow(1).getCell(1)                  

5. After you obtain the cell that contains the data, you can read the information in different formats like Cord, Date, Number using the different methods which are based upon the format of the cell you specify in the excel sheet.

• String - getStringCellValue()  [It can be used to read Name of the educatee from Excel ]
• Number - getNumericCellValue() [It can exist used to read the mobile number of the student]
• Date - getDateCellValue() [It tin be used to read the Date of Nascence of the student]

The image to a higher place shows the formats feasible for a cell value in Excel(General, Text, Number, Date, Fourth dimension). For an easier approach, you tin can specify all values as Text (fifty-fifty numbers) and read them in the String variable.

How to read a specific cell value?

At present that nosotros are familiar with the different classes and the method provided by the Apache POI library, allow'southward endeavour to combine them in a code snippet, where nosotros effort to read the Address of the student in the first row in our sample Excel. The Address is nowadays in prison cell number 5 of the row.

Note: Index starts from zero for both the row and cell.

Yous can apply the below lawmaking snippet to print the address equally highlighted in the higher up image using the methods explained above -

                      import            org.apache.poi.hssf.usermodel.HSSFCell;            import            org.apache.poi.hssf.usermodel.HSSFRow;            import            org.apache.poi.hssf.usermodel.HSSFSheet;            import            org.apache.poi.hssf.usermodel.HSSFWorkbook;            import            java.io.File;            import            java.io.FileInputStream;            import            java.io.IOException;            public            class            ApachePOI            {            public            static            void            principal            (String args[])            throws            IOException {            //Create an object of File class to open xlsx file            File            file            =            new            File("E:\\TestData\\TestData.xls");            //Create an object of FileInputStream class to read excel file            FileInputStream            inputStream            =            new            FileInputStream(file);            //Creating workbook instance that refers to .xls file            HSSFWorkbook wb=new            HSSFWorkbook(inputStream);            //Creating a Sheet object using the canvass Name            HSSFSheet sheet=wb.getSheet("STUDENT_DATA");            //Create a row object to retrieve row at alphabetize 1            HSSFRow row2=sheet.getRow(ane);            //Create a prison cell object to retreive jail cell at index 5            HSSFCell cell=row2.getCell(five);            //Get the address in a variable            String address= cell.getStringCellValue();            //Printing the address            System.out.println("Address is :"+ address);     } }                  

When we run the above program, we will get the output as follows:

The highlighted area shows the address of the commencement educatee that is printed using the code.

Now that we have understood how to read a particular cell value, we will now take a look at how to read the complete data from the Excel File.

How to read the entire Excel sail?

To read the consummate data from Excel, you can iterate over each cell of the row, present in the sheet. For iterating, yous need the total number of rows and cells nowadays in the sheet. Additionally, we tin can obtain the number of rows from the sheet, which is basically the full number of rows that have data present in the sheet past using the calculation -

RowCount = LastRowNumber -Get-go Row Number

To become the last and beginning-row number, there are two methods in the sheet   class:

• getLastRowNum()
• getFirstRowNum()

Then, we can obtain the row count using the below lawmaking:

                      int            rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();                  

Once you get the row, yous can iterate over the cells present in the row by using the total number of cells, that we can summate using getLastCellNum() method:

                      int            cellcount=sheet.getRow(one).getLastCellNum();                  

Let's try to print the entire data present in the canvas using the below lawmaking:

                      import            org.apache.poi.hssf.usermodel.HSSFSheet;            import            org.apache.poi.hssf.usermodel.HSSFWorkbook;            import            java.io.File;            import            java.io.FileInputStream;            import            coffee.io.IOException;            public            class            ApachePOI            {            public            static            void            primary            (String args[])            throws            IOException {            //Create an object of File course to open up xlsx file            File            file            =            new            File("E:\\TestData\\TestData.xls");            //Create an object of FileInputStream course to read excel file            FileInputStream            inputStream            =            new            FileInputStream(file);            //creating workbook example that refers to .xls file            HSSFWorkbook wb=new            HSSFWorkbook(inputStream);            //creating a Sheet object            HSSFSheet sheet=wb.getSheet("STUDENT_DATA");            //become all rows in the sail            int            rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();            //iterate over all the row to print the information present in each cell.            for(int            i=0;i<=rowCount;i++){            //get jail cell count in a row            int            cellcount=sheet.getRow(i).getLastCellNum();            //iterate over each cell to print its value            System.out.println("Row"+ i+" information is :");            for(int            j=0;j<cellcount;j++){                 System.out.print(sail.getRow(i).getCell(j).getStringCellValue() +",");             }             Organization.out.println();         }     } }                  

The output of the lawmaking snippet is:

In the above image, you tin see that the information present in the excel prints and besides notice that Row 0 prints the Championship. Moreover, you can avoid printing the title past starting the loop from value =1 instead of 0.

How to write data in Excel in Selenium tests using Apache POI?

Similar to reading, writing data in Excel files can exist as important, as it can serve to save the test results back in the Excel sheets. Apache POI provides various set methods, which nosotros tin utilize to write the information in an Excel in Selenium tests itself. Consequently, allow's  come across how we tin achieve the same:

How to write to a new jail cell in an existing row?

Suppose, nosotros want to write the effect of the examination run using the given test data in the same row, in which nosotros have the input data. Consider, nosotros merely accept to put a "Pass/FAIL" in the last column of the row, we can attain the aforementioned using Apache POI every bit shown beneath:

                      HSSFCell            jail cell            =            canvass.getRow(1).createCell(6);            if(confirmationMessage.isDisplayed()){               cell.setCellValue("Pass");           }else{               cell.setCellValue("Neglect");           }            //To write into Excel File            FileOutputStream            outputStream            =            new            FileOutputStream("Due east:\\TestData\\TestData.xls"); wb.write(outputStream);                  

Suppose consider a scenario that on folio "https://demoqa.com/automation-practice-form" , nosotros have the fill the student registration form past reading the information from an Excel file and so append the consequence in the last cell of the row, if information technology was successful. Afterward, we can reach the aforementioned with the help of using information of Excel in Selenium using Apache POI library, as shown below:

                      import            org.apache.poi.hssf.usermodel.HSSFCell;            import            org.apache.poi.hssf.usermodel.HSSFSheet;            import            org.apache.poi.hssf.usermodel.HSSFWorkbook;            import            org.openqa.selenium.By;            import            org.openqa.selenium.JavascriptExecutor;            import            org.openqa.selenium.WebDriver;            import            org.openqa.selenium.WebElement;            import            org.openqa.selenium.chrome.ChromeDriver;            import            java.io.File;            import            coffee.io.FileInputStream;            import            java.io.FileOutputStream;            import            java.io.IOException;            import            java.util.concurrent.TimeUnit;            public            class            WriteToExcel            {            public            static            void            main            (String args[])            throws            IOException {            //set the ChromeDriver path            System.setProperty("webdriver.chrome.commuter","E:\\Projects\\chromedriver.exe");            //Create an object of File class to open xls file            File            file            =            new            File("E:\\TestData\\TestData.xls");            //Create an object of FileInputStream class to read excel file            FileInputStream            inputStream            =            new            FileInputStream(file);            //creating workbook instance that refers to .xls file            HSSFWorkbook wb=new            HSSFWorkbook(inputStream);            //creating a Canvas object            HSSFSheet sheet=wb.getSheet("STUDENT_DATA");            //get all rows in the sheet            int            rowCount=sheet.getLastRowNum()-canvas.getFirstRowNum();            //Creating an object of ChromeDriver            WebDriver            driver            =            new            ChromeDriver();            //Navigate to the URL            driver.get("https://demoqa.com/automation-practise-form");            //Identify the WebElements for the student registration form            WebElement firstName=driver.findElement(By.id("firstName"));         WebElement lastName=driver.findElement(Past.id("lastName"));         WebElement email=driver.findElement(By.id("userEmail"));         WebElement genderMale= driver.findElement(By.id("gender-radio-1"));         WebElement mobile=driver.findElement(Past.id("userNumber"));         WebElement address=driver.findElement(Past.id("currentAddress"));         WebElement submitBtn=commuter.findElement(Past.id("submit"));            //iterate over all the rows in Excel and put data in the form.            for(int            i=i;i<=rowCount;i++) {            //Enter the values read from Excel in firstname,lastname,mobile,email,address            firstName.sendKeys(sheet.getRow(i).getCell(0).getStringCellValue());             lastName.sendKeys(sail.getRow(i).getCell(one).getStringCellValue());             electronic mail.sendKeys(sheet.getRow(i).getCell(ii).getStringCellValue());             mobile.sendKeys(sheet.getRow(i).getCell(four).getStringCellValue());             accost.sendKeys(sheet.getRow(i).getCell(5).getStringCellValue());            //Click on the gender radio push button using javascript            JavascriptExecutor            js            =            (JavascriptExecutor) driver;             js.executeScript("arguments[0].click();", genderMale);            //Click on submit push            submitBtn.click();            //Verify the confirmation message            WebElement            confirmationMessage            =            driver.findElement(By.xpath("//div[text()='Cheers for submitting the form']"));            //create a new cell in the row at index 6            HSSFCell            jail cell            =            sail.getRow(i).createCell(6);            //check if confirmation bulletin is displayed            if            (confirmationMessage.isDisplayed()) {            // if the message is displayed , write Pass in the excel sheet            prison cell.setCellValue("PASS");                              }            else            {            //if the bulletin is not displayed , write FAIL in the excel sheet            cell.setCellValue("FAIL");             }            // Write the data back in the Excel file            FileOutputStream            outputStream            =            new            FileOutputStream("E:\\TestData\\TestData.xls");             wb.write(outputStream);            //shut the confirmation popup            WebElement            closebtn            =            driver.findElement(By.id("closeLargeModal"));             closebtn.click();            //look for page to come back to registration page after close push is clicked            driver.manage().timeouts().implicitlyWait(2000, TimeUnit.SECONDS);         }            //Close the workbook            wb.close();            //Quit the driver            driver.quit();         } }                  

Nosotros can see the output of the above code by opening the excel file that we use in the code (in our case "E:\TestData\TestData.xls" ) :

Equally seen in the image above, Laissez passer is written into Excel File after the examination execution confronting the student information which was registering at the given time.

Now suppose, if we need to write the data in a new row altogether, we tin can also reach the same past using Apache POI. After, permit's see how to write data to a new cell in a new row?

How to write to a new cell in a new row?

The Apache POI canvass class provides methods to create new rows and then cells in these rows. Moreover, you tin can create a new row in an Excel sheet as follows:

          HSSFRow row3=canvas.createRow(3);                  

After the row creates, we tin create the cells and input the data in the excel sheet, as shown beneath-

          row3.createCell(0).setCellValue("Diana"); row3.createCell(1).setCellValue("Jane"); row3.createCell(2).setCellValue("Female");                  

Afterward, let'south write additional educatee data in the sheet past creating a new row in our sample Excel:

                      import            org.apache.poi.hssf.usermodel.HSSFRow;            import            org.apache.poi.hssf.usermodel.HSSFSheet;            import            org.apache.poi.hssf.usermodel.HSSFWorkbook;            import            java.io.File;            import            coffee.io.FileInputStream;            import            coffee.io.FileOutputStream;            import            java.io.IOException;            public            class            WriteToExcel            {            public            static            void            main            (Cord args[])            throws            IOException {            //Create an object of File grade to open xlsx file            File            file            =            new            File("Eastward:\\TestData\\TestData.xls");            //Create an object of FileInputStream course to read excel file            FileInputStream            inputStream            =            new            FileInputStream(file);            //creating workbook example that refers to .xls file            HSSFWorkbook wb=new            HSSFWorkbook(inputStream);            //creating a Sheet object using the sheet Name            HSSFSheet canvas=wb.getSheet("STUDENT_DATA");            //Create a row object to retrieve row at index iii            HSSFRow row2=sail.createRow(three);            //create a cell object to enter value in it using cell Alphabetize            row2.createCell(0).setCellValue("Diana");         row2.createCell(ane).setCellValue("Jane");         row2.createCell(ii).setCellValue("[email protected]");         row2.createCell(iii).setCellValue("Female");         row2.createCell(4).setCellValue("8786858432");         row2.createCell(5).setCellValue("Park Lane, Flat C1 , New Jersey");            //write the data in excel using output stream            FileOutputStream            outputStream            =            new            FileOutputStream("E:\\TestData\\TestData.xls");         wb.write(outputStream);         wb.close();      } }                  

Consequently, the output of the above code will appear in the Excel sail which looks like  -

Equally seen in the above epitome, we add an additional row to the Excel Sail having the same details as in the code.

seitzeatepas.blogspot.com

Source: https://www.toolsqa.com/selenium-webdriver/excel-in-selenium/

Share this post