Hey guys, I am working on a program that opens an excel sheet, adds auto filter to row 5 then closes. I have it to where it does that perfectly but once you open the sheet and try to sort it crashes. I have found that if it is the XLSX type of excel sheet then it works and doesn't crash. But due to the our SSRS reporting server creating the file then it will be an XLS type and thus causing the crash. I have tried to convert it from HSSF to XSSF but I have failed every time. What would you guys recommend I do to resolve this?
That looks like it should work. Have you tried manually creating the autofilter in the .xls file to see if it works or crashes ie is the problem with excel or POI.
I found this stack overflow thread based on this thread from the ranch which claims to be able to convert the .xls excel workbook to .xlsx
I have went in and manually added the filter and it has no issues. I have also found that if I add the filter thru my code then I open the sheet and filter then sort it then it won't crash. But if the first thing I do, after it runs thru my code, is sort then it crashes every time.
I have been on there trying to modify it toconvert with the links you provided and I keep running into issues. I am going to keep trying the conversion method unless you have another idea that could resolve this?
Just as a matter of interest which version of POI are you using?
The current version is 3.15 so if you are using an earlier version it would be worth upgrading and testing to see if the problem is fixed. If not it may be worth checking the release notes of 3.16 Beta to see if anything that may effect you has been fixed and if so trying the beta version.
I started out using 3.15, I have tried 3.13, 3.14, and 3.16 with the same issue. I have reported this on their site as a bug on to Microsoft community since excel is crashing.
The only other thing I can think of (assuming you can't convert the file to .xlsx format) is to sort the data in Java (AFAIK POI doesn't support sorting), write it back to the sheet and then apply the filter. I have no idea if this will help, I'm grasping at straws now.
I'm sure glad that he's gone. Now I can read this tiny ad in peace!
Gift giving made easy with the permaculture playing cards