Writing to an Excel File : Apache POI, TestNG & Selenium

Question

I have been trying to read and write an excel file using TestNG Data Provider + Apache POI.

The problem that I am having is when writing to the excel file.

In a nutshell I have done the following:

1. reading from an excel
2. using that data to search something on a website using selenium webdriver
3. Outputting the results into another excel sheet.

The problem i am having is when writing to an excel sheet I am unable to increment the row number needed due to TestNG's framework, so my work around has been:

1. Adding another column to the excel input file and extracting that number from the excel sheet
2. Using that integer to pass it to the write method.

I am either getting a null pointer exception or the results are getting written to the same cell. I will be much appreciated if someone can point me on the right path. From thorough research I could only find someone indicating it is not possible using data provider with no explanation why or another way of tackling this problem.

Input File:

Output File:

public class WordpressLogin {
int CurrentRow=0;
@Test(dataProvider = "wordpressData")
public void loginToWordpress(String postcode, int rowNumber) throws IOException 
{       
    WebDriver driver;
    String result="";

    File f = new File("C:\\Users\\username\\workspace\\JAR\\IEDriverServer.exe");
    System.setProperty("webdriver.ie.driver", f.getAbsolutePath());

    DesiredCapabilities sCaps = DesiredCapabilities.internetExplorer();
    sCaps.setCapability(InternetExplorerDriver.INTRODUCE_FLAKINESS_BY_IGNORING_SECURITY_DOMAINS, true);
    sCaps.setJavascriptEnabled(true);
    driver = new InternetExplorerDriver(sCaps);
    driver.manage().window().maximize();
    driver.manage().timeouts().implicitlyWait(20,  TimeUnit.SECONDS);
    driver.get("website");


    System.out.println(driver.getTitle());


  WebElement address_elem = driver.findElement(By.id("cp-search"));


    char[] chars = postcode.toCharArray();
    for (int i = 0, n = chars.length; i < n; i++) {
        char c = chars[i];
    address_elem.sendKeys(""+c);

        }

    ArrayList<String> ar = new ArrayList<String>();     
    List<WebElement> drop = driver.findElements(By.xpath("//*[@id='cp-search_results']"));
    java.util.Iterator<WebElement> i = drop.iterator();

    while(i.hasNext()) {

            WebElement row = i.next();
            String b = row.getText();
            ar.add(b);
        }

    String r = ar.toString();


    ExcelDataConfig write = new ExcelDataConfig("C:\\Users\\username\\workspace\\TestData.xlsx");
    write.writeData(0, rowNumber, 1, r);







    driver.close();


}



@DataProvider(name = "wordpressData")
public Object[][] passData() throws IOException
{
    ExcelDataConfig config = new ExcelDataConfig("C:\\Users\\ffatemif\\workspace\\TestData.xlsx");
    int rows = config.getRowCount(0);
    System.out.println(rows);
    Object[][] data = new Object[rows][2];

    for(int i=0;i<rows;i++)
    {

         data[i][0]=config.getData(0, i, 0);
         data[i][1]=config.getInt(0, i, 1);

         // writemyshiz(0,i,1,temp);

            // MyWriteFunction(Column,data[i][0]=config.getData(0, i, 0);


        //data[i][1]=config.getData(0, i, 1);
    }

    return data;

}

}

public class ExcelDataConfig {  
XSSFWorkbook wb;    
XSSFSheet sheet1;
public ExcelDataConfig(String excelPath)
{

try
{
    File src=new File(excelPath);
    FileInputStream fis = new FileInputStream(src);

    wb=new XSSFWorkbook(fis);
}

catch (Exception e){

    System.out.println(e.getMessage());
}


}
public String getData(int sheetNumber,int row,int column)
{
    sheet1=wb.getSheetAt(sheetNumber);
    String data = sheet1.getRow(row).getCell(column).getStringCellValue();
    return data;
}

public int getInt(int sheetNumber,int row,int column)
{
    sheet1=wb.getSheetAt(sheetNumber);
    int data = (int) sheet1.getRow(row).getCell(column).getNumericCellValue();
    return data;
}


public void writeData(int sheetNumber, int row,int column,String result) throws IOException{

    sheet1=wb.getSheetAt(sheetNumber);
    Row rw = sheet1.getRow(row);
    Cell cell =rw.createCell(row);
    cell.setCellType(cell.CELL_TYPE_STRING);
    cell.setCellValue(result);
    FileOutputStream fos=new FileOutputStream("C:\\Users\\username\\workspace\\TestData2.xlsx");
    wb.write(fos);

}

public int getRowCount(int sheetIndex){


    int row = wb.getSheetAt(sheetIndex).getLastRowNum();
    row = row+1;

            return row;



}

}