Is there any way to export table to excel with excelHtml5, with defined type for certain columns?

tjurinec

I have a table in which one column has mixed values(text and numbers: {12AE15152}, {1531215}). My problem is that MS Excel formats number values as number(aligned on the right, and adds E+ for greater values) but i want it to be shown as simple text.

Here is what I have done so far:

   var buttonCommon = {
            exportOptions: {
                trim: false,
                format: {
                    body: function (data, column, row) {
                        // Strip $ from salary column to make it numeric
                        data = data.replace(/<span title=".+\">/, '');
                        data = data.replace(/<span class="fa fa-check text-success\">/, 'YES');
                        data = data.replace(/<span class="fa fa-minus text-danger\">/, 'NO');
                        data = data.replace(/<\/span>/, '');
                        return data.toString();
                    }

                }
            }
        };


        $("#userA_tbl").dataTable({
            dom: 'Bfrtip',
            buttons: [
                $.extend(true, {}, buttonCommon, {
                    extend: 'excelHtml5',
                    title: '<fmt:message key="limit.change.history" />',
                    text: '<i class="fa fa-file-excel-o"></i>',
                    titleAttr: 'Excel'}),
                $.extend(true, {}, buttonCommon, {
                    extend: 'pdfHtml5',
                    title: '<fmt:message key="limit.change.history" />',
                    orientation: 'landscape',
                    pageSize: 'A3',
                    text: '<i class="fa fa-file-pdf-o"></i>',
                    titleAttr: 'PDF'
                })
            ],
            "columnDefs": [
                {"type": "html", "targets": "_all"}
            ]
        });

allan

Currently no - sorry. This is something that I plan to add as a feature in a future version, but at the moment the cell format is on a per cell basis and will be either numeric or string.

Allan

tjurinec

Hi allan, thanks for your response. I was wondering if it was possible to manually set all cells to be typeof String in buttons.html5.js ? Or is it possible in any other way to set all cells to be shown as text in xlsx file?

allan

Sure - remove the number handling here.

Allan

tjurinec

It worked! Thanks a bunch.

frigo

@tjurinec Try to add 8203; before the value you want to keep as a simple text in Excel, width & and # before 8

​12AE15152

srie

Hi,
I did remove line 659 as suggested by Allan number handling
but it still didn't work. When I open the excel file, a message appear to recover the content (error1.jpg) and after I click Yes, the file loaded with a column that suppose to fix but still remain wrong (error2.jpg). You can refer error3.jpg for the log file. Am I removing the wrong line since @tjurinec got it worked?. I'm using Excel 2007 and my debug code is onesej. Thanks in advance.

allan

Yeah that's the wrong line now. I should have linked to a particular version - sorry. This is the block where Buttons' Excel export will detect numbers. Just add false || at the start of the number detection.

Allan

srie

Thanks!