0

I am trying to build a tool which will run an index match in a cell on a spreadsheet, and then display the result of the formula in python. My understanding is Openpyxl will not actually run the formulas but I can write to excel and then refresh the file to run it? 

from openpyxl import load_workbook
path= "C:\\Users\\Me\\Documents\\Python\\File.xlsx"
myworkbook=load_workbook(path)
worksheet=myworkbook.get_sheet_by_name('Sheet1')
mycell=worksheet['B2']
mycell.value="index(B4:B72,match(B1,A4:A72,0))"
print(mycell)

Anyway, I receive and error and I am not sure what is going on. Ouput:

DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]). worksheet=myworkbook.get_sheet_by_name('Sheet1')

Process finished with exit code 0

To be clear, the formula works if I just do it in Excel because B1 is populated in the file.

I am not sure what the output is doing. It is not throwing an error but I have no idea what <Cell 'Sheet1'.B2> and "Process Finished with Exit code 0" is trying to tell me. I expected a string output because the I am trying to index in a sentence.

help-info.de
  • 6,695
  • 16
  • 39
  • 41
Steve Burt
  • 69
  • 1
  • 8

1 Answers1

1

A) Running the formulas requires opening the file in Excel. As you state:

My understanding is Openpyxl will not actually run the formulas but I can write to excel and then refresh the file to run it?

If by "refresh the file to run it" you mean open the file in Excel, then yes, Excel will execute the formula.

B) The error you get is actually just a warning telling you that a feature you used will not be available in future versions of Openpyxl. In this case, the warning also tells you the correct way to acccess the sheet.

Call to deprecated function get_sheet_by_name (Use wb[sheetname]) [emphasis added]

To avoid the warning, replace the line with:

worksheet=myworkbook['Sheet1']

C) When you add a formula to a cell using Openpyxl, you have to include the = character at the beginning of the formula, so your line should be:

mycell.value="=index(B4:B72,match(B1,A4:A72,0))"

D) In your print statement, you pass it the mycell object, which is why it prints the 'name' of the object <Cell 'Sheet1'.B2>. This could be useful if you want to verify that your mycell variable is pointing to the correct cell in the workbook, but it isn't what you want. To access the contents of a cell, you need to refer to the .value of that cell. Modify your print statement to be:

print(mycell.value)

Putting all of that together:

from openpyxl import load_workbook
path= "C:\\Users\\Me\\Documents\\Python\\File.xlsx"
myworkbook=load_workbook(path)
worksheet=myworkbook['Sheet1']
mycell=worksheet['B2']
mycell.value="=index(B4:B72,match(B1,A4:A72,0))"
print(mycell.value)
Craig
  • 4,605
  • 1
  • 18
  • 28
  • Thanks, I will try this. Then I would add the openpyxl command myworkbook.RefreshAll and then the print command as you have written and it would display correctly? – Steve Burt Oct 05 '19 at 17:56
  • There is no `.RefreshAll()` method in Openpyxl. The closest thing is `.RefreshAllConnections`which is a parameter used to tell Excel to refresh connections when opening the sheet. In order to do what you want, you will need to use `win32com` to open and refresh the workbook in Excel as explained in the answers to: https://stackoverflow.com/questions/40893870/refresh-excel-external-data-with-python – Craig Oct 05 '19 at 18:28
  • https://stackoverflow.com/questions/58359177/evaluating-an-index-match-with-openpyxl-and-xlwings-formula-returns-not-value Hi Craig, since you were an incredible help before I wanted to ping you. I posted a new question after taking your advice. I would be deeply grateful if you could chime in if you have a free moment. – Steve Burt Oct 14 '19 at 02:45