Write into excel at runtime during test execution

Currently Katalon provides an inbuilt featute to read data from exel. But doesn’t have option to write into excel.

It would be great if Katalon support’s excel write feature, out of the box rather using custom keyword.

The excel write feature would be a big selling point for Katalon.

Thanks team for looking into this.

14 Likes

Pls suggest a solution until the writing into Excel not available.

1 Like

sandeep kumar said:

Pls suggest a solution until the writing into Excel not available.

discover.selenium said:

Currently Katalon provides an inbuilt featute to read data from exel. But doesn’t have option to write into excel.

It would be great if Katalon support’s excel write feature, out of the box rather using custom keyword.

The excel write feature would be a big selling point for Katalon.

Thanks team for looking into this.

Hey guys,

Thank you for using Katalon Studio and my apologies for late response. I will put this request in the development queue and will share it with the team to consider for future release.

Let us know if you need anything else, we are happy to help :slight_smile:

Liam

3 Likes

Hi Sandeep, You have to use custom keyword to write into excel.

@discover.selenium said:
Hi Sandeep, You have to use custom keyword to write into excel.

And how looks the code?

tod2020@mail.ru said:

@discover.selenium said:

Hi Sandeep, You have to use custom keyword to write into excel.

And how looks the code?

You could create a custom keyword with the code as the example below:


@Keyword
def void writeToExcel(String text ){
	FileInputStream file = new FileInputStream (new File("D:\\Testdata.xlsx"))
	XSSFWorkbook workbook = new XSSFWorkbook(file);
	XSSFSheet sheet = workbook.getSheetAt(0);
	Cell searchText = sheet.getRow(1).getCell(1);
	searchText.setCellValue(text);
	file.close();
	FileOutputStream outFile =new FileOutputStream(new File("D:\\Testdata.xlsx"));
	workbook.write(outFile);
	outFile.close();
}
2 Likes

thx a lot!

which package should I import?

Cell searchText = sheet.getRow(1).getCell(1);

Thanks
this works


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
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.lang.String 
FileInputStream file = new FileInputStream (new File("E:\\Testdata.xlsx"))
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);

'Read data from excel'
String Data_fromCell=sheet.getRow(1).getCell(1).getStringCellValue();
'Write data to excel'
sheet.getRow(1).createCell(1).setCellValue("Mahesh2");
file.close();
FileOutputStream outFile =new FileOutputStream(new File("E:\\Testdata.xlsx"));
workbook.write(outFile);
outFile.close();
5 Likes

Hi, I would like to write different texts into different cells with this in one case. Could anyone help me how?

3 Likes

Sweet. Works for me!

I would like to get different texts and write them into excel into different cells, but now only the last appears not all. I don’t know why.

Melinda Lakner said:

I would like to get different texts and write them into excel into different cells, but now only the last appears not all. I don’t know why.

I needed to write data to excel. I created Custom Keyword for that.
Under Keywords right click and select NEW → PACKAGE.
Name it writeToExcel (or however you want)
After that right click your package and select NEW → KEYWORD.
Name it WriteExcel (or however you want).
Inside your new keyword add this code (my code has package name writeToExcel and keyword name WriteExcel, if you change yours then you need to modify my code):

package writeToExcel

import org.apache.poi.ss.usermodel.Cell

import org.apache.poi.ss.usermodel.Row

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.kms.katalon.core.annotation.Keyword

public class WriteExcel {

@Keyword

def void writeToExcel(int iRow, int iCell, String iText ){

FileInputStream file = new FileInputStream (new File(“C:\\YOUR_LOCATION_OFFILE\\FILE_NAME.xlsx”))

XSSFWorkbook workbook = new XSSFWorkbook(file);

XSSFSheet sheet = workbook.getSheet(“YOUR_SHEET_NAME”)

//Write data to excel’

Row oRow;

oRow = sheet.getRow(iRow);

if(oRow == null){

sheet.createRow(iRow);

oRow = sheet.getRow(iRow);

}

Cell oCell;

oCell = oRow.getCell(iCell - 1);

if(oCell == null ){

oRow.createCell(iCell - 1);

oCell = oRow.getCell(iCell - 1);

}

oCell.setCellValue(iText);

FileOutputStream outFile =new FileOutputStream(new File(“C:\\YOUR_LOCATION_OFFILE\\FILE_NAME.xlsx”));

workbook.write(outFile);

outFile.close();

}

}

You call it like this:

CustomKeywords.‘writeToExcel.WriteExcel.writeToExcel’(your_Row, your_cell, ‘your_text’)

Remember to close your EXCEL file before you run your script or it will be read-only and you want be able to write.

1 Like

Ok, other problem.
I get an excel with datas that I use for testing, and I want to keep them. After I get back a popup text and write into the cell, other datas disappear from the other cells. It is not good.
How can I write popup message into excel without other test datas disappear from it?
For example:
I get datas in A1, B1, C1, D1
I want to write back popup text into E1
Now I can get popup text and write into E1, it is ok, but A1, B1, C1, D1 disappear, it is not ok.

I use Excel to read and write data into same sheet with the code that I pasted in the post above, and I don’t have your problems. Nothing gets deleted.
Did you use the code that I pasted?
Can you post screenshot of your script code where you call your WriteExcel keyword?

1 Like

Hi,

Thanks very much since I needed this so badly !

I lightly adjusted the Keyword, adding sheet name in parameter and location and name of excel file from a GlobalVariable.

I use the keyword in my test cases and it works just fine, I just notice that it takes about 15 seconds to write a data in Excel which is quite a long time when you call this Keyword ~20 times in a test case.

Is there a way to speed up the writing process ?

1 Like

Christophe Lebot said:

Hi,

Thanks very much since I needed this so badly !

I lightly adjusted the Keyword, adding sheet name in parameter and location and name of excel file from a GlobalVariable.

I use the keyword in my test cases and it works just fine, I just notice that it takes about 15 seconds to write a data in Excel which is quite a long time when you call this Keyword ~20 times in a test case.

Is there a way to speed up the writing process ?

I didn’t fine one. Sorry.

Ok, thank you for replying

**all interaction-function with the excel-file **
https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html

**ATTENTION
**
**1. check cell
**if the cell is null, you should set any value in it otherwise you will get an error
if (sheet.getRow(1).getCell(1).getStringCellValue()==null) {
sheet.getRow(1).createCell(1).setCellValue(“something”);}

**2. do ****not to confuse
**sheet.getRow(1).createCell(1).setCellValue(“Mahesh2”);