Menu
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))
}
})();
957
Create the add-in projectUsing the search box, enter add-in. Choose Excel Web Add-in, then select Next. Name your project ExcelWebAddIn1 and select Create. In the Create Office Add-in dialog window, choose Add new functionalities to Excel, and then choose Finish to create the project.
An Excel add-in interacts with objects in Excel by using the Office JavaScript API, which includes two JavaScript object models: Excel JavaScript API: Introduced with Office 2016, the Excel JavaScript API provides strongly-typed objects that you can use to access worksheets, ranges, tables, charts, and more.
On the Home tab, in the Cells group, click Insert, and then click Insert Sheet. Tip: You can also right-click the selected sheet tabs, and then click Insert.
getWorksheet('sheetname'); // replace sheetname with actual sheet name worksheet. getRow('rowNumber'). getCell('cellNumber'). value = 350; // replace rowNumber and cellNumber with the row and cell you want to modify workbook.
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
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
