Excel Export Button Filtering Hangs on A to Z Sort

mikeg

I noticed that the nightly version of buttons.html5.min.js now includes an autoFilter for Excel exports. Unfortunately this hangs any spreadsheet that's created from the button when the A to Z (or Z to A) sort is selected from the filter. The filtering itself works fine.

Does anyone know how to fix this or at least remove the filter before export? This would be a nice feature if it worked!

I've had to revert back to the published version 1.53 for the buttons to remove the filter.

Regards
mikeg

allan

Hi,

What browser and version did you use to export the file? Also what version of Excel are you using?

I've just tried the demo on Chrome / Win10 with Excel 1808 and it seems to work as expected. Nothing hanging.

Allan

mikeg

Hi Alan,

I've tried it on IE11 , Chrome (69.0.3497.100) and Edge (42.17134.1.0) using Excel 2013 (15.0.5067.1000) MSO (15.0.5059.1000) 64 bit.

Let me know if you need anything else.

Thanks
Mike

mikeg

Alan,

As some additional info - I'm using Win 10 build 1803. I used V 1.10.19 of jquery.dataTables.min.js.

allan

I've just tried it with IE11 exporting the file and it seems to work okay from there as well. I can filter and sort in Excel okay.

I don't have Excel 2013 to try it on unfortunately - just 2016.

If anyone else reads this and has 2013 available could you try this please?

Thanks,
Allan

matthew_b

I can confirm that I experience this issue (Excel hangs and then crashes).

Exported from Chrome (70.0.3538.110) using Excel 2010, Windows 10 v1803. I had a co-worker test in Firefox (not sure the version) and he experienced the same thing (Excel/Windows version are the same).

colin

Hi @matthew_b ,

We struggled to reproduce this one - do you have a test case, or a link to your site, that demonstrates the issue? Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

matthew_b

Here is my JS Fiddle where I experience the issue:

https://jsfiddle.net/matthew__b/uy2g1w3t/

colin

Hi @matthew_b ,

Thanks for that fiddle - I just tried it, and everything appears to be working as expected (Chrome/Fedora). Could you please give steps on how to reproduce that problem,

Cheers,

Colin

matthew_b

Here is what I do to produce that error:

• Click the "Excel" button
• Open the exported file (in Excel 2010)
• Click to "Enable Editing" (Excel recognizes it as a file from the web)
• Click on any of the filter dropdown menus (e.g. "Age")
• Click to sort "A -> Z" or "Z -> A"

At this point, Excel hangs and shortly thereafter closes.

I don't experience this issue if I use the ribbon to sort (Data > Sort > select column and click "OK"). I also don't experience this using the right-click menu (Sort > Sort A to Z). Interestingly, when I sort in either of the two ways that do work, Excel automatically removes the filters from the header row.

Also, if I remove the filters and then add them back manually, everything is fine. I only experience this when using the sort function from the filter menu automatically added in the export.

colin

Hi @matthew_b ,

Thanks for those instructions - unfortunately, it's all working as expected for me. I'm using Office 365 (and also tried with LibreOffice), with XLS files created by both Chrome and Edge under Windows 10 and Fedora 28, and all the ordering behaved as expected. I'll see if we've got Excel 2010.

Cheers,

Colin

mikeg

Colin,

With reference to my posts on 27 September, the fiddle setup by matthew_b doesn't work for me using Excel 2013. It fails in exactly the same way.

As Matthew stated, removing the filter and adding a new one works so it must have something to do with how the original filter is created.

Mike

colin

Hi @mikeg and @matthew_b ,

I've just reproduced it - I stole the wife's laptop which has Excel 2010 on it, and am now seeing it. We'll poke about and let you know once we've debugged further,

Cheersm

Colin

mikeg

Hi Colin,

Has there been any progress on this?

Regards
Mike

allan

Hi Mike,

No sorry. I had to put it on the back burner due to other things going on, and haven't looked at it since them I'm afraid. I have the file from Colin showing the difference, I just need to find some time to work on it.

Allan

urnotbeanz

I've been having the same issue. A workaround I found is filtering out all but one of the rows, and then sorting it. After it is sorted, remove the filter for those rows, and then sorting works fine. But this is only a workaround. I'm hoping that by knowing the workaround the actual cause might be found.
All The Best!