Write data into Excel file at runtime during test execution


This is a companion discussion topic for the original entry at https://docs.katalon.com/katalon-studio/docs/write-data-into-excel-file-at-runtime-during-test-execution.html

trying to run your example but having this error

Dont worry I fixed it, I had to modify a bit your code, just wanted to write in spreadsheet. :smiley:

1 Like

The following code helps you to the copy data from application and paste it in specific cell.it is applicable for iterations also

–>Creation of Custom Key Word

package myPack
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords
import internal.GlobalVariable
import MobileBuiltInKeywords as Mobile
import WSBuiltInKeywords as WS
import WebUiBuiltInKeywords as WebUI
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel{

private  static int i=0;

@Keyword
public static void demoKey(String name,String Column_Name) throws IOException{
	FileInputStream fis = new FileInputStream("D:\\Katalon Studio\\TestData\\Katalon_DDF.xlsx");
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	XSSFSheet sheet = workbook.getSheet("Sheet1");
	int rowCount = i;
	if (Column_Name=='Credential1'){
		Row row = sheet.getRow(rowCount+1);
		Cell cell = row.createCell(2,0);
		cell.setCellType(cell.CELL_TYPE_STRING);
		cell.setCellValue(name);
	}

	if (Column_Name=='Credential2'){
		Row row2 = sheet.getRow(rowCount+1);
		Cell cell2 = row2.createCell(3,0);
		cell2.setCellType(cell2.CELL_TYPE_STRING);
		cell2.setCellValue(name);
                    i = rowCount+1;
	}

FileOutputStream fos = new FileOutputStream("D:\\Katalon 
   Studio\\TestData\\Katalon_DDF.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Test Case Code :

String Credential1 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_Sign in Katalon Studio/Page_Sign in Katalon Studio_01/div_Enter your details below’))

CustomKeywords.‘myPack.WriteExcel.demoKey’(Credential1,‘Credential1’)

System.out.println(Credential1) //Output will be “Enter your details below”

//WebUI.scrollToElement(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/a_Company’,5))

WebUI.click(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/a_Company’))

String Credential2 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_3’))

CustomKeywords.‘myPack.WriteExcel.demoKey’(Credential2,‘Credential2’)

System.out.println(Credential2)//Output will be 3K

Copy the Code and try it

All the best

Hey thanks for this I tried to implement your code and got this error

this is my script


PLease help

Hi pgonzalez,

Copy my Code As it as …including Names Also …It will work …Then understand the Flow …

Note:Just change your Path of the Excel file and give data in the Cells exactly.

The Code is still working for me …

All the Best

Thank you

Mikky…!

Same issue here with code complete copied into katalon 6.1.5
Getting NPE on Cannot invoke method createCell() on null object

paste your code and error log .lets try to find RCA.

Has anyone had any luck with this on a mac? Doesn’t seem to like the file path…

Have tried everything I can think of e.g.
“/Users/admark⁩/Desktop/fsoIds.xlsx”
“Users/admark⁩/Desktop/fsoIds.xlsx”
“/admark⁩/Desktop/fsoIds.xlsx”
"admark⁩/Desktop/fsoIds.xlsx

Hi teja3535,
Could you help me to get it to work on a csv file since my working environment has no excel available.

Thanks,

Jane

Hi Admark,

Still Facing the Issue or Resolved…

Thanq

Hi Jane,

The above code is use full for only Excel as you might know Data driven Framework concept.

Do one thing raise a ticket saparatley for CSV file issue ,so that group members will show you a way 100%

Thanq…

@teja3535
I tried this code too, but made changes specific to my test and excel. But the method is not even running. Wondering if I have some formatting issues or if I am making a mistake calling the method. Any comments/help with this issue is appreciated. TIA.

Error shown -


Code at the beginning of the test -
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject
import org.openqa.selenium.Keys as Keys
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.By as By
import org.openqa.selenium.WebDriver as WebDriver
import org.openqa.selenium.WebElement as WebElement
import com.kms.katalon.core.webui.driver.DriverFactory as DriverFactory

import java.io.FileInputStream as FileInputStream
import java.io.FileNotFoundException as FileNotFoundException
import java.io.FileOutputStream as FileOutputStream
import java.io.IOException as IOException
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.Row as Row
import org.apache.poi.xssf.usermodel.XSSFCell as XSSFCell
import org.apache.poi.xssf.usermodel.XSSFCellStyle as XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFRow as XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet as XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook

public class WriteExcel{

private  static int i=0;

@Keyword
public static void demoKey(String name,String Column_Name) throws IOException{
	FileInputStream fis = new FileInputStream("C:\\Katalon Studio\\SDWP_FlatIron Certification.xlsx");
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	XSSFSheet sheet = workbook.getSheet("ISA Bulk Certification - SDWP");
	int rowCount = i;
	if (Column_Name=='ContractNumber'){
		Row row = sheet.getRow(rowCount+1);
		Cell cell = row.createCell(1,0);
		cell.setCellType(cell.CELL_TYPE_STRING);
		cell.setCellValue(name);
	}

	if (Column_Name=='PassFail'){
		Row row2 = sheet.getRow(rowCount+1);
		Cell cell2 = row2.createCell(2,0);
		cell2.setCellType(cell2.CELL_TYPE_STRING);
		cell2.setCellValue(name);
					i = rowCount+1;
	}

FileOutputStream fos = new FileOutputStream("C:\\Katalon Studio\\SDWP_FlatIron Certification_PassFail.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Test Code -
// add the contract number to the excel file
String ContractNumber = WebUI.getText(findTestObject(‘Admin Portal/Page_Certification App/span_ContractNumber’))
System.out.println(ContractNumber)

CustomKeywords.‘myPack.WriteExcel.demoKey’(ContractNumber, ‘ContractNumber’)

Hi Mridula,

Try the same code without doing any changes .understand the logic .once it is working fine then change the code as per your requirements …As it working for me …from past 6 months …

Thanq …

@mridula.palivela

What you showed to us:

You need to change it as:

package myPack

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
...

Thanks for the response @kazurayam

I actually had to make a change in the custom keyword class… changed public static void demoKey to def demokey and then it worked, atleast got the excel and moved further in the code.

But I just saw that we can use Excel keyword that is available with the tool. Any insight into using that is appreciated,

Excel-Keywords (https://store.katalon.com/product/34/Excel-Keywords) is a Katalon Plugin which is most frequently downloaded = 3.1K downloads. I believe you should try that.

The following would be informative for you :

@kazurayam

I am trying to create table in the excel file from Katalon, I wrote a code which works fine in eclipse but not work in Katalon kindly help.
Here I am providing your code below:
Katalon basic all imports
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
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.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFTableStyleInfo;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable
public class getTable {
@Keyword
def main() throws FileNotFoundException, IOException {

String Directory = “D:\Softwares\DPIP\TA\”;

Workbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet) wb.createSheet();

// Set which area the table should be placed in
AreaReference reference = wb.getCreationHelper().createAreaReference(
        new CellReference(0, 0), new CellReference(2, 2));

// Create
XSSFTable table = sheet.createTable(reference); //creates a table having 3 columns as of area reference
// but all of those have id 1, so we need repairing
table.getCTTable().getTableColumns().getTableColumnArray(1).setId(2);
table.getCTTable().getTableColumns().getTableColumnArray(2).setId(3);

table.setName("Test");
table.setDisplayName("Test_Table");

// For now, create the initial style in a low-level way
table.getCTTable().addNewTableStyleInfo();
table.getCTTable().getTableStyleInfo().setName("TableStyleMedium2");

// Style the table
XSSFTableStyleInfo style = (XSSFTableStyleInfo) table.getStyle();
style.setName("TableStyleMedium2");
style.setShowColumnStripes(false);
style.setShowRowStripes(true);
style.setFirstColumn(false);
style.setLastColumn(false);
style.setShowRowStripes(true);
style.setShowColumnStripes(true);

// Set the values for the table
XSSFRow row;
XSSFCell cell;
for (int i = 0; i < 3; i++) {
    // Create row
    row = sheet.createRow(i);
    for (int j = 0; j < 3; j++) {
        // Create cell
        cell = row.createCell(j);
        if (i == 0) {
            cell.setCellValue("Column" + (j + 1));
        } else {
            cell.setCellValue((i + 1.0) * (j + 1.0));
        }
    }
}
// Save
FileOutputStream fileOut = new FileOutputStream(Directory+"ooxml-table.xlsx") 
    wb.write(fileOut);

}
}
It shows following error:
Test Cases/Table FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: groovy.lang.MissingMethodException: No signature of method: org.apache.poi.xssf.usermodel.XSSFCreationHelper.createAreaReference() is applicable for argument types: (org.apache.poi.ss.util.CellReference, org.apache.poi.ss.util.CellReference) values: [org.apache.poi.ss.util.CellReference [A1], org.apache.poi.ss.util.CellReference [C3]]
at table.getTable.invokeMethod(getTable.groovy)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:50)
at Table.run(Table:16)
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:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
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 TempTestCase1581401567739.run(TempTestCase1581401567739.groovy:21)
Caused by: groovy.lang.MissingMethodException: No signature of method: org.apache.poi.xssf.usermodel.XSSFCreationHelper.createAreaReference() is applicable for argument types: (org.apache.poi.ss.util.CellReference, org.apache.poi.ss.util.CellReference) values: [org.apache.poi.ss.util.CellReference [A1], org.apache.poi.ss.util.CellReference [C3]]
at table.getTable.main(getTable.groovy:76)
… 14 more

I have no experience of creating “Table” in Excel. I do not have any insight.

I tried to reproduce your problem on my side. I copied and pasted your code to create a Keyword, and made a Test Case which calls the Keyword. When I ran the Test Case I got the following error:

2020-02-11 23:11:02.720 ERROR c.k.katalon.core.main.TestCaseExecutor   - ❌ Test Cases/TC1 FAILED.
Reason:
groovy.lang.MissingMethodException: No signature of method: org.apache.poi.xssf.usermodel.XSSFSheet.createTable() is applicable for argument types: (org.apache.poi.ss.util.AreaReference) values: [org.apache.poi.ss.util.AreaReference [A1:C3]]
Possible solutions: createTable(), removeTable(org.apache.poi.xssf.usermodel.XSSFTable)
	at GetTable.main(GetTable.groovy:26)
	at GetTable$main.call(Unknown Source)
	at TC1.run(TC1:20)
	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:337)
	at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
	at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
	at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
	at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
	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 TempTestCase1581430256088.run(TempTestCase1581430256088.groovy:23)

2020-02-11 23:11:02.850 INFO  c.k.katalon.core.main.TestCaseExecutor   - END Test Cases/TC1

The diagnostics is different from the one you presented above. I do not see the reason why different.