Getting value from Excel cell with formula via openpyxl

Question

I need to get value from Excel cell with formula, but I don't need formula. I need a value, but not the formula from cell. How can I do it? I tried using just

working_sheet.cell(row=row_number, column=column_number+1).value

and extending a list. But I getting list like

[
    '2020',
    'остальные грузы',
    'КОЛУМБИЯ',
    'НОВ.ПОРТ-ЭКС',
    'НОВ.ПОРТ-ЭКС',
    'НОВ.ПОРТ-ЭКС',
    'НОВ.ПОРТ-ЭКС',
    'МЕКСИКА',
    'повагонная',
    'порожние',
    '0',
    '0',
    '=VLOOKUP(C28038,$AB$2:$AC$1048576,2,FALSE)', 
    '=VLOOKUP(H28038,$AB$2:$AC$1048576,2,FALSE)',
    '=VLOOKUP(E28038,$X$2:$Y$1048576,2,FALSE)',
    '=VLOOKUP(F28038,$X$2:$Y$1048576,2,FALSE)'
]

so i don't need for values like =VLOOKUP...

What can I do?

score 5 · Accepted Answer · answered Oct 05 '21 at 02:03

Make sure you are using data_only=True when loading the workbook:

import openpyxl

workbook = openpyxl.load_workbook(filename, data_only=True)
worksheet = workbook.active  # active worksheet

print(worksheet.cell(column=10, row=5).value)

Be aware: Excel stores evaluated values in the file. That said, these values are only available if the file was opened with Excel (or maybe with a similar application) before.

Getting value from Excel cell with formula via openpyxl

1 Answers1