import excel in bonita studio
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?
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.
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
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.
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.
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.
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.
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.