pandas 熊猫读取excel值而不是公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41722374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pandas read excel values not formulas
提问by Colton T
Is there a way to have pandas read in only the values from excel and not the formulas? It reads the formulas in as NaN unless I go in and manually save the excel file before running the code. I am just working with the basic read excel function of pandas,
有没有办法让Pandas只读取 excel 中的值而不是公式？除非我在运行代码之前进入并手动保存excel文件，否则它将公式读取为NaN。我只是在使用 pandas 的基本读取 excel 功能，
import pandas as pd
df = pd.read_excel(filename, sheetname="Sheet1")
This will read the values if I have gone in and saved the file prior to running the code. But after running the code to update a new sheet, if I don't go in and save the file after doing that and try to run this again, it will read the formulas as NaN instead of just the values. Is there a work around that anyone knows of that will just read values from excel with pandas?
如果我在运行代码之前进入并保存了文件，这将读取值。但是在运行代码以更新新工作表后，如果我在这样做之后不进入并保存文件并尝试再次运行它，它会将公式读取为 NaN 而不仅仅是值。是否有任何人都知道的解决方法只会从 excel 中使用Pandas读取值？
回答by RobatStats
That is strange. The normal behaviour of pandas is read values, not formulas. Likely, the problem is in your excel files. Probably your formulas point to other files, or they return a value that pandas sees as nan.
这很奇怪。pandas 的正常行为是读取值，而不是公式。很可能，问题出在您的 excel 文件中。可能您的公式指向其他文件，或者它们返回一个值，pandas 将其视为 nan。
In the first case, the sheet needs to be updated and there is nothing pandas can do about that (but read on).
在第一种情况下，工作表需要更新，pandas 对此无能为力（但请继续阅读）。
In the second case, you could solve by setting explicit nan values in read_excel:
在第二种情况下，您可以通过在 read_excel 中设置显式 nan 值来解决：
pd.read_excel(path, sheetname="Sheet1", na_values = [your na identifiers])
As for the first case, and as a workaround solution to make your work easier, you can automate what you are doing by hand using xlwings:
对于第一种情况，作为使您的工作更轻松的变通解决方案，您可以使用 xlwings 自动执行您正在执行的操作：
import pandas as pd
import xlwings as xl
def df_from_excel(path):
app = xl.App(visible=False)
book = app.books.open(path)
book.save()
app.kill()
return pd.read_excel(path)
df = df_from_excel(path to your file)
If you want to keep those formulas in your excel file just save the file in a different location (book.save(different location)). Then you can get rid of the temporary files with shutil.
如果您想将这些公式保留在您的 Excel 文件中，只需将文件保存在不同的位置（book.save（不同位置））。然后你可以用shutil清除临时文件。
回答by jeansergecardinal
I had this problem and I resolve it by moving a graph below the first row I was reading. Looks like the position of the graphs may cause problems.
我遇到了这个问题，我通过在我正在阅读的第一行下方移动一个图表来解决它。看起来图表的位置可能会导致问题。
