How do I get Katalon to add my number to a different line in excel everytime I run my test?

lisa.s · June 29, 2023, 4:13pm

Hi I have done many many searchs on this topic and I think yet again, ive over thought it.

I have this test case (“borrowed” from here)

FileInputStream fis = new FileInputStream(location)

XSSFWorkbook workbook = new XSSFWorkbook(fis)

XSSFSheet sheet = workbook.getSheet(‘Sheet1’)

// cell A2
Row row = sheet.createRow(2)

Cell cell = row.createCell(0)

cell.setCellType(cell.CELL_TYPE_STRING)

cell.setCellValue(number)

//GlobalVariable.bookingId instanceof String
FileOutputStream fos = new FileOutputStream(location)

workbook.write(fos)

fos.close()

fis.close()

and what I want it to do is write to the next line each time its run. But other than changing the row number in each test, is there a better way that I am able to do this?

At the moment, I run my test, it takes the number and puts it into an excel sheet. Its rare for this test that I will need that number again, so I want to put it into an excel sheet and file it away in the event my testing is called into question.

Grateful again for everyones help in getting me as far as I have

Dan_Bown · June 29, 2023, 4:39pm

Could you instead print the value in the test script, so that when you run your test suite and obtain a report, the value is clearly visible in the report? This way you get a record of execution along with test data used, and even a screenshot if you so wish.

grylion54 · June 29, 2023, 9:52pm

The question that I have is are you going to be running the test again at some future date or are you going to be saving the “bookingId” again within the same test?

If you are going to Save the “bookingId” again within the same test, then you can put a variable within the createRow() method and then just increase the variable. Unfortunately, it probably will get more complicated than this because when you run your test again in the future, I can see a situation, but I don’t know everything you want to do.

// cell A2
bookingRow = 2;
Row row = sheet.createRow(bookingRow )
Cell cell = row.createCell(0)
bookingRow++   // increase the booking row by 1

This would be better if you created a Keyword and had the row to save the “bookingID” to as a parameter.

// cell A2
bookingRow = 2;
CustomKeywords.'com.Tools.saveBookingToExcel'(strExcelpathway, bookingId, bookingRow)
bookingRow++   // increase the booking row by 1

Keyword could be like:

@Keyword
public void saveBookingToExcel(String location, String bookingId, int bookingRow) {
    FileInputStream fis = new FileInputStream(location)

    XSSFWorkbook workbook = new XSSFWorkbook(fis)

    XSSFSheet sheet = workbook.getSheet('Sheet1')

    Row row = sheet.createRow(bookingRow)

    Cell cell = row.createCell(0)

    cell.setCellType(cell.CELL_TYPE_STRING)

    cell.setCellValue(bookingId)
 
    FileOutputStream fos = new FileOutputStream(location)

    workbook.write(fos)

    fos.close()
    fis.close()
}

If you need to run your script over multiple times and get the “bookingId” and update your spreadsheet if you have a new one, then, can you overwrite the old “bookingId” that you had written to the spreadsheet or not? If not, then you need to determine which row where you last saved to and then move from there.

Finding the next row maybe like:

FileInputStream fis = new FileInputStream (GlobalVariable.gTestIdPathWay);
XSSFWorkbook workbook = new XSSFWorkbook (fis);

XSSFSheet sheet = workbook.getSheet("Sheet1");

bookingRow = 1;
hasFound = false;

while (!hasFound) {
    Row row = sheet.getRow(bookingRow);
    Cell cell = row.getCell(0);
    bookingId = cell.getStringCellValue();
    if (bookingId == "")  {  //  if the bookingId is empty
        hasFound = true;
    }
    bookingRow++;
}
fis.close();

lisa.s · June 30, 2023, 12:18pm

This is amazing, thank you so much. I have been working on this for quite a few days and couldnt think of a solution at all.

I dont intend to reuse the numbers again as the enviroments get wiped everymonth, but its just to keep a record for me.

Once again thanks for your time,

lisa.s · June 30, 2023, 2:31pm

Thanks Dan :). This gave me food for thought on something else as I had forgotten I could do this :).

lisa.s · July 11, 2023, 11:34pm

@grylion54
I am experiencing an issue and wondered if you wouldnt mind helping again please. I have put your script in my test but it is failing at the Cell cell = row.getCell(0); bit and Ive googled and changed some stuff and changed some more stuff but it is just not working and I am stuck and I would buy you cake if you are local to me
String location = ‘xxxxx’

//GlobalVariable.bookingId = WebUI.getText(findTestObject(‘Search drop down/Page_Checkout/BookingID’))
String number = WebUI.getText(findTestObject(‘Search drop down/CheckoutPage/BookingID’))

System.out.println(number)

//GlobalVariable.bookingId = bkg_id
FileInputStream fis = new FileInputStream(location)

XSSFWorkbook workbook = new XSSFWorkbook(fis)

XSSFSheet sheet = workbook.getSheet(‘Sheet1’)

bookingRow = 1;
hasFound = false;

while (!hasFound) {
Row row = sheet.getRow(bookingRow);
Cell cell = row.getCell(0);
bookingId = cell.getStringCellValue();
if (bookingId == “”) { // if the bookingId is empty
hasFound = true;
}
bookingRow++;
}
fis.close();

Caused by: java.lang.NullPointerException: Cannot invoke method getCell() on null object

Test Cases/A new Sanity Test/Ocean cruises/Two guest booking (SOA) Checked FAILED.
Reason:
com.kms.katalon.core.exception.StepErrorException: Call Test Case ‘Test Cases/In progress things/New Excel’ failed because of error(s)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.doCall(CallTestCaseKeyword.groovy:66)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.call(CallTestCaseKeyword.groovy)
at com.kms.katalon.core.keyword.internal.KeywordMain.runKeyword(KeywordMain.groovy:74)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.callTestCase(CallTestCaseKeyword.groovy:81)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.execute(CallTestCaseKeyword.groovy:44)
at com.kms.katalon.core.keyword.internal.KeywordExecutor.executeKeywordForPlatform(KeywordExecutor.groovy:74)
at com.kms.katalon.core.keyword.BuiltinKeywords.callTestCase(BuiltinKeywords.groovy:334)
at Two guest booking (SOA) Checked.run(Two guest booking (SOA) Checked:56)
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:448)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:439)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:418)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:410)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:285)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:142)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:133)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1689117848552.run(TempTestCase1689117848552.groovy:25)
Caused by: java.lang.NullPointerException: Cannot invoke method getCell() on null object
at New Excel.run(New Excel:47)
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:448)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:439)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:418)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:410)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:285)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:142)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.doCall(CallTestCaseKeyword.groovy:59)
… 18 more

grylion54 · July 12, 2023, 12:42am

Do you have all of the following at the top of your code?

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;

lisa.s · July 12, 2023, 1:11am

I do have these…

import org.openqa.selenium.Keys as Keys
import org.apache.poi.xssf.usermodel.XSSFCell as XSSFCell
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
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.Row as Row

grylion54 · July 12, 2023, 2:37pm

I put together a small sample of our concern and the issue is that the Row the NullPointerException occurs on is the one that is blank, perhaps because it was not “created” by Excel via code. So, how about if we use the Exception and get the bookingRow because of the error it generates (Generally, not a good idea, however, I actually had to do this before). We know the column is “A”, so let’s get the row. like you have above but we add a try / catch to it, like below:

FileInputStream fis = new FileInputStream(location)
XSSFWorkbook workbook = new XSSFWorkbook(fis)
XSSFSheet sheet = workbook.getSheet('Sheet1')

bookingRow = 1;
hasFound = false;
try {
	while (!hasFound) {
		Row row = sheet.getRow(bookingRow);
		Cell cell = row.getCell(0);
	
		bookingId = cell.getStringCellValue();
		//println("Found ${bookingId}")
		if (bookingId == "") { // if the bookingId is empty
			hasFound = true;
		}
		bookingRow++;
	}
} catch (NullPointerException) {
	println("now on A${bookingRow}")
}

println("now on row number: ${bookingRow}")

fis.close();

lisa.s · July 26, 2023, 11:45am

I am so sorry as I didnt say thank you for this and the time and effort you put in.

Topic		Replies	Views
Error trying to write in the following row after every iteration (test suite) Archive katalon-studio , web-testing	7	1105	August 8, 2022
How would I assign each data row to each test suite? Katalon Studio	2	223	December 21, 2023
Writing to Excel file row by row during runtime Katalon Studio katalon-studio , web-testing	4	919	May 15, 2020
Adding a different text input for each run Archive	2	1273	November 21, 2018
How to add a different text value after every run Katalon Studio katalon-studio , web-testing	2	679	March 11, 2021

How do I get Katalon to add my number to a different line in excel everytime I run my test?

Related Topics