PyXLL User Guide
PyXLL User Guide
Release 5.4.4
PyXLL Ltd.
Aug 15, 2022
CONTENTS
1
.
.
.
.
.
1
1
1
2
3
3
2
Whats new in PyXLL 5
2.1 New Features and Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Important notes for upgrading from previous versions . . . . . . . . . . . . . . . . . . . . . . .
5
5
7
3
User Guide
3.1 Installing PyXLL . . . . . . . .
3.2 Configuring PyXLL . . . . . .
3.3 Worksheet Functions . . . . . .
3.4 Real Time Data . . . . . . . . .
3.5 Cell Formatting . . . . . . . . .
3.6 Charts and Plotting . . . . . . .
3.7 Custom User Interfaces . . . .
3.8 Customizing the Ribbon . . . .
3.9 Context Menu Functions . . . .
3.10 Macro Functions . . . . . . . .
3.11 Python as a VBA Replacement
3.12 Using Pandas in Excel . . . . .
3.13 Menu Functions . . . . . . . .
3.14 Reloading and Rebinding . . .
3.15 Error Handling . . . . . . . . .
3.16 Distributing Python Code . . .
3.17 Workbook Metadata . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
9
9
17
38
71
76
81
94
104
108
113
115
122
125
127
131
133
138
API Reference
4.1 Function Decorators . . . . .
4.2 Plotting Functions and Classes
4.3 Custom Task Panes . . . . . .
4.4 Utility Functions . . . . . . .
4.5 Ribbon Functions . . . . . . .
4.6 Cell Formatting . . . . . . . .
4.7 Event Handlers . . . . . . . .
4.8 Excel C API Functions . . . .
4.9 Other Classes . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
140
140
145
147
150
155
157
162
164
167
4
5
Introduction to PyXLL
1.1 What is PyXLL? . . . . . . . . . . . . . . . . .
1.2 How does it work? . . . . . . . . . . . . . . . .
1.3 How does PyXLL compare with other packages?
1.4 Before You Start . . . . . . . . . . . . . . . . .
1.5 Next Steps . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Examples
172
5.1 UDF Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
5.2 Pandas Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
5.3 Cached Objects Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
i
5.4
5.5
5.6
5.7
Index
Custom Type Examples . .
Menu Examples . . . . . .
Macros and Excel Scripting
Event Handler Examples . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
179
182
184
187
189
ii
CHAPTER
ONE
INTRODUCTION TO PYXLL
1.1 What is PyXLL?
PyXLL is an Excel Add-In that enables developers to extend Excels capabilities with Python code.
PyXLL makes Python a productive, flexible back-end for Excel worksheets, and lets you use the familiar Excel
user interface to interact with other parts of your information infrastructure.
With PyXLL, your Python code runs in Excel using any common Python distribution(e.g. Anaconda, Enthoughts
Canopy or any other CPython distribution from 2.3 to 3.10).
Because PyXLL runs your own full Python distribution you have access to all third party Python packages such
as NumPy, Pandas and SciPy and can call them from Excel.
Example use cases include:
? Calling existing Python code to perform calculations in Excel
? Data processing and analysis thats too slow or cumbersome to do in VBA
? Pulling in data from external systems such as databases
? Querying large datasets to present summary level data in Excel
? Exposing internal or third party libraries to Excel users
Read more about PyXLL features on the features page.
1.2 How does it work?
PyXLL runs Python code in Excel according to the specifications in its config file, in which you configure how
Python is run and which modules PyXLL should load. When PyXLL starts up it loads those modules and exposes
certain functions that have been tagged with PyXLL decorators.
For example, an Excel user defined function (UDF) to compute the n th Fibonacci number can be written in Python
as follows:
from pyxll import xl_func
@xl_func
def fib(n):
"Naiive Fibonacci implementation."
if n == 0:
return 0
elif n == 1:
return 1
return fib(n-1) + fib(n-2)
1
PyXLL User Guide, Release 5.4.4
The xl_func decorated function fib is detected by PyXLL and exposed to Excel as a user-defined function.
Excel types are automatically converted to Python types based on an optional function signature. Where there is
no simple conversion (e.g. when returning an arbitrary class instance from a method) PyXLL stores the Python
object reference as a cell value in Excel. When another function is called with a reference to that cell PyXLL
retrieves the object and passes it to the method. PyXLL keeps track of cells referencing objects so that once an
object is no longer referenced by Excel it can be dereferenced in Python.
1.3 How does PyXLL compare with other packages?
There are many different Python packages for working with Excel.
The majority of these are for reading and writing Excel files (e.g. openpyxl and xlsxwriter).
PyXLL is very different to these other packages. Instead of just allowing you to read and write Excel files, PyXLL
integrates Python into Excel. This allows you to run Python inside of Excel to extend Excels capabilities with
your own Python code!
It is also possible to interact with Excel using a technology called COM. This allows a separate process to call into
Excel and script it. PyXLL is different as it actually embeds Python inside the Excel process, rather than calling
into it from an external process. This has huge implications for performance and means that PyXLL is by far the
fastest way of integrating Python and Excel.
By integrating Python into Excel, PyXLL not only achieves the best possible performance, but it is also able to
support many more features that are not possible using COM alone or only reading and writing files.
Just some of the features available in PyXLL that are not available in these other packages include:
? Fast, in-process, user defined functions
? Access to the full Excel Object Model for macros and more
? Real time data functions
? Custom ribbon toolbars and context menus
? Python user interfaces (PyQt, PySide etc) in Excel
? Use Excels multiple worker threads for Python functions
? Excel native asynchronous functions for IO bound tasks
PyXLL is used by large teams across many different industries and is designed to be able to be distributed to
non-technical, non-Python users easily. If this is something you need please contact us and we will be happy to
help.
For more information about other Python tools for Excel please see our blog post Tools for Working with Excel
and Python, or for a more detailed comparision of PyXLL and xlwings please see this FAQ article What is the
difference between PyXLL and xlwings.
All PyXLL subscriptions include technical support and upgrades to new releases.
If youre not sure if PyXLL is right for your project or not, why not take advantage of our free 30 day trial to see
for yourself? If you need any help getting started then just let us know.
1.3. How does PyXLL compare with other packages?
2
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- python quick guide
- functions in python department of computer science and electrical
- python reference manual massachusetts institute of technology
- python agm global vision
- ppyytthhoonn ffuunnccttiioonnss
- ibm open enterprise python for z os v3 8 user s guide
- basic python by examples ltam
- python scripting in fme safe software
- pyxll user guide
- python guide documentation read the docs