Service Task: write/get data into/from existing xls-file

Hello everyone,

I would like to add a Service Task to the process which writes form data (previous user task) into an existing excel-file and gets data from the same excel-file. Eventually, it should delete the written data again as the same file will be used for the next process instantance with the cells being empty. I have only very limited eclipse knowledge and desgined the process in Camunda only. Deployment was done directly within Camunda using a Heroku-url.

Thank you for your help.

@DaniHorn where is your xsl located?

Given your listen use of Camunda, you could fairly easily do this with a script. Just add POI lib to your classpath for the camunda install, and then you can edit the file using Groovy or Javascript, and this can all be done alongside your BPMN file (no need to write a java delegate).

Edit: Also does this need to be a excel file? Can it just be a CSV that you open in excel afterwards?

@DaniHorn I pulled some older code for you to start from:

var Files = Java.type('java.nio.file.Files')
var Paths = Java.type('java.nio.file.Paths')
var CSVPrinter = Java.type('org.apache.commons.csv.CSVPrinter')
var CSVFormat = Java.type('org.apache.commons.csv.CSVFormat')
var StandardOpenOption = Java.type('java.nio.file.StandardOpenOption')

var firstName = execution.getVariable('firstName')
var lastName = execution.getVariable('lastName')
var title = execution.getVariable('title')
var category = execution.getVariable('category')

var csvPath = "/csv_files/output.csv";
var writer = Files.newBufferedWriter(Paths.get(csvPath), StandardOpenOption.APPEND);

// Example of adding a header row.
// Make sure to add logic for detecting header row and not adding the header row on subsequent appends
// Simple workaround is to just make sure the first row is always included in your CSV file.
// var csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader("First", "Last", "Title", "Category"));

var csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT);

// If you have multiple records to add to the csv, 
// you can loop through this line:
csvPrinter.printRecord(firstName, lastName, title, category);

csvPrinter.flush();
writer.flush();
writer.close();
csvPrinter.close();

Working example is in the link.

wow! thanks a lot for the quick response! I guess it can also be a csv file. The xls already exists and contains several formulas to perform sort of a scoring for the values that need to be collected from camunda form. The result of this scoring should be pushed back to camunda.

I am a bit embarrased to ask and I am really sorry for my lack of knowledge (I am a business guy trying to learn IT-stuff), but do I need to install docker compose to run docker-compose up -d?

No problem!
Yes you need to install docker on your computer. You can do with with “Docker for Windows” or “Docker for Mac”

If your camunda instance was setup by someone else, you just need to ask them to “add the following Jar to the classpath”: https://github.com/DigitalState/camunda-variations/tree/master/csv-file-writer/docker/camunda/lib

edit: You can use excel POI, its just a little more complicated to setup, but the principal of usage is the exact same as in the script that uses the Commons CSV.

The scoring, these need to be in excel? Could you just do the scoring as a small chunk of code ?

Agree - I was considering suggesting a decision table…

Most probably it could also just be coded. Considering my basic knowledge in coding and that the xls already works, I would prefer to use excel for this one. In addiditon to this, it’s not the highest priority for the process to be most efficient, but
it’s also about trying to connect other services. I will go through the example files you sent me and try to adapt it to my work. Thanks a lot for your support! Highly appreciated.

Hallo Dany,

Have you managed to make XLS parser work? If yes, what solution you have implemented?

Cheers, Gabor

@gabor.fodor have you tried the code at the start of this thread?