HTML Table to Excel Javascript

Question

I'm trying to use this script to save a html table to an Excel file, and it works fine, however it doesn't come up in the proper name, but rather with a random string. And I can't see why .

I call it with:

<input type="button" onclick="tableToExcel('tablename', 'name')" value="Export to Excel">

code

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

You are passing parameters 'name' and 'tablename' to `tableToExcel` but the function doesn't declare any parameters — Yuriy Galanter, Jun 15 '13 at 18:35
@Yuriy Galanter - the variable `tableToExcel` is set to the return value of an unnamed function which gets executed immediately as the page loads. The return value is a reference to the anonymous function `function(table,name)`, so when you call `tableToExcel` it's really this function which gets executed. — Tim Williams, Jun 16 '13 at 00:20
@TimWilliams yup figured it out already, now figuring answer to the question — Yuriy Galanter, Jun 16 '13 at 00:27

score 65 · Answer 1 · edited Apr 09 '19 at 18:52

65

You can use download attribute supported by modern browsera for a anchor element. First modify your HTML by adding an invisible anchor:

<a id="dlink"  style="display:none;"></a>

<input type="button" onclick="tableToExcel('tablename', 'name', 'myfile.xls')" value="Export to Excel">

Notice also that the call to function tableToExcel now has 3rd parameter - where you specify file name.

Now use this modified code of your original function:

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, filename) {
            if (!table.nodeType) table = document.getElementById(table)
            var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }

            document.getElementById("dlink").href = uri + base64(format(template, ctx));
            document.getElementById("dlink").download = filename;
            document.getElementById("dlink").click();

        }
    })()

Notice last 3 code lines: Instead of assigning URL to window - they assign it to the new anchor, then use new download attribute to force download as the given file name and then simple call click() method of the anchor.

Give it a try.

Update - For supporting utf-8 characters

As per the comment below by @WorldSEnder, a simple meta tag in the template would make the excel support utf-8 characters like Hindi.

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"><meta charset="utf-8"/><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>'

edited Apr 09 '19 at 18:52

Md. Farhan Memon

6,055
2
11
36

answered Jun 16 '13 at 00:40

Yuriy Galanter

38,833
15
69
136

2

Awesome! The name works, but now I'm getting this error: `The file you are trying to open, 'name.xls', is a different format than specified in the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.` – Coolcrab Jun 16 '13 at 09:46
2

This is normal, you should've gotten this before as well. This is happening because the file is not a real Excel, but rather XML in disguise. But after that warning the file should be opening OK anyway. You can play with file extension to try to get rid of the warning (e.g. change it to .XML etc.) – Yuriy Galanter Jun 16 '13 at 12:02
1

Hi, I know this is an older post but I get an error saying Object doesn't support property or method 'btoa'. – seroth Feb 27 '14 at 15:41
@seroth It may not be supported in older browsers (IE9 and below). Try to include this file to add support: https://code.google.com/p/stringencoders/source/browse/trunk/javascript/base64.js?r=230 – Yuriy Galanter Feb 27 '14 at 18:43
1

Check my answer here http://stackoverflow.com/questions/22317951/export-html-table-data-to-excel-using-javascript-jquery-is-not-working-properl/24081343#24081343 – sampopes Apr 29 '15 at 10:56
1

@Yuriy...Similar like this I have three tables and I want those tables to be the worksheets in the same excel file....I have tried this [http://stackoverflow.com/a/29717451/6547301](http://stackoverflow.com/a/29717451/6547301) but table styling is not retained, I want to retain the styles of the table in excel sheet – Venkat Sep 17 '16 at 11:57
1

You can simply add `` to the template where you'd expect it if you want to export utf data. – WorldSEnder Mar 28 '17 at 12:33
@YuriyGalanter - Not working in IE10+. On clicking download link, user is redirected to `data:application/vnd.ms-excel;base64,PGh0bWwgeG1sbnM6bz0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTpvZmZpY2U6b2Zm..............................` and results to **404 Page Not Found** error! – Aakash Goplani Nov 07 '17 at 12:33
i can save my excel file but it loses all numbers' decimal places like 5,1234 -> 51234 or even -> 51.234. I've tried to save as .xlsx but this time file does not open but i can preview it on macOS with the correct decimals are in place – ggnoredo Jul 28 '18 at 10:11
fantastic ...great one – Sarat May 10 '20 at 08:50
is it possible to text align center in excel programatically... – Sarat May 10 '20 at 08:52

sokphea chea · Answer 2 · 2017-12-05T10:39:52.403

The 3 code lines above doesn't work in my case, but here what I found and I hope it could help.

function tableToExcel(table, name, filename) {
        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"><title></title><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(decodeURIComponent(encodeURIComponent(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}

        var link = document.createElement('a');
        link.download = filename;
        link.href = uri + base64(format(template, ctx));
        link.click();
}

<!DOCTYPE html>
<html>
<head>
<style>
table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 100%;
}

td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}

tr:nth-child(even) {
    background-color: #dddddd;
}
</style>
</head>
<body>
<input 
  type="button" 
  onclick="tableToExcel('myTable', 'name', 'myfile.xls')" 
  value="Export to Excel"
>
<table id="myTable">
  <tr>
    <th>Company</th>
    <th>Contact</th>
    <th>Country</th>
  </tr>
  <tr>
    <td>Alfreds Futterkiste</td>
    <td>Maria Anders</td>
    <td>Germany</td>
  </tr>
  <tr>
    <td>Centro comercial Moctezuma</td>
    <td>Francisco Chang</td>
    <td>Mexico</td>
  </tr>
  <tr>
    <td>Ernst Handel</td>
    <td>Roland Mendel</td>
    <td>Austria</td>
  </tr>
  <tr>
    <td>Island Trading</td>
    <td>Helen Bennett</td>
    <td>UK</td>
  </tr>
  <tr>
    <td>Laughing Bacchus Winecellars</td>
    <td>Yoshi Tannamuri</td>
    <td>Canada</td>
  </tr>
  <tr>
    <td>Magazzini Alimentari Riuniti</td>
    <td>Giovanni Rovelli</td>
    <td>Italy</td>
  </tr>
</table>

</body>
</html>

Sometimes, we need to encode UTF-8 for `window.btoa` to work. See [Failed to execute 'btoa' on 'Window': The string to be encoded contains characters outside of the Latin1 range.](https://stackoverflow.com/q/23223718/1287812) — brasofilo, Feb 11 '18 at 15:17
Big Table cannot be Exported..! Please Help (More than 20000 records) — Chathura Liyanage, Nov 01 '19 at 06:34

score 0 · Answer 3 · answered Jul 11 '23 at 03:57

Try this simple method.

Keep this button wherever you want to display the Export Button

<button id="btnExport" onclick="fnExcelReport('myExcelFile.xlsx');" class="btn btn-sm btn-primary"> Export </button>

Add these lines to the bottom of the page

<script src="https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js"></script>
<script>
         function fnExcelReport(fileName) {
                    let table = document.getElementsByTagName("table");
                    return TableToExcel.convert(table[0], {
                        name: fileName,
                        sheet: {
                            name: 'Sheet 1'
                        }
                    });
                }
</script>

HTML Table to Excel Javascript

3 Answers3

Linked

Related