These decorators, functions and classes are used to expose Python functions to Excel as macro functions (Subs).
Please see Macro Functions for complete details on writing Excel macro function in Python.
See also Type Conversion.
xl_macro([signature,] [allow_abort,] [name,] [shortcut,] [nan_value,] [posinf_value,] [neginf_value,] [none_value,] [disable_calculation=False,] [disable_screen_updating=False,] [disable_alerts=False,] [restore_selection=False,] [restore_active_sheet=False])xl_macro is a decorator for exposing python functions to Excel as macros. Macros can be triggered from controls, from VBA or using COM.
| Parameters: |
|
|---|
Example usage:
from pyxll import xl_macro, xlcAlert
@xl_macro
def popup_messagebox():
"""pops up a message box"""
xlcAlert("Hello")
@xl_macro
def py_strlen(s):
"""returns the length of s"""
return len(s)
See Macro Functions for more details about using the xl_macro decorator.
xl_app(com_package=None)Gets the Excel Application COM object and returns it as a win32com.Dispach,
comtypes.POINTER(IUknown), pythoncom.PyIUnknown or xlwings.App object, depending on which COM package
is being used.
Many methods and properties Excel Application COM object will fail if called from outside of an
Excel macro context. Generally, xl_app should only be used from Python code called from an
Excel macro [1], menu [1], or worksheet function [1] [2]. To use
it from any other context, or from a background thread, schedule a call using schedule_call.
| Parameters: | com_package (string) – The Python package to use when returning the COM object. It should be None, ‘win32com’, ‘comtypes’, ‘pythoncom’ or ‘xlwings’. If None the com package set in the configuration file will be used, or ‘win32com’ if nothing is set. |
|---|---|
| Returns: | The Excel Application COM object using the requested COM package. |
Warning
Excel COM objects should never be passed between threads. Only use a COM object in the
same thread it was created in. Doing otherwise is likely to crash Excel! If you are using a background
thread the safest thing to do is to only call into Excel using COM via functions scheduled
using schedule_call.
New in PyXLL 5.9
xl_disable(automatic_calculations=True, screen_updating=True, alerts=True, restore_selection=True, restore_active_sheet=True)Context manager to disable Excel while performing operations that interact with Excel.
Can only be used from inside an Excel macro, or from a function scheduled using pyxll.schedule_call.
Example:
@xl_macro
def macro_function():
with xl_disable():
# do some work here that updates Excel where we do not
# want Excel to automatically recalculate or update.
xl = xl_app()
xl.Range("A1").Value = 1
# After the with block, Excel reverts to its previous calculation mode.
return
| Parameters: |
|
|---|
XLCellXLCell represents the data and metadata for a cell (or range of cells) in Excel.
XLCell instances are passed as an xl_cell argument to a function registered with xl_func, or
may be constructed using from_range.
Some of the properties of XLCell instances can only be accessed if the calling function has been registered as a macro sheet
equivalent function [3].
Example usage:
from pyxll import xl_func
@xl_func("xl_cell cell: string", macro=True)
def xl_cell_test(cell):
return "[value=%s, address=%s, formula=%s, note=%s]" % (
cell.value,
cell.address,
cell.formula,
cell.note)
from_range(range)Static method to construct an XLCell from an Excel Range instance or address (e.g. ‘A1’).
The Range class is part of the Excel Object Model, and can be obtained via xl_app.
See Python as a VBA Replacement.
The XLCell instance returned can be used to get and set values in Excel using PyXLL type converters and object cache.
| Parameters: | range – An Excel Range object or cell address as a string. |
|---|
Example usage:
xl = xl_app()
range = xl.Selection
cell = XLCell.from_range(range)
cell.options(type='object').value = x
Must be called from a macro or macro sheet equivalent function [3]
valueGet or set the value of the cell.
The type conversion when getting or setting the cell content is determined by the type
passed to XLCell.options. If no type is specified then the type conversion
will be done using the var type.
Must be called from a macro or macro sheet equivalent function [3]
addressString representing the address of the cell, or None if a value was passed to the function and not a cell reference.
Must be called from a macro or macro sheet equivalent function [3]
formulaFormula of the cell as a string, or None if a value was passed to the function and not a cell reference or if the cell has no formula.
Must be called from a macro or macro sheet equivalent function [3]
noteNote on the cell as a string, or None if a value was passed to the function and not a cell reference or if the cell has no note.
Must be called from a macro or macro sheet equivalent function [3]
rectXLRect instance with the coordinates of the cell.
is_calculatedTrue or False indicating whether the cell has been calculated or not. In almost all cases this will always be True as Excel will automatically have recalculated the cell before passing it to the function.
options(self[, type][, auto_resize][, type_kwargs][, nan_value][, posinf_value][, neginf_value])Sets the options on the XLCell instance.
| Parameters: |
|
|---|---|
| Returns: | self. The cell options are modified and the same instance is returned, for easier method chaining. |
Example usage:
cell.options(type='dataframe', auto_resize=True).value = df
to_range(self, com_wrapper=None)Return an Excel Range COM object using the COM package specified.
| Parameters: | com_package – COM package to use to return the COM Range object. |
|---|
com_package may be any of:
@Since PyXLL 4.4.0
offset(self[, rows][, columns])Return a clone of the XLCell, offset by rows and columns.
| Parameters: |
|
|---|
@Since PyXLL 5.8.0
resize(self[, rows][, columns])Return a clone of the XLCell, resized to rows and columns.
| Parameters: |
|
|---|
@Since PyXLL 5.8.0
XLRectXLRect instances are accessed via XLCell.rect to get the coordinates of the cell.
Footnotes
| [1] | (1, 2, 3) Do not use async functions when using xl_app as async functions run in the asyncio event loop
on a background thread. If you need to use xl_app from an async function, schedule it using
schedule_call. |
| [2] | Certain things will not work when trying to call back into Excel with COM from an Excel worksheet function
as some operations are not allowed while Excel is calculating. For example, trying to set the value of a cell
will fail. For these cases, use schedule_call to schedule a call after Excel has finished calculating. |
| [3] | (1, 2, 3, 4, 5, 6) A macro sheet equivalent function is a function exposed using xl_func with macro=True. |
