Write xls multiple times using multiple Test Annotations

use [b:1p9dq0fs][color=#FF0000:1p9dq0fs]xlsx [/color:1p9dq0fs][/b:1p9dq0fs]instead of [b:1p9dq0fs]xls[/b:1p9dq0fs].

chnaged functio to //Method to read XL
public static String[][] readXL(String fPath, String fSheet) throws Exception{
String[][] xData;
int xRows, xCols;

File myxl = new File(fPath);
FileInputStream myStream = new FileInputStream(myxl);
XSSFWorkbook myWB = new XSSFWorkbook(myStream);
XSSFSheet mySheet = myWB.getSheet(fSheet);
xRows = mySheet.getLastRowNum()+1;
xCols = mySheet.getRow(0).getLastCellNum();
System.out.println("Total Rows in Excel are " + xRows);
System.out.println("Total Cols in Excel are " + xCols);
xData = new String[xRows][xCols];
for (int i = 0; i < xRows; i++) {
XSSFRow row = mySheet.getRow(i);
for (int j = 0; j < xCols; j++) {
XSSFCell cell = row.getCell(j);
String value = "-";
if (cell!=null){
value = cellToString(cell);
}
xData[i][j] = value;
// System.out.print(value);
// System.out.print("----");
}
// System.out.println("");
}
return xData;
}

//Change cell type
public static String cellToString(XSSFCell cell2) {
// This function will convert an object of type excel cell to a string value
int type = cell2.getCellType();
Object result;
switch (type) {
case XSSFCell.CELL_TYPE_NUMERIC: //0
result = cell2.getNumericCellValue();
break;
case XSSFCell.CELL_TYPE_STRING: //1
result = cell2.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_FORMULA: //2
throw new RuntimeException("We can't evaluate formulas in Java");
case XSSFCell.CELL_TYPE_BLANK: //3
result = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN: //4
result = cell2.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_ERROR: //5
throw new RuntimeException ("This cell has an error");
default:
throw new RuntimeException("We don't support this cell type: " + type);
}
return result.toString();
}

// Method to write into an XL
public static void writeXL(String fPath, String fSheet, String[][] xData) throws Exception{

File outFile = new File(fPath);
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet osheet = wb.createSheet(fSheet);
int xR_TS = xData.length;
System.out.println(xR_TS);
int xC_TS = xData[0].length;
System.out.println(xC_TS);
for (int myrow = 0; myrow < xR_TS; myrow++) {
XSSFRow row = osheet.createRow(myrow);
for (int mycol = 0; mycol < xC_TS; mycol++) {
XSSFCell cell = row.createCell(mycol);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(xData[myrow][mycol]);
}
FileOutputStream fOut = new FileOutputStream(outFile);
wb.write(fOut);
fOut.flush();
fOut.close();
}
}



imported the xlsx poi files

error : "java.lang.NullPointerException"

Can u pls let me know how to handle xls files rather than xlsx files ps
its really urgent

HI,

I used xlsx file , still its doing the same thing

erasing the old written data and rewriting again

is there any thing i need to change n the write file ?

A NullPointerException means that one of the variables you are passing is null, but the code tries to use it like it is not.

For example, If I do this:

Integer myInteger = null;
int n = myInteger.intValue();
The code tries to grab the intValue of myInteger, but since it is null, it does not have one: a null pointer exception happens.

[quote:1gv69bkq]Can u pls let me know how to handle xls files rather than xlsx files ps
its really urgent
[/quote:1gv69bkq]

i'm afraid we don't have working code for that. but you can convert files to xlsx easily. and then use xls_reader.java

I have converted in to xlsx file , but the problem still remails, if I try to write to xls sheet from more than one Test Annotation, its over writing ,

Could you let me know if u have some other read and write code which i can use

I dont mind using other read write code

[quote:2dkdlxc7]if I try to write to [color=#FF0000:2dkdlxc7]xls [/color:2dkdlxc7]sheet from more than one Test Annotation, its over writing[/quote:2dkdlxc7]

first of all are you using [color=#FF0000:2dkdlxc7]xls [/color:2dkdlxc7]or [color=#FF0000:2dkdlxc7]xlsx[/color:2dkdlxc7].

how is it overwriting? does the code over write on a few cells or does it overwrites the entire file?

Hi,

Its over writing entire file

For example " Read xls file has data in few cells (eg : "hello" in cell (1,1)) , so in write file each annotation while executing is writing all the data from read xls sheet + what ever output from the particular annotation ,

1st test annotation ("hai" in cell (2,1)) : it writes the data from read xls sheet + data out put from Test annotation 1 ==
output in write xls sheet will be hello (1,1) and hai (2,1)

when 2nd annotation runs
2nd test annotation ("how are u" in cell (3,2)): it writes the data from read xls sheet + data out put from Test anotation 2 (clears all the data witten by Test anotation 1 )
output will be : hello (1,1) and ("how are u" in cell (3,2)) but clears hai (2,1)

My problem is when both annotation run , the output must be
hello (1,1) and hai (2,1) and ("how are u" in cell (3,2)) SHOULD NOT clear hai (2,1)

Pls help me

are you using xls or xlsx?

its very important that you use xlsx. in your reply you're always implying that you're using xls and not xlsx.

It does not matter if i am using xls or xlsx i am facing same issue

upload your entire project.

Sir, can i call you , if possible, so that I can explain the problem. kindy let me now when can i call you
Its really urgent , as i am facing this problem since last 20 days
my no is : 614-787-4213

I understood your problem now upload your code so I can run it.

Sir, Is it safe to upload my code in this general forum , i am little skeptical abt the security, as I am concerned abt the code.

I can email u to ur personal email , if u can provide .

U can call me at 614-787-4213 or kindly let me know when can i call u

Regards,
Rakesh Gudla

qtpselenium.supp0rt@gmail.com

Thanks ,

I have sent you email with the script , function and utils

Kindly check ur email