Friday 12 July 2013

Reading and writing data in excel sheet using Selenium web driver 2.0

Before using this code ,you need import following jars.
1.Import JXL jar file...
2.mport jxl.write.Label and use Label l = new Label(0, 0, text);





Reading and writing data in excel sheet is quite easy. Here is the simple example to read and write data using selenium web driver 2.0. Import jxl jar file and then run the script

Reading data from excel:


 public String[][] getXLData(String location, String sheetname)
       {
               Workbook w = null;
               try {
                       w = Workbook.getWorkbook(new File(location));
               } catch (BiffException e) {
                       e.printStackTrace();
               } catch (IOException e) {
                       e.printStackTrace();
               }
               Sheet s = w.getSheet(sheetname);
               String a[][] = new String[10][10];
               try
               {
               for (int j=0;j<s.getColumns();j++)
               {
                       for (int i=0;i<s.getRows();i++)
                       {
                               a[j][i] = s.getCell(j, i).getContents();
                               System.out.println(j+" and "+i+" "+a[j][i]);
                       }
               }

               }
               catch(Exception e)
               {
                       e.printStackTrace();
               }
               return a;
       }

Writing data to excel:

A simple example to write the title of the page in the excel sheet


public class google {
private WebDriver driver;

@Before
public void setUp() throws Exception {
driver = new FirefoxDriver();
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

@Test
public void test() throws Exception {
driver.get("http://www.google.co.in/");
driver.findElement(By.id("gbqfq")).clear();
driver.findElement(By.id("gbqfq")).sendKeys("Testing");
driver.findElement(By.id("gbqfq")).sendKeys(Keys.ENTER);
driver.findElement(By.linkText("Software testing - Wikipedia, the free encyclopedia")).click();
String s = driver.getTitle();
writereport(s);

}

@After
public void tearDown() throws Exception {
driver.quit();
}



public void writereport(String text) 
       { 
        try
        {
       FileOutputStream f = new FileOutputStream("c:\\Test\\output.xls",true);
       WritableWorkbook book = Workbook.createWorkbook(f); 
       WritableSheet sheet = book.createSheet("output", 0);
       Label l = new Label(0, 0, text);
       sheet.addCell(l);
       book.write(); 
       book.close(); 
        }
        catch (Exception e)
        {
         e.printStackTrace();
        }
        }

17 comments:

  1. Hi,
    I am getting error for writing to excel by using this code

    C:\Users\TEMP\AppData\Local\Temp\testng-eclipse-984789193\testng-customsuite.xml

    FAILED: test
    java.lang.NullPointerException
    at pack12.Writing2Excel$google.test(Writing2Excel.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:128)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1203)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1128)
    at org.testng.TestNG.run(TestNG.java:1036)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)

    ReplyDelete
  2. Hi Gouthami,
    Have You Downloaded jxl jar file.............If that jar file not available in your build path,just download and Configure.

    This Code is working Fine.

    package learing;

    import java.io.FileOutputStream;
    import java.util.concurrent.TimeUnit;

    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;

    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    import org.openqa.selenium.By;
    import org.openqa.selenium.Keys;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.firefox.FirefoxDriver;



    public class Excel {
    private WebDriver driver;

    @Before
    public void setUp() throws Exception {
    driver = new FirefoxDriver();
    driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
    }

    @Test
    public void test() throws Exception {
    driver.get("http://www.google.co.in/");
    driver.findElement(By.id("gbqfq")).clear();
    driver.findElement(By.id("gbqfq")).sendKeys("Testing");
    driver.findElement(By.id("gbqfq")).sendKeys(Keys.ENTER);
    driver.findElement(By.linkText("Software testing - Wikipedia, the free encyclopedia")).click();
    String s = driver.getTitle();
    System.out.println(s);
    writereport(s);
    }
    @After
    public void tearDown() throws Exception {
    driver.quit();
    }
    public void writereport(String text)
    {
    try
    {
    System.out.println("Hello");
    FileOutputStream f = new FileOutputStream("D:\\output.xls",true);
    WritableWorkbook book = Workbook.createWorkbook(f);
    WritableSheet sheet = book.createSheet("output", 0);
    System.out.println("Hello11");
    Label l = new Label(0, 0, text);
    sheet.addCell(l);
    book.write();
    book.close();
    }
    catch (Exception e)
    {
    e.printStackTrace();
    }
    }
    }

    ReplyDelete
  3. Label l = new Label(row, col, text); is showing an error in my ecllipse project...plz reply

    ReplyDelete
    Replies
    1. May be you have used awt.Label. I received the same error when i used it. Then I changed the declaration to jxl.write.Label. The script nothing gave me any error after that. but my excel didn't show me the page title.

      Delete
  4. Hi Ankit,

    Can You Post That Error Message......

    cheers,
    Brahmi

    ReplyDelete
  5. Thanks for Sharing.. Able to Write into excel without any Flaws :)

    ReplyDelete
  6. Hi ,

    Can you please tell me howto write and read the data from the Excel , in One TestNG program

    ReplyDelete
  7. Hi
    The script is running fine for testNG but it is not creating any worksheet on specified path.Please reply.

    ReplyDelete
  8. I was searching for java code to write data to existing excel files and found java library for excel which allows you to perform all excel tasks without having to install MS excel in your system. I found this library very useful as i can read and write data to/from excel file using this library. You should try it also.

    ReplyDelete
  9. Hi

    script is executed but output is not generated , is there any issue with
    import jxl.write.Label;

    ReplyDelete
    Replies
    1. Hi Sagar ,

      This Script has no Problem....if you are facing any problem please paste it here

      Delete
  10. Hi,

    I am getting below problem.

    jxl.read.biff.BiffException: Unable to recognize OLE stream

    ReplyDelete
  11. Hi sandhya,

    "save as" your workbook as "Excel 97-2003 workbook" (option comes in Microsoft Excel 2007) it saves your file in OLE container.

    Cheers,
    Brahmi

    ReplyDelete
  12. Hi,

    This code is run fine without any error for the TestNG but not provide any o/p
    But work fine for the Core Java as

    package mypackage;

    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;

    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    import jxl.write.biff.RowsExceededException;

    public class wriExl {

    public static void main(String args[]) throws IOException, RowsExceededException, WriteException
    {
    System.out.println("Hello");
    FileOutputStream f = new FileOutputStream("D:\\output.xls",true);
    WritableWorkbook book = Workbook.createWorkbook(f);
    WritableSheet sheet = book.createSheet("output", 0);
    System.out.println("Hello11");
    Label l = new Label(0, 0, "sfsdfsdfsdfsdf");
    sheet.addCell(l);
    book.write();
    book.close();
    }

    }

    ReplyDelete
  13. HI , can you please share the code in which it is explained, how to integrate the above code with testNG class program.I would be really apreciatiate if somebody response on this asap.

    ReplyDelete
  14. hi, I am getting error for above code
    String excelpath = "/home/....../testdata.xlsx";
    FileInputStream fis = new FileInputStream(excelpath);
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(2);
    String value = "PASS";
    Cell cellresult;
    cellresult = row.getCell(14);
    cellresult.setCellValue(value);
    FileOutputStream fos=new FileOutputStream(excelpath);
    workbook.write(fos);
    fos.flush();
    fos.close();

    getting error for setCellValue(); line

    ReplyDelete
  15. Hi i used the same code u have given first i am reading data from ecel into my application then i am writing data into a copy of the excel for first iteration it works fine but later on test script fails and its throws java,lang,nullpointer Exception could you pls help me with this?

    ReplyDelete

Angular JS Protractor Installation process - Tutorial Part 1

                     Protractor, formally known as E2E testing framework, is an open source functional automation framework designed spe...