Write Back into Excel doesn't work Correctly

Product Forums Katalon Studio
,
1

Hi all,

maybe someone can help me with the following problem:

I want to write back into an Excel 2 times, first time i let Katalon read out the E-Mail of another Excelfile and when the Step in the Webapplication with pasting in the Mail, I let write back the Mail into another Excel. The second Time I want to write a “Done” in the next row (near to the Mail written in before) after all steps has been made.

I have the Problem that it’s just writing back a Done back into the Excel 1 time. Normally it should write back 14 Mail addresses and 14 “Dones”.

The Code for first time Writing back:

public class WriteExcel {

@Keyword
public void demoKey(String name) throws IOException{
	FileInputStream fis = new FileInputStream("D:\\Demo\\Demo.xlsx");
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	XSSFSheet sheet = workbook.getSheet("Sheet1");
	int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
	Row row = sheet.createRow(rowCount+1);
	Cell cell = row.createCell(1);
	cell.setCellType(cell.CELL_TYPE_STRING);
	cell.setCellValue(name);
	FileOutputStream fos = new FileOutputStream("D:\\Demo\\Demo.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Method in Test Case:

String result1 = findTestData(‘UserImport/TestExcel’).getValue(5, rowNum)
CustomKeywords.‘writeBackIntoTheExcel.WriteExcel2.demoKey’(result1)

Code second time writing back:
public class WriteExcel {

@Keyword
public void demoKey(String name) throws IOException{
	FileInputStream fis = new FileInputStream("D:\\Demo\\Demo.xlsx");
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	XSSFSheet sheet = workbook.getSheet("Sheet1");
	int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
	Row row = sheet.createRow(rowCount+1);
	Cell cell = row.createCell(2);
	cell.setCellType(cell.CELL_TYPE_STRING);
	cell.setCellValue(name);
	FileOutputStream fos = new FileOutputStream("D:\\Demo\\Demo.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Method in Test Case:

String result = ‘Done’
CustomKeywords.‘writeBackIntoTheExcel.WriteExcel.demoKey’(result)

grafik
grafik511×506 13.1 KB

Thanks a lot in forward

2

Hello,

you can try adding fis.close() on both method

cell.setCellValue(name);
fis.close();
FileOutputStream fos = new FileOutputStream(“D:\Demo\Demo.xlsx”);

3

Salut Antoine,

thanks for your fast idea, i tried it out but it doesn’t work.
Maybe it has to do something with the Excel (xlsx)

4

I like this kind of problem ^^ Got it :

for(int i=0; i<10; i++)
{
CustomKeywords.‘WriteExcel.demoKey1’(i.toString())
CustomKeywords.‘WriteExcel.demoKey2’(“Done”)
}

Here we solve the problem for the first index row

@Keyword
public void demoKey1(String name) throws IOException{
FileInputStream fis = new FileInputStream(“D:\Demo\Demo.xlsx”);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet(“Feuil1”);
int rowCount = sheet.getPhysicalNumberOfRows();
Row row = sheet.createRow(rowCount);
Cell cell = row.createCell(0);
cell.setCellType(cell.CELL_TYPE_STRING);
cell.setCellValue(name);
fis.close()
FileOutputStream fos = new FileOutputStream(“D:\Demo\Demo.xlsx”);
workbook.write(fos);
fos.close();
}

Here we do not create another line each time : we get the one we just created before (or you erase everything on this line)

@Keyword
public void demoKey2(String name) throws IOException{
FileInputStream fis = new FileInputStream(“D:\Demo\Demo.xlsx”);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet(“Feuil1”);
int rowCount = sheet.getLastRowNum();
Row row = sheet.getRow(rowCount);
Cell cell = row.createCell(1);
cell.setCellType(cell.CELL_TYPE_STRING);
cell.setCellValue(name);
fis.close()
FileOutputStream fos = new FileOutputStream(“D:\Demo\Demo.xlsx”);
workbook.write(fos);
fos.close();
}

Result :

image

2 Likes
5

Merci beaucoup Antoine :slight_smile:

1 Like