The problem here is that Excel generates invalid XML which is why it's the underlying libraries have problems. I don't propose to solve this in openpyxl but we could at least document a helper function that can perform any necessary substitutions.
Do you mean syntactically invalid? I dug into my .xlsx files and don't find any syntax errors.
Continuing my comment here about not wanting to break my code for some users while fixing it for others. Here is a simple example of something that can be distinguished using Excel, but not using openpyxl. Using post-processing like this unescape function will not allow that distinction to be made:
Excel is able to distinguish the unicode character from a string that looks like _x0001_, so it must be possible to get that data from the .xlsx file. Excel seems to use the "sharedStrings.xml" file for that, with t="s" (not t="str") on the cell:
The problem is that this is non-standard and undefined escaping.
sharedStrings.xml is what Excel always used so that strings could be implemented using pointers. But it's optional, and makes streaming more complicated, which is why many libraries use inline strings.
Just to be clear, we're not asking for openpyxl to support writing to sharedStrings.xml, only reading it properly. When writing out, inline strings are fine.
I'm revisiting this after #2099. I think I understand a little more what might be happening. Some libraries seem to using unicode codepoints as opposed to characters fairly arbitrarily and the codepoints themselves are being escaped in XML.
Looking at the specification I think it's fine for openpyxl to do this most of the time escaping is only supposed to be used for control characters:
"""
For all characters which cannot be represented in XML as defined by the XML 1.0 specification, the characters are escaped using the Unicode numerical character representation escape character format xHHHH, where H represents a hexadecimal character in the character's value. [Example: The Unicode character 8 is not permitted in an XML 1.0 document, so it must be escaped as x0008. end example]
This simple type's contents are a restriction of the W3C XML Schema string datatype.
"""
But, in this case, we should be aware of the escaping convention, though I'm not sure we will implement when serialising and may continue to raise exceptions: using control characters in Excel files doesn't seem to be a good idea.
For control characters, I think a mapping might make most sense and we can drop down to a parser for other escaped strings such as _x0033 instead of 3.
The code also needs to be able to differentiate between the escaped representation and the string that would look the same. Yuck!