How to combine value in multiple cell references in Excel by Python?

Question

I am trying to do something like grabbing all values in each cell while they are referencing one by one. Maybe an example help illustration.

Example:

A	B	C
=B2	='I am' & C2	'Peter

Example2 - in term of number:

A	B	C	D
=B2	=C2*D2	12	56

So I want to get a concat string 'I am Peter' or 672 (from 12*56) when I reading the cell A2

Code I tried:

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

The formula will become 'NaN'

Any suggestion to achieve it? Thanks!

Answer 1

If you want to have the actual values of the cells, you have to use data_only=True

wb = load_workbook(filename = 'new.xlsx', data_only=True)

Look here: Read Excel cell value and not the formula computing it -openpyxl

Anyway, as you use pandas, it would be way easier to go directly:

import pandas as pd
df = pd.read_excel('new.xlsx')
print(df)

which grabs the first sheet (but could be specified) and gives the values as output.

Answer 2

openpyxl supports either the formula or the value of the formula. You can select which using the data_only parameter when loading a workbook.

You can change your code like below:

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook(filename='new.xlsx', data_only=True)
sheet_names = wb.get_sheet_names()
name = sheet_names[0]
sheet_ranges = wb[name]
df = pd.DataFrame(sheet_ranges.values)
print(df)