I have the following code:
<a id="dlink" style="display:none;"></a>
<input type="button" onclick="tableToExcel('cereriTable', 'Tabel Date', 'myfile.xls')" value="Export to Excel">
<script Language="javascript">
var tableToExcel = (function() {
var 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>' +
'<!--[if gte mso 9]>' +
'<xsl:template name="styles">' +
'<style>' +
'table {' +
'mso-displayed-decimal-separator:"\,";' +
'mso-displayed-thousand-separator:" ";' +
'}' +
'@page {' +
'margin:.25in .25in .25in .25in;' +
'mso-header-margin:.15in;' +
'mso-footer-margin:.15in;' +
'mso-page-orientation:landscape;' +
'}' +
'tr {' +
'mso-height-source:auto;' +
'}' +
'col {' +
'mso-width-source:auto;' +
'}' +
'br {' +
'mso-data-placement:same-cell;' +
'}' +
'td {' +
'mso-style-parent:style0;' +
'padding-top:1px;' +
'padding-right:1px;' +
'padding-left:1px;' +
'mso-ignore:padding;' +
'color:windowtext;' +
'font-size:11.0pt;' +
'font-weight:300;' +
'font-style:normal;' +
'text-decoration:none;' +
'font-family:Calibri;' +
'mso-generic-font-family:auto;' +
'mso-font-charset:0;' +
'mso-number-format:General;' +
'text-align:general;' +
'vertical-align:bottom;' +
'border:none;' +
'mso-background-source:auto;' +
'mso-pattern:auto;' +
'mso-protection:locked visible;' +
'white-space:wrap;' +
'mso-rotate:0;' +
'}' +
'.longdate {' +
'mso-style-parent:style0;' +
'mso-number-format:"General Date";' +
'}' +
'.shortdate {' +
'mso-style-parent:style0;' +
'mso-font-charset:0;' +
'mso-number-format:"Short Date";' +
'white-space:normal;' +
'}' +
'.number {' +
'mso-style-parent:style0;' +
'mso-font-charset:0;' +
'mso-number-format:"0";' +
'white-space:normal;' +
'}' +
'.center {' +
'mso-style-parent:style0;' +
'font-weight:700;' +
'text-align:center;' +
'}' +
'.right {' +
'mso-style-parent:style0;' +
'text-align:right;' +
'}' +
'.header {' +
'mso-style-parent:style0;' +
'font-weight:700;' +
'}' +
'</style>' +
'<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) {
while (s.indexOf('ş') != -1) s = s.replace('ş','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();
}
})();
</script>
and I have a dataTable:
dataTable = $('#cereriTable').dataTable({...
and I'd want to:
1) delete a column from the table - in the exported excel (the last column)
2) set the cell format to each column (text/date/number), not general...
3) the mso-page-orientation:landscape doesn't works and I need that...
4) I have multiple pages in the DataTable but this exports me only the records on the selected page.
5) I'd want to set the width of each column... (in the exported excel)
I've solve it that way:
<table id="cereriTable" style="text-align:center">
<thead>
<tr>
<th>Operator economic</th>
<th>Punct de lucru</th>
<th>Cod Fiscal</th>
<th>Judet</th>
<th>Data Introducere</th>
<th>Domeniu Acces</th>
<th>Tip cerere</th>
<th>Status</th>
<th>Username</th>
<th>Detalii</th>
</tr>
</thead>
<a id="dlink" style="display:none;"></a>
<input type="button" onclick="tableToExcel('cereriTable', 'Tabel Date', 'myfile.xls')"
title="If you are using Office 2007, please select 'Yes' from the resulting dialog."
value="Export to Excel">
<script Language="javascript">
var tableToExcel = (function() {
var 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>' +
'<!--[if gte mso 9]>' +
'<xsl:template name="styles">' +
'<style>' +
'@page {' +
'margin:.25in .25in .25in .25in;' +
'mso-header-margin:.15in;' +
'mso-footer-margin:.15in;' +
'mso-page-orientation:landscape;' +
'}' +
'tr { mso-height-source:auto; }' +
'col { mso-width-source:auto; }' +
'br { mso-data-placement:same-cell; }' +
'td {' +
'mso-style-parent:style0;' +
'padding-top:1px;' +
'padding-right:1px;' +
'padding-left:1px;' +
'mso-ignore:padding;' +
'color:windowtext;' +
'font-size:11.0pt;' +
'font-weight:300;' +
'font-style:normal;' +
'text-decoration:none;' +
'font-family:Calibri;' +
'mso-generic-font-family:auto;' +
'mso-font-charset:0;' +
'mso-number-format:General;' +
'text-align:general;' +
'vertical-align:bottom;' +
'border:none;' +
'mso-background-source:auto;' +
'mso-pattern:auto;' +
'mso-protection:locked visible;' +
'white-space:wrap;' +
'mso-rotate:0;' +
'}' +
'.header {' +
'mso-style-parent:style0;' +
'font-weight:700;' +
'}' +
'.text {mso-number-format:"@";}' +
'.number {mso-number-format:"0";}' +
'.date {mso-number-format:"dd-mm-yyyy";text-align:left;}' +
'</style>' +
'<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) {
while (s.indexOf('â') != -1) s = s.replace('â','a');
while (s.indexOf('ş') != -1) s = s.replace('ş','s');
while (s.indexOf('ă') != -1) s = s.replace('ă','a');
while (s.indexOf('ţ') != -1) s = s.replace('ţ','t');
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 output = '';
$("[id=cereriTable]").each(function(){
output += $(this).find("thead").html();//capul de tabel
$('#cereriTable tbody tr').each(function() {
var j=0;
output += '<tr class="">';
$.each(this.cells, function(){ //parcurg celulele de pe rand
j++;
if(j<10)//mai putin coloana de detalii
{
if(j==5)//coloana de data
output += '<td class="date">';
else if(j==3) //coloana de cod fiscal
output += '<td class="number">';
else//restul coloanelor sunt text
output += '<td class="text">';
output += $(this).html();
output += '</td>';
}
});
output += '</tr>';
});
});
var ctx = {worksheet: name || 'Worksheet', table: output}//table.innerHTML}
document.getElementById("dlink").href = uri + base64(format(template, ctx));
document.getElementById("dlink").download = filename;
document.getElementById("dlink").click();
}
})();
</script>