How to read Excel File in Selenium

  • by

How to read Excel File in Selenium Webdriver
Step – 1 Create excel file and save in any location or import / drag in selenium package where jars are stored.
Step – 2 Make sub program
– Make class
– Create method including file path, file name and sheet name
– Make file instance
– Take FileInputStream obtains input bytes from a file in a file system
– Create workbook
– Check excel file extension
– Check sheet data row by row

package excelProgram;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
public class ReadExcl {
public void readXL(String filePath, String fileName, String sheetName) throws IOException
{
//eclipse support one forward slash (/) or two backword slash (\\)
File file = new File(“E:\\Documents and Settings\\Kumar\\Desktop\\DataExl.xlsx”);
// OR we can also use bellow line instead of above
//File file = new File(filePath+”//”+fileName);
FileInputStream inputStream=new FileInputStream(file);
Workbook workbook=null;
String extension = fileName.substring(fileName.indexOf(“.”));
if (extension.equals(“.xlsx”))
{
//If excel file extension is .xlsx then we use XSS method
workbook= new XSSFWorkbook(inputStream); //Initializing workbook object
}
else
{
//If excel file extension is .xls then we use HSS method
workbook=new HSSFWorkbook(inputStream);
}
Sheet sheet=workbook.getSheet(sheetName);
int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();
for (int i=0;i<rowCount+1;i++)
{
Row row=sheet.getRow(i);
for (int j=0;j<row.getLastCellNum();j++)
{
System.out.println(row.getCell(j).getStringCellValue()+” “);
}
}
}
}

Step – 3 Make Main program
– Write file path
– Make sub program object
– Read object
package excelProgram;
import java.io.IOException;
public class MainRead {
public static void main(String[] args) throws IOException {
// File path location user.dir
String filePath=System.getProperty(“E:\\Documents and Settings\\Kumar\\Desktop\\DataExl.xlsx”);
// OR we can also use bellow line instead of above
//String filePath=System.getProperty(“user.dir”)
ReadExcl obj= new ReadExcl();
// ReadExcl from other file
obj.readXL(filePath,”DataExl.xlsx”,”Sheet1″);
}
}

Leave a Reply