CODEWITHSUNDEEP
excelpandasexcel-formula
John Taylor
John Taylor

Reputation: 737

Pandas read_excel with formulas and get values

So, I had some code running that opened a series of excel files and converted to dataframes using pd.read_excel, no issues. Just today, the files are now all displaying NaN instead of values. The excel cells have formulas. Searched SO, found several questions on this. None solved it. Figured I would give it another try and see if anyone had a method to get a tab of a multi-tab workbook with formulas into a dataframe displaying the workbook values and not NaN. Thanks.

Upvotes: 0

Views: 2645

Answers (1)

John Taylor
John Taylor

Reputation: 737

Well, after hours of searching, this code, for some reason that is opaque to me, enabled me to use pd.read_excel() and read the values instead of just all NaN values from cells with formulas.

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)

Upvotes: 1

Related Questions