import excel in bonita studio

Q&A
1

Hi everyBody

I have an Excel file where the data is stored inside it. And I want to bring it inside Bonita studio. how can i import this file to bonita?

 

2

In order to get data from an Excel file (.xls or .xlsx) into your own database tables (not BDM) you need to:

  • Get the Excel file from the user an have him upload it to Bonita server. To do so see step 1 and 2 in Dibyajit comment.
  • Then you need to create a custom connector that will take the document (the file attached to Bonita process definition) as an input parameter (+ maybe some settings to connect to the database). In your custom connector code you should use a library such as Apache POI to read the data from the Excel file and use JDBC (with a datasource and connection pooling for better performance) to send the data to your database.
3

Hello
Create a Contract of type File. Create a Document . Map the Document to the contract. Upload the excel from the external page and save it in the Document that you created. 
Now Using FileInputstream and bufferedReader, you can read the document.
Create a object of the table and pass the values to the table object.
This mostly works with csv files.

Regards

4

thanks for your answer

but i think I did not pass the purpose correctly. i want to import the data from excel to dataBase(h2). in the other words I want to save the data directly in the database. (or convert) if possible!

5

Hello
I got the sceanrio. You want to Load Data from Excel Sheet to Database directly.
To do that, please see the Steps that I followed.

1) Create a contract for the Process. Make sure that contract is of type File.

2)Create a Document in the Data tab. Select File contract input and choose your document contract. You need to upload the Document from external Page and make sure that the document saves the uploaded file.

3) Now create a Operation on a service task. You can use the below code.

byte[] content = apiAccessor.getProcessAPI().getDocumentContent(uploadedFile.getContentStorageId())

 

create a new BufferedReader object.Pass Inputstrea Reader Object and pass the content.

its like this

br = new BufferedReader (new FileInputstream( content));
Now Loop through br by reading each line.
Create a table object. Set Table Object column name = br.objectname;

Return table object

 

6

Thank you very much

7

Hello
Have you tried the above scenario ? DId it work for you?
For me, I can only use csv and not any other file type.
let me know if you can manage a better solution.

 

Regards

8

Hello

It did not work, or I was unsuccessful in doing it. And I'm still unable to solve this problem

9

Hello
Same happened with me. I actually got a ready solution and hence was able to implement it.
I will try to create a smaple and upload in example section so that others can use it.
I will update you after I have completed it.

10

Thank you for your attention

11

Just to make sure I correctly understand your use case a few extra questions:

  • Are you using an actual Excel file (e.g. .xls, .xlsx) or a CSV file?
  • Do you want to store the data in the Bonita Business Data Model database? This database is by default in Bonita Studio h2 but can be a different database in production (e.g. MySQL, PostgreSQL, Oracle, SQL Server)
  • Or do you want to store the data in an external database?

Thanks for the clarification.

12

1- It is an Excel file

2- i want to store data in an external data base(PostgreSQl)

i want to find a way how to store data from excel in database in bonita. First i want to learn how to store in h2 and more in other database

 

13

Hello antoine
Just a Small doubt. To use Apache POI, do we require to load any JDK in our Process ?
Is Apache POI included in Bonita package ? For Production servers, do we need to add to to Bonita.war and redeploy war.

 

regards

14

Apache POI is not provided by Bonita (i.e. not packaged in Bonita web application nor added to Tomcat/WildFly classloader).

In order to use Apache POI in a connector implementation you should:

  1. Go in "Development" -> "Manage dependencies..." and add the poi-4.0.1.jar file
  2. When you create the connector implementation add the poi-4.0.1.jar file as a dependency

Most of POI dependencies (commons-codec, log4j...) should already be provided by Bonita. If needed follow the same steps as for poi-4.0.1.jar to add them.