Deugging some broken SpreadsheetML using pivotTables

Maury Markowitz 1

We have a small library that builds SpreadsheetML documents without the use of the Office related libraries (originally to allow porting). I have been trying to add PivotTable/PivotCache support without luck, all of my attempts result in Excel reporting Unreadable Content and failing to fix it, and thus reporting nothing useful.

To try to debug this, I'm decompressing the file from my library and using an XML diff to compare it to one I created manually in Excel. This is not turning up anything very interesting, and the differences I do find do not seem to be important (attributes out of order, ID's being in a different order, etc.)

I am wondering if there are any tools that may provide more information on the actual problems that might help be debug these sorts of issues? Something that tries to load the file "like excel does" and reports the actual issues? Is there perhaps some way to get more detail within Excel?

Sreekanth Nadendla 411 Reputation points Microsoft Employee

2022-10-06T19:27:56.487+00:00

Hello Maury Markowitz, thank you for your question about Microsoft Office Open Specifications. We have created an incident to track the investigation for this issue. One of the team members will review your question and contact you shortly.

Regards,
Sreekanth Nadendla
Microsoft Windows Open Specifications
Tom Jebo 1,996 Reputation points Microsoft Employee

2022-10-06T23:56:01.13+00:00

Hi @Maury Markowitz ,

Here are some tools you might find useful:

Open XML SDK Productivity tool
OOXML Viewer VSCode Extension
Open XML SDK Validator method
Validate a word processing document (Open XML SDK)

If it were me, I would start by validating the resulting package (from your library) and see what errors it calls out. At the same time, I would try to open in Excel and if Excel offers to repair, accept the repair as a new document and then compare the two.

It can be tedious to find errors in your package but typically if you use a methodical or elimination approach, you can find out what caused the problem. I should also add that you may want to inspect relationships and part names very carefully as well as other elements and attributes. Diffing the repaired copy can find these as well.

Best regards,
Tom Jebo
Sr Escalation Engineer
Microsoft Open Specifications Support
Maury Markowitz 1 Reputation point

2022-10-28T15:39:57.04+00:00

Hi Tom, I did not receive a notification of this reply so I'm just seeing it now.

I downloaded the Open XML SDK Productivity Tool and used Validate. It reports the files are fine. It validates many files that will not open in Excel without errors (not mine, lots of others I found on the 'net). I also tried adding the Validator method to my own code, and it reports no errors. It seems they don't do whatever Excel is doing.

The Tool does include a useful DIFF function. Using this I was able to find a number of minor differences. Simply reading the file in and then writing it out again would result in working pivots, but it would also make minor changes as noted above; IDs might change, the order of items and attributes, etc., but none of these cause problems on their own. If there are differences in the rels, which I too believe is the problem, these minor differences hide them.

I have created a VERY simple file, 13k that demonstrates this problem. Try as I might, I cannot find anything in the file that is broken. Can you suggest any other tool that might help me?
Tom Jebo 1,996 Reputation points Microsoft Employee

2022-10-28T17:44:31.987+00:00

@Maury Markowitz ,

At this point, I can take a look at the simple repro file from the file format perspective to see what Excel might be complaining about. Can you share the simple repro file with me?

Tom
Tom Jebo 1,996 Reputation points Microsoft Employee

2022-11-02T18:23:09.463+00:00

For the sake of the community here, we are discussing this on the Open XML SDK discussion list here:

https://github.com/OfficeDev/Open-XML-SDK/discussions/1220

The sample repro files have been shared and analyzed.
Tom Jebo 1,996 Reputation points Microsoft Employee

2022-11-03T20:21:44.613+00:00

@Maury Markowitz ,

I responded on your discussion on github. Copying here for reference:

is the "one pivot one slicer out.xlsx" supposed to be the before file? Excel reports that line 2, column 521 is a problem. When I look at that location, in slicerCache1.xml, there is an extra "<" at that position. If I remove that, it opens. But perhaps that's not the problem you're trying to solve?

Tom

Share via

Deugging some broken SpreadsheetML using pivotTables

Your answer