Ferin Patel Ferin Patel - 3 months ago 18
Java Question

Dont know what's wrong with my code (Apache POI)

package apachePOIFrameWork.ObjectRepository;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class excelDataDrivenProcess {

XSSFWorkbook mWorkbook;
XSSFSheet mDataSheet;
XSSFRow mRowPointer;
XSSFCell mColPointer;
FileInputStream mFis;
FileOutputStream mFos;
String mLocation;

public excelDataDrivenProcess(String workBook){
mLocation = workBook;
}

private void openFile(){
try {
mFis = new FileInputStream(mLocation);
mWorkbook = new XSSFWorkbook(mFis);
}catch (IOException ioe){
ioe.printStackTrace();
System.out.println("File Not Found");
}
}

private void saveFile(){
try {
mFos = new FileOutputStream(mLocation);
mWorkbook.write(mFos);
mFos.close();
}catch (IOException ioe){
ioe.printStackTrace();
System.out.println("Cannot Write");
}
}

public void showDataAt(String sheetName, int rowNum, int colNum){

openFile();
mDataSheet = mWorkbook.getSheet(sheetName);
mRowPointer = mDataSheet.getRow(rowNum);
mColPointer = mRowPointer.getCell(colNum);

System.out.println(mColPointer.getStringCellValue());
}

public void setDataAt(String value, String sheetName, int rowNum, int colNum){

openFile();
mDataSheet = mWorkbook.getSheet(sheetName);
mRowPointer = mDataSheet.createRow(rowNum);
mColPointer = mRowPointer.createCell(colNum);

mColPointer.setCellValue(value);
saveFile();
System.out.println("Your Value: " + value + " is set to your desired Location");

}

}

package apachePOIFrameWork.testCases;

import apachePOIFrameWork.ObjectRepository.excelDataDrivenProcess;
import org.testng.annotations.Test;

public class valuePrinter {

@Test
public void valueExtractor(){
excelDataDrivenProcess eddp = new excelDataDrivenProcess("C:\\SeleniumWorks\\excelDataCollector.xlsx");
eddp.showDataAt("Sheet1", 0, 0);
eddp.showDataAt("Sheet1", 0, 1);
eddp.setDataAt("abc", "Sheet1", 1, 0);
eddp.setDataAt("xyz", "Sheet1", 1, 1);

/*When I open excel file only xyz is printed on location (1,1) whereas abc is never saved in the file*/

}
}


Using TestNG Framework and Apache POI -- The program runs successfully but when I open the excel sheet only the last value(in this case 'xyz') is saved in the file. I don't understand what happened to 'abc' value at (1,0) Location

Answer

The problem is in:

 mRowPointer = mDataSheet.createRow(rowNum);

each time you recreate the entire row, overlapping previous values. You might use something like:

 mRowPointer = mDataSheet.getRow(rowNum);
 if(mRowPointer==null)
    mRowPointer = mDataSheet.createRow(rowNum);