How to Use custom HTML and JavaScript To Export Data to Excel
Hi
I have BonitaSoft BPM 7.3.1 .
I have a Custom Application Page fetching Data from an API call. The Data is displayed in a tabular format.
Now I need to export the same data as an Excel Sheet.
I need to have a button to export the Data .
I have come across several Links for this Process.
1) http://jsfiddle.net/hybrid13i/JXrwM/
2) http://stackoverflow.com/questions/16078544/export-to-csv-using-jquery-a...
3) http://jsfiddle.net/terryyounghk/KPEGU/
I am a newbie here and cannot figure out how to use these custom HTML and JavaScript in my Application page.
I have tried adding the information to a custom Widget.
HTML code in the Template and JavaScript code in the Controller section. But That doesn't Work.
Also I tried taking a TEXT field in the Page and adding the HTML Code there. Creating a JavaScript Variable .
Upon Preview, Nothing happens.
Any Guidance will be Much Appreciated.
Kindly Provide a Step by Step guide if Possible.
Also If Someone have already Created such a Page, Please share a link to download.
Thanks
Hi, here is my answer:
Create a Custom Widget
Add a CSS Asset with the following - you can change this to your needs later:
- a.export,
- a.export:visited {
- display: inline-block;
- text-decoration: none;
- color: #000;
- background-color: #ddd;
- border: 1px solid #ccc;
- padding: 8px;
- }
Add an External Javascript Asset pointing to http://code.jquery.com/jquery-1.11.0.min.js (use whichever jQuery library you want but this one I know works...)
Add the following to Template as a sample table:
the first line a is the Button to press and is formatted by the CSS above, if you change the table div id="dvData" then make sure you also change it in the code below.
- <a id="exportToCSV" href="#" class="export" ng-click="ctrl.exportTableToCSV02()">Export Table data into Excel</a>
- <div id="dvData">
- <table>
- <tr>
- <th>Column One</th>
- <th>Column Two</th>
- <th>Column Three</th>
- </tr>
- <tr>
- <td>row1 Col1</td>
- <td>row1 Col2</td>
- <td>row1 Col3</td>
- </tr>
- <tr>
- <td>row2 Col1</td>
- <td>row2 Col2</td>
- <td>row2 Col3</td>
- </tr>
- <tr>
- <td>row3 Col1</td>
- <td>row3 Col2</td>
- <td>row3 Col3</td>
- </tr>
- <tr>
- <td>row4 'Col1'</td>
- <td>row4 'Col2'</td>
- <td>row4 'Col3'</td>
- </tr>
- <tr>
- <td>row5 "Col1"</td>
- <td>row5 "Col2"</td>
- <td>row5 "Col3"</td>
- </tr>
- <tr>
- <td>row6 "Col1"</td>
- <td>row6 "Col2"</td>
- <td>row6 "Col3"</td>
- </tr>
- </table>
- </div>
Add the following code to the Controller:
- function ($scope) {
- // define a function to be used in template with ctrl.toggleBackgroundColor()
- this.exportTableToCSV02 = function() {
- function exportTableToCSV029($table, filename) {
- var $rows = $table.find('tr:has(td)'),
- // Temporary delimiter characters unlikely to be typed by keyboard
- // This is to avoid accidentally splitting the actual contents
- tmpColDelim = String.fromCharCode(11), // vertical tab character
- tmpRowDelim = String.fromCharCode(0), // null character
- // actual delimiter characters for CSV format
- colDelim = '","',
- rowDelim = '"\r\n"',
- csv = '"' + $rows.map(function(i, row) {
- var $row = $(row),
- $cols = $row.find('td');
- return $cols.map(function(j, col) {
- var $col = $(col),
- text = $col.text();
- return text.replace(/"/g, '""'); // escape double quotes
- }).get().join(tmpColDelim);
- }).get().join(tmpRowDelim)
- .split(tmpRowDelim).join(rowDelim)
- .split(tmpColDelim).join(colDelim) + '"';
- // Deliberate 'false', see comment below
- if (false && window.navigator.msSaveBlob) {
- var blob = new Blob([decodeURIComponent(csv)], {
- type: 'text/csv;charset=utf8'
- });
- // Crashes in IE 10, IE 11 and Microsoft Edge
- // See MS Edge Issue #10396033
- // Hence, the deliberate 'false'
- // This is here just for completeness
- // Remove the 'false' at your own risk
- window.navigator.msSaveBlob(blob, filename);
- } else if (window.Blob && window.URL) {
- // HTML5 Blob
- var blob = new Blob([csv], {
- type: 'text/csv;charset=utf-8'
- });
- var csvUrl = URL.createObjectURL(blob);
- //this trick will generate a temp <a /> tag
- var link = document.createElement("a");
- link.href = csvUrl;
- //set the visibility hidden so it will not effect on your web-layout
- link.style = "visibility:hidden";
- link.download = filename; //fileName + ".csv";
- //this part will append the anchor tag and remove it after automatic click
- document.body.appendChild(link);
- link.click();
- document.body.removeChild(link);
- } else {
- // Data URI
- var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
- //this trick will generate a temp <a /> tag
- var link = document.createElement("a");
- link.href = csvData;
- //set the visibility hidden so it will not effect on your web-layout
- link.style = "visibility:hidden";
- link.download = filename; //fileName + ".csv";
- //this part will append the anchor tag and remove it after automatic click
- document.body.appendChild(link);
- link.click();
- document.body.removeChild(link);
- }
- }
- //use a default name to export - change as necessary
- //if the table name in the div id= changes - you MUST change the name here also
- //default is dvData
- var args = [$('#dvData>table'), 'export.csv'];
- exportTableToCSV029.apply(this, args);
- };
- }
Save and add the Widget to a page,
Save and Preview...
Voila - a working solution.
regards
Seán
PS: As this reply offers an answer your question, and if you like it, please Mark UP and/or as Resolved.
Wonderfull!!! thes best code is which one you wrote on Submitted by Sean McP on Thu, 02/16/2017 - 15:54
Thanks a lot
Comments
Hello
One drawback is the table headers dont come in the excel.
Were you able to get the table header.
Also if you have a repeatable container then this will not work. This is applicable for table only.
Hi Dibyajit and Loreley2018,
No the header isn't part of the deal. I didn't consider it at the time and haven't investigated it...I might have a look but can't promise anything.
Regarding XLS or XLSX this is not possible unless you purchase other plugins etc. CSV is a fully recognised method of downloading any data to Excel. There will be no provisioning to Excel format direct.
Regards
Seán
Hello Sean.
First of all, I would like to thank you for all your Comments, Posts and answers in the bonita Community.
A lot of things make sense thanks to you.. the above code works for me and has helped me several times. Its a Working model.
If you do come up with a solution to add the header in the above example, it would be great ... but its no problem as of now..
Let me know if you can guide me for a similar scenario but instead of a table widget, i have a repeatable custom container with text fields to display same as a table..
Everything is same as above.. except the API response variable is set as value to the container.
Again many many thanks for your valuable feedback.
Regards
Hi Dibyajit.Roy!
Try using this component:
http://ui-grid.info/
To do so, create a custom widget, in the html field, put:<div ui-grid="gridOptions" ui-grid-selection ui-grid-exporter class="grid"></div>
In the Javascript field, inside the function declaration, put:
$scope.gridOptions = {
columnDefs: [
// put here the fields that yout data table has
{ field: 'name' },
{ field: 'gender', visible: false},
{ field: 'company' }
],
enableGridMenu: true,
enableSelectAll: true,
//define here the name of your exported file
exporterCsvFilename: 'myFile.csv'
exporterCsvLinkElement: angular.element(document.querySelectorAll(".custom-csv-link-location")),
onRegisterApi: function(gridApi){
$scope.gridApi = gridApi;
}
In Required angular modules field, put:ui.grid, ui.grid.selection, ui.grid.exporter
In assets field, import this two:
https://cdn.rawgit.com/angular-ui/bower-ui-grid/master/ui-grid.min.js
https://cdn.rawgit.com/angular-ui/bower-ui-grid/master/ui-grid.min.css
At least, I recommend you to read more at http://ui-grid.info/ about this component and how to use it more efficiently.
Cheers,
Marcos Vinícius Pinto
Comments
Thanks Sean.
I tried and its works.
Thanks a lot.
Hi Sean
Thanks again for the Above example.
I was wondering , in my Page I am returning JSON data from an API Call.
How will I bind the Data from the API to the HTML table Directly.
Thanks
Open the page in UI Designer, click PREVIEW
The following instructions are for Chrome, if using a different Browser you'll need to modify them for your environment.
Select the top line of the table you want to export
Right Click and select INSPECT
Look for the PRECEDING DIV for the Table, in this example I have:
It is the DIV class name that you need, in this case: table-responsive
All you have to do is change this line in the code:
to
If you are using a non-Bonitasoft provided table then i can't help as I don't know the format of the table, but this will give you the idea.
regards
Seán
Hi Sean
Thanks for the Response.
I inspected the Element. The Table Div is table-responsive same as you have.
On Modifying the Above line , The Excel sheet is Coming empty.
Thanks
Are you sure you've used exactly the same line . Instead of #
Otherwise can you export and share the page so I can have a look?
Hi Sean
Here the link for the Page.
https://drive.google.com/file/d/0B-MHHSlP6-kMRWtwSlZ3SEZMWk0/view?usp=sh...
Thanks
Thanks,
I've just imported the page, clicked on Preview and then Clicked on the Button. The export.csv file is created and there is Data in it.
I opened it in Notepad++ and Excel and both show data from the table. However in this data there are extra invalid characters that look like new lines. I will investigate and get back to you in the morning.
regards
Yes, I saw that.
Apparently, the Cells have to be dragged and expanded for the Data to appear.
Initially I opened with Notepad++, It was empty.
Then I expanded the Cells and then reopened with Notepad++, I got the data.
Its a bit strange behavior.
I've updated the code to remove all unnecessary characters, the export now comes out clean...and as expected. Glad to be of service.
regards
Seán
Thanks a lot Sean.
This code works like a charm.
Thanks for the Help. I appreciate it.