CODEWITHSUNDEEP
pythonexcelpandasopenpyxl
user3313834
user3313834

Reputation: 7827

get Excel sheet content on a pandas dataframe but with formulas not values

I would like to have a pandas dataframe with the content of an excel file with the formulas if any.

So from this test.xlsx file:

>>> from openpyxl import load_workbook
>>> sheet = load_workbook('test.xlsx', data_only=False).active
>>> for i in range(1, 4, 1):
>>>     for j in range(1, 3, 1):
>>>         print(sheet.cell(i, j).value)
>>>         
4
a
3
b
=A1+A2
=B1&"_"&B2
>>>

How can I get this kind of pandas dataframe ?:

In [8]: df
Out[8]:
          4              a
0         3              b
1  '=A1+A2'   '=B1&"_"&B2'

For now I only get values::

In [6]: import pandas as pd
In [7]: df = pd.read_excel('test.xlsx')
In [8]: df 
Out[8]:
   4    a
0  3    b
1  7  a_b

Note: I'm on Linux so I can not use xlwings

Upvotes: 1

Views: 488

Answers (1)

user3313834
user3313834

Reputation: 7827

openpyxl do this by default and sheet.values can be use to construct pandas dataframe:

>>> from openpyxl import load_workbook
>>> sheet = load_workbook('test.xlsx', data_only=False).active
>>> df = pd.DataFrame(sheet.values)
>>>
>>> df
0       4           a
1       3           b
2  =A2+A3  =B2&"_"&B3
>>>

Upvotes: 1

Related Questions