javascript 使用javascript将工作表添加到excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26301811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding worksheet to the excel file using javascript
提问by Sharathi RB
Just assume I have 3 tables. I want to insert tables in 3 work sheets (per page one table) into a single excel file (without need of ActiveXObject)
假设我有 3 张桌子。我想将 3 个工作表（每页一个表格）中的表格插入到一个 excel 文件中（不需要 ActiveXObject）
I tried following code, but its creating only one worksheet
我尝试了以下代码，但它只创建了一个工作表
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><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))
}
})();
回答by Axel Richter
The approach you have shown uses a mixture of Spreadsheet XML and HTML. With this mixture it is not possible to fill multiple worksheets. To do this we have to use only Spreadsheet XML consequently. This is because only XML can describe multiple worksheets. The HTML table data is not related to any worksheets except the active worksheet.
您展示的方法混合使用了电子表格 XML 和 HTML。使用这种混合物不可能填充多个工作表。为此，我们必须仅使用电子表格 XML。这是因为只有 XML 可以描述多个工作表。HTML 表格数据与除活动工作表之外的任何工作表无关。
To use only Spreadsheet XML is possible, but then we have to work carefully with data types. If Excel imports HTML, it tries to detect data types as if the user would enter the values into cells manually. With XML it does not so. It takes the given data types from the XML. If they do not fit, then it produces errors. So in my example, I use "data-" attríbutes to describe the data-type, data-style and data-value. So it is possible to have the data-value different from the data presentation within the HTML table cell (TD). Since HTML is a format for data presentation and not for data exchange, this is also good practice in my opinion.
仅使用电子表格 XML 是可能的，但是我们必须小心处理数据类型。如果 Excel 导入 HTML，它会尝试检测数据类型，就像用户将值手动输入单元格一样。对于 XML，情况并非如此。它从 XML 中获取给定的数据类型。如果它们不合适，则会产生错误。因此，在我的示例中，我使用“data-”属性来描述数据类型、数据样式和数据值。因此，数据值可能与 HTML 表格单元格 (TD) 中的数据表示不同。由于 HTML 是一种用于数据呈现而不是用于数据交换的格式，因此在我看来这也是一种很好的做法。
For Spreadsheet XML see: http://msdn.microsoft.com/en-us/library/aa140066.aspx
有关电子表格 XML，请参阅：http: //msdn.microsoft.com/en-us/library/aa140066.aspx
The example uses data URI as download link, so it works only with browsers that support this. It will not work with Microsoft Internet Explorer.
该示例使用数据 URI 作为下载链接，因此它仅适用于支持此功能的浏览器。它不适用于 Microsoft Internet Explorer。
Example:
例子：
<script type="text/javascript">
var tablesToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
+ '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
+ '<Styles>'
+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
+ '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
+ '</Styles>'
+ '{worksheets}</Workbook>'
, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
, 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(tables, wsnames, wbname, appname) {
var ctx = "";
var workbookXML = "";
var worksheetsXML = "";
var rowsXML = "";
for (var i = 0; i < tables.length; i++) {
if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
for (var j = 0; j < tables[i].rows.length; j++) {
rowsXML += '<Row>'
for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;
var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;
ctx = { attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''
, nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'
, data: (dataFormula)?'':dataValue
, attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''
};
rowsXML += format(tmplCellXML, ctx);
}
rowsXML += '</Row>'
}
ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
worksheetsXML += format(tmplWorksheetXML, ctx);
rowsXML = "";
}
ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
workbookXML = format(tmplWorkbookXML, ctx);
console.log(workbookXML);
var link = document.createElement("A");
link.href = uri + base64(workbookXML);
link.download = wbname || 'Workbook.xls';
link.target = '_blank';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
})();
</script>
<table id="tbl1">
<tr>
<td>Name</td>
<td>Birthday</td>
<td>Amount</td>
<td>Rebate (10%)</td>
</tr>
<tr>
<td>Smith</td>
<td data-type="DateTime" data-style="Date" data-value="1980-03-23">Mar 23 1980</td>
<td data-type="Number" data-style="Currency" data-value="1234.56">$ 1,234.56</td>
<td data-formula="=RC[-1]/10" data-type="Number" data-style="Currency">$ 123.45</td>
</tr>
<tr>
<td>Doe</td>
<td data-type="DateTime" data-style="Date" data-value="1978-11-05">Nov 05 1978</td>
<td data-type="Number" data-style="Currency" data-value="2345.67">$ 2,345.67</td>
<td data-formula="=RC[-1]/10" data-type="Number" data-style="Currency">$ 234.56</td>
</tr>
</table>
<hr>
<table id="tbl2">
<tr>
<td>Product</td>
<td>Price</td>
<td>Available</td>
<td>Count</td>
</tr>
<tr>
<td>Bred</td>
<td data-type="Number" data-style="Currency" data-value="1.89">$ 1.89</td>
<td data-type="Boolean" data-value="1">yes</td>
<td data-type="Number" data-value="123">123</td>
</tr>
<tr>
<td>Butter</td>
<td data-type="Number" data-style="Currency" data-value=".89">$ .89</td>
<td data-type="Boolean" data-value="0">no</td>
<td data-type="Number" data-value="0">0</td>
</tr>
</table>
<button onclick="tablesToExcel(['tbl1','tbl2'], ['Customers','Products'], 'TestBook.xls', 'Excel')">Export to Excel</button>
<button onclick="tablesToExcel(['tbl1','tbl2'], ['Customers','Products'], 'TestBook.xls', 'Calc')">Export to Calc</button>
Fiddle: http://jsfiddle.net/qxLn3h86/
小提琴：http: //jsfiddle.net/qxLn3h86/
Greetings
问候
Axel
阿克塞尔
