dotnet / dotnet/Open-XML-SDK

OpenXmlValidator rejects a valid CT_Filters holding both filter and dateGroupItem

Open
#2,128 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
4.6k
Forks
605
PR merge metrics
No merged PRs in 30d

Description

**Describe the bug**

`OpenXmlValidator` reports `Sch_UnexpectedElementContentExpectingComplex` for a `x:filters` element containing both `filter` and `dateGroupItem` children. That content is valid: ECMA-376 defines `CT_Filters` as an `xsd:sequence` of `filter [0..*]` followed by `dateGroupItem [0..*]`, not a choice.

It matters because this is the shape Excel writes for a mixed tick-box selection on a date column — a fully ticked month or year becomes a `dateGroupItem`, individually ticked dates become `filter` entries. Validating a genuine Excel-produced workbook therefore reports an error that is not there.

**To Reproduce**

Self-contained console app, no document or external resources needed — the element validates on its own.

```csharp
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Validation;

var filters = new Filters();
filters.Append(new Filter { Val = "Cookies" });
filters.Append(new DateGroupItem { DateTimeGrouping = DateTimeGroupingValues.Year, Year = 2024 });

Console.WriteLine(filters.OuterXml);

foreach (var error in new OpenXmlValidator(FileFormatVersions.Office2007).Validate(filters))
Console.WriteLine($"{error.Id}: {error.Description}");
```

Output:

```xml

Sch_UnexpectedElementContentExpectingComplex: The element has unexpected child element 'http://schemas.openxmlformats.org/spreadsheetml/2006/main:dateGroupItem'.
```

**Observed behavior**

Any mixture of the two child kinds is rejected; either kind alone, repeated, is accepted. Reproduced on all seven `FileFormatVersions` values (`Office2007` through `Microsoft365`).

| `filters` content | Validator | ECMA-376 |
|---|---|---|
| `filter` ×1 | valid | valid |
| `filter` ×2 | valid | valid |
| `dateGroupItem` ×1 | valid | valid |
| `dateGroupItem` ×2 | valid | valid |
| **`filter` then `dateGroupItem`** | **rejected** | **valid** |
| `dateGroupItem` then `filter` | rejected | invalid (out of sequence order) |

Only the fifth row is a false positive. The last row is correctly rejected, though as a choice violation rather than an ordering one.

**Expected behavior**

`filter*` followed by `dateGroupItem*` validates.

**Root cause**

`data/schemas/schemas_openxmlformats_org_spreadsheetml_2006_main.json` on `main` models the particle as a `Choice`:

```json
{
"Name": "x:CT_Filters/x:filters",
"ClassName": "Filters",
"Particle": {
"Kind": "Choice",
"Items": [
{ "Name": "x14:CT_Filter/x14:filter", "Occurs": [{}], "InitialVersion": "Office2010" },
{ "Name": "x:CT_Filter/x:filter", "Occurs": [{}] },
{ "Name": "x:CT_DateGroupItem/x:dateGroupItem", "Occurs": [{}] }
]
}
}
```

For contrast, `CT_FilterColumn` in the same file is correctly a bounded `Choice` (`"Occurs": [{ "Max": 1 }]` on the group), which matches its XSD — so the format does distinguish the two cases and this entry looks like a straightforward data error rather than a modelling limitation.

I have not proposed a patch because the SDK's model also carries `x14:CT_Filter/x14:filter` as a member, which has no counterpart in the ECMA sequence. How that extension composes with a corrected `Sequence` seemed better left to maintainers than guessed at.

**Schema references**

- [`CT_Filters`](http://www.datypic.com/sc/ooxml/t-ssml_CT_Filters.html) — `Sequence [1..1]` of `filter [0..*]`, `dateGroupItem [0..*]`, with a sample instance showing both
- The [`Filters` class reference](https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.filters), quoting ISO/IEC 29500-1, lists both `dateGroupItem` and `filter` as child elements with no choice constraint

**Desktop**

- OS: Windows 11 Pro 10.0.22631
- .NET Target: .NET 8.0 (also reproduces on .NET 11 preview)
- DocumentFormat.OpenXml Version: 3.5.1 (current latest on NuGet); particle data unchanged on `main`

**Additional context**

Found while adding schema-validation coverage to [XLibur](https://github.com/XLibur/XLibur). I had used `OpenXmlValidator` as the authority on the schema and filed an issue against our own writer on the strength of it; the report was wrong and the validator was the thing at fault. ClosedXML has carried a test comment noting this same false positive, with validation disabled for that one case, for several years.

I searched open and closed issues for `dateGroupItem`, `CT_Filters`, `Sch_UnexpectedElementContentExpectingComplex` and `autofilter` and did not find a duplicate; #1218 is a different false positive in presentations.

Contributor guide

Open the contributing guide

Research direction

Start with data/schemas/schemas_openxmlformats_org_spreadsheetml_2006_main.json and the Filters model, then run the self-contained validator example from the issue. Compare the CT_Filters particle with the cited ECMA-376 sequence and inspect how the x14 filter member is represented. Done means filter children followed by dateGroupItem children validate, while the reverse order remains invalid, with regression coverage added.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
devtools
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.