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: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.
<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:
// 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.
Ouch, a lot of answers!
You can use the AlaSql widget, it will do all you ask for. It does as well the EXPORT and the IMPORT
https://community.bonitasoft.com/project/widgetalasqloperation
PS: I think this library does the PDF as well.
Thanks a lot. It is working.
To export data in Excel and CSV in Laravel, use the maatwebsite package.
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:
<table class="table table-striped" ng-class="{'table-hover': ctrl.isClickable()}">
<thead>
<tr>
<!-- ngRepeat: header in properties.headers --><th ng-repeat="header in properties.headers" class="ng-binding ng-scope">
Departure date
</th>
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.
* The controller is a JavaScript function that augments the AngularJS scope and exposes functions that can be used in the custom widget template
*
* Custom widget properties defined on the right can be used as variables in a controller with $scope.properties
* To use AngularJS standard services, you must declare them in the main function arguments.
*
* You can leave the controller empty if you do not need it.
*/
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().replace(/(\r\n|\n|\r)/gm,"").trim();
//debug code to print the strings and find out what's in there
//for(var x = 0; x< text.length; x++) {
// console.log("Character " + x + " is " + text.charCodeAt(x) + "<br />");
//}
// escape double quotes and remove unwanted newlines
return text.replace(/"/g, '""'); // .replace(/(\r\n|\n|\r)/gm,"")
}).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'];
var args = [$('.table-responsive>table'), 'export.csv'];
exportTableToCSV029.apply(this, args);
};
}
regards
Seán
Thanks a lot Sean.
This code works like a charm.
Thanks for the Help. I appreciate it.