Export to Excel button error in IE 11

Jason5489

I'm using DataTables with the buttons extension (specifically the Export to Excel button) to display data from SharePoint lists. It has worked great for the last year or so, but recently my users have started to get errors when opening the Excel files generated by the Export to Excel button.

When I click the button, then click "Open" in the IE prompt, Excel opens and pops up:

"We found a problem with some content in 'so-and-so.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

I click "Yes", and a blank workbook opens. I then get another popup that states:

"Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Undeclared entity. Line 1, column 321020."

This only happens in IE-11. I've tested the same page in Chrome and Firefox and it works fine. After reading a similar post, I updated all of my buttons libraries with the nightly versions (datatables.buttons.min.js, buttons.datatables.min.css, and buttons.html5.min.js). I also updated to the most recent version of jszip, but I'm still getting the error.

Any suggestions on what else I can do to try to correct this? Also, is there a way for me to determine which column the Excel error message is referring to?

colin

Hi @Jason5489 ,

This sounds like this problem here (and here). The way to confirm is to try the nightly release of Buttons.

We hope to make the release which will address this next week.

Cheers,

Colin

Jason5489

Thanks for the quick response!

As I mentioned in my post, I've already updated to the nightly release of the Buttons extension and jszip as of yesterday. This morning, I also updated to the most recent version of Datatables to see if that made a difference, but I'm still getting the same error message when I try to export to excel in IE 11.

colin

Hi @Jason5489 ,

Could you try running the debugger on that page, that'll report which versions there are. Also, FYI, 1.5.4 was released today, but that will be the same as nightly version.

Cheers,

Colin

Jason5489

I used the debugger and verified that I'm using Buttons version 1.5.4 and DataTables version 1.10.18

colin

Thanks for trying that. Could you verify whether the exported files from this example work as expected? If that works, then it suggests there's something wrong with either your data or export functions.

C

colin

Scrub that last one, it's not using the nightly or the new releases. Could you try this example here: http://live.datatables.net/kotoxuce/1/edit

I tried with on Windows 10 with IE11, and the XLS loaded as expected.

Jason5489

That example works for me in IE11 (I'm also on Windows 10).

I don't think there's anything wrong with the export functions because I've got 4 other tables on the same page with the same export functions and they are running fine.

I don't think there's something wrong with the data because it's working fine in other browsers. Also, the data appears to be rendering correctly in the table and I'm not getting any errors in the developer console.

If it is the data, what kinds of data issues can cause this error? What should I be looking for? A particular character or data type?

kthorngren

Can you post a link to your page or a test case with an example of your data replicating the issue? This way the developers can take a look to determine what the problem might be.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Jason5489

Unfortunately I can't do that. The page is on a internal government site and I'm not able to publicly share the data from it. I could send you an email with a screenshot if you can provide me with an email address where I can contact you. Or I could possibly send you a private message on this site if I'm able to attach screenshots to private messages.

kthorngren

You could PM them to Allan who is the owner of this site. Not sure if screenshots would help but you can try. Its more a matter of trying to figure out what data or type of data is causing an issue with the export.

Maybe you can troubleshoot by removing columns from the exported data until you find the offending data. If you can reduce down to a small subset maybe you can send that directly to Allan as well.

If its a bug in the export code Allan would like to fix it.

Kevin

colin

Kevin's idea of removing columns until you can pin point the offending one is good. Another option is to modify my example, with data similar to yours, and see if it breaks there. And finally, as it's working on those other tables, you could see what the differences are in the data (size, types, character encodings, etc), and the export functions.

C

Jason5489

I found the offending data. It was a character known as a "soft hyphen" [unicode: & shy ;]. I have no idea how it got there, but I'm guessing a user copied some text from an email or something and pasted it into the InfoPath form.

I found this data by saving the Excel file as a zip, and opening the sheet1.xml within that zip in Notepad++. It turns out that the Excel error message was referring to the line and column numbers of that XML file. I searched for the line and column (character) number that it was referring to, and found that soft hyphen.

Once I replaced the soft hyphen with a normal hyphen, the Export to Excel function worked fine.

This link describes the method I used: https://christianspecht.de/2014/01/14/excel-found-unreadable-content-when-exporting-a-reporting-services-report/

kthorngren

That's a handy link, thanks for sharing. Glad you found the issue.

Kevin

Charley

so, I literally just stumbled onto this during a client demo. I'm trying to figure out if I need to filter something on the data entry side or not

Charley

so, it doesn't seem to have anything to do with the data in the table for me. I'm going to investigate a bit more, and see if I can get permission to attach some data for an example.

Charley

Edit: Looks like this is the issue. https://datatables.net/forums/discussion/comment/133423/#Comment_133423

Evidently I have an older version of dataTables than I thought I did.

I generated the excel in both edge and ie, with the filters set to the same data item.

edge works, ie does not.

renamed both files to zip, started digging throught he differences

in the bad file, sheet1.xml, I removed some blank name spaces

sheet1.xml: removed:
xmlns:NS1="" NS1:
xmlns:NS2="" NS2:
xmlns:NS3="" NS3:
xmlns:NS4="" NS4:

they were all of the form:
<t xmlns:NS?="" NS?:xml:space="preserve">

zipped this back up, renamed back to xlsx, and it worked.

allan

Thanks for posting back. That shouldn't be an issue in Buttons 1.5.4 (and the same HTML5 export version). What version are you using?

during a client demo

Bloody typical isn't it. Sorry about that!

Allan