[Pivot Table] Excel Export

[Pivot Table] Excel Export

  
Forge Component
(6)
Published on 2017-10-05 by Gonçalo Borrêga
6 votes
Published on 2017-10-05 by Gonçalo Borrêga

Hi Dears,

Is it hard export the Pivot Table data to an Excel sheet ? How can I do this ?

Best Regards,

Roberto


Hi Roberto,

The base component doesn't provide any capability to export the result to Excel. But the following might be a possible solution:

1. Add the following javascript to your Screen's (or module) Javascript:

 
         

var uri = 'data:application/vnd.ms-excel;base64,'
  , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>'
  , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
  , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) };

function saveAs(uri, filename) {
  var link = document.createElement('a');
  if (typeof link.download === 'string') {
    link.href = uri;
    link.download = filename;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
  } else {
    window.open(uri);
  }
}

function exportPivotToExcel(pivotBlockId, name) {
    var tbl = $('#' + pivotBlockId).find('.pvtTable');
    var ctx = {worksheet: name || 'Worksheet', table: tbl.html()};
    var uriFile = uri + base64(format(template, ctx));
    saveAs(uriFile, name+'.xls');
}

The exportPivotToExcel function will basically pick the pivot table inner html, format it as an Excel XML/HTML format and convert it to Base64 for the browser to download. The saveAs is just a workaround to suggest/force a given filename for the download.


2. Add a button to your screen that calls that function, giving it the pivot component Id. Note that you need to call the client side javascript function. One way to do it is to have a Link that Navigates to an ExternalURL and give it the javascript code to execute:

javascript:exportPivotToExcel('" + pivot.Id + "', 'Sales data');


Added one example you can check at http://borrega.outsystemscloud.com/PivotSample/Pivot_Sales_Table.aspx

It won't format all the numbers but it is probably a good start...

Hope that helps

GB

Hi Gonçalo,

Great!!!!!! It works fine! Thank you very much for your help and attention. This export is very helpful to users that always need paste the data in excel to match it with other informations. The format really will be need to component, that works now with american format. But for now, we will work with this format and in future to waste an effort to modify it.

Thank you,

Roberto

Hi Gonçalo,

Sorry to bother you again. The file is exported in the old Excel format xls. Is it easy export in the xlsx format?

Thank you,

Roberto