Openpyxl, formulae and cell references

This is the place for queries that don't fit in any of the other categories.

Openpyxl, formulae and cell references

Postby GSD4ME » Sat Sep 17, 2016 3:35 pm

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!
Many thanks
Posts: 11
Joined: Sat Jul 25, 2015 4:39 pm

Re: Openpyxl, formulae and cell references

Postby buran » Sat Sep 17, 2016 4:54 pm

631 words and not a single line of code?
worksheet names in excel are not case sensitive by the way...
We are moving to as of 1 October. Did you register there?
Posts: 372
Joined: Tue Apr 08, 2014 4:53 pm
Location: Bulgaria, EU

Re: Openpyxl, formulae and cell references

Postby GSD4ME » Sat Sep 24, 2016 12:00 pm

If you look and read, there is code present (where I describe the situation as A=B)

I asked for a technical explanation and possible fix for the problem regarding inserted formulae into a cell, hence the explanation - has anyone tried to replicate this?
Posts: 11
Joined: Sat Jul 25, 2015 4:39 pm

Re: Openpyxl, formulae and cell references

Postby micseydel » Sat Sep 24, 2016 6:28 pm

GSD4ME wrote:has anyone tried to replicate this?

Can you provide a minimal, runnable snippet of code to reproduce the problem?
Due to the reasons discussed here we will be moving to on October 1, 2016.

This forum will be locked down and no one will be able to post/edit/create threads, etc. here from thereafter. Please create an account at the new site to continue discussion.
User avatar
Posts: 3000
Joined: Tue Feb 12, 2013 2:18 am
Location: Mountain View, CA

Return to General Coding Help

Who is online

Users browsing this forum: Google [Bot] and 10 guests