How can I see the formulas of an excel spreadsheet in pandas / python?

ThrowAway23948238 picture ThrowAway23948238 · Feb 8, 2017 · Viewed 11.8k times · Source

I would like to read in an excel spreadsheet to python / pandas, but have the formulae instead of the cell results.

For example, if cell A1 is 25, and cell B1 is =A1, I would like my dataframe to show:

25    =A1

Right now it shows:

25    25

How can I do so?

Answer

Aleksey Bilogur picture Aleksey Bilogur · Feb 8, 2017

OpenPyXL provides this capacity out-of-the-box. See here and here. An example:

from openpyxl import load_workbook
import pandas as pd
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_names = wb.get_sheet_names()
name = sheet_names[0]
sheet_ranges = wb[name]
df = pd.DataFrame(sheet_ranges.values)