Home > Programming, Programming, QA, Test and Automation Stuffs, QA > Using Excel Sheet as BD – Selenium – (Read)

Using Excel Sheet as BD – Selenium – (Read)

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

Untitled

 

 

 

 

 

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 🙂

Advertisements
  1. Leandro Alves
    September 24, 2014 at 11:28 pm

    Hello Rafaela!
    What’s the “ws.setEncoding(“Cp1252″);”?

    • September 25, 2014 at 8:04 am

      Hi Leandro !
      This code convert to Java own unicode format. If you have problems with some junk characters while reading Excel using this API or if you want support all european characters(accentuation, tilde, ç, etc…).

      Practical Example:
      The sheet with the contents could contain unicode, the åäö are multibyte characters, while the ASCII ones are normal single byte characters. It is most definitely not Latin1. If you print the “contents” string with printLn and redirect it to a hello.txt file, you will find that the letter “ö” is represented with two bytes, C3 B6 in hex. (195 and 179 in decimal.)

      Is it clear ? Bye 🙂

  2. Leandro Alves
    September 25, 2014 at 5:33 pm

    Ok, Rafaela.
    Thanks, now its clear.

  3. September 25, 2014 at 7:15 pm

    Great Article, thank.

    what do you say about publishing in http://www.QATestingTools.com as well?

    • September 26, 2014 at 6:26 am

      Many thanks !
      Seems great ! How can I do this ? Just sharing my posts to you ?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: