Is there a way to call Excel add-ins from python? In my company there are several excel add-ins that are available, they usually provide direct access to some database and make additional calculations.
What is the best way to call those functions directly from python?
To clarify, I'm NOT interested in accessing python from excel. I'm interested in accessing excel-addins from python.
There are at least 3 possible ways to call an Excel add-in, call the COM add-in directly or through automation.
Microsoft provide online documentation for it's Excel interop (https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel). Whilst it's for .NET, it highlights the main limitations. You cannot directly call a custom ribbon (which contains the add-in). This is intended to protect one add-in from another. (Although you can install/ uninstall add-in).
COM add-in You can call the COM add-in directly using win32com.client. However you can only run methods that are visible to COM. This means you may have to alter the add-in. See a C# tutorial https://learn.microsoft.com/en-us/visualstudio/vsto/walkthrough-calling-code-in-a-vsto-add-in-from-vba?view=vs-2019.
Once the method is exposed, then it can be called in Python using win32com.client. For example:
import win32com.client as win32
def excel():
# This may error if Excel is open.
return win32.gencache.EnsureDispatch('Excel.Application')
xl = excel()
helloWorldAddIn = xl.COMAddIns("HelloWorld") # HelloWorld is the name of my AddIn.
#app = helloWorldAddIn.Application
obj = helloWorldAddIn.Object # Note: This will be None (null) if add-in doesn't expose anything.
obj.processData() # processData is the name of my method
Web scraping If you're able to upload your add-in to an office 365 account. Then you could preform web scraping with a package like Selenium. I've not attempted it. Also you'll most likely encounter issues calling external resources like connection strings & http calls.
Automation You can run the add-in using an automation package. For example pyautogui. You can write code to control the mouse & keyboard to simulate a user running the add-in. This solution will mean you shouldn't need to update existing add-ins.
A very basic example of calling add-in through automation:
import os
import pyautogui
import time
def openFile():
path = "C:/Dev/test.xlsx"
path = os.path.realpath(path)
os.startfile(path)
time.sleep(1)
def fullScreen():
pyautogui.hotkey('win', 'up')
time.sleep(1)
def findAndClickRibbon():
pyautogui.moveTo(550, 50)
pyautogui.click()
time.sleep(1)
def runAddIn():
pyautogui.moveTo(15, 100)
pyautogui.click()
time.sleep(1)
def saveFile():
pyautogui.hotkey('ctrl', 's')
time.sleep(1)
def closeFile():
pyautogui.hotkey('alt', 'f4')
time.sleep(1)
openFile()
fullScreen()
findAndClickRibbon()
runAddIn()
saveFile()
closeFile()
This link list some of the available packages to work with Excel and Excel files. You might find the answer to your question there.
As a summary, here are the name of some of the listed packages:
- openpyxl - Read/Write Excel 2007 xlsx/xlsm files
- xlrd - Extract data from Excel spreadsheets (.xls and .xlsx, versions 2.0 onwards) on any platform
- xlsxwriter - Write files in the Excel 2007+ XLSX file format
- xlwt - Generate spreadsheet files that are compatible with Excel 97/2000/XP/2003, OpenOffice.org Calc, and Gnumeric.
And especially this might be interesting for you:
ExPy - ExPy is freely available demonstration software that is simple to install. Once installed, Excel users have access to built-in Excel functions that wrap Python code. Documentation and examples are provided at the site.
