Selenium Webdriver Duplicating ExcelSheet

Question

I tried to copy an excel sheet containing 7 colomns named Excel.xls sheet into ExcelCopy.xls , but Getting an Java Null Exception error at @After Test and as am very new to this selenium coding please help me !!

 package TestNG;


import java.io.FileInputStream;
import java.io.FileOutputStream;

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

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.Test;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.AfterTest;

public class DuplicateExcelSheet {
    WebDriver driver;
    WebDriverWait wait;

    Workbook w;
    Sheet s;
    FileInputStream fi;
    FileOutputStream fo;
    WritableWorkbook ww;
    WritableSheet ws;
  @Test
  public void f() throws Exception{
    int colCount=s.getColumns();
    System.out.println(colCount);
    ww=Workbook.createWorkbook(fo, w);
    ws=ww.createSheet("Data", 0);

    for (int i = 0; i < colCount; i++) 
    {
    String s1=s.getCell(i, 0).getContents();
     Label l=new Label(i,0,s1);
     ws.addCell(l);
    }
     }

  @BeforeTest
  public void beforeTest() throws Exception{

      fi=new FileInputStream("E:\\selenium\\Excel.xls");
        w=Workbook.getWorkbook(fi);
        s=w.getSheet(0);
          fo=new FileOutputStream("E:\\selenium\\ExcelCopy.xls");
      }

  @AfterTest
  public void afterTest() throws Exception{
       ww.write();
       w.close();
      fi.close();
      fo.close();


  }

}

@AfterTest afterTest java.lang.NullPointerException at TestNG.DuplicateExcelSheet.afterTest(DuplicateExcelSheet.java:55) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) the error is something like this — rahul bommanaboina, May 22 '15 at 19:27

Saifur · Accepted Answer · 2015-05-25T16:15:14.147

1

I assume you want to copy the full excel workbook. If, so use FileUtil which is much more easier and lot less coding. Good thing is you do not need to worry about the destination directory, files etc. It will also overwrite if the destination exists.

File sourceExcel = new File("D:\\Users\\Saifur\\Desktop\\Delete\\excelFrom\\Selenium.xlsx");
File dstExcel = new File("D:\\Users\\Saifur\\Desktop\\Delete\\excelTo\\Selenium_Copy.xlsx");

try {

    FileUtils.copyFile(sourceExcel, dstExcel);

} catch (IOException e) {

    e.printStackTrace();
}

Make sure you have the following import

import org.apache.commons.io.FileUtils;

Maven repo here

edited May 25 '15 at 16:15

answered May 25 '15 at 16:09

Saifur

16,081
6
49
73

@rahulbommanaboina Glad it did. Mark the answer as accepted if this is what you have been looking for. See [this](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Saifur May 26 '15 at 13:23

score 0 · Answer 2 · answered May 22 '15 at 19:38

0

It looks like you declared FileOutputStream fo as a class member, but you are not assigning it anywhere. So, it is NULL at line 55 when you try to call fo.close()

If fo is not used, I suggest you just remove it from this class.

answered May 22 '15 at 19:38

timato

184
2
12

I modified the program using fileOutputStream , still am getting the same error .. !! :( – rahul bommanaboina May 22 '15 at 20:01
I suggest putting all the object creation in @BeforeTest – timato May 22 '15 at 20:03
If you're using Eclipse, you can use a debugging breakpoint and watch the state of fo change as the test executes. [Notes on breakpoints](http://stackoverflow.com/questions/4733835/how-use-break-points-in-eclipse) – timato May 22 '15 at 20:04
ahh !! its throwing me errors if declare the objects in @ before test, as i used the objects in @Test , and coming to break points am not sure how to use them or check , as am a beginner in selenium and coding . – rahul bommanaboina May 22 '15 at 20:08
i got rid of exception , changed the program little bit, but now , excel sheet has been created , but data dint get copied, can some one please check the program and let me know , why the data has not been copied ?? – rahul bommanaboina May 23 '15 at 11:29

Selenium Webdriver Duplicating ExcelSheet

2 Answers2

Linked