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.