Last active
February 8, 2024 15:14
-
-
Save insin/1031969 to your computer and use it in GitHub Desktop.
Export a <table> to Excel - http://bl.ocks.org/insin/1031969
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>tableToExcel Demo</title> | |
| <script src="tableToExcel.js"></script> | |
| </head> | |
| <body> | |
| <h1>tableToExcel Demo</h1> | |
| <p>Exporting the W3C Example Table</p> | |
| <input type="button" onclick="tableToExcel('testTable', 'W3C Example Table')" value="Export to Excel"> | |
| <table id="testTable" summary="Code page support in different versions of MS Windows." rules="groups" frame="hsides" border="2"><caption>CODE-PAGE SUPPORT IN MICROSOFT WINDOWS</caption><colgroup align="center"></colgroup><colgroup align="left"></colgroup><colgroup span="2" align="center"></colgroup><colgroup span="3" align="center"></colgroup><thead valign="top"><tr><th>Code-Page<br>ID</th><th>Name</th><th>ACP</th><th>OEMCP</th><th>Windows<br>NT 3.1</th><th>Windows<br>NT 3.51</th><th>Windows<br>95</th></tr></thead><tbody><tr><td>1200</td><td style="background-color: #00f; color: #fff">Unicode (BMP of ISO/IEC-10646)</td><td></td><td></td><td>X</td><td>X</td><td>*</td></tr><tr><td>1250</td><td style="font-weight: bold">Windows 3.1 Eastern European</td><td>X</td><td></td><td>X</td><td>X</td><td>X</td></tr><tr><td>1251</td><td>Windows 3.1 Cyrillic</td><td>X</td><td></td><td>X</td><td>X</td><td>X</td></tr><tr><td>1252</td><td>Windows 3.1 US (ANSI)</td><td>X</td><td></td><td>X</td><td>X</td><td>X</td></tr><tr><td>1253</td><td>Windows 3.1 Greek</td><td>X</td><td></td><td>X</td><td>X</td><td>X</td></tr><tr><td>1254</td><td>Windows 3.1 Turkish</td><td>X</td><td></td><td>X</td><td>X</td><td>X</td></tr><tr><td>1255</td><td>Hebrew</td><td>X</td><td></td><td></td><td></td><td>X</td></tr><tr><td>1256</td><td>Arabic</td><td>X</td><td></td><td></td><td></td><td>X</td></tr><tr><td>1257</td><td>Baltic</td><td>X</td><td></td><td></td><td></td><td>X</td></tr><tr><td>1361</td><td>Korean (Johab)</td><td>X</td><td></td><td></td><td>**</td><td>X</td></tr></tbody><tbody><tr><td>437</td><td>MS-DOS United States</td><td></td><td>X</td><td>X</td><td>X</td><td>X</td></tr><tr><td>708</td><td>Arabic (ASMO 708)</td><td></td><td>X</td><td></td><td></td><td>X</td></tr><tr><td>709</td><td>Arabic (ASMO 449+, BCON V4)</td><td></td><td>X</td><td></td><td></td><td>X</td></tr><tr><td>710</td><td>Arabic (Transparent Arabic)</td><td></td><td>X</td><td></td><td></td><td>X</td></tr><tr><td>720</td><td>Arabic (Transparent ASMO)</td><td></td><td>X</td><td></td><td></td><td>X</td></tr></tbody></table> | |
| </body> | |
| </html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)) | |
| } | |
| })() |
This works well as long as there's not a lot of data. I'm trying to export a table with a couple thousand rows and it causes the browser to bomb and display the dreaded about:blank page. Bummer, now I have to find another way to export my table.
I want to export the excel from right to left
How can i do it?
I add dir="rtl" it didn't help
I found the result how to export the excel from right to left:
add this-<x:DisplayRightToLeft/> in the <x:WorksheetOptions> tag like this
When i want to save the excel i get this message:
Someone can help me to solve it?
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '
, 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))
}
})()
Above function works fine for me. I have hyperlinks in my table. How do I remove those hyperlinks ?
Above function works fine for me. table include input tags how to hide the input tags
How do I export to OpenOffice?
Could someone change the name when doing the download?
how to save file with my given name , how to change name while save
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '
| '+sessionStorage.getItem("sellerInfo")+' | ||||||
|---|---|---|---|---|---|---|
| GSTIN : '+sessionStorage.getItem("GSTNumber")+' | State Code : '+sessionStorage.getItem("stateCodeNumber")+' | |||||
| Invoice No | Invoice Date | Buyer Name | Buyer GST | Buyer State | Invoice Amount | Tax |
, 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: "test" || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))
/// please change name while save
}
})()
I export on ubuntu It do not know extension
It works great on Excel in Windows, but when exporting it in Mac Excel, the gridlines won't show up
integer value shows as scientific number, how to slove that?
For encoding problems, add the following in the template string:
<meta charset="utf-8"></meta>Like this:
let uri = 'data:application/vnd.ms-excel;charset=utf-8;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><meta charset="utf-8"></meta><!--[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]; }) }
Can anyone tell me how can i give the default name to the files when i download?
For encoding problems, add the following in the template string:
<meta charset="utf-8"></meta>Like this:
let uri = 'data:application/vnd.ms-excel;charset=utf-8;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><meta charset="utf-8"></meta><!--[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]; }) }
Thank you
to change the default value of the download file :
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,';
var 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>';
var base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))); };
var format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }); };
return function(table, filename, name) {
if (!table.nodeType) table = document.getElementById(table);
if (!table) {
console.error("Table element with provided ID not found.");
return;
}
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};
var downloadLink = document.createElement("a");
downloadLink.href = uri + base64(format(template, ctx));
downloadLink.download = filename || 'export.xls';
downloadLink.click();
};
})();
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Someone knows how to save the file with a default name?