Generate excel sheet from html tables using jquery

Question

I wanted to generate an excel sheet once a user clicks on a button . Basically i want to do exactly what is dicussed here

how to pass html table values to excel sheet cells (Kalle H. Väravas answer )

JavaScript to export HTML tables to Excel

But somehow nothing happens when i click on the button . I am using Mozilla browser . My code needs ActiveXObject enabled . DO i need to do something extra to get it done . ?

I created this fiddle for testing . If this works i will try out my real code . if this works for you then also please let me know . Thanks

jFiddle

Code :

JS :

<script type="text/javascript">
    function CreateExcelSheet() {
        var i, j, str,
                myTable = document.getElementById('mytable'),
                rowCount = myTable.rows.length,
                excel = new ActiveXObject('Excel.Application');// Activates Excel
        excel.Workbooks.Add(); // Opens a new Workbook
        excel.Application.Visible = true; // Shows Excel on the screen
        for (i = 0; i < rowCount; i++) {
            for (j = 0; j < myTable.rows[i].cells.length; j++) {
                str = myTable.rows[i].cells[j].innerText;
                excel.ActiveSheet.Cells(i + 1, j + 1).Value = str; // Writes to the sheet
            }
        }
        return;
    }
</script>

Html :

<table id ="myTable" >
            <tr>
                <td>1</td>
                <td>Jan</td>
                <td>01/04/2012</td>
                <td>Approved</td>
            </tr>
            <tr>
                <td>2</td>
                <td>Feb</td>
                <td>01/04/2012</td>
                <td>Approved</td>
            </tr>
        </table>
        <form>
            <input type="button" onclick="CreateExcelSheet();" value="Create Excel Sheet" >
        </form>

ActiveXObjects are Internet Explorer only, as far as I know. — Nick Andriopoulos, Mar 22 '13 at 09:44
You can create excel from the server side. What server side language are you using? — Sharun, Mar 22 '13 at 09:50
@hexblot i guess you are right . My friend just pointed that out to me . What do i do to get it working on all web browers ? — rockstar, Mar 22 '13 at 09:55
@slacker i am useing Java via JavaServer Pages (*.jsp). I am also using some JavaScript . — rockstar, Mar 22 '13 at 09:59
Did you check this? http://stackoverflow.com/questions/6955627/export-dynamic-html-table-to-excel-in-javascript-in-firefox-browser — Sharun, Mar 22 '13 at 10:03
@slacker thnaks so much . i tested out the fiddle . it sure works . i will try my code with it . I had previosuly tried searching for such a link but ended up with varied results ( proably because i used the term mozilla instead of firefox browser :( ) . Anyways thanks . Let me go over it ! — rockstar, Mar 22 '13 at 10:09
@slacker : Thanks i got my code up and running . Can you post the above an answer so that i can accept it ! — rockstar, Mar 22 '13 at 10:49

score 19 · Accepted Answer · edited May 23 '17 at 11:55

Here is the answer: Export dynamic html table to excel in javascript in firefox browser by insin

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))
      }
    })()

it is working fine in mozilla but does not work in IE. Any Ideas? — Alz, Feb 04 '15 at 05:09
it removes all decimal points/commas from all numbers like 5,1234 -> 51234 — ggnoredo, Jul 26 '18 at 09:20

score 14 · Answer 2 · answered Jul 14 '15 at 12:50

To answer the question about IE ( I could not answer in the comment box due to reputation restictions), the code above posted by Sharun won't work on IE 10+ ('permission denied' error). Here's a snippet that will work in IE 10+ along with older versions of IE, Chrome and FF:

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 cellspacing="0" rules="rows" border="1" style="color:Black;background-color:White;border-color:#CCCCCC;border-width:1px;border-style:None;width:100%;border-collapse:collapse;font-size:9pt;text-align:center;">{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 }
        if (navigator.msSaveBlob) {
            var blob = new Blob([format(template, ctx)], { type: 'application/vnd.ms-excel', endings: 'native' });
            navigator.msSaveBlob(blob, 'export.xls')
        } else {
            window.location.href = uri + base64(format(template, ctx))
        }
    }
})()

score 2 · Answer 3 · answered Nov 23 '17 at 05:52

      $("#btnExport").click(function(e) {
              e.preventDefault();

            //getting data from table
            var data_type = 'data:application/vnd.ms-excel';
            var table_div = document.getElementById('table_wrapper');//table_wrapper is table id
            var table_html = table_div.outerHTML.replace(/ /g, '%20');

            var a = document.createElement('a');
            a.href = data_type + ', ' + table_html;
            a.download = 'table_name_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
            a.click();
          });

score 0 · Answer 4 · answered Mar 15 '17 at 20:46

0

You can use this good library: https://github.com/jmaister/excellentexport

answered Mar 15 '17 at 20:46

artamonovdev

2,260
1
29
33

But it is showing a warning message begore opening the file. – rafidheen Jul 06 '17 at 06:03

Generate excel sheet from html tables using jquery

4 Answers4

Linked

Related