How read the data from excel and write the pass/fail status to another excel sheet

Hi,

This post would help you all to understand in better manner how to read and write to an excel file using jxl. Please go through the following code,


package excel;

import com.thoughtworks.selenium.*;
import org.testng.annotations.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import static org.junit.Assert.*;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class write2excel{
private Selenium selenium;

@Before
public void startSelenium() throws Exception {
selenium = new DefaultSelenium("localhost", 4444, "*chrome", "http://www.google.co.in");
selenium.start();
selenium.windowMaximize();
}


@Test
public void testGoogle1() throws Exception{

String s;
selenium.open("/");
Thread.sleep(3000);
FileInputStream file = new FileInputStream("src\\Resources\\Data\\data1.xls");
Workbook wb = Workbook.getWorkbook(file);
Sheet st = wb.getSheet("DataPool");
String a[][] = new String[st.getRows()][st.getColumns()];
System.out.println("s.getRows() = " + st.getRows());
FileOutputStream op = new FileOutputStream("src\\Resources\\Data\\data2.xls");
WritableWorkbook wb1 = Workbook.createWorkbook(op);
WritableSheet st1 = wb1.createSheet("DataPool",0);
for(int i=0;i<st.getRows();i++)
{
for(int t=0;t<st.getColumns();t++)
{
a[i][t] = st.getCell(t, i).getContents();
Label l = new Label(t, i, a[i][t]);
Label l1 = new Label(2, 0, "Status");
st1.addCell(l);
st1.addCell(l1);
}
}
for(int i=1;i<st.getRows();i++)
{
System.out.println("Opened");
String k = st.getCell(0,i).getContents();
selenium.type("id=gbqfq", k);
Thread.sleep(3000);
System.out.println("Typed");
selenium.click("id=gbqfb");
Thread.sleep(3000);
System.out.println("Clicked");
String j = st.getCell(1,i).getContents();
if(selenium.isTextPresent(j))
{
s= "Pass";
}
else
{
s="Fail";
}

Label l2=new Label(2,i,s);
st1.addCell(l2);

}
wb1.write();
wb1.close();
}

@After
public void tearDown() throws Exception {
//selenium.stop();
}


}

Code explanation:

Don't try to get/create an excel before "selenium.open("/");" function, if you do the browser does not open the url which need to be opened by the browser.

Below code helps to set the source file and sheet from which it gonna get the input data,

FileInputStream file = new FileInputStream("src\\Resources\\Data\\data1.xls");
Workbook wb = Workbook.getWorkbook(file); //Setting the workbook which will be used to get/read the data
Sheet st = wb.getSheet("DataPool"); //Setting the worksheet where the data is stored

Below code helps to create new file and sheet,

FileOutputStream op = new FileOutputStream("src\\Resources\\Data\\data2.xls");
WritableWorkbook wb1 = Workbook.createWorkbook(op); //Creating workbook
WritableSheet st1 = wb1.createSheet("DataPool",0);//Creating worksheet

Below code helps to copy the data from one excel to another excel file,


for(int i=0;i<st.getRows();i++)//st.getRows()-->count the number of rows present in sheet
{
for(int t=0;t<st.getColumns();t++)//st.getColumns()-->count the number of columns present in sheet
{
a[i][t] = st.getCell(t, i).getContents();//Read the data from sheet and Storing it into the array variable
Label l = new Label(t, i, a[i][t]);//Helps to set to which cell the data need to be inserted and specify which data to be inserted
Label l1 = new Label(2, 0, "Status");
st1.addCell(l);//Insert the data to the specified cell
st1.addCell(l1);
}
}

Below code helps to check the pass/fail status of the scenario,


if(selenium.isTextPresent(j))//selenium.isTextPresent("Text")-->helps to check whether specified text present in the loaded page
{
s= "Pass";
}
else
{
s="Fail";
}



Below code helps to write the test case status to the excel


Label l2=new Label(2,i,s);
st1.addCell(l2);

}
wb1.write();

Happy Testing

Write data to excel sheet using selenium

Dudes,

In my previous post i was explained about how to read the data from excel sheet and use those in our selenium script. In this post i will explain about how to write the data to a excel sheet through selenium script.

As known by everyone selenium does n't support read/write data from/to external resources like excel, etc. JXL jar file helps to overcome that issue. Jxl jar helps selenium to read/write the data from/to external sources like excel, etc. I would be explaining how could implement this in this post.

Example: (Google search and write the pass fail status to excel sheet)

package excel;

import com.thoughtworks.selenium.*;
import org.testng.annotations.*;
import java.io.FileOutputStream;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import static org.junit.Assert.*;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class write2excel{
private Selenium selenium;

@Before
public void startSelenium() throws Exception {
selenium = new DefaultSelenium("localhost", 4444, "*chrome", "http://www.google.co.in");
selenium.start();
selenium.windowMaximize();
}


@Test
public void testGoogle1() throws Exception{
String s;
selenium.open("/");
Thread.sleep(3000);
System.out.println("Opened");
selenium.type("id=gbqfq", "Software Testing");
Thread.sleep(3000);
System.out.println("Typed");
selenium.click("id=gbqfb");
Thread.sleep(3000);
System.out.println("Clicked");
if(selenium.isTextPresent("en.wikipedia.org/wiki/Software_testing"))
{
s= "Pass";
}
else
{
s="Fail";
}
write(s);
}

@After
public void tearDown() throws Exception {
//selenium.stop();
}
public void write(String t) {
try
{
FileOutputStream f = new FileOutputStream("src\\Resources\\Data\\data2.xls",true);
      WritableWorkbook book = Workbook.createWorkbook(f);
      WritableSheet sheet = book.createSheet("output", 0);
      Label l = new Label(0, 0, t);
      sheet.addCell(l);
      book.write();
      book.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}

}

In the above example following code used to create new excel file and write the data into excel sheet. And isText Present function helps to verify whether the mentioned text or word or sentence present in loaded page. Using that we can find whether the test case is pass or fail.


FileOutputStream f = new FileOutputStream("src\\Resources\\Data\\data2.xls",true);
       WritableWorkbook book = Workbook.createWorkbook(f);//Creating the excel sheet
       WritableSheet sheet = book.createSheet("output", 0);//Creating work sheet for excel
       Label l = new Label(0, 0, t);
       sheet.addCell(l);//Adding data to cell.
       book.write();
       book.close();

Happy Testing guys.