In the real word automation script, the test data shouldn’t be hardcoded. Some external resources (excel sheet, properties file, xml file, json file etc.) should be used to read or write data.
In Selenium, Java provides some different classes or interfaces to perform file manipulation. Apache POI libraries are used to perform such operations. Some of the interfaces are given below to read or write data from the external resources:
- POIFS (Poor Obfuscation Implementation File System)
- HSSF(Horrible Spreadsheet Format)
- XSSF(XML Spreadsheet Format)
- HPSF(Horrible Property Set Format)
- HWPF(Horrible Word Processor Format)
- XWPF(XML Word Processor Format)
- HSLF(Horrible Slide Layout Format)
- HGDF(Horrible Diagram Format)
- HDBF(Horrible PuBlisher Format)
To create or maintain Excel Workbooks , Apache POI provides ” Workbook” as super-interface of all classes. It belongs to org.apache.poi.ss.usermodel package. It uses WorkbookFactory class for creating the appropriate kind of Workbook (i.e. HSSFWorkbook or XSSFWorkbook). The two classes which implements “Workbook” interface are given below:
- HSSFWorkbook- This class methods are used to read/write data to Microsoft Excel file in .xls format.
- XSSFWorkbook- This class methods are used to read write data to Microsoft Excel and OpenOffice xml files in .xls or .xlsx format.
Note: For Apache POI
Libraries installation, please refer to the blog– https://webkul.com/blog/apache-poi-installation-to-project-library/
Read Data From Excel Sheet:
Below Code is written to read the data from the excel sheet:
package testng; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.testng.annotations.Test; public class excelsheet { @Test public void excel() throws EncryptedDocumentException, InvalidFormatException, FileNotFoundException, IOException { //get the excel sheet file location String filepath=".\\excelsheet\\testdata.xlsx"; Workbook wb= new WorkbookFactory().create(new FileInputStream(new File(filepath))); //get the sheet which needs read operation Sheet sh = wb.getSheet("sheet1"); //get the total row count in the excel sheet int rowcount = sh.getLastRowNum(); for (int i = 0; i <= rowcount; i++) { // get the total cell count in the excel sheet int cellcount = sh.getRow(i).getLastCellNum(); for (int j = 0; j < cellcount; j++) { //get cell value at the given position [i][j] String value = sh.getRow(i).getCell(j).getStringCellValue(); //print the cell value System.out.println(value); } } } }
“Workbook” interface throws some exceptions which needs to be handled i.e. EncryptedDocumentException, InvalidFormatException, FileNotFoundException & IOException.
Note: In Excel Sheet, there shouldn’t be any voided cell in-between the data fields, else the code will throw an error message at the time of execution & the data after the voided cell will be skipped.
Workbook wb= new WorkbookFactory().create(new FileInputStream(new File(filepath)));
The above code can also be written as below for better understanding:
File file= new File(".\\excelsheet\\testdata.xlsx"); FileInputStream fis= new FileInputStream(file); Workbook wb= WorkbookFactory.create(fis);
Write Data to Excel Sheet:
Below code is written to write the code to the excel sheet:
package testng; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.testng.annotations.Test; public class writeexcel { @Test public void write() throws IOException { //define the file path where excel sheet will be generated String filepath= (".\\excelsheet\\write.xlsx"); File file= new File(filepath); FileOutputStream fos=new FileOutputStream(file); //create the blank workbook Workbook wb= new XSSFWorkbook(); //create the blank excel sheet Sheet sh= wb.createSheet("Webkul"); //define the position in the excel sheet where write operation needs to perform Row rw= sh.createRow(4); Cell cl=rw.createCell(1); //define input type cl.setCellType(CellType.STRING); //define the data value cl.setCellValue("Webkul Software"); //write the workbook wb.write(fos); } }
In case you have any queries then feel free to ask in the comment section below.
17 comments
I have a doubt in writing data to excel.
createRow() is a non static method which belongs to Sheet class and as per java rule to invoke a non static method we need to create an object of the respective class..but in the above example we aren’t creating object of Sheet class,which creating object of Sheet class we are invoking createRow().Can you please explain this.Thanks in advance.
as you can check the above code again carefully, I have already created the object for the sheet class as sh i.e
Sheet sh= wb.createSheet(“Webkul”);
Thanks for responding.But object is created using new keyword na? In the above example we haven’t created object of Sheet class using new keyword know?
Ok, I got your point actually you are asking for the “new” keyword for the dynamic object creation.
Then I would like to tell you that there is a hierarchy of excelsheet extensions i.e Workbook(I) <–extends– Sheet(I) <–extends– Row(I) <–extends– Cell(I), where I stands for interface; so as you can see Workbook(I) is the parent Interface which is extended by Sheet(I), so if you want to call any of the method which is declared in the parent Interface then you have to use the parent interface’s object (wb) i.e
Sheet sh= wb.createSheet(“Webkul”);
where createSheet() is the method present in the parent interface Workbook.
Hope so the point is clear now.
Can you tell me how to read date cells too, in this code.
You can read the date cell format in two ways:
sh.getRow(i).getCell(j).getNumericCellValue();
sh.getRow(i).getCell(j).getStringCellValue();[But to read the date data in string format you need to write the date in excel sheet like “09/09/2020”]
Thanks
You can’t read and write to the same file at the same time due to Readers-Writer lock problem.
But, yes you can write two scripts in the same program i.e. one is for the read process and another one is for the write process.
Thanks & Regards,
How to read or skip the blank cell data?
You can read the blank cell by the following code(variables are same as used in the above code):
if (sh == null) {
Value = ” “;
} else {
Value = sh.getStringCellValue();
}
Thanks a lot for the interest. We will be shortly coming up with a new blog featuring the email part. Stay connected for the least updates.
Thanks and Regards,
Thank you for this wonderful blog on Selenium Webdriver. Its really an amazing blog to read. It has lots of information regarding Selenium Webdriver.
Thanks for the valuable words.