Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

python - Pandas read_excel - returning nan for cells having formula

I have an excel file that contains accounting data and also the file use formula for some of the cells. When I use pandas read_excel to read the values in the file, it returns nan value for cells having formula's. I have also used openpyxl, but still having the same issue.

Is there any way to read values instead of formula for cells having formula.

I have also attached the xlsx file used.

https://drive.google.com/file/d/1aOTYwtKTrqyjF16vDizzzvMkUsvdgRe1/view?usp=sharing

Thanks...

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Before working with your excelsheet make sure you have set the permissions of your excel file to read and write,if it is a read only file,then change to read-write.

import pandas as pd
your_data = pd.read_excel('yourfile.xlsx',sheet_name='your_sheet_name')
print(your_data) #checking
your_data.dropna(inplace=True)
your_data.rename(columns = {'Unnamed: 1':'Total'},inplace =True) 
print(your_data['Total'].tolist()) #The column name where your formula is being calculated.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...