How can I see the formulas of an excel spreadsheet in pandas / python?
18,972
Solution 1
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)
Solution 2
Yes, it is possible. I recently found a package that solves this issue in a quite sophisticated way. It is called portable-spreadsheet (available via pip install portable-spreadsheet
). It basically encapsulates xlsxwriter
. Here is a simple example:
import portable_spreadsheet as ps
sheet = ps.Spreadsheet.create_new_sheet(5, 5)
# Set values
sheet.iloc[0, 0] = 25 # Set A1
sheet.iloc[1, 0] = sheet.iloc[0, 0] # reference to A1
# Export to Excel
sheet.to_excel('output/sample.xlsx')
It works in a similar way as Pandas Dataframe.
Related videos on Youtube
Author by
ThrowAway23948238
Updated on September 14, 2022Comments
-
ThrowAway23948238 over 1 year
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?
-
juanpa.arrivillaga about 7 yearsI'm fairly certain there is no built-in way to do this in
pandas
.
-
-
ThrowAway23948238 about 7 yearsI'm having trouble with implementation: stackoverflow.com/questions/42397769/…
-
Aleksey Bilogur about 7 yearsLooks like someone else got to it before I did. Also ,you should accept this post as an answer.
-
ThrowAway23948238 about 7 yearsWas just waiting to validate. Now I have. Thanks!
-
yaswant singh over 5 yearsHi @AlekseyBilogur any idea how we can do in CSV?
-
Avik Aggarwal almost 4 yearsHi @AlekseyBilogur I did give this a try, while this method reads the formula, but it seems to be removing the value itself? I read and just wrote the excel into new file, it maintained the formula, formatting and everything but truncated all values in it.