Hi,
I’ve encountered an issue with AutoFilter function when the workbook is created from an xml template. Please find the attached example solution - it loads an xml with AutoFilter function defined, and then saves it. After the template is saved, it then reads the AutoFilter.Range and reapplies the exact same value back into AutoFilter.Range, and saves the file. With the resulting files, on the one that was saved directly, If you then open the autofilter and attempt to sort it A-Z it causes excel to crash. If you then try the same on the other file, sorting works fine. While this is a simple enough workaround, I think autofilter should work on its own - Could you please have a look?
AutoFilterInXml.zip (4.4 KB)
It sounds like you’re experiencing a frustrating issue with the AutoFilter functionality when working with an XML template in Aspose.Cells. Here’s a structured approach to address the problem:
Cause: The issue may stem from an improperly structured XML template, outdated software, or compatibility problems with Excel.
Solution: Follow these steps to troubleshoot and potentially resolve the problem:
-
Check XML Structure: Ensure that the XML template is correctly structured and adheres to the expected format for AutoFilter. Any discrepancies in the XML could lead to unexpected behavior.
-
Reapply AutoFilter: After loading the XML and before saving, explicitly set the AutoFilter range again. This can help refresh the filter settings.
-
Update Aspose.Cells: Ensure you are using the latest version of Aspose.Cells, as newer releases may contain bug fixes or improvements related to AutoFilter functionality.
-
Test with Minimal Example: Create a minimal version of your XML template that only includes the AutoFilter settings. This can help isolate the issue and determine if it’s related to the specific content of your original template.
-
Review Excel Compatibility: Since one file causes Excel to crash while the other does not, check the compatibility of the generated files with different versions of Excel. Certain features may behave differently across versions.
Next Steps: If these steps do not resolve the issue, please share a simplified version of your XML template and the code you are using to load and save it. This will assist in diagnosing the problem more effectively.
I hope this helps! Please let me know if you have any further questions or need additional assistance.
@acturisaspose
By testing on the latest version v25.8 using sample files and the following sample code, and sorting the data in the generated result file from a to z, no abnormalities were found. Please refer to the attachment. result.zip (39.1 KB)
var workbook = new Workbook(filePath + @"SpreadsheetMlWithAutoFilter.xml");
workbook.Save(filePath + "SpreadsheetMlWithAutoFilter.xlsx", SaveFormat.Xlsx);
var range = workbook.Worksheets.FirstOrDefault().AutoFilter.Range;
workbook.Worksheets.FirstOrDefault().AutoFilter.Range = range;
workbook.Save(filePath + @"SpreadsheetMlWithAutoFilter_fixed.xlsx", SaveFormat.Xlsx);
We recommend you to kindly try using our latest version: Aspose.Cells for .NET 25.8.
If you still find the issue, kindly do provide your result files, we will check it soon.
Hi,
I am using version 25.8, even though it’s the .net framework version of the package. Attaching two files: SpreadsheetMlWithAutoFilter and SpreadsheetMlWithAutoFilter_Fixed. The first one is created directly from the xml attached previously, and the one with _Fixed in the name had the Range attribute re-applied in code. Opening the files in Excel (2016), the one without _Fixed in the name crashes Excel when attampting to sort the autofiltered data A->Z (clicking on the Autofilter’s dropdown and selecting the Sort option).
SpreadsheetMlWithAutoFilter.zip (14.6 KB)
Thank you for sharing the sample files.
I tested your scenario using the output files you provided in MS Excel 2019 (I currently have MS Excel 2019 installed, not MS Excel 2016), and it works perfectly. I opened the “SpreadsheetMlWithAutoFilter.xlsx” file in MS Excel 2019. Excel did not crash when sorting the auto-filtered data from A to Z. Specifically, I clicked on the autofilter dropdowns (H1, H2, H3) and selected the Sort option without encountering crashing or other issues.
Could you please try running your scenario in a newer version of Excel and share your feedback with us?
As Amjad has mentioned, in newer version ms excel the file can work fine. However, we do find that the operation causes old versions of ms excel crashed. So we opened the one new ticket(s) in our internal issue tracking system to investigate this issue further and check whether we can solve it(improve the generated xlsx file) at our end. If it can be fixed, the fix will be delivered according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58925
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Hi,
I’ve tried it in Office 2019 as well and I can confirm that, as per Amjad’s message, it does work in Office 2019. We do have clients using both versions, so it would be great if there would be a way for this to work on 2016 as well.
Thanks for opening the ticket - if it’s any help, i’ve noticed that the working version of the xlsx has an additional bit of xml in the underlying .zip - in xl/workbook.xml there’s
<definedNames>
<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Claims '!$B$23:$D$23</definedName>
</definedNames>
which, when copied to the faulty version, allows it to be zipped again and seems to fix the issue.
Thank you for confirming that you did not encounter any issues when using newer versions of MS Excel.
We truly appreciate your insights and would be happy to investigate the matter further to ensure a seamless experience for all your clients, regardless of whether they are using older or newer versions of MS Excel. Thank you once again for sharing your findings with us. We will look into this promptly.
Once we figure it out or we have other updates on it (“CELLSNET-58925”), we will let you know.
We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58925”) has been resolved. The fix/enhancement will be incorporated in our next release (Aspose.Cells v25.9), which is scheduled for the first half of September 2025. You will receive a notification once the new version is released.
The issues you have found earlier (filed as CELLSNET-58925) have been fixed in this update. This message was posted using Bugs notification tool by leoluo