Python: read .xls with formula inside

1
votes

I need to transform an input file (.xls) with formula into an .xlsx file that has only the value/data of the formula.

-Openpyxl cant read xls files, but got the "data only" flag when reading the file. -xlrd etc. can read xls files, but cant read these with "data only" flag like openpyxl can..

When I try to transform the xls file to an xlsx in python to open it with openpyxl afterwards, all the values with formula become "0".

Does anyone know how I can deal with this issue?

1
pythonpandasdataframeopenpyxlxlrd

1 Answers

0
votes

You can use xlwings

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_from_excel('path to xls')