The excel export exportOptions trim property not working

The excel export exportOptions trim property not working

ManiPSManiPS Posts: 12Questions: 5Answers: 0
edited June 5 in Free community support
"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

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 2,073Questions: 0Answers: 380

    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

  • ManiPSManiPS Posts: 12Questions: 5Answers: 0

    @colin

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

    It is not exported with whitespace.

  • ManiPSManiPS Posts: 12Questions: 5Answers: 0

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

    Can you pls help me on it

  • colincolin Posts: 2,073Questions: 0Answers: 380

    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...

  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin
    Answer ✓

    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

  • ManiPSManiPS Posts: 12Questions: 5Answers: 0

    Thanks @allan it works fine. CHeers.

  • BluericBlueric Posts: 2Questions: 0Answers: 0

    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>
    
  • BluericBlueric Posts: 2Questions: 0Answers: 0

    @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.

  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin

    @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

  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin

    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

  • Manoj542Manoj542 Posts: 5Questions: 1Answers: 0

    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..

  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin

    Workaround for IE committed here. Thanks!

    Allan

  • snisarsnisar Posts: 9Questions: 2Answers: 0

    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.

  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin

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

    Allan

  • snisarsnisar Posts: 9Questions: 2Answers: 0

    Any news on 1.5.3 ? Thanks.

  • SURYAMANOJSURYAMANOJ Posts: 1Questions: 0Answers: 0
    edited August 12

    @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: '*',
    
  • allanallan Posts: 47,749Questions: 1Answers: 6,824 Site admin

    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

Sign In or Register to comment.