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 🙂