Issue while writing on excel file with parallel execution

Hello,

Am trying to write on excel while parallel executing. However sometimes on different cells doesn’t write any value and I don’t face any issues on the console output. Below is the write keyword code on excel.

def WriteValues(int cell, int row,String value,String inputexcel) {
	boolean pass=false
	while(pass==false){
		try{
			FileInputStream file = new FileInputStream (new File(RunConfiguration.getProjectDir() + inputexcel))
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			XSSFSheet sheet = workbook.getSheetAt(0);

			'Write data to excel'
			sheet.getRow(row).createCell(cell).setCellValue(value);
			file.close();
			FileOutputStream outFile =new FileOutputStream(new File(RunConfiguration.getProjectDir() + inputexcel));
			workbook.write(outFile);
			outFile.close();
			pass=true
			break;
		}catch(Exception e){

			WebUI.delay(1)
			println(e)
		}
	}

I created a while-loop with try-catch functionality to handle the multiple writing functionality. Any ideas why sometimes returns empty values to my cells and without any warning on console output?

Am trying to write on excel while parallel executing. However sometimes on different cells doesn’t write any value and I don’t face any issues on the console output.

Your code does not implement any Mutual Exclusion for the .xlsx file at all. That is the whole reason why you get unsatisfactory result.

Let me explain. Here I assume:

  • You have an Excel file “a.xlsx” with an empty sheet “MySheet”
  • You have 2 Test Suites: TS1, TS2. You bind them in a single Test Suite Collection, and execute it in Parallel execution mode.

The following processing sequence is likely to occur:

  1. TS1 opens a.xlsx, get MySheet. TS1 finds the MySheet is empty. TS1 writes “Hello” in a cell A1
  2. TS2 opens a.xlsx, get MySheet. TS2 finds the MySheet is empty. TS2 writes “Bye” in a cell B2
  3. TS1 closes FileOutputStream, then MySheet in TS1 is saved into a.xlsx.
  4. TS2 closes FileOutputStream, then MySheet in TS2 is saved into a.xlsx.

How the a.xlsx will look like eventually?

You possibly expect to see

  • A1 has “Hello”
  • B2 has “Bye”

But in fact you would find

  • A1 is empty
  • B2 has “Bye”

The empty A1 would look puzzling for you.


How to overcome the difficulty?

I am negative if you can implement Mutual exclusion for bare local files (including *.xlsx files). In this case you need to invent great magics for yourself.

One idea could be DBMS (MySQL, SQL Server etc) in the backend as the data storage for your Test Suites. See https://docs.katalon.com/katalon-studio/docs/database-settings.html . In this case all the difficult mutex issues will be managed by the DB. Or I may try using Google SpreadSheet API, which provides users with built-in concurrency managements. Both are challenging though.

The easiest solution would be to run the Test Suite Collection sequentially, not parallel. Then the Mutex issues will disappear.

Another practical solution would be separating your process into 2 stages. Stage1 processes data in parallel mode while emitting the result into log-like files for each processes. After Stage1 done, Stage2 reads the log files to collect information and update an Excel file as you like.

Or sqlite if starting a server is not desired.
One day i will write a guide how sqlite can be used with katalon to grab and manage various data for persistent storage. But not yet… :stuck_out_tongue:

Yes, SQLite will be desired.

A quote from SQLite Frequently Asked Questions

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds.

“it must lock the entire database file for the duration of its update” — this is what I meant as “great magics”.

By the way, I have found out that the Test Suites in a Test Suite Collection forks OS-level processes (= Java VM instances). They do not run on multiple Java Threads. I reported this in the following post.

Multi-processes or Multi-threads — this difference is not significant very much. A sophisticated Mutual Exclusion for persistent data storage needs to be implemented anyway.

I’ve got interested in the idea of usin SQLite3 in Test Case script in Katalon Studio. I tried it and got a small success. I have created a repository in GitHub

1. I already had SQLite installed into my Mac. I can not remember when and how. Possibly I installed it using Homebrew some months ago.
2. I downloaded the sqlite-jdbc-3.7.2.jar from Maven Central, saved in the Drivers directory of the project
3. created a test case where I just copy and pasted a sample code from http://www.octodecillion.com/groovy-with-sqlite/, as this Test Cases/TC1
4. run the test case — worked fine. In the console I saw messages:

2020-12-14 12:24:41.612 DEBUG testcase.TC1                             - 7: sql.eachRow("select * from person", { -> ... })
id=1, name= leo
id=2, name= yui
2020-12-14 12:24:41.689 INFO  c.k.katalon.core.main.TestCaseExecutor   - END Test Cases/TC1

Now I know that I can read/write SQLite database in test case. My next question is how to write code that uses database table like an Excel worksheet. I need some Object-Relational Mapping mechanism such as groovy.sql.DataSet.

I developed Test Cases/TC2 that employs groovy.sql.DataSet.

This script did not work. When I executed it I got following error.

2020-12-14 18:38:43.141 ERROR c.k.katalon.core.main.TestCaseExecutor   - ❌ Test Cases/TC2 FAILED.
Reason:
groovy.lang.GroovyRuntimeException: DataSet unable to evaluate expression. AST not available for closure: Script1607931888293$_run_closure2. Is the source code on the classpath?
	at TC2.run(TC2:49)
	...

I searched for solution. I found

… quite puzzling. After few hours of struggle, I got an idea. I made a test case Test Cases/TC3

import my.SQLiteDataSetExample

SQLiteDataSetExample.run()

I made a Groovy class my.SQLiteDataSetExample in the Keywords directory. This class does

package my

import groovy.sql.Sql
import groovy.sql.DataSet

public class SQLiteDataSetExample {
	
	static void run() {

            // The code of Test Case/TC2 is just copied here

	}
}

The Test Cases/TC3 worked fine.The error of “DataSet unable to evaluate expression. AST not available for closure” disappeared.

Now I am sure that we can use SQLite as the local data storage for Katalon Studio test cases.

1 Like

I think it is not really needed to have sqlite installed as a package, since it is just a client.
The driver should be able to handle everything needed just fine (create/query the db).
Not sure on this, will check.
However it can help to have it so one can take a look at the db for debug.

To inspect the DB, there are also some GUI tools available, like : https://sqlitebrowser.org/
This one is running fine on Linux and Windows. Never tried on Mac but should work too.

To overcome the issue with AST error, we can try the ‘old’ query form to retrieve the data:

def qry = """SELECT * FROM Author
  WHERE (firstname > ?)
  AND (lastname < ?)
  ORDER BY lastname DESC"""
def params = ['Dierk', 'Pragt']
def result = sql.rows(qry, params)

this one should not need to evaluate the underlying script.
Anyway i will use a keyword for this, so both solutions should work fine in this case

Great sample project, thank you!

I think you are right.

Mhm… For some reason i cannot make it work on 8.0.0 (the alpha version), got some import errors.
But is working fine with 7.8.0 (will try also some newest versions)

@ThanhTo did something changed in 8.0.0 on the external drivers import mechanism?
It behaves like the jar from the Drivers folder is not loaded.
Will dig a bit more on this if the time afford it, but some devs may want to try this sample project on various versions.
@kazurayam btw, you have something wrong in the keyword, there is a line started with ‘==’ but was easy to fix (has to be on the above line after the closing bracket of the list)

Thank you, I have fixed it.

I could reproduce the problem. In Katalon Studio 8.0 alpha, my test case is unable to import groovy.sql.Sql and groovy.sql.DataSet class. See the screenshot below.

I have found out the reason why.

I could confirm that KS 8.0 uses Groovy ver 3.0.2

whereas KS 7.8 uses Groovy ver 2.4.7

I checked which groovy* jar files are bundled in KS 7.8.

I checked the groovy* jar files bundled in KS 8.0 as well.

The List of bundled groovy* jar files are quite different.

And I noticed that KS 8.0 does not include groovy-sql*.jar. This must be the reason why my test case could not import groovy.sql.Sql class in KS 8.0.

@ThanhTo
@duyluong
@Jass
@devalex88

Question: groovy-all-3.*.jar is available. Any reason why KS 8.0 refrains from groovy-all-3*.jar ?

1 Like

Hi @kazurayam, @bionel,

In Groovy 2.5+, Apache does not provide groovy-all jar like Groovy 2.4. You can compare the differences in:
https://mvnrepository.com/artifact/org.codehaus.groovy/groovy-all/2.4.7
https://mvnrepository.com/artifact/org.codehaus.groovy/groovy-all/3.0.2.

In KS 8.0.alpha2, we added groovy-text, groovy-xml, groovy-json. We will add groovy-sql or other groovy libs as built-in libraries if you think they are necessary for the next alpha or the beta release.

The work-around solution in v8.0.0.alpha2 is adding groovy-sql-3.0.2 to the Drivers folder.

1 Like

Yes, of course.

I do not quite understand this. groovy-all-3.0.2 is there in the Maven Central, isn’t it? Is it a fake?

It is the pom definition file. There is no jar file to download here in v3.0.2.

For groovy-2.4.7-all, you can download the jar file in the Files section

.

OK, I understand it, thank you.

1 Like

aha, make sense.
@kazurayam thx for digging into it.
@duyluong i think will be useful to have groovy-sql added by default. there may be lot of users using custom keywords based on it, at the moment of migrating to the new version they may have the surprise that suddenly all testcases fail.

1 Like

Yes, please.

I found a solution by creating temporary excel files for each parallel execution and then a TC will read those temporary excel files and merge them into one excel file