Hello guys !

Today I will post about how to use Excel Sheet as a database with Selenium.

– First create your BD as this example:

Sheet 1 – Consumers

 

 

 

 

 

Sheet 2 – Products: Now, you can create other sheets with information about the products or something that you want.

– Always save as .xls

– Create a class for open the Data base (Sheet) – remember download the lib jxl > Link with download of all versions

import java.io.File;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

public class OpenDataBase {

Sheet sheet;
Workbook bddatabase;

    public Sheet Open() throws BiffException, IOException, Exception {

WorkbookSettings ws = new WorkbookSettings();
ws.setEncoding("Cp1252");

//Change for the folder of the .xls
bddatabase = Workbook.getWorkbook(new File("BD//BDFile.xls"), ws);  

//Change the name of your sheet
sheet = bddatabase.getSheet("tbclient");
return sheet;
}

public void Close(Workbook bddatabase) throws BiffException, IOException, Exception {

    bddatabase.close();


    }
}

 

– Create a class to list all the informations inside of the sheet (table):

import org.openqa.selenium.WebDriver;
import java.io.IOException;
import jxl.Cell;
import jxl.read.biff.BiffException;
import org.openqa.selenium.By;


public void TestCase() throws BiffException, IOException, Exception {

// open Data base
opendatabase.Open();


// bring the cells from BD
for (int i = 1; i < opendatabase.sheet.getRows(); i++) {
Cell id_client = opendatabase.sheet.getCell(0, i);
Cell casenameBD = opendatabase.sheet.getCell(1, i);
Cell email = opendatabase.sheet.getCell(2, i);
Cell name = opendatabase.sheet.getCell(3, i);
Cell surname = opendatabase.sheet.getCell(4, i);
Cell date_born = opendatabase.sheet.getCell(5, i);
Cell gender = opendatabase.sheet.getCell(6, i);
Cell discount = opendatabase.sheet.getCell(7, i);
Cell pass = opendatabase.sheet.getCell(8, i);


driver.findElement(By.id("field")).sendKeys(id_client.getContents());
driver.findElement(By.id("field")).sendKeys(casenameBD.getContents());
driver.findElement(By.id("field")).sendKeys(email.getContents());
driver.findElement(By.id("field")).sendKeys(name.getContents());
driver.findElement(By.id("field")).sendKeys(surname.getContents());
driver.findElement(By.id("field")).sendKeys(date_born.getContents());
driver.findElement(By.id("field")).sendKeys(gender.getContents());
driver.findElement(By.id("field")).sendKeys(discount.getContents());
driver.findElement(By.id("field")).sendKeys(pass.getContents());

}

}

 

If you have any questions, just write below !

Thank you 🙂