[Solved] How to populate table/grid

Q&A
1

Hi,

As a newby in using Bonita, I am getting a little confused here when trying to populate a grid in version 6.2

I have the following groovy code the get some values from an Excel file:

import java.io.File; import java.util.Date; import jxl.*;

String p;
List excellist = new ArrayList();

excellist.clear();
//read excel file
Workbook wb = Workbook.getWorkbook(new File('D:/someexcelfile.xls'));
Sheet sheet = wb.getSheet(0);

for (int i=1; i<sheet.getRows();i++)
{
	for (int j=0; j<sheet.getColumns(); j++)
	{
		p=sheet.getCell(j,i).getContents();
		excellist.add(p);
		
	}
}

wb.close();
return excellist;

When evaluating the code gives the desired list van string values. However I see no means to link it to the table in order to populate it. Each time I get an page form error of which I cannot tell what the error exactly is? Any help is appreciated.

Best regards
Frank

2

Hello,

You should have a look at our Trello Example:
http://community.bonitasoft.com/project/trello-application-example-including-trello-connector

There is a step where data are displayed in a table, and downloadable .csv is generated with the file widget. Tell me if it helps !

3

In order to display your data in a list or a grid, you should use a list of lists, that would be structured in two dimensions, something like this (not tested):

import java.io.File; import java.util.Date; import jxl.*; //create list of lists List<List> resultTable = new ArrayList<List>();

//read excel file
Workbook wb = Workbook.getWorkbook(new File(‘D:/someexcelfile.xls’));
Sheet sheet = wb.getSheet(0);

for (int i=1; i<sheet.getRows();i++)
{
List excellist = new ArrayList();
for (int j=0; j<sheet.getColumns(); j++)
{
String p;
p=sheet.getCell(j,i).getContents();
excellist.add(p);
}
resultTable.add(excellist);
}

wb.close();
return resultTable;

4

Hello ttoine,

Thanks for pointing out the example. I’m afraid using connecters etc. is a bridge too far this moment. Just want to understand why a (simple) return list (refer to the groovy script) is not accepted as data input for the table. I see you using all kind of JSON stuff to manage this. Hopefully there is some simple straightforward example available which make me understand the data requirements of a grid. By the way I also tried the same script on a listbox, again with the same negative result.
Finally I’m not a JAVA guru just considering myself as entry level novice.

best regards,
Frank

5

OK, a 2 dimensional array makes sense in repesct to the table to fill. I adjusted the script and now I get, when evaluating, the values of the rows and columns for the Excel sheet. However when showing in the browser it still reports “Error while getting the form page list”. Why?

6

Great, you are moving forward :slight_smile:
Could you get us extract from the log, corresponding to that error, please?

7

Can point out which log you’re referring too?

8

I assume that you are testing in the Studio, so it would be Engine log, accessible through Help menu in the Studio

9

Ahh, found some valueable resource here, was looking for that.
Two errors are reported:

Script1.groovy: 11: unable to resolve class Workbook
@ line 11, column 10.
Workbook wb = Workbook.getWorkbook(new File(‘someexcel.xls’));
Script1.groovy: 12: unable to resolve class Sheet
@ line 12, column 7.
Sheet sheet = wb.getSheet(0);

Apparently the jxl-2.6jar is not referenced automatically. Strange as this is working when evaluting the script directly.

10

Can you try to add this jar as a dependency to your process, using process configuration?

11

When searching for adding dependecies I found

Dependencies are primarily managed through the Development menu. This makes the external module available for all processes. You can also import a dependency when you configure a process, but in this case, it is only available for the process you are configuring.

The first part I already did, added the jxl.jar globally. In order to import the .jar excusievely to a process I can not find the entry where to do so. Is this done in the groovy script? When deselecting the automatic dependencies resolution, I still can not add the jxl.jar, only the table object name is listed here for selection

12

I was rather refering to this section.

13

Finally it’s working!! Still the process dependencies window of the configuration is confusing as you can add the dependency to the ‘groovy scripts’ section (by selecting deselecting the process dependencies item in the treeview), as I thought we’re talking scripts now. But apparently the dependency must added to the ‘others’ section otherwhise the added dependency is not saved and when calling configuration again it is disappeared. Thanks for your support and patience.

14

Great news, thank you for your feedback and happy to hear that it works. This will definitely help others, as well.
Good luck and have fun with Bonita BPM :slight_smile: