python - Pandas read_excel - returning nan for cells having formula

Question

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...

Answer 1

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.