Can anyone help me here please because this one is causing a lot of swearing!
I am using openpyxl to do some data manipulation on a workbook.
The workbook has several sheets of raw data and I do a 2-phase analysis of the data – phase 1 reads in the data and outputs some basic ‘facts’, phase 2 does detailed analysis, each phase producing summary sheets. The names of the worksheets to be created are held in global variables and all have the format “__xxXxxxYxx__”. Note the double underscores at start and end and the upper case letters in the name – purely for my usage and the have no meaning beyond readability
At the start of the program, all sheets starting with “__” are recognised as being mine and are deleted and are dynamically re-created as required when the program is running, so upon exit the (eg) sheet “__phase1SheetNumber2__” is not the exact same sheet of the same name that was present when the workbook was opened.
There is a final worksheet “__summaryWorksheet__” which is filled with data that comprises references to cells of data in the other worksheets:
So my python code generates such statements in the summary cells as:
mySummarySheetPointer.cell (row = x, column = y).value = “=” + whatEverSheet + “.” + whatEverSheetPointer.cell (row = n, column = m).coordinate
(call this A=B for simplicity) and whatEverSheet has the format specified above: “__xxXxxxYxx__”
However, what is actually being placed in the cell A is “__whateversheet__” instead of “__whatEverSheet__” (note case variation) and so the cell contents only contain the Excel error “#NAME?” when the workbook is opened after the analysis is completed. Also – the cell coordinate calculated above appears in lower case in the formulae.
If I rename the “whatever” sheet to be the same case as the formulae references, no change is made to the cells, so no effect.
If I change the case of the cell coordinates (so I have a ‘wrong’-case sheet reference but an upper-case cell reference) then it works and the sheet reference auto-corrects (ie __whateversheet__.a6, corrected manually to __whateversheet__.A6, then the value is correct and the reference becomes __whatEverSheet__.A6)
If I manually change the worksheet name in the formulae to be correct, the whole thing works and the correct data value appears in the cell
In summary, changing the case of either side of the formulae references (manually) sorts everything out.
If I change my program to force an .upper() call on the cell coordinate, then the output is STILL in lower case and so still doesn't work
If I rename all the sheet types by removing the leading “__” characters (but leaving the trailing ones), the problems still persist
If I leave the leading “__” characters but remove the trailing ones, then the problems still persist as well
I haven’t tried removing both leading and trailing underscores as that means a fiddly re-write of some of the code which I don’t want to do at the moment.
I have played around with the output generated by the Python program and it only changes the case when the output to the cell A is set to contain a formula – if various combinations are output simply as a string, the case is correct.
Can anyone explain what is going on here as I either can’t see the problem for looking at it, or it is way beyond my knowledge of how Python or openpyxl operate.
Is it something to do with the double underscore characters (“specials” in the language I know) being part of the worksheet name and being output as part of the A=B statement above? If so – can anyone explain why and is the change being forced by Excel or Python in the output?
I look forward to a rational explanation!