SpreadsheetML -> XLSX: The custom criteria of auto filter could not be null

Aspose.Cells Product Family
1

I have the following file auto-filter.zip (6.5 KB), but here are the steps how to create it from scratch if needed:

  1. Add any text into A1 cell.
  2. Click: Data → Filter.
  3. Click on the arrow in the A1 filter and select: Text Filters > Does Not Equal > OK (it might be reproducible for other filters as well, the important is to leave the value empty).
  4. Save as XLSX.

This is how the filter looks like in sheet1.xml:

<autoFilter ref="A1:B4">
  <filterColumn colId="0">
    <customFilters>
      <customFilter operator="notEqual" val=" "/>
    </customFilters>
  </filterColumn>
</autoFilter>

When I convert this file to SpreadsheetML via Aspose, it succeed and the filter looks like this:

<AutoFilter x:Range="R1C1:R4C2" xmlns="urn:schemas-microsoft-com:office:excel">
  <AutoFilterColumn x:Index="1" x:Type="Custom">
    <AutoFilterCondition x:Operator="DoesNotEqual" />
  </AutoFilterColumn>
</AutoFilter>

Then I want to convert this file back to XLSX, but Aspose throws The custom criteria of auto filter could not be null.

I’d expect that Aspose is consistent and does not throw this error when converting back to XLSX, since it works when converting to SpreadsheetML.

2

@tomasbelusky

Summary
When an XLSX file contains a custom auto‑filter with the Does Not Equal operator and the criterion is left blank, Excel writes the filter as <customFilter operator="notEqual" val=" " /> (a single space character). During conversion to SpreadsheetML Aspose.Cells omits the val attribute, so the in‑memory CustomFilter ends up with a null value. When the workbook is later saved back to XLSX the library validates that a custom‑filter value cannot be null and throws the exception “The custom criteria of auto filter could not be null.”.

Why it happens

Step What Excel stores What Aspose.Cells writes Result
Create a Does Not Equal filter with an empty criterion <customFilter operator="notEqual" val=" " /> (space) <customFilter operator="notEqual" /> – val omitted CustomFilter.Value becomes null
Load the generated SpreadsheetML back into a Workbook – The filter’s Value remains null Saving to XLSX triggers the internal check if (filter.getValue() == null) throw … and the exception is raised

The root cause is a mismatch between how Excel represents an empty criterion (a space) and how Aspose.Cells serialises it in SpreadsheetML.

What to do next

  1. Check your Aspose.Cells version – many XML‑serialization issues were fixed in recent releases. Upgrade to the latest version and retest.
  2. Patch the missing value after loading the SpreadsheetML:
Workbook wb = new Workbook("auto-filter.xml");
AutoFilter af = wb.getWorksheets().get(0).getAutoFilter();
FilterColumn col = af.getFilterColumns().get(0);
CustomFilter cf = col.getCustomFilters().get(0);
if (cf.getValue() == null) {
cf.setValue(" "); // preserve the space Excel uses for an empty criterion
}
wb.save("result.xlsx");
  1. Force Aspose.Cells to emit empty <customFilter> elements when saving to SpreadsheetML (if the option exists in your version):
XmlSaveOptions opts = new XmlSaveOptions(SaveFormat.SpreadsheetML);
// look for a property such as ExportCustomFilters or PreserveEmptyCustomFilters
opts.setExportCustomFilters(true);
wb.save("auto-filter.xml", opts);

Keeping the val attribute (even as a space) prevents the null value on round‑trip.
4. If the problem persists, open a support ticket with Aspose:

  • Include the exact Aspose.Cells version.
  • Attach the original XLSX, the generated SpreadsheetML, and a minimal code snippet that reproduces the error.
  • Provide the full stack trace.
    The Aspose engineering team can confirm whether this is a version‑specific bug and may supply a fix.

Quick checklist

  • [ ] Verify you are using the latest Aspose.Cells for Java/.NET.
  • [ ] If not, upgrade and repeat the conversion.
  • [ ] Load the SpreadsheetML and inspect customFilter.getValue().
  • [ ] If it is null, set it to a single space (" ") before saving to XLSX.
  • [ ] Enable any export option that preserves empty custom‑filter values when saving to SpreadsheetML.
  • [ ] If the exception still occurs, submit a detailed support request to Aspose.

Note: The above explanation is based on typical behavior of Aspose.Cells with custom auto‑filters. The provided evidence does not contain a specific reference to this exact scenario, so you may need to verify the steps in your environment or contact Aspose support for confirmation.

:warning: I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.

3

@tomasbelusky,

Thanks for the template XLSX file and details.

I was able to reproduce the issue as you mentioned by using your template XLSX file and following sample code. I found an exception “The custom criteria of auto filter could not be null” when loading the converted SpreadsheetML file via Aspose.Cells’ object model.

Workbook wb = new Workbook("d:\\files\\auto-filter.xlsx");
SpreadsheetML2003SaveOptions opts = new SpreadsheetML2003SaveOptions();
wb.save("d:\\files\\auto-filter.xml", opts);
wb = new Workbook("d:\\files\\auto-filter.xml", new LoadOptions(LoadFormat.SPREADSHEET_ML));//exception
wb.save("d:\\files\\auto-filter1.xlsx");

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46550

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.

4

@tomasbelusky,

This is to inform you you that your issue (Ticket ID: “CELLSJAVA-46550”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v25.12) that we plan to release in the first half of December 2025. You will be notified when the next version is published.