0

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)
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Nitesh Palankar
  • 11
  • 1
  • 3
  • This question is similar to your issues: https://stackoverflow.com/questions/45949630/getting-formula-while-reading-cell-data-in-openpyxl – amarynets Sep 01 '17 at 12:58
  • It also sounds like this question: [How to access the real value of a cell using the openpyxl module for python](https://stackoverflow.com/questions/22613272/how-to-access-the-real-value-of-a-cell-using-the-openpyxl-module-for-python) – Eric Jensen Sep 01 '17 at 13:07
  • @marni .. Thanks for your reply, but i am using data_only=True while loading the workbook using openpyxl, but i used it then its giving me None – Nitesh Palankar Sep 01 '17 at 13:36
  • I have attached below the sample code, where cell B276 contains auto poupulated values from macro.... import openpyxl output_path = "C:\\Users\\DEMO\\macro_xls\\output\\sample_test.xlsm" wb = openpyxl.load_workbook(output_path, data_only=True) new_sh = wb.get_sheet_by_name('II.8') print(new_sh['B276'].value) – Nitesh Palankar Sep 01 '17 at 13:43
  • 1
    How about showing us the code you've tried? – Charlie Clark Sep 02 '17 at 10:30
  • @CharlieClark I have attached sample code above in main question. Can you please help me out for finding the solution. Thanks in advance – Nitesh Palankar Sep 04 '17 at 10:49
  • @NiteshPalankar as stated in the [official doc](http://openpyxl.readthedocs.io/en/default/api/openpyxl.reader.excel.html) using `keep_vba=True` preseves vba content (this does NOT mean you can use it). In that way, you are not actually triggering the macro like Excel would do after you inserted the value. I'm afraid that you need a different library to trigger a macro. Check [this answer](https://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python) out or try to start from [here](http://www.python-excel.org/). – floatingpurr Sep 04 '17 at 11:34
  • It seems you have completely failed to understand how openpyxl works. – Charlie Clark Sep 04 '17 at 14:40
  • @CharlieClark Agree with you, but can you please suggest any solution on this requirement (by using openpyxl or any other library) ? – Nitesh Palankar Sep 12 '17 at 06:48

1 Answers1

2

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

floatingpurr
  • 7,749
  • 9
  • 46
  • 106
  • i have tried the same solution(code is mentioned in above comment), but its giving me None value. Actually i am giving some value to normal cell in .xlsm file and as soon as i give that value, the macro will run and there are some cells whose values will get auto populated and i am trying to read the value of those cells in the same execution. – Nitesh Palankar Sep 01 '17 at 13:51
  • So the macro has to be triggered in order to insert data in your cell otherwise it is blank, right? – floatingpurr Sep 01 '17 at 13:52
  • macro is getting triggered. In the same execution, i am writing data to cell, from that macro is getting triggered and its populating the values also. after that i am saving that modified workbook and just after that i am again opening that workbook with data_only=True in the same execution. – Nitesh Palankar Sep 01 '17 at 14:01
  • Well, what happens if you try to save the file with the `.xlsx` format and then you use `openpyxl` to read data in such a file? – floatingpurr Sep 01 '17 at 14:13
  • Saving file with .xlsx and then reading it with openpyxl is also not working – Nitesh Palankar Sep 04 '17 at 07:22
  • Actually, the issue is with i am modifying .xlsm file(populating macro cell value) and then reading those values in the same execution. If we only modify .xlsm file in first execution and then open it and save it using normal excel application/software and later on we try to read macro cell contents using openpyxl in the second execution, then its correctly giving calculated values instead of macro formula. So the problem is with my requirement, that i have to do all this in the same execution. Any solution on this ? Thanks in advance – Nitesh Palankar Sep 04 '17 at 07:28
  • Can you show us the code? You can insert it in the main question. – floatingpurr Sep 04 '17 at 09:02