Problem with jQuery Datatables Buttons - Excel Export - Using Special Character like "&" or "<"

Problem with jQuery Datatables Buttons - Excel Export - Using Special Character like "&" or "<"

ThorstenThorsten Posts: 7Questions: 1Answers: 0

Hi,

like i mentioned in the header, we have a problem with the excel export funtion from jQuery Datatable Buttons - when using special characters like "&" or "<". The excel export function works great if the data in the Datatable doesnt contain any "&" or "<".

the csv export function has no problem with the special character its seems like this problem only happens in the export to excel function.

is there any solution for this problem?

best regards

This question has an accepted answers - jump to answer

«1

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    Are you using Buttons 1.0.3? This issue should be resolved there. If you are not, please try it. If you are, please link to a test case showing the issue.

    Allan

  • ThorstenThorsten Posts: 7Questions: 1Answers: 0

    With version 1.0.3 it works fine.
    Thanks for the quick help

  • ThorstenThorsten Posts: 7Questions: 1Answers: 0

    Hi Allan,

    i made a mistake, sorry. The special sign "<" still not working.

    Sample: http://jsfiddle.net/ebRXw/715/

    Special sign "&" and ">" work fine.

  • sharonsharon Posts: 3Questions: 2Answers: 0

    Hi allan,

    I'm using 1.0.3, but it still doesn't solve the problem of html entities like >
    Sample: http://jsfiddle.net/sharonhsy/Lhyon7en/
    Any ideas how to work for html entities?

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Thanks for the links. I'll take a look into this shortly.

    Allan

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Sorry for the massive delay on this. This has now been resolved in the Buttons nightly.

  • pdringpdring Posts: 8Questions: 2Answers: 0
    edited November 2015

    Hi,

    I'm also having this problem with exactly these characters as well as conventional spaces which appear as & nbsp;

    I'm using this script path : https://cdn.datatables.net/r/dt/jqc-1.11.3,jszip-2.5.0,dt-1.10.9,b-1.0.3,b-flash-1.0.3,b-html5-1.0.3,sc-1.3.0/datatables.min.js

    I've also tried variations on different types of button (Flash and HTML5) as well as different formats (Excel and CSV) to no avail.

    Thanks in advance.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    As noted above, the current release, 1.0.3, has issues in this regard. They have been resolved in the nightly versions and you can use those files if you wish.

    Alternatively, wait for the next release which should be soon.

    Allan

  • pdringpdring Posts: 8Questions: 2Answers: 0

    Thanks for your help Allan,

    I hadn't seen the nightlies previously :)

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Buttons 1.1.0 is now available which includes this fix.

    Allan

  • pdringpdring Posts: 8Questions: 2Answers: 0

    Thank you :)

  • pdringpdring Posts: 8Questions: 2Answers: 0

    Hi Allan,

    It seems that this updates reintroduces the problem of "Excel found unreadable data". I also get the impression that the previous script is no longer available or won't build.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    I also get the impression that the previous script is no longer available

    Still available on the CDN but the builder always uses the current release version.

    I've just committed a fix that will address the error in 1.1.0. Sorry about that. The nightly has the fix and I'll release 1.1.1 soon.

    Allan

  • pdringpdring Posts: 8Questions: 2Answers: 0

    Cheers.

  • KogogaKogoga Posts: 1Questions: 0Answers: 0

    Hi!
    Unfortunately, I've met the similar problem with special symbols (at least with '<') during exporting data to excel.
    Data from the body of the tables exports fine, but if data in the header contains '<' it appears as '<' in the excel file.
    Buttons version: 1.1.2

    Thanks for the help!

  • iris8733iris8733 Posts: 1Questions: 0Answers: 0

    Same in Buttons 1.2.1. Seeing this behavior at least with &, <, and >.
    https://jsfiddle.net/h9gdm998/1/

  • natarajannatarajan Posts: 2Questions: 0Answers: 0
    edited July 2016

    Hi iris8733,

    I had the same problem with 1.2.1. I've deleted the following lines from buttons.flash.js (l.1204-l.1206) to solve this behavior :
    .replace(/&(?!amp;)/g, '& amp;')
    .replace(/</g, '& lt;')
    .replace(/>/g, '& gt;')

    @allan : can you explain why you're replacing non standard characters for text output ?

    Thanks

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    can you explain why you're replacing non standard characters for text output ?

    Do you mean the search for the control characters ([\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F])? They need to be replaced as they kill Excel...

    The test case from @iris8733 is very useful (@natarajan I assume that shows the issue you are seeing?). I'll look into it an post back when I've got a fix.

    Allan

  • natarajannatarajan Posts: 2Questions: 0Answers: 0

    Thanks Allan for your fast reply.

    Ya ok i can understand now. But why you're encoding HTML entites too (see l.1204-l.1206) ? So while I export to excel, my file contains '& amp;' instead of '&'... Do you have a fix for this ?

    Thanks for your help

  • nitashavermanitashaverma Posts: 1Questions: 0Answers: 0

    @allan
    Asking the same question as @natarajan would you please explain why the '&', '<' and '>' symbols need to be replaced in the text output?

    We need them to be spit out as is on the excel export.

    Thanks for your help.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    They don't - its a bug. I need to make some time to fix it. Hopefully tomorrow or next week.

    Allan

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28
    edited July 2016

    For excel buttons, decodeEntities is not working right now.
    exportOptions: { columns: ':visible', trim: true, decodeEntities: true, orthogonal: { display: ':null' }, format: { footer: function( data, columnIdx){ return ''; } } }

    To fix this, remove the ! in buttons.html5.js on line 1068
    // Replace non standard characters for text output var text = row[i].replace ? row[i] : row[i] .replace(/&(?!amp;)/g, '&amp;') .replace(/</g, '&lt;') .replace(/>/g, '&gt;') .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');

  • RagnarGrootKoerkampRagnarGrootKoerkamp Posts: 48Questions: 14Answers: 1

    Thanks @F12Magic, your fix seems to work indeed.

    Nice that a few hours before I encountered this problem, a solution was posted :smiley:

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Fix committed - finally! Sorry for the delay.

    I'll release an updated version of Buttons with this fix later this week, or you can use the nightly version in the meantime, which will rebuild with the fix in the next few minutes.

    Regards,
    Allan

  • SaiMadhanRockzzSaiMadhanRockzz Posts: 2Questions: 0Answers: 0

    1.while in export to excel i want to replace header columns with another names in excel is this possible please help me

    thanks

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Use the customize method to modify the XML that is generated.

    Allan

  • suiporsuipor Posts: 1Questions: 0Answers: 0
    edited February 2017

    Hi, I'm using version 1.10.12, and when using export, I am still having problem with the & being replaced with &amp;

    Shall I remove them manually in the buttons.html5.js and buttons.flash.js files? But I'm afraid the update will replace them later.

    Please advice

    Thanks!!

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    1.10.13 is the current version of DataTables and 1.2.4 if the release version of Buttons. If they aren't working for you, please link to a test case showing the issue.

    Allan

  • techiebudtechiebud Posts: 1Questions: 0Answers: 0

    I have the latest version of jquery.datatables.js, dataTables.buttons.js and buttons.html5. I have a table cell with data like "WSLK0H87<>LG06,261-262<>WSLKOHST". When I export to the Excel spreadsheet, the "<>" characters are removed and not replaced with anything. Please help!

    Thank you,

    Johnny Sims

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Disable the stripHtml option of the butons.exportData method (which you can do for the provided buttons via the button's formOptions object.

    Buttons uses a fairly lazy HTML regex stripper, as it is fast and works in 99% of cases. I fear you are in the 1% where it doesn't.

    Allan

This discussion has been closed.