The excel export exportOptions trim property not working

ManiPS

"buttons": [
                {
                    extend: 'excelHtml5',
                    title: $(".companyName").text(),
                    filename: $('#reportTitle').text().trim(),
                    "createEmptyCells" : true,
                    sheetName: excelSheetName,
                    messageTop: $(".stdReportTitle").text(),    
                        exportOptions: {
                                        trim : false;
                                    }
                    customize: function( xlsx ) {   
                                       // my code
                                    }
                            }
];

The trim property not working. I want to excel exported cell content with empty spaces before text;
Ex:
Cell sting is " text"

Please help me. The cell contents are trimmed by default when i export

colin

Hi @ManiPS ,

DataTables by default removes data from DOM loaded tables - so if you want the whitespace, you have to get it back from the DOM, as in this example here.

If it was Ajax loaded, spaces should be preserved, and then what you have should be working - I verified that and it does for me.

Cheers,

Colin

ManiPS

@colin

I checked the example http://live.datatables.net/hizomebo/1/edit

It is not exported with whitespace.

ManiPS

@colin
When i export the data, I want the whitespace before the exported excel cell content.

Can you pls help me on it

colin

I just tried it here - and it works on LibreOffice - see this example: http://live.datatables.net/kufazoje/2/edit .

I think this may be an issue with Excel itself...

allan

I've just taken a look at the XML that is in the xlsx file produced by Colin's example and the white space is in there:

<c t="inlineStr" r="B3"><is><t>   B</t></is></c>

So Excel is stripping it on import. To resolve this it appears we need to add xml:space="preserve". I don't see a problem with that being there by default, since we strip by default anywhere, so committed that.

It will be in the nightly builds shortly.

Note that if you use Ajax sourced data you don't need to use the customizeData workaround in Colin's example. DataTables strips text content by default when working with DOM sourced data, but not Ajax / JS sourced data.

Allan

ManiPS

Thanks @allan it works fine. CHeers.

Blueric

Looks like this change broke Excel exports for IE (but it still works in Edge and chrome).
Because of differences in how the XML is generated with name spaces, it results in a double : attribute. This causes Excel to complain about it being corrupt and it won't open the file.

/** Edge/Chrome/FF **/
<t xml:space="preserve">Id</t>

/** IE11 (and presumable older IE...but untested) **/
<t xmlns:NS1="" NS1:xml:space="preserve">Id</t>

Blueric

@allen, also... just noticed that there is special logic in the _addToZip function which deals with empty name spaces for IE/Edge/Safari, as well as putting the XML header there. I'm not sure all that is working as intended in conjunction with this change either.

allan

@Blueric - Thanks for pointing that out - I agree, it looks to be broken in IE11. I'll post back when I've committed a fix. Thanks for letting me know about that.

Allan

allan

Fixed here and it will be in the nightly build of Buttons shortly. I've checked it locally and it works fine, but if anyone else could offer feedback that would be great and I can get it tagged up and released.

Allan

Manoj542

Thanks @allan.. This works like a charm but there is another issue with print button in IE 11. When we click on print button, it just shows the data as a report but it doesn't trigger windows printer dialog box.

Working fine in Firefox and chrome. Request you to please check on this..

allan

Workaround for IE committed here. Thanks!

Allan

snisar

Thanks @allan

We are also experiencing this issue. Currently on 1.5.2, referenced via the CDN. Will the fix be released in 1.5.3 and any idea when that will be available, roughly?

Many thanks.

allan

Yes 1.5.3 will resolve this. So release schedule, but I'll probably package it up in the next week or two.

Allan

snisar

Any news on 1.5.3 ? Thanks.

SURYAMANOJ

@allan
Any update on 1.5.3 or Fix to IE 11 excel download issue ?

Tried changing it to below, but facing the same issue.

win.setTimeout( function () {
        var autoPrint = function () {
            if ( config.autoPrint ) {
                win.print(); // blocking - so close will not
                win.close(); // execute until this is done
            }
        }, 1000 );
        };
        if ( navigator.userAgent.match(/Trident\/\d.\d/) ) { // IE needs to call this without a setTimeout
            autoPrint();
        }
        else {
            win.setTimeout( autoPrint, 1000 );
        }
    },
    title: '*',

allan

We are working on a few other things at the moment, but I'll try to get it out soon.

Regarding the auto print issue, can you give me a link to a test case showing that issue please?

Allan

gt45200

I solved the problem by adding a script code and it ran in IE damn
<script src="https://nightly.datatables.net/buttons/js/buttons.html5.min.js"></script>