Javascript - export a JQuery dataTable to Excel problems -
i have following code:
<a id="dlink" style="display:none;"></a> <input type="button" onclick="tabletoexcel('cereritable', 'tabel date', 'myfile.xls')" value="export 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 have datatable:
datatable = $('#cereritable').datatable({... and i'd want to:
1) delete column table - in exported excel (the last column)
2) set cell format each column (text/date/number), not general...
3) mso-page-orientation:landscape doesn't works , need that...
4) have multiple pages in datatable exports me records on selected page.
5) i'd want set width of each column... (in exported excel)
i've solve 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 using office 2007, please select 'yes' resulting dialog." value="export 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>
Comments
Post a Comment