How to read values from macro cells in excel using python 3

Question

I am trying read the content from macro based xls file(.xlsm) in python by using openpyxl library. I want to read the value from the cell, but i am getting the macro formulas instead of populated values from macro in cell. Is there any way to get the values from the cell content in python ?

######## Below is the sample code ###########

import openpyxl

def generate_output_file(template_path, output_path):

    # Load/open the existing workbook template
    old_wb = openpyxl.load_workbook(template_path, keep_vba=True)
    old_sheet = old_wb['sheet1']

    # Sample logic for inserting data in sheet1 which will trigger macro and populate some values
    # in other cells(eg. C276) of sheet1
    old_sheet['B55'] = 'abcd'

    # Save the modified workbook
    old_wb.save(output_path)

    # Now open the modified file in same instance
    new_wb = openpyxl.load_workbook(output_path, data_only=True)
    new_sh = new_wb.get_sheet_by_name('sheet1')

    # this should show populated values of macro cell, but showing None
    print(new_sh['C276'].value)

if __name__ == "__main__":

    template_path = "C:\\macro_xls\\sample_test.xlsm"
    output_path = "C:\\macro_xls\\sample_test_output.xlsm"
    generate_output_file(template_path, output_path)

Answer 1

You may set data_only=True to read values from cells with a formula.

from openpyxl import load_workbook
wb = load_workbook("yourData.xlsm", data_only=True)
ws = wb['test']
cell = ws['A1']
value = cell.value

From doc:

data_only (bool) – controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet