Angular2 - Export table to html excel and give filename

Question

I have an Angular2 application and now it was asked me to implement a basic table to excel export.

The function that I'm using works but I don't know how to set the filename of the generated excel.

This is the function:

tableToExcel(e,table, name) {
  console.log(e);

  let 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(decodeURI(decodeURIComponent(s))) 
     },
format = function (s, c) {
     return s.replace(/{(\w+)}/g, 
        function (m, p) { return c[p]; 
      }) 
     }
if (!table.nodeType) 
     table = document.getElementById(table)
var ctx = { 
      worksheet: name || 'Worksheet', table: table.innerHTML 
}
//window.location.href = uri + base64(format(template, ctx))  
console.log(uri + base64(format(template, ctx)));
window.open(uri + base64(format(template, ctx)), '_blank', 'top=0,left=0,height=100%,width=auto');

  return false;
 }

and this is the button:

<a type="button" href="#" (click)="tableToExcel($event,'testTable', 'W3C Example Table')" download="Schede.xlsx" class="btn btn-warning"> Excel </a>

In this moment I can download the file excel but the filename is completely random.

Thanks to support

https://stackoverflow.com/questions/283956/is-there-any-way-to-specify-a-suggested-filename-when-using-data-uri You may have to give uri as href and filename as value for `download` param — Vamshi, Jun 15 '17 at 12:59
In my scenario the suggestions on your link are not applicables... thanks anyway — DarioN1, Jun 15 '17 at 15:05

score 1 · Answer 1 · answered Aug 02 '17 at 09:11

You can create fake a tag with your uri as href and filename as download attribute and then call click on it, this should work:

function tableToExcel(e,table, filename) {
  console.log(e);
  // your variables here
  let link = document.createElement('a');
  link.setAttribute('href', uri + base64(format(template, ctx)));
  link.setAttribute('download', filename);
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
  return false;
}

score 1 · Accepted Answer · answered Feb 02 '18 at 21:23

Try this code. You can give your desired file name in place of Test file.xls

tableToExcel(table, name) {
template = 
'<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"e
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>
(<HTMLScriptElementdocument.getElementById(table)).innerHTML</table>
</body>
</html>',

if (window.navigator.msSaveBlob) {
var blob = new Blob([templete], {type: "application/csv;charset=utf-8;"});
navigator.msSaveBlob(blob, 'Test file.xls');
}
}

I don't prefer this solution since the msSaveBlob is not supported by Chrome, it's for IE only — Ahmed El-Araby, Jan 02 '19 at 09:13

score 0 · Answer 3 · answered May 30 '18 at 09:45

You can use the JQuery plugin "table2excel".

1- Add those files to your js directory in your assets

=> jquery.table2excel.js

=> table2csv.js

=> jquery.table2excel.min.js

you can find them here => https://github.com/rainabba/jquery-table2excel

2- Add an id to your table in your HTML file.

3- Create your function in your component.ts to export your table :

exportToExcel(){

  $("#YourTableId").table2excel({
    exclude: ".noExl",
    name: "Worksheet Name",
    filename: "SomeFile", //do not include extension
    fileext: ".xls",
    exclude_img: true,
    exclude_links: true,
    exclude_inputs: true
  }); }

Angular2 - Export table to html excel and give filename

3 Answers3