How to Use custom HTML and JavaScript To Export Data to Excel

1
0
-1

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

13 answers

1
+4
-1
This one is the BEST answer!

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.

Comments

Submitted by Dibyajit.Roy on Mon, 02/13/2017 - 10:37

Thanks Sean.

I tried and its works.

Thanks a lot.

Submitted by Dibyajit.Roy on Mon, 02/13/2017 - 13:49

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

Submitted by Sean McP on Mon, 02/13/2017 - 22:37

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:

<div class="table-responsive">
    <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:

var args = [$('#dvData>table'), 'export.csv'];

to

var args = [$('.table-responsive>table'), 'export.csv'];

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

Submitted by Dibyajit.Roy on Tue, 02/14/2017 - 12:45

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

Submitted by Sean McP on Tue, 02/14/2017 - 20:03

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?

Submitted by Dibyajit.Roy on Wed, 02/15/2017 - 08:34
Submitted by Sean McP on Wed, 02/15/2017 - 10:13

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

Submitted by Dibyajit.Roy on Wed, 02/15/2017 - 10:57

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.

Submitted by Sean McP on Thu, 02/16/2017 - 19:54

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

Submitted by Dibyajit.Roy on Mon, 02/20/2017 - 07:27

Thanks a lot Sean.

This code works like a charm.

Thanks for the Help. I appreciate it.

1
0
-1

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.

1
0
-1

Thanks a lot. It is working.

To export data in Excel and CSV in Laravel, use the maatwebsite package.

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

Comments

Submitted by Dibyajit.Roy on Sat, 05/02/2020 - 20:22

Hello
I am not sure if you can generate PDF from UI Designer.
You can use Apache POI Jar files to create and display PDF files.
There are many examples for Apache POI. Check the Community.

Regards

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

1
0
-1

Hi every one ,what about generate a PDF from form in ui designer with button?

thank you

1
0
-1

hi

Notifications