"valid" OOXML document opens with errors in Excel

[maurymarkowitz]

Please forgive me for posting here, but this is the closest thing to a forum for Open Office I could find.

I am maintaining a large library built on System.IO.Packaging - we would have used the SDK had it existed at the time. It builds Excel workbooks by reading the package, rebuilding the workbook and worksheet Parts with new data, and then writing it all out again. This way you can have a book that acts as a template with things like charts or images, and we can add new sheets of data. That does cause many minor changes, like the addition of xmlns attributes, reordering, changing rIds etc, but these have never caused an issue.

A customer has sent us a book that does not work. The original book opens without error, but when we read and write the resulting file returns errors when you open it in Excel. The book includes a slicer on a pivot table, and when you open it, Excel complains that there is "unreadable content" and deletes the slicer and everything related to it.

tomjebo suggested I look at Open XML Productivity Tool. When asked to Validate, it reports no errors. I also used the Validator method from Open XML SDK in my code, and it also reports no errors. I assume they both call the same underlying code.

The Tool has a useful DIFF feature, but there are so many minor changes it's difficult to find anything "real". I can see that the slicer and slicerCache are identical before and after writing, as are their IDs. The changes causing the problem would seemingly have to be in the bits we rewrite, the workbook and sheets, but their changes are all minor and the entry in ExtLst does seem valid. All of the Targets I can find related to it seem to be correct.

I strongly suspect the issue has to do with a TargetURI or _rels pointing into space, or maybe a wrong ID or such. Can anyone suggest any way to track down these sorts of errors? Not with slicers in particular, but any sort of "you have a reference to rId=7 in your workbook but that does not exist in _rels" would make life a whole lot easier!

[tomjebo]

Hi @maurymarkowitz,

I saw your post on Q&A and will take a look at the simple repro file. If you want to provide the file here.

[maurymarkowitz]

Well in the time since I posted I **think** I found the issue. The reason I couldn’t find it is that it was one of the items that *didn’t* change! The original slicerCache points to something called “tabId=3”, which really should be called “sheetId”! In any event, because Packaging re-numbers things some times, this is actually on sheet 1 now, but because I didn’t change the slicerCache, just read it and wrote it back, it still have tabId=3 for what is now tabId=1. I’m pretty sure that’s the issue anyway, if not I’ll send you the file, it’s only 13k.

…

On Oct 28, 2022, at 1:48 PM, Tom Jebo ***@***.***> wrote: Hi @maurymarkowitz <https://github.com/maurymarkowitz>, I saw your post on Q&A and will take a look at the simple repro file. If you want to provide the file here. — Reply to this email directly, view it on GitHub <#1220 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABGU4ZZOSERDUS622SXWKDWFQGVFANCNFSM6AAAAAARRHJGPU>. You are receiving this because you were mentioned.

[tomjebo]

@maurymarkowitz do you still need help with this? Was tabId the issue?

[maurymarkowitz]

No luck, I've become very frustrated trying to change that tabId without pulling the entire document apart and putting it back together. But here is a before and after file. Note the after has different r:Ids and ordering, and sharedStrings have been loaded into the XML directly (so there's no sharedStrings.xml) but all of that is known to work. It just this slicer, and I'm only assuming it's because of that tabId.

Thank you for getting back to me, I really appreciate it!

one pivot one slicer.xlsx
one pivot one slicer out.xlsx

[tomjebo]

@maurymarkowitz 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?

[maurymarkowitz]

Tom, I’m so sorry, I only saw this email now for some reason. I have only worked on the slicer issue on and off over the last few weeks. During that time I was able to find that, yes, the tabId was the problem. Since we renumber the WorkSheets on the way to the Package, I had to add some code to change the tabId and that did the trick. Now I’m trying to figure out how to create a Drawing in code, so I can associate it with the slicer. As before, for testing purposes I simply copy a drawing object from a working example and put it in the new file. Unfortunately, when it opens Excel complains and removes the drawing, which makes the slicer non-operational. I’m sure this is a missing reference or similar issue. You said “when I open this in excel”, and you got much more detail than I do. Is this a development version, or is there some way to get additional information from Excel when these issues arise? All of my files pass Verification, so finding issues typically ends up just being long DIFF sessions - and the Tool has proved extremely useful in that regard.

…

On Nov 1, 2022, at 2:40 PM, Tom Jebo ***@***.***> wrote: @maurymarkowitz <https://github.com/maurymarkowitz> 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? — Reply to this email directly, view it on GitHub <#1220 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABGU42BTXVX4B47OBQHHKDWGFPYDANCNFSM6AAAAAARRHJGPU>. You are receiving this because you were mentioned.