html to excel export with .xls extension in javascript or php

Question

I need export html table to excel with .xls extension in javascript or php

I am using below code but it does not export to file with .xls extension If possible in php code embedded in javascript code then its fine.

Link to fiddle.

var tableToExcel = (function() {
 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]-->
 </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];}) }
 return function(table, name) {
  if (!table.nodeType) table = document.getElementById(table)
   var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
   window.location.href = uri + base64(format(template, ctx)) 
 }
});

@Matrix123 - sorry. I meant, could you add the code into your question, not the link. — andrewsi
– andrewsi, Commented May 7, 2013 at 17:48
little known trick: if you simply save the html table markup in a file with an "xls" extension, it will open in excel and work as expected. both php and js can output HTML easily, so you're in luck. — dandavis
– dandavis, Commented May 7, 2013 at 17:50

dandavis · Accepted Answer · 2013-05-07 18:28:00Z

1

using a download lib from http://danml.com/js/download.js, it's easy.

function download(strData, strFileName, strMimeType) {
    var D = document,
        A = arguments,
        a = D.createElement("a"),
        d = A[0],
        n = A[1],
        t = A[2] || "text/plain";

    //build download link:
    a.href = "data:" + strMimeType + "," + escape(strData);


    if (window.MSBlobBuilder) {
        var bb = new MSBlobBuilder();
        bb.append(strData);
        return navigator.msSaveBlob(bb, strFileName);
    } /* end if(window.MSBlobBuilder) */



    if ('download' in a) {
        a.setAttribute("download", n);
        a.innerHTML = "downloading...";
        D.body.appendChild(a);
        setTimeout(function() {
            var e = D.createEvent("MouseEvents");
            e.initMouseEvent("click", true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
            a.dispatchEvent(e);
            D.body.removeChild(a);
        }, 66);
        return true;
    } /* end if('download' in a) */
    ; //end if a[download]?

    //do iframe dataURL download:
    var f = D.createElement("iframe");
    D.body.appendChild(f);
    f.src = "data:" + (A[2] ? A[2] : "application/octet-stream") + (window.btoa ? ";base64" : "") + "," + (window.btoa ? window.btoa : escape)(strData);
    setTimeout(function() {
        D.body.removeChild(f);
    }, 333);
    return true;
} /* end download library function () */

// code that implements OP's functionality:

 function tableToExcel (table) {
   if (!table.nodeType) table = document.getElementById(table);
   download(table.outerHTML, "table.xls", "application/vnd.ms-excel");
 }

works in IE10, FF, and Chrome, maybe others as well.

answered May 7, 2013 at 18:28

dandavis

16.8k5 gold badges43 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2789934 Over a year ago

thanks it works perfect for my example, but i can't set utf-8. How do i do this?

dandavis Over a year ago

i belive it should use whatever encoding is used on the page. you could change to mimetype to a text-based one that includes encoding, as long as the filename keeps i think excel will open it.

Elavarasan Muthuvalavan - Lee · Accepted Answer · 2013-05-08 06:21:28Z

In you Current File:

<?php
  //Your Table code.
  <a href="http://your_site_url.com/export_excel.php" target="_blank">
   <input id="export-btn" type="button" value="Export as Excel" onclick="export()"/>
  </a>
?>

Inside export_excel.php

<?php
  $filename = 'Youe_Filename_without_extension';
  header('Content-type: application/vnd.xls');
  header('Content-Disposition: attachment; filename="'.$filename.'.xls"');
  //Your Table code.
?>

Example Url: Demo

Update:
If you want it in the same file [to avoid duplication of the same code], then following code'll help:

<?php
$same_page = $_POST['same-page'];
if(!empty($same_page) && $same_page == 1) {
  $filename = 'Sample Table';
  header('Content-type: application/vnd.xls');
  header('Content-Disposition: attachment; filename="'.$filename.'.xls"');
}?>
  //Your Table code.
<?php if(empty($same_page)): ?>
  //write whatever you want to hide in excel like export button,heading etc.
 <form method="POST" action="" target="_balnk">
   <input type="hidden" name="same-page" value="1"/>
   <input id="export-btn" type="submit" value="Export as Excel on Form Submit"/>
 </form>
<?php endif; ?>

Collectives™ on Stack Overflow

html to excel export with .xls extension in javascript or php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related