Reading cached external fields from excel file, results different than what I see in excel

Question

I am trying to read an excel file using xlrd in python3, but I am not getting the results from reading the cells as I do see them in excel.

It seems the cells I am trying to access have some external source set, from which they are refreshed, but since that source is unavailable to me, excel displays some cached values. These values reside at some other place in the same excel file, so while in the sheet you see e.g. "2.65111" the actual contents of the cell shows up as "=BC12" in this edit bar in excel.

When I try to read this via xlrd like:

wb=xlrd.open_workbook("filename.xls",formatting_info=True)
wb.sheet_by_name("sheetname").cell(12,9)

The cell contents shows up as:

'text:'[XXXX]' (XF:95)'

(where XXXX is most likely the name of a column of this external .csv file)

I would either expect xlrd to return the "2.65111" I see in excel, or at least the "=BC12" as a reference to the other cell, but I can not use in any way the "[XXXXX]" thing.

Is there a way to get xlrd to show any of these two?

I am really confused as to where it has this "[XXXXX]" from, and why this is so different to what I see in excel.

PS: I can not just use the data from the BC12 cell, as this should be an automated processing of files, and only the "original" cells have a fixed position, and these others can be just at another random position the next day.

Answer 1

One solution might be to make a copy of your excel sheet that ONLY contains values, no formulas or external sources.

You can do that by highlighting and copying everything, then 'paste special' into a new sheet, and select 'values only' or something like that.