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

Submitted by Dibyajit.Roy on Mon, 02/06/2017 - 07:35
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

#javascript
export to excel
html
custom widgets
4 answers

Submitted by Sean McP on Sat, 02/11/2017 - 00:17
1
+3
-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:

  1. a.export,
  2. a.export:visited {
  3.   display: inline-block;
  4.   text-decoration: none;
  5.   color: #000;
  6.   background-color: #ddd;
  7.   border: 1px solid #ccc;
  8.   padding: 8px;
  9. }


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.




  1. <a id="exportToCSV" href="#" class="export" ng-click="ctrl.exportTableToCSV02()">Export Table data into Excel</a>
  2. <div id="dvData">
  3.   <table>
  4.     <tr>
  5.       <th>Column One</th>
  6.       <th>Column Two</th>
  7.       <th>Column Three</th>
  8.     </tr>
  9.     <tr>
  10.       <td>row1 Col1</td>
  11.       <td>row1 Col2</td>
  12.       <td>row1 Col3</td>
  13.     </tr>
  14.     <tr>
  15.       <td>row2 Col1</td>
  16.       <td>row2 Col2</td>
  17.       <td>row2 Col3</td>
  18.     </tr>
  19.     <tr>
  20.       <td>row3 Col1</td>
  21.       <td>row3 Col2</td>
  22.       <td>row3 Col3</td>
  23.     </tr>
  24.     <tr>
  25.       <td>row4 'Col1'</td>
  26.       <td>row4 'Col2'</td>
  27.       <td>row4 'Col3'</td>
  28.     </tr>
  29.     <tr>
  30.       <td>row5 "Col1"</td>
  31.       <td>row5 "Col2"</td>
  32.       <td>row5 "Col3"</td>
  33.     </tr>
  34.     <tr>
  35.       <td>row6 "Col1"</td>
  36.       <td>row6 "Col2"</td>
  37.       <td>row6 "Col3"</td>
  38.     </tr>
  39.   </table>
  40. </div>


Add the following code to the Controller:




  1. function ($scope) {
  2.  
  3.     // define a function to be used in template with ctrl.toggleBackgroundColor()
  4.     this.exportTableToCSV02 = function() {
  5.  
  6.         function exportTableToCSV029($table, filename) {
  7.  
  8.             var $rows = $table.find('tr:has(td)'),
  9.  
  10.             // Temporary delimiter characters unlikely to be typed by keyboard
  11.             // This is to avoid accidentally splitting the actual contents
  12.             tmpColDelim = String.fromCharCode(11), // vertical tab character
  13.             tmpRowDelim = String.fromCharCode(0), // null character
  14.  
  15.             // actual delimiter characters for CSV format
  16.             colDelim = '","',
  17.             rowDelim = '"\r\n"',
  18.  
  19.             csv = '"' + $rows.map(function(i, row) {
  20.                 var $row = $(row),
  21.                 $cols = $row.find('td');
  22.  
  23.             return $cols.map(function(j, col) {
  24.                 var $col = $(col),
  25.                 text = $col.text();
  26.  
  27.             return text.replace(/"/g, '""'); // escape double quotes
  28.  
  29.             }).get().join(tmpColDelim);
  30.  
  31.             }).get().join(tmpRowDelim)
  32.               .split(tmpRowDelim).join(rowDelim)
  33.               .split(tmpColDelim).join(colDelim) + '"';
  34.  
  35.             // Deliberate 'false', see comment below
  36.             if (false && window.navigator.msSaveBlob) {
  37.  
  38.                 var blob = new Blob([decodeURIComponent(csv)], {
  39.                     type: 'text/csv;charset=utf8'
  40.                     });
  41.  
  42.                 // Crashes in IE 10, IE 11 and Microsoft Edge
  43.                 // See MS Edge Issue #10396033
  44.                 // Hence, the deliberate 'false'
  45.                 // This is here just for completeness
  46.                 // Remove the 'false' at your own risk
  47.                 window.navigator.msSaveBlob(blob, filename);
  48.  
  49.             } else if (window.Blob && window.URL) {
  50.  
  51.                 // HTML5 Blob        
  52.                 var blob = new Blob([csv], {
  53.                     type: 'text/csv;charset=utf-8'
  54.                     });
  55.                 var csvUrl = URL.createObjectURL(blob);
  56.  
  57.                 //this trick will generate a temp <a /> tag
  58.                 var link = document.createElement("a");    
  59.                 link.href = csvUrl;
  60.  
  61.                 //set the visibility hidden so it will not effect on your web-layout
  62.                 link.style = "visibility:hidden";
  63.                 link.download = filename; //fileName + ".csv";
  64.  
  65.                 //this part will append the anchor tag and remove it after automatic click
  66.                 document.body.appendChild(link);
  67.                 link.click();
  68.                 document.body.removeChild(link);
  69.  
  70.             } else {
  71.  
  72.                 // Data URI
  73.                 var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
  74.  
  75.                 //this trick will generate a temp <a /> tag
  76.                 var link = document.createElement("a");    
  77.                 link.href = csvData;
  78.  
  79.                 //set the visibility hidden so it will not effect on your web-layout
  80.                 link.style = "visibility:hidden";
  81.                 link.download = filename; //fileName + ".csv";
  82.  
  83.                 //this part will append the anchor tag and remove it after automatic click
  84.                 document.body.appendChild(link);
  85.                 link.click();
  86.                 document.body.removeChild(link);
  87.  
  88.             }
  89.  
  90.         }
  91.  
  92.         //use a default name to export - change as necessary
  93.         //if the table name in the div id= changes - you MUST change the name here also
  94.         //default is dvData
  95.         var args = [$('#dvData>table'), 'export.csv'];
  96.         exportTableToCSV029.apply(this, args);
  97.  
  98.     };
  99.  
  100. }


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.


		

			 Permalink
					

		

	





	

		

			
		

		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


		

			 Permalink
					

		

	





	

		

			
		

		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:




  1. <div class="table-responsive">
  2.     <table class="table table-striped" ng-class="{'table-hover': ctrl.isClickable()}">
  3.         <thead>
  4.             <tr>
  5.                 <!-- ngRepeat: header in properties.headers --><th ng-repeat="header in properties.headers" class="ng-binding ng-scope">
  6.                     Departure date
  7.                 </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:




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


to




  1. 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


		

			 Permalink
					

		

	





	

		

			
		

		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


		

			 Permalink
					

		

	





	

		

			
		

		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?


		

			 Permalink
					

		

	





	

		

			
		

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

	

		
Hi Sean


Here the link for the Page.
https://drive.google.com/file/d/0B-MHHSlP6-kMRWtwSlZ3SEZMWk0/view?usp=sh...


Thanks


		

			 Permalink
					

		

	





	

		

			
		

		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


		

			 Permalink
					

		

	





	

		

			
		

		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.


		

			 Permalink
					

		

	





	

		

			
		

		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.




  1. /**
  2.  * The controller is a JavaScript function that augments the AngularJS scope and exposes functions that can be used in the custom widget template
  3.  * 
  4.  * Custom widget properties defined on the right can be used as variables in a controller with $scope.properties
  5.  * To use AngularJS standard services, you must declare them in the main function arguments.
  6.  * 
  7.  * You can leave the controller empty if you do not need it.
  8.  */
  9. function ($scope) {
  10.  
  11. // define a function to be used in template with ctrl.toggleBackgroundColor()
  12. this.exportTableToCSV02 = function() {
  13.  
  14. function exportTableToCSV029($table, filename) {
  15.  
  16. var $rows = $table.find('tr:has(td)'),
  17.  
  18. // Temporary delimiter characters unlikely to be typed by keyboard
  19. // This is to avoid accidentally splitting the actual contents
  20. tmpColDelim = String.fromCharCode(11), // vertical tab character
  21. tmpRowDelim = String.fromCharCode(0), // null character
  22.  
  23. // actual delimiter characters for CSV format
  24. colDelim = '","',
  25. rowDelim = '"\r\n"',
  26.  
  27. csv = '"' + $rows.map(function(i, row) {
  28. var $row = $(row),
  29. $cols = $row.find('td');
  30.  
  31. return $cols.map(function(j, col) {
  32. var $col = $(col),
  33. text = $col.text().replace(/(\r\n|\n|\r)/gm,"").trim();
  34.  
  35. //debug code to print the strings and find out what's in there
  36. //for(var x = 0; x< text.length; x++) {
  37. //  console.log("Character " + x + " is " + text.charCodeAt(x) + "<br />");
  38. //}
  39.  
  40. // escape double quotes and remove unwanted newlines 
  41. return text.replace(/"/g, '""'); // .replace(/(\r\n|\n|\r)/gm,"") 
  42.  
  43. }).get().join(tmpColDelim);
  44.  
  45. }).get().join(tmpRowDelim)
  46. .split(tmpRowDelim).join(rowDelim)
  47. .split(tmpColDelim).join(colDelim) + '"';
  48.  
  49. // Deliberate 'false', see comment below
  50. if (false && window.navigator.msSaveBlob) {
  51.  
  52. var blob = new Blob([decodeURIComponent(csv)], {
  53. type: 'text/csv;charset=utf8'
  54. });
  55.  
  56. // Crashes in IE 10, IE 11 and Microsoft Edge
  57. // See MS Edge Issue #10396033
  58. // Hence, the deliberate 'false'
  59. // This is here just for completeness
  60. // Remove the 'false' at your own risk
  61. window.navigator.msSaveBlob(blob, filename);
  62.  
  63. } else if (window.Blob && window.URL) {
  64.  
  65. // HTML5 Blob
  66. var blob = new Blob([csv], {
  67. type: 'text/csv;charset=utf-8'
  68. });
  69. var csvUrl = URL.createObjectURL(blob);
  70.  
  71. //this trick will generate a temp <a /> tag
  72. var link = document.createElement("a");
  73. link.href = csvUrl;
  74.  
  75. //set the visibility hidden so it will not effect on your web-layout
  76. link.style = "visibility:hidden";
  77. link.download = filename; //fileName + ".csv";
  78.  
  79. //this part will append the anchor tag and remove it after automatic click
  80. document.body.appendChild(link);
  81. link.click();
  82. document.body.removeChild(link);
  83.  
  84. } else {
  85.  
  86. // Data URI
  87. var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
  88.  
  89. //this trick will generate a temp <a /> tag
  90. var link = document.createElement("a");
  91. link.href = csvData;
  92.  
  93. //set the visibility hidden so it will not effect on your web-layout
  94. link.style = "visibility:hidden";
  95. link.download = filename; //fileName + ".csv";
  96.  
  97. //this part will append the anchor tag and remove it after automatic click
  98. document.body.appendChild(link);
  99. link.click();
  100. document.body.removeChild(link);
  101.  
  102. }
  103.  
  104. }
  105.  
  106. //use a default name to export - change as necessary
  107. //if the table name in the div id= changes - you MUST change the name here also
  108. //default is dvData
  109. //var args = [$('#dvData>table'), 'export.csv'];
  110. var args = [$('.table-responsive>table'), 'export.csv'];
  111. exportTableToCSV029.apply(this, args);
  112.  
  113. };
  114.  
  115. }


regards

Seán


		

			 Permalink
					

		

	





	

		

			
		

		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.


		

			 Permalink
					

		

	



  

      

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by Loreley2018 on Sat, 02/02/2019 - 13:57    

    
    

      

            


1

0


-1

            

      

        

        
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

      
  


	

		

			
		

		Submitted by Dibyajit.Roy on Sat, 02/02/2019 - 20:12
	

	

		
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.


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by Loreley2018 on Sat, 02/02/2019 - 22:06
	

	

		
Hello Dibyajit.Roy

Yes you are right, I could not  the table header, but at least i get only the button because i edited widget template and i only use this line:
Export Table data into Excel¨

Don´t worry about the container, it was very usefull for me.

Is it possible to get an .xlsx o .xls format?


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by Sean McP on Sun, 02/03/2019 - 18:19
	

	

		
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


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by Dibyajit.Roy on Sun, 02/03/2019 - 19:36
	

	

		
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


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by Loreley2018 on Mon, 02/04/2019 - 00:42
	

	

		
Hello Sean McP !

I agree with Dibyajit, your code Its a Working model, but i need the export the headers.

Don´t worry about that, I will continue my research.

Thanks very much


		

			 Permalink
					

		

	



  

      

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by hramirez on Mon, 07/03/2017 - 22:15    

    
    

      

            


1

0


-1

            

      

        

        
Thank you to everyone.

Already I could solve my problem


Regards


Hugo Ramírez


        


                

                

        
              

    

  


      

          

  
      

          

  

  

  

    


  


              

        
      

        
        

    Submitted by mvpdev on Thu, 02/09/2017 - 16:28    

    
    

      

            


1

0


-1

            

      

        

        
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

      
  


	

		

			
		

		Submitted by Dibyajit.Roy on Fri, 02/10/2017 - 16:38
	

	

		
Hi Marcos


Thanks for the Detailed Help.


I followed the Example. When I add the custom widget, I dont see any preview.

I must have followed some step wrong.

Could you export the Custom widget and share it


Many Thanks

Dibyajit


		

			 Permalink
					

		

	





	

		

			
		

		Submitted by mvpdev on Fri, 02/10/2017 - 17:42
	

	

		
Hi,

Later I'll do this.


Cheers,

Marcos Vinícius Pinto


		

			 Permalink
					

		

	



  

      

    

  


      

          

  
      

          

  

  

    

  
  
  
  
  
  

    

  
      

          

  




  

      


                
      
    

  





    

    

    

        Notifications