Interest in ignore errors feature

[Chi Ho Kwok]

We tend to dump a lot of number like text into cells and Excel loves to highlight that "0222" or "31123456789" indeed looks like a number, but if you let it do that, it turns into 222 or 3.11E+10. Especially long numbers get messed up by rounding, like phone numbers and SIM card ICCIDs.

To fix those warnings, we monkey patched Openpyxl to enable an unfinished feature, openpyxl.worksheet.errors.IgnoredErrors.

https://gist.github.com/chkwok/f8a04a4aba9e9fe577fb043ab1899c6e

The actual patch is just to hook WorksheetWriter.write_tail to dump out an IgnoredErrors node, that we set to with

self.worksheet.ignored_errors = IgnoredErrors(
ignoredError=(IgnoredError(sqref=self.worksheet.dimensions, numberStoredAsText=True),))

to ignore all numbers stored as text.

The IgnoredError.sqref definition is wrong (sqref = CellRange, no call) and to fix that, you need to redeclare the class as the metaclass does some magic on class creation.

The current "API" is super low level so it isn't production ready, but if there is enough interest, maybe we can build it out together to integrate this properly upstream. It mostly needs an API design, after that, creating the right IgnoredError(s) node and adding that to write_tail is simple.

Kind regards,

Chi Ho Kwok

[Charlie Clark]

On 21 Dec 2021, at 12:15, Chi Ho Kwok wrote:

> We tend to dump a lot of number like text into cells and Excel loves to
>
> highlight that "0222" or "31123456789" indeed looks like a number, but if
>
> you let it do that, it turns into 222 or 3.11E+10. Especially long numbers
>
> get messed up by rounding, like phone numbers and SIM card ICCIDs.

By this I guess you mean implementing the ignoredErrors element in worksheets?

That would be pretty straightforward to submit a PR, for all modes. But I'm not sure about the usefulness. It's almost always better to tell Excel to treat these values as text and this is probably simpler-

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Charlie Clark]

On 21 Dec 2021, at 15:22, Chi Ho Kwok wrote:

> Yes indeed, by implementing worksheets.ignoredErrors, but with an API nicer
> than passing an instance of IgnoredErrors.
>
> I already tell Excel that it is a text value, by calling setting cell.value
> to a str of "0123", but then whole columns get marked with a green arrow:
>
> "Number stored as text" and a helpful hint to convert it to number; an
> example: [https://imgur.com/QcTM8hm](https://imgur.com/QcTM8hm)
>
> By serializing an <ignoredErrors><ignoredError sqref="A1:P601"
> numberStoredAsText="1"/></ignoredErrors> in the worksheet for the right
> range or the whole sheet, you prevent these green arrows from showing up.
>
> This tells Excel that we know these text looks suspeciously like a number
> but no, leave it alone please.

Excel really is braindead in many situations like this, as this part of the specification suggests. It really should be possible to have a suitable number format and be done. This would still let Excel make suggestions when converting from text.

> While searching for references, I noticed that the same feature request was
> filed and implemented in xlsxwriter
>
> @ [https://github.com/jmcnamara/XlsxWriter/issues/678](https://github.com/jmcnamara/XlsxWriter/issues/678); yup, same use case,
>
> long numbers that are identifiers get butchered in Excel if aren't careful
> and you don't want the green arrows.
>
> Do you have a complete pull request (different modes, tests?) that I can
> use as a reference and a starting point?

Fortunately, the Serialisable stuff is dead easy to write tests for.

Otherwise you might want to look at 409, but it should be fairly straightforward. Base your work on the 3.1 branch and we can discuss details as you go.