使用javascript将工作表添加到excel文件 [英] Adding worksheet to the excel file using javascript
本文介绍了使用javascript将工作表添加到excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我尝试了以下代码，但它只创建一个工作表
var tableToExcel =（function（）{
var uri ='data：application / vnd.ms-excel; base64， ，
template ='< html xmlns：o =urn：schemas-microsoft-com：office：officexmlns：x =urn：schemas-microsoft-com：office：excelxmlns =http： //www.w3.org/TR/REC-html40\"><head><!--[if gte mso 9]>< xml>< x：ExcelWorkbook>< x：ExcelWorksheets>< ; X：ExcelWorksheet>< X：名称> {工作表}< / X：名称>< X：WorksheetOptions>< X：DisplayGridlines />< / X：WorksheetOptions>< / X：ExcelWorksheet>< ; / X：ExcelWorksheets>< / X：ExcelWorkbook>< / XML>< ENDIF] - GT;< /头><主体><表> {表}< / TABL e>< 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）{
返回c [p];
}）
}
返回函数（表，名称）{
if（！table.nodeType）table = document.getElementById（table）
var ctx = {
工作表：name || '工作表'，
表：table.innerHTML
}
window.location.href = uri + base64（format（template，ctx））
}
}） （）;
解决方案
您所展示的方法使用了电子表格XML和HTML。使用这种混合物是不可能填充多个工作表。为此，我们必须仅使用Spreadsheet XML。这是因为只有XML可以描述多个工作表。 HTML表数据与活动工作表之外的任何工作表无关。
只能使用电子表格XML，但是我们必须仔细研究数据类型。如果Excel导入HTML，它会尝试检测数据类型，就像用户手动将值输入到单元格中一样。使用XML不是这样的。它从XML中获取给定的数据类型。如果它们不适合，则会产生错误。所以在我的例子中，我使用data-attríbutes来描述数据类型，数据样式和数据值。因此，可以使数据值与HTML表格单元格（TD）中的数据显示不同。因为HTML是数据呈现的格式，而不是数据交换的格式，这在我看来也是很好的做法。
对于电子表格XML，请参阅： http://msdn.microsoft.com/en-us/library/aa140066.aspx
该示例使用数据URI作为下载链接，因此它仅适用于支持此功能的浏览器。它不适用于Microsoft Internet Explorer。
示例：
< 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：spreadsheetxmlns：ss =urn：schemas-microsoft-com： office：spreadsheet>'
+'< DocumentProperties xmlns =urn：schemas-microsoft-com：office：office>< Author> Axel Richter< / Author>< Created> {created} < / Created>< / DocumentProperties>'
+'< Stylees>'
+'< Style ss：ID =Currency>< NumberFormat ss：Format =Currency < / NumberFormat>< / Style>'
+'<样式ss：ID =Date>< NumberFormat ss：Format =Medium Date>< / NumberFormat>< ; / Style>'
+'< / Styles>'
+'{worksheets}< / Workbook>'
，tmplWorksheetXML ='< Worksheet ss：Name ={nameWS }><表> {行}&升t; / Table>< / Worksheet>'
，tmplCellXML ='< Cell {attributeStyleID} {attributeFormula}< Data ss：Type ={nameType}> {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 [一世]）; （var j = 0; j< tables [i] .rows.length; j ++）{
rowsXML + ='< Row>'
for（var k = k< table [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};
workheetsXML + = 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>生日< / td>
< td>金额< / td>
< td> Rebate（10％）< / td>
< / tr>
< tr>
< td> Smith< / td>
< td data-type =DateTimedata-style =Datedata-value =1980-03-23> 1980年3月23日< / td>
< td data-type =Numberdata-style =Currencydata-value =1234.56> $ 1,234.56< / td>
< td data-formula == RC [-1] / 10data-type =Numberdata-style =Currency> $ 123.45< / td>
< / tr>
< tr>
< td> Doe< / td>
< td data-type =DateTimedata-style =Datedata-value =1978-11-05> Nov 05 1978< / td>
< td data-type =Numberdata-style =Currencydata-value =2345.67> $ 2,345.67< / td>
< td data-formula == RC [-1] / 10data-type =Numberdata-style =Currency> $ 234.56< / td>
< / tr>
< / table>
< hr>
< table id =tbl2>
< tr>
< td>产品< / td>
< td>价格< / td>
< td>可用< / td>
< td> Count< / td>
< / tr>
< tr>
< td> Bred< / td>
< td data-type =Numberdata-style =Currencydata-value =1.89> $ 1.89< / td>
< td data-type =Booleandata-value =1> yes< / td>
< td data-type =Numberdata-value =123> 123< / td>
< / tr>
< tr>
< td> Butter< / td>
< td data-type =Numberdata-style =Currencydata-value =。89> $ .89< / td>
< td data-type =Booleandata-value =0> no< / td>
< td data-type =Numberdata-value =0> 0< / td>
< / tr>
< / table>
< button onclick =tablesToExcel（['tbl1'，'tbl2']，['Customers'，'Products']，'TestBook.xls'，'Excel' >导出到Excel< / button>
< button onclick =tablesToExcel（['tbl1'，'tbl2']，['Customers'，'Products']，'TestBook.xls'，'Calc'）>导出到Calc&按钮>
小提琴： http://jsfiddle.net/qxLn3h86/
问候语
Axel
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)
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))
}
})();
解决方案
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.
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.
For Spreadsheet XML see: 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.
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/
Greetings
Axel
这篇关于使用javascript将工作表添加到excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
