Defined-name lookups by name should be case-insensitive

Issue #1270 new
MattS created an issue

In openpyxl/workbook/defined_name.py there is currently a class DefinedNameList that represents a set of Excel defined names, with several methods (get, delete etc) that operate on such defined names by their string name.

Each of these methods looks up the defined name using a case-sensitive == comparison. Thus, a call to workbook.defined_names.get("FooBar") will not retrieve the defined name with name FoObAr.

According to this brief note hidden within the Excel online documentation, defined names' names are case-insensitive:

https://support.office.com/en-us/article/names-in-formulas-fc2935f9-115d-4bef-a370-3aa8bb4c91f1

Additionally, when I define a formula in LibreOffice Calc for Linux that refers to a defined name using the defined name’s exact, case-sensitive name, then use OpenPyXL to read the resulting spreadsheet file, the formula that I read out of that cell contains the defined name’s name in all uppercase. I know of no way to defeat this unrequested case conversion by LibreOffice Calc.

Since the Excel documentation says names are case-insensitive, and since at least one implementation (LibreOffice Calc) changes the case of defined names' names in ways that seem difficult for the user to defeat, I think the code in openpyxl/workbook/defined_name.py that deals with such names should do so case-insensitively.

Comments (2)

  1. CharlieC

    Thanks for the note. The official OOXML specification does not actually cover case-sensitivity so any behaviour is implied. I can understand why LO behaves that way as it removes any ambiguity, but it is a little unfortunate as can be seen from your use case.

  2. MattS reporter

    Aha, I had failed to check the OOXML specification – thanks, that’s good to know. So OpenPyXL would be compliant with the specification if it were to become case-insensitive, is that right?

    Independently of case sensitivity or otherwise, I think it would be best if case were preserved, in all software that reads/writes xlsx files. If nothing else, I think my range name looks much prettier Capitalised_Like_This than whispering_like_this or (worse) SHOUTING_LIKE_THIS 😊

    So rather than upper- or lower-casing the names on input to OpenPyXL (in which case iterating over the names would retrieve them in a potentially different case), I’m envisaging performing a full Unicode case-insensitive string comparison each time names are compared, instead of the current == test.

    That would be slower than canonicalising and upper (or lower) casing each name once on input, then simply using == as currently, but I think the additional CPU is worth it to preserve case.

  3. Log in to comment