Javascript - export a JQuery dataTable to Excel problems

Question

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)

Thank you very much for: table { mso-displayed-decimal-separator: "\."; mso-displayed-thousand-separator: " "; } Helped me ALOT! — Perty
– Perty, Commented Apr 16, 2014 at 8:43

WDrgn · Accepted Answer · 2013-07-29 08:48:43Z

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>

Collectives™ on Stack Overflow

Javascript - export a JQuery dataTable to Excel problems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related