Write into excel at runtime during test execution

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”);

Hello All,

The examples listed here deal with static row numbers. Can i use data binding feature of katalon Studio to pass the row number to the test case? Can anyone suggest a method for the same?

hi, on using the above code i am getting filenotfound exception, then i created file manually and ran code, even then same error is coming,pls help me.

Have you verified that the file is in the correct file path?
can you post a snippet of your code so we can see what your importing and how you are handling the data?

also, look into this as a possible solution https://store.katalon.com/product/34/Read-Write-Excel-Custom-Keywords#overview-content

1 Like

hey @ehernandez , thank u so much for trying to help ,anyway i solved the problem

Hi,
Thankyou for this article. I tried these codes in my test case and it seems to be failing due to error:
Error log is pasted for reference. Due to the POI error, I added latest "poi-bin-4.1.0-20190412.jar’ in my external Libraries. still the same error. Please advise.

Reason:
org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
** at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:62)**
** at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:403)**
** at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)**
** at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:207)**
** at com.test.WriteExcel.demoKey(WriteExcel.groovy:39)**
** at com.test.WriteExcel.invokeMethod(WriteExcel.groovy)**
** at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:49)**
** at Writing_To_Excel_Test.run(Writing_To_Excel_Test:21)**
** at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)**
** at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)**
** at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:336)**
** at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:327)**
** at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:306)**
** at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:298)**
** at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:232)**
** at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)**
** at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)**
** at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)**
** at TempTestCase1557363639882.run(TempTestCase1557363639882.groovy:21)**
Caused by: java.lang.reflect.InvocationTargetException
** at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:60)**
** at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:403)**
** at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)**
** at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:207)**
** at com.test.WriteExcel.demoKey(WriteExcel.groovy:39)**
** at com.test.WriteExcel.invokeMethod(WriteExcel.groovy)**
** at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:49)**
** at Script1557245794605.run(Script1557245794605.groovy:21)**
** … 11 more**
Caused by: java.lang.LinkageError: loader constraint violation in interface itable initialization: when resolving method “org.apache.xmlbeans.impl.store.Xobj$NodeXobj.getChildNodes()Lorg/w3c/dom/NodeList;” the class loader (instance of org/codehaus/groovy/tools/RootLoader) of the current class, org/apache/xmlbeans/impl/store/Xobj$NodeXobj, and the class loader (instance of ) for interface org/w3c/dom/Node have different Class objects for the type org/w3c/dom/NodeList used in the signature
** at org.apache.xmlbeans.impl.store.Cur.createDomDocumentRootXobj(Cur.java:247)**
** at org.apache.xmlbeans.impl.store.Cur.createDomDocumentRootXobj(Cur.java:236)**
** at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.(Cur.java:2841)**
** at org.apache.xmlbeans.impl.store.Locale$SaxHandler.initSaxHandler(Locale.java:3164)**
** at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3475)**
** at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)**
** at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)**
** at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)**
** at org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument$Factory.parse(Unknown Source)**
** at org.apache.poi.xssf.model.ThemesTable.(ThemesTable.java:44)**
** … 19 more**

I think ive listed a good explanation of how I do it. this may help someone, so here it is

‘Specifies which File to look at’
FileInputStream file = new FileInputStream(new File(‘Automation.xlsx’)); //put the workbook in your root katalon project folder
XSSFWorkbook workbook = new XSSFWorkbook(file);
‘Specifies which sheet to look at’
XSSFSheet sheet = workbook.getSheet(‘Sheet1’);

‘adding data to the workbook’
sheet.getRow(THE_EXCEL_ROW_YOU_ARE_TARGETTING).createCell(THE_EXCEL_COLUMN_YOU_ARE_TARGETTING).setCellValue(WHAT_YOU_ARE_ADDING_SPREADSHEET);

file.close()

FileOutputStream outFile = new FileOutputStream(new File(‘Automation.xlsx’));
workbook.write(outFile);
outFile.close();