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

Question

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?

I'm fairly certain there is no built-in way to do this in `pandas`. — juanpa.arrivillaga, Feb 08 '17 at 01:00
@juanpa.arrivillaga agreed, though it's always nice to receive confirmation. Hopefully there's a package or something that can allow for it. — ThrowAway23948238, Feb 08 '17 at 02:13

score 14 · Accepted Answer · edited Feb 22 '17 at 20:35

14

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)

edited Feb 22 '17 at 20:35

ThrowAway23948238

83
2
2
12

answered Feb 08 '17 at 06:35

Aleksey Bilogur

3,686
3
30
57

I'm having trouble with implementation: http://stackoverflow.com/questions/42397769/openpyxl-worksheet-object-has-no-attribute-values – ThrowAway23948238 Feb 22 '17 at 17:01
1

Looks like someone else got to it before I did. Also ,you should accept this post as an answer. – Aleksey Bilogur Feb 22 '17 at 19:29
1

Was just waiting to validate. Now I have. Thanks! – ThrowAway23948238 Feb 22 '17 at 20:12
Hi @AlekseyBilogur any idea how we can do in CSV? – yaswant singh Dec 19 '18 at 10:31
Hi @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. – Avik Aggarwal May 08 '20 at 07:28

score 2 · Answer 2 · edited Jul 12 '21 at 22:39

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.

score 1 · Answer 3 · answered Dec 01 '21 at 15:31

There is one option to do this using xlwings and pandas modules. xlwings provides a way to automate the excel via python scripts.

Create one "sample.xlsx" file and add random formula in range("A1").

Below is the sample code which will read the value as well as the formula from the given file:

import pandas as pd
import xlwings as xw

wbk = xw.Book('sample.xlsx')
ws = wbk.sheets[0]
print(ws.cells(1,1).value)
print(ws.cells(1,1).formula)

Same thing applies on range as well. You can assign the range.value into dataframe and vice versa. In case you want to get formulas from big range, you can get that too, but it will return tuple.

Hope this is helpful at some extent.

score 0 · Answer 4 · answered Mar 01 '23 at 11:27

0

Use xlwings. This is a very useful tool in Python, it has detailed documentation and videos. Good luck!

answered Mar 01 '23 at 11:27

Aruparna Maity

174
3
7

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

4 Answers4

Linked

Related