Write into excel at runtime during test execution

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