ClosedXML / ClosedXML/ClosedXML

Pivot table - support groups fields

Open
#2,130 12 comments 1 reaction 0 assignees View on GitHub
area:load/save enhancement feature:pivot-table triaged
Dominant language
C#
Stars
5.7k
Forks
933
Avg merge
12h 14m
Merged PRs (30d)
1

Description

**Do you want to request a *feature* or report a *bug*?**
- [x] Bug
- [ ] Feature
- [ ] Question

**Did you test against the latest CI build?**
- [ ] Yes
- [x] No

If you answered `No`, please test with [the latest development build](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) first.

I cloned and ran locally via ClosedXML.Sandbox.

**Version of ClosedXML**

0.102.0

**What is the current behavior?**

Exception during load of a vendor provided spreadsheet.
```
System.ArgumentOutOfRangeException: The column 'xyz' does not appear in the source range. (Parameter 'sourceName')
at ClosedXML.Excel.XLPivotFields.Add(String sourceName, String customName)
at ClosedXML.Excel.XLPivotFields.Add(String sourceName)
at ClosedXML.Excel.XLWorkbook.LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet)
at ClosedXML.Excel.XLWorkbook.LoadSheets(Stream stream)
at ClosedXML.Excel.XLWorkbook.Load(Stream stream)
at ClosedXML.Excel.XLWorkbook..ctor(Stream stream, LoadOptions loadOptions)
at ClosedXML.Excel.XLWorkbook..ctor(Stream stream)
...
```

**What is the expected behavior or new feature?**

Spreadsheet is loaded.

**Is this a regression from the previous version?**

Yes. Works in 0.101.0

## Reproducibility

**Code to reproduce problem:**
The exception happens during the load of an existing file with a lot of pivot tables and connections. I'm sorry but a code sample isnt really helpful here.
```
new XLWorkbook("file")
```

- [ ] I attached a sample spreadsheet. (You can drag files on to this issue)

I dont have enough Excel experience to recreate this issue myself and I cant attach the file from the vendor.

---

I did spend a bit troubleshooting in the code and noticed a couple things.

At the previous commit, there was a check that the field is available before trying to add it in several places:

https://github.com/ClosedXML/ClosedXML/blob/69e37ad5f9d936c67d259e46582374930a23dd5f/ClosedXML/Excel/XLWorkbook_Load.cs#L714-L720

I assume this is what avoided the exception that is currently being thrown in `XLPivotFields.Add`.

---

When i skipped the bad adds in troubleshooting, I also ran into an exception where wss is null in ParsePivotSourceReference:

https://github.com/ClosedXML/ClosedXML/blob/9cded9044050523f87567271d72b382505ec2c2e/ClosedXML/Excel/XLWorkbook_Load.cs#L913-L920

`wss` can be null with external sources so it needs to return early.

Contributor guide

Open the contributing guide

Research direction

Start in ClosedXML/Excel/XLWorkbook_Load.cs at the pivot-field loading code around the referenced lines, then inspect XLPivotFields.Add and ParsePivotSourceReference. Reproduce with new XLWorkbook("file") where possible; done means workbooks with missing pivot fields or external sources load without these exceptions, though the vendor file is unavailable.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.