The excel export exportOptions trim property not working

ManiPS · June 2018

"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 · June 2018

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 · June 2018

@colin

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

It is not exported with whitespace.

ManiPS · June 2018

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

Can you pls help me on it

colin · June 2018

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 · June 2018

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 · June 2018

Thanks @allan it works fine. CHeers.

Blueric · June 2018

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 · June 2018

@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 · June 2018

@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 · July 2018

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 · July 2018

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 · July 2018

Workaround for IE committed here. Thanks!

Allan

snisar · July 2018

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 · July 2018

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 · July 2018

Any news on 1.5.3 ? Thanks.

SURYAMANOJ · August 2018

@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 · August 2018

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 · February 2019

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>

The excel export exportOptions trim property not working

The excel export exportOptions trim property not working

Answers

Howdy, Stranger!

Categories

DataTables

The excel export exportOptions trim property not working

The excel export exportOptions trim property not working

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables