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.
我试图在python3中使用xlrd读取一个excel文件，但我没有从读取单元格得到结果，因为我在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.
我试图访问的单元格似乎有一些外部源集，从中刷新它们，但由于该源不可用，excel会显示一些缓存值。这些值位于同一个excel文件中的其他位置，因此在工作表中您会看到例如“2.65111”单元格的实际内容在excel的此编辑栏中显示为“= BC12”。
When I try to read this via xlrd like:
当我尝试通过xlrd读取此内容时：
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)
（其中XXXX很可能是此外部.csv文件的列的名称）
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.
我要么期望xlrd返回我在excel中看到的“2.65111”，或者至少将“= BC12”作为对其他单元格的引用，但我不能以任何方式使用“[XXXXX]”的东西。
Is there a way to get xlrd to show any of these two?
有没有办法让xlrd显示这两个中的任何一个？
I am really confused as to where it has this "[XXXXX]" from, and why this is so different to what I see in excel.
我真的很困惑这个“[XXXXX]”来自哪里，以及为什么这与我在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.
PS：我不能只使用BC12单元格中的数据，因为这应该是文件的自动处理，只有“原始”单元格具有固定位置，而其他单元格可能在第二天处于另一个随机位置。
3 个解决方案
#1
1
One solution might be to make a copy of your excel sheet that ONLY contains values, no formulas or external sources.
一种解决方案可能是制作仅包含值，无公式或外部源的Excel工作表的副本。
You can do that by highlighting and copying everything, then 'paste special' into a new sheet, and select 'values only' or something like that.
您可以通过突出显示并复制所有内容，然后将“粘贴特殊”粘贴到新工作表中，然后选择“仅限值”或类似的内容来实现。
#2
0
"the 12,9 cell" is known to Excel as J13 (NOT J12). Are you sure that you are comparing like with like? Is it expected that J13 would contain a reference to BC12? Can you deduce anything by examining cells around J13 and BC12?
“12,9单元”在Excel中称为J13（不是J12）。你确定你喜欢比较吗？是否预计J13会包含对BC12的引用？你能通过检查J13和BC12周围的细胞来推断出任何东西吗？
#3
0
It may be that your only option is to preprocess the file with the actual Excel through Excel COM API. You can do something simple in .NET which will load the excel file, get the data and save it in a similary structured excel file but with no external references. Loading an excel file with Excel takes a long time (a few seconds) and will certainly fail if you overload it on a server, but if you have a few files a day, it will work. Of course it won't work if you are not running on Windows.
可能您唯一的选择是使用Excel COM API通过实际Excel预处理文件。你可以在.NET中做一些简单的事情，它将加载excel文件，获取数据并将其保存在类似的结构化excel文件中，但没有外部引用。使用Excel加载excel文件需要很长时间（几秒钟），如果在服务器上重载它肯定会失败，但是如果你每天有几个文件，那么它将起作用。当然，如果您不在Windows上运行，它将无法工作。
#1
1
One solution might be to make a copy of your excel sheet that ONLY contains values, no formulas or external sources.
一种解决方案可能是制作仅包含值，无公式或外部源的Excel工作表的副本。
You can do that by highlighting and copying everything, then 'paste special' into a new sheet, and select 'values only' or something like that.
您可以通过突出显示并复制所有内容，然后将“粘贴特殊”粘贴到新工作表中，然后选择“仅限值”或类似的内容来实现。
#2
0
"the 12,9 cell" is known to Excel as J13 (NOT J12). Are you sure that you are comparing like with like? Is it expected that J13 would contain a reference to BC12? Can you deduce anything by examining cells around J13 and BC12?
“12,9单元”在Excel中称为J13（不是J12）。你确定你喜欢比较吗？是否预计J13会包含对BC12的引用？你能通过检查J13和BC12周围的细胞来推断出任何东西吗？
#3
0
It may be that your only option is to preprocess the file with the actual Excel through Excel COM API. You can do something simple in .NET which will load the excel file, get the data and save it in a similary structured excel file but with no external references. Loading an excel file with Excel takes a long time (a few seconds) and will certainly fail if you overload it on a server, but if you have a few files a day, it will work. Of course it won't work if you are not running on Windows.
可能您唯一的选择是使用Excel COM API通过实际Excel预处理文件。你可以在.NET中做一些简单的事情，它将加载excel文件，获取数据并将其保存在类似的结构化excel文件中，但没有外部引用。使用Excel加载excel文件需要很长时间（几秒钟），如果在服务器上重载它肯定会失败，但是如果你每天有几个文件，那么它将起作用。当然，如果您不在Windows上运行，它将无法工作。