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

Thorsten

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

allan

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

Thorsten

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

Thorsten

Hi Allan,

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

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

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

sharon

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?

allan

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

Allan

allan

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

pdring

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.

allan

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

pdring

Thanks for your help Allan,

I hadn't seen the nightlies previously :)

allan

Buttons 1.1.0 is now available which includes this fix.

Allan

pdring

Thank you :)

pdring

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.

allan

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

pdring

Cheers.

Kogoga

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!

iris8733

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

natarajan

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

allan

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

natarajan

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

nitashaverma

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

allan

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

Allan

F12Magic

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, '');

RagnarGrootKoerkamp

Thanks @F12Magic, your fix seems to work indeed.

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

allan

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

SaiMadhanRockzz

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

thanks

allan

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

Allan

suipor

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

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

allan

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

techiebud

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

allan

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