Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python in Excel

My thoughts on Python integration in Excel I submitted in the survey listed on https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language

  1. Git & code reviews & external editors => Open + Save As dialogs MUST support a plain text option, e.g.:

a) the un-zipped directory that currently makes the .xlsm file, or

b) have 2 files .xslx + .xslpy where:

  • the excel file would contain a link to the python file similar to "Data > Connections"
  • the python file would be plain text:
    • enabling static analysis of object model, e.g.:
import Excel as e

@e.UDF
def my_udf(a: e.String, b: e.Number) -> e.String
    return a[:b].upper()

@e.Macro
...
  • in the future, someone would be able create open source testing library, e.g.:
import ExcelMocks as em
from my_functions.xslpy import my_udf123

em.addSheet(name="Sheet 1", formulas=[["abc", "def"], ["=my_udf123(A1, 2)", None]])
em.calculate()

assert em.getSheet(name="Sheet 1").values[1][0] == "AB"
  1. VBA Object Model is probably not 1:1 fit for Pythonic way of thinking - e.g. runtime exceptions from VBA are very cryptic and Python developers expect better than random error code numbers ¯\_(ツ)_/¯
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment