OpenXml XLSX: Special XML characters (<"&) are not encoded properly...

In XLSX files that contain a "table1.xml" within the internal zip structure, if the <tableColumn>'s name attribute contains an apostrophe in the translation, it's escaped an extra level (&apos; instead of simply '). This causes the translated file to not open correctly in Excel post-translation (though I'm not sure why the extra escape causes an error rather than simply incorrect display).I'm going to try to scrub client-specific text from one of the files that has the issue, run it through "translation", and get those files to you. I'm also going to see if this happens with other special XML characters - <"& - as well as the apostrophe.

Where the cell strings themselves are stored, it's escaped correctly. It's where the table column headers are stored that the strings get the extra escapes. What likely happens is this.
Excel sees that the table column header strings don't match the cell strings. This is a problem.
If you choose to repair it, Excel likely just replaces the table header strings with the cell strings.So my guess is that the problem is happening where the table column header strings are being written out. To put it into pseudo-code, this is what I imagine is happening:

CellString = xmlEscape(translatedString); //This is fine. One level of escape protects the "special" characters.
...
TableHeaderString = xmlEscape(CellString); //This is not fine. Too many escapes.

‌

Comments (2)