TL;DR: PyXLL is an Excel add-in that embeds Python into Excel for seamless, high performance Excel Python integration. xlwings is a higher level wrapper around the COM/AppleScript Excel Object Model APIs for automating Excel tasks with Python.
- Feature Differences
- Different Use Cases
- Technical Differences
- Coupling of Excel Worksheets and Python code
PyXLL lets you write Excel add-ins using Python running in Excel. xlwings drives Excel from Python scripts that run outside of Excel. PyXLL provides native support for custom Excel functions whereas xlwings has to generate VBA in your sheet to call out to an external Python process.
The following are some of the features available in PyXLL but not in xlwings:
|Read Time Data (RTD) functions||✘||✔||Stream data from Python to Excel in realtime.|
|Ribbon Customization||✘||✔||Extend the Excel user interface with your own toolbars.|
|Menu Functions||✘||✔||Quick and easy menu functions to call Python code from Excel.|
|Advanced Object Cache||✘||✔||Pass complex Python objects between Excel worksheet functions.|
|Cell Formatting||✘||✔||Automatically format cells when returning values from worksheet functions.|
|Multi-threading||✘||✔||Use Excel's multithreading calculation capabilities.|
|Asynchronous Functions||✘||✔||Take advantage of Excel's native support for async functions.|
|Keyboard Shortcuts||✘||✔||Assign keyboard shortcuts to commonly used Python macros.|
|Automatic Reloading||✘||✔||Reload Python code automatically for faster development.|
|IntelliSense Integration||✘||✔||Auto-complete functions as you enter them in Excel.|
There are many other differences, though because PyXLL runs Python in Excel Python code run by PyXLL can also call xlwings. As xlwings runs outside of Excel it cannot call the low level Excel C API, and so not all PyXLL functions can be called from xlwings.
Different Use Cases
PyXLL is used to write Excel add-ins in Python. This usually entails writing UDFs (worksheet functions) in Python that are to be called from Excel worksheets in formulas. Macros and menu functions can also be written using PyXLL, as well as designing custom ribbon interfaces.
With PyXLL, the end user is an Excel user and they use Excel to call Python code. A typical use case is a team where some users are Excel users, but those users require access to analytics or other code that is more suitable to being written in Python. PyXLL bridges Python and Excel and allows Python developers to target Excel as a front end.
xlwings makes it easy to automate Excel tasks from a Python prompt, script or Jupyter notebook. For example, an analyst who wants to copy a pandas DataFrame to an open Excel workbook can do so using xlwings. While xlwings can call Python code from worksheet functions, it is not the primary focus of xlwings and it does so by injecting VBA into the workbook and, by default, having Python code live alongside that workbook.
Excel has two main APIs. The Excel C API is used by add-in developers and provides the lowest level direct access to Excel for exposing user defined functions. The Excel Object Model is exposed via COM on Windows, and AppleScript on macOS. The Excel Object Model is what you may be used to if you have written any VBA code and is used for scripting and automation of Excel.
PyXLL is a high performance Excel add-in written in C++ that embeds the Python runtime into Excel. Using PyXLL, fully featured Excel add-ins can be written entirely in Python without the use of VBA. PyXLL uses the Excel C API for its core features, enabling it to take full advantage of the low level access to Excel for the best possible performance. PyXLL also uses the Excel Object Model for some of its features.
xlwings is a Python package that sits on top of the win32com COM wrapper for calling the Excel Object Model from outside of Excel on Windows, and appscript on macOS. It has some capabilities for calling Python code from Excel on Windows. This is achieved via auto-generating VBA code that calls an external Python process.
Coupling of Excel Workbooks and Python code
Typically VBA code is written as part of a workbook, and when that workbook is copied or emailed to someone else the VBA code goes with it. In some situations this is convenient, but in most organisations this represents an operational risk as multiple copies of the same code exist in different places, and it is impossible to roll out bug fixes to each workbook or audit what code is being used in an organisation.
PyXLL is written as an Excel add-in, and as such is not tied to any particular Excel workbook. This means that code exposed to Excel using PyXLL is accessible from any Excel workbook as long as the PyXLL add-in is loaded. The Python code can be deployed to the local PC, or referenced from a network drive. Each user will require access to the code but it can be controlled and audited, even if the workbooks that use that code are copied.
xlwings adds VBA code to a specific workbook and by default calls a Python module in the same folder as the workbook. This means that the Python code is directly coupled to that Excel workbook and cannot be used from another workbook without copying the VBA code. This makes it harder to share common code between Excel workbooks, and increases the risk of not being able to track down all versions or copies of live code.
Coupling code to worksheets may be convenient for individual users, but it is not good practice when you consider factors like ensuring consistency between workbooks, code and version control, and deploying updates and fixes to all users.
PyXLL makes it possible to write Excel macros in Python code, without needing any VBA. Python can access the full Excel Object Model via COM with the win32com package. PyXLL provides the pyxll.xl_app function to make it easy to access the Excel Application object from Python code.
For some tasks it may make sense to use xlwings' higher level API instead of the Excel Object Model API. As xlwings is simply a wrapper around the Excel COM API it is possible to get the xlwings App object for the running Excel Application from Python code running in Excel. The PyXLL function pyxll.xl_app simplifies this by taking an optional argument "com_package" that can be set the "xlwings" to return the xlwings.App object instead of the default win32com Excel.Application object.
PyXLL was written from the first version with performance in mind. Calling Python functions from Excel with PyXLL is highly optimised to ensure the conversion of Excel arguments to Python arguments is as efficient as possible, and the overhead of calling the Python function itself is minimal. PyXLL ensures that for each function call only the minimal amount of work is performed, while maintaining flexibility over the types and number of arguments a function may have.
xlwings uses VBA to call an external Python process, so inevitably there will be considerable overhead when calling an xlwings function from Excel.
If performance is an important factor then you should perform your own tests to ensure that the performance you get is adequate for your needs. We do pride ourselves on the performance of PyXLL so if you find that it is not what you expect you should contact us so we can help figure out why that is.
Our blog post includes some tests and the results showing the performance difference between PyXLL and xlwings: https://www.pyxll.com/blog/performance-comparison/
In our tests we found that PyXLL was consistently materially faster than xlwings for calling Python functions from Excel. The table below is from one of the tests performed in the above blog post.
|Cells||PyXLL (seconds)||xlwings (seconds)||Times Faster|
Similar results have been found independently by other users. The following blog post shows how large arrays can be passed efficiently between Excel and Python using PyXLL's numpy type, which is much faster than using VBA: https://newtonexcelbach.com/2019/01/05/transferring-large-arrays-with-pyxll/. This blog also notes that using PyXLL's object type reduces the time taken to return data to Excel to virtually zero.