"""
Copyright PyXLL Ltd
www.pyxll.com
PyXLL allows you to create Excel addins written in Python.
*************************************************************************
* IMPORTANT: *
* This module is NOT the actual pyxll module used when your PyXLL *
* addin runs in Excel. *
* *
* It is just a module of stubs functions so that you can import pyxll *
* from other python interpreters outside of Excel (for unit testing, *
* for example). *
*************************************************************************
This module contains decorators used by the PyXLL Excel addin to expose
python functions as worksheet functions, macro functions and menu items
to Excel.
For full documentation please refer to www.pyxll.com.
"""
from xml.dom import minidom
import warnings
import traceback
import datetime
import logging
import sys
_log = logging.getLogger(__name__)
__version__ = "5.0.9"
nan = 1e10000 * 0
xlCalculationAutomatic = 1
xlCalculationSemiAutomatic = 2
xlCalculationManual = 3
xlDialogTypeNone = 0
xlDialogTypeFunctionWizard = 1
xlDialogTypeSearchAndReplace = 2
[docs]def reload():
"""
Causes the PyXLL addin and any modules listed in the config file to be reloaded
once the calling function has returned control back to Excel.
If the `deep_reload` configuration option is turned on then any dependencies
of the modules listed in the config file will also be reloaded.
The Python interpreter is not restarted.
"""
raise Exception("Not supported when running outside of Excel")
def com_reload():
"""
Causes the COM part of the PyXLL addin to be reloaded once the calling function
has returned control back to Excel.
This doesn't reload any Python modules or rebind any functions. The COM addin
is responsible for the ribbon user interface and reloading it will cause the
ribbon to be reloaded.
"""
raise Exception("Not supported when running outside of Excel")
[docs]def rebind():
"""
Causes the PyXLL addin to rebuild the bindings between the exposed Python
functions and Excel once the calling function has returned control back to Excel.
This can be useful when importing modules or declaring new Python functions
dynamically and you want newly imported or created Python functions to be exposed to Excel without reloading.
Example usage::
from pyxll import xl_macro, rebind
@xl_macro
def load_python_modules():
import another_module_with_pyxll_functions
rebind()
"""
raise Exception("Not supported when running outside of Excel")
def about():
"""
Show the PyXLL 'about' dialog.
"""
raise Exception("Not supported when running outside of Excel")
[docs]def xl_arg_type(name, base_type, allow_arrays=True, macro=None, thread_safe=None):
"""
Decorator for adding custom types for use with
functions exposed via xl_func and xl_macro.
eg:
class myobject:
def __init__(self, name):
self.name = name
@xl_arg_type("myobject", "string")
def myobject_from_string(name):
return myobject(name)
@xl_func("myobject obj: string")
def get_name(obj):
return obj.name
in this example, get_name is called from excel with a string argument
that is converted to a myobject instance via myobject_from_string.
If allow_arrays is True, arrays of the custom type are allowed
using the standard signature notation 'myobject[]' (for the example
above).
macro and thread_safe can be set if the function using this type
must be a macro equivalent function (set macro=True) or must not
be registered as thread safe (set thread_safe=False).
"""
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def xl_return_type(name, base_type, allow_arrays=True, macro=None, thread_safe=None):
"""
Decorator for adding custom types for use with
functions exposed via xl_func and xl_macro.
eg:
class myobject:
def __init__(self, name):
self.name = name
@xl_return_type("myobject", "string")
def myobject_to_string(obj):
return obj.name
@xl_func("string name: myobject")
def get_object(name):
return myobject(name)
in this example, get_object is called from excel with a string
argument and returns a myobject instance. That is converted to a
string by the registered myobject_to_string function and returned
to excel as a string.
If allow_arrays is True, arrays of the custom type are allowed
using the standard signature notation 'myobject[]' (for the example
above).
macro and thread_safe can be set if the function using this type
must be a macro equivalent function (set macro=True) or must not
be registered as thread safe (set thread_safe=False).
"""
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def xl_return(_type=None, **kwargs):
"""
Add type information for a function's return type.
This can be used instead or as well as providing the return type information
in the function signature.
Using this decorator can be simpler when the return type is generic and requires
complex type arguments.
@xl_func
@xl_return("dataframe", index=True, columns=True)
def return_pandas_dataframe():
pass
:param _type: Type expected to be returned by the function
:param kwargs: Keyword arguments provided to the return type converter
"""
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def xl_arg(_name, _type=None, **kwargs):
"""
Add type information for a function's argument type.
This can be used instead or as well as providing the return type information
in the function signature.
Using this decorator can be simpler when the return type is generic and requires
complex type arguments.
@xl_func
@xl_arg("x", "dataframe", index=True, columns=True)
def return_pandas_dataframe(x):
pass
:param _name: Name of the argument this relates to
:param _type: Type expected for the argument
:param kwargs: Keyword arguments provided to the argument type converter
"""
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def get_type_converter(src_type, dest_type, src_kwargs={}, dest_kwargs={}):
"""
Return a function that converts from one type registered
with PyXLL to another.
When this function is called from outside of Excel then it
is purely a stub function. It returns a dummy function that simply
returns the argument it is passed.
This is so the functions can be written that take var arguments when
called from Excel and use PyXLL's type conversion to convert to proper
python types, but accept proper python types when called from other python
code outside of Excel (e.g. when testing in an interactive prompt or in
unit tests).
For example::
@xl_func("var a_date: var")
def test_func(a_date):
if a_date is not None:
var_to_date = get_type_converter("var", "date")
a_date = var_to_date(a_date) # does nothing if not called in Excel
return a_date.strftime("%Y-%m-%d")
>> test_func(datetime.date(2014,2,11))
'2014-02-11'
"""
# This is a dummy function here only so it can be imported and does not perform
# any type conversion when no imported from inside Excel.
return lambda x: x
def get_active_object():
"""
Deprecated. Use xl_app instead.
"""
warnings.warn("pyxll.get_active_object is deprecated. Use xl_app instead.", DeprecationWarning)
# this is only used when calling from outside Excel.
# the builtin pyxll module does 'the right thing'.
xl = xl_app()
return xl.Windows[0]
[docs]def xl_app(com_package="win32com"):
"""
Return the COM Excel Application object for the Excel
instance the PyXLL addin is running in.
When called from outside of Excel, this will return the first
open Excel found. If there is no Excel window open, this
function will raise and Exception.
"""
# this is only used when calling from outside Excel.
# the builtin pyxll module does 'the right thing'.
if com_package == "pythoncom":
import pythoncom
return pythoncom.GetActiveObject("Excel.Application")
elif com_package == "win32com":
import pythoncom
xl = pythoncom.GetActiveObject("Excel.Application")
return _wrap_iunknown(xl)
elif com_package == "comtypes":
import comtypes.client
return comtypes.client.GetActiveObject("Excel.Application")
elif com_package == "xlwings":
import xlwings
try:
version = tuple(map(int, xlwings.__version__.split(".")[:2]))
except Exception:
_log.warning("Error parsing xlwings version '%s'" % xlwings.__version__)
version = (0, 0)
assert version >= (0, 9), "xlwings >= 0.9 required (%s is installed)" % xlwings.__version__
if xlwings.apps.count == 0:
return xlwings.App()
return xlwings.apps.active
else:
raise ValueError("Unexpected com_package '%s'" % com_package)
[docs]def get_dialog_type():
"""
Returns a value indicating what type of dialog a function was
called from, if any.
This can be used to disable slow running calculations in the
function wizard or when doing a search and replace operation.
"""
return xlDialogTypeNone
[docs]def get_last_error(cell):
"""
When a Python function is called from an Excel worksheet, if an uncaught exception is raised PyXLL
caches the exception and traceback as well as logging it to the log file.
The last exception raised while evaluating a cell can be retrieved using this function.
The cache used by PyXLL to store thrown exceptions is limited to a maximum size, and so if there are
more cells with errors than the cache size the least recently thrown exceptions are discarded. The
cache size may be set via the error_cache_size setting in the config file.
When a cell returns a value and no exception is thrown any previous error is **not** discarded. This
is because doing so would add additional performance overhead to every function call.
:param xl_cell: XLCell instance or a COM Range object (the exact type depends
on the com_package setting in the config file.
:return: The last exception raised by a Python function evaluated in the cell, as a tuple
(type, value, traceback).
Example usage::
from pyxll import xl_func, xl_menu, xl_version, get_last_error
import traceback
@xl_func("xl_cell: string")
def python_error(cell):
exc_type, exc_value, exc_traceback = pyxll.get_last_error(cell)
if exc_type is None:
return "No error"
return "".join(traceback.format_exception_only(exc_type, exc_value))
"""
raise Exception("Not supported when running outside of Excel")
[docs]def load_image(filename):
"""
Loads an image file and returns it as a COM IPicture object suitable for use when
customizing the ribbon.
This function can be set at the Ribbon image handler by setting the loadImage attribute on
the customUI element in the ribbon XML file.
.. code-block:: xml
:emphasize-lines: 2, 11
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
loadImage="pyxll.load_image">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="Tools" label="Tools">
<button id="Reload"
size="large"
label="Reload PyXLL"
onAction="pyxll.reload"
image="reload.png"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Or it can be used when returning an image from a getImage callback.
:param string filename: Filename of the image file to load. This may be an absolute path or relative to
the ribbon XML file.
:return: A COM IPicture object (the exact type depends
on the com_package setting in the config file.
"""
raise Exception("Not supported when running outside of Excel")
[docs]def cached_object_count():
"""Return the number of objects cached in the internal object cache"""
return 0
def message_box(message, caption="", flags=0):
"""Show a message dialog box."""
import win32api
return win32api.MessageBox(None, message, caption, flags)
[docs]def xlfGetDocument(arg_num, name=None):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfGetWorkspace(arg_num):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfGetWorkbook(arg_num, workbook_name=None):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfGetWindow(arg_num, workbook_name=None):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfWindows(match_type=None, mask=None):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfCaller():
raise Exception("Not supported when running outside of Excel")
[docs]def xlAsyncReturn(async_handle, value):
raise Exception("Not supported when running outside of Excel")
[docs]def xlcAlert(message):
raise Exception("Not supported when running outside of Excel")
[docs]def xlcCalculation(calculation_type):
raise Exception("Not supported when running outside of Excel")
[docs]def xlcCalculateNow():
raise Exception("Not supported when running outside of Excel")
[docs]def xlcCalculateDocument():
raise Exception("Not supported when running outside of Excel")
[docs]def xlAbort(retain=True):
raise Exception("Not supported when running outside of Excel")
[docs]def xlSheetNm(sheet_id):
raise Exception("Not supported when running outside of Excel")
[docs]def xlSheetId(sheet_name):
raise Exception("Not supported when running outside of Excel")
[docs]def xlfVolatile(volatile):
# has no effect when running outside of Excel
pass
[docs]class XLAsyncHandle(object):
caller = None
function_name = None
def __init__(self, *args, **kwargs):
assert Exception("Not supported when running outside of Excel")
[docs] def set_value(self, value):
assert Exception("Not supported when running outside of Excel")
[docs] def set_error(self, exc_type, exc_value, exc_traceback):
assert Exception("Not supported when running outside of Excel")
[docs]class XLCell(object):
def __init__(self, *args, **kwargs):
assert Exception("Not supported when running outside of Excel")
[docs] @staticmethod
def from_range(range):
assert Exception("Not supported when running outside of Excel")
[docs] def to_range(self, com_package=None):
"""Return an Excel.Range object using the COM package specified.
:param com_package: COM package to use to return the COM Range object.
May be any of:
- win32com
- comtypes
- xlwings
"""
assert Exception("Not supported when running outside of Excel")
[docs] def options(self, type=None, auto_resize=None, type_kwargs=None, formatter=None):
"""Set options that control how values are retrieved and set.
:param type: Type to convert values to when getting.
:param auto_resize: If True, auto-resize when setting arrays.
:param type_kwargs: Options for type converter.
:param formatter: Formatter to apply when setting value.
:return: Return self so this method can easily be chained.
"""
return self
@property
def rows(self):
"""Return the number of rows in the range."""
assert Exception("Not supported when running outside of Excel")
@property
def columns(self):
"""Return the number of columns in the range."""
assert Exception("Not supported when running outside of Excel")
def row(self, i):
"""Return a new XLCell for a row of this instance.
:param i: Index from 0 for the row to return.
"""
assert Exception("Not supported when running outside of Excel")
def column(self, i):
"""Return a new XLCell for a column of this instance.
:param i: Index from 0 for the column to return.
"""
assert Exception("Not supported when running outside of Excel")
def cell(self, row, column):
"""Return a new XLCell for an individual cell of this instance.
:param row: Index from 0 for the row to select.
:param column: Index from 0 for the column to select.
"""
assert Exception("Not supported when running outside of Excel")
def resize(self, rows, columns):
"""Resize the range.
The object is updated in-place.
:param rows: Number of rows to resize to.
:param columns: Number of columns to resize to.
"""
assert Exception("Not supported when running outside of Excel")
[docs]class RTD(object):
def __init__(self, *args, **kwargs):
assert Exception("Not supported when running outside of Excel")
if sys.version_info[:3] >= (3, 5, 1):
import asyncio
[docs] def get_event_loop():
"""
Get the async event loop used by PyXLL for scheduling async tasks.
If called in Excel and the event loop is not already running it is
started in a background thread.
If called outside of Excel then the event loop is returned
without starting it.
:return: asyncio.AbstractEventLoop
"""
return asyncio.get_event_loop()
_pd_pandas = None
_pd_numpy = None
def _pandas_get_imports():
"""Convenience function to lazily import pandas and numpy."""
global _pd_pandas, _pd_numpy
if _pd_pandas is None:
import pandas as pandas_module
_pd_pandas = pandas_module
if _pd_numpy is None:
import numpy as numpy_module
_pd_numpy = numpy_module
return _pd_pandas, _pd_numpy
class ObjectCacheKeyError(KeyError):
"""Key not found in object cache"""
pass
#
# API functions / decorators
#
[docs]def get_config():
"""returns the PyXLL config as a ConfigParser.RawConfigParser instance"""
raise Exception("Not supported when running outside of Excel")
[docs]def xl_version():
"""
returns the version of Excel the addin is running in as a float.
8.0 => Excel 97
9.0 => Excel 2000
10.0 => Excel 2002
11.0 => Excel 2003
12.0 => Excel 2007
14.0 => Excel 2010
"""
raise Exception("Not supported when running outside of Excel")
[docs]def xl_func(signature=None,
category="PyXLL",
help_topic="",
thread_safe=False,
macro=False,
allow_abort=None,
volatile=None,
disable_function_wizard_calc=False,
disable_replace_calc=False,
arg_descriptions=None,
name=None,
auto_resize=None,
hidden=False,
transpose=False,
recalc_on_open=None,
formatter=None):
"""
Decorator for exposing functions to excel, e.g.:
@xl_func
def my_xl_function(a, b, c):
'''docstrings appear as helptext in excel'''
return "%s %s %s" % (a, b, c)
A signature may be provided to give type information for the
arguments and return type, e.g.:
@xl_func("string a, int b, float c: string")
def my_xl_function(a, b, c)
return "%s %d %f" % (a, b, c)
Valid types are:
str, string, int, bool, float, float[], var or types registered
with xl_arg_type.
The return type is optional, it will default to var.
Or where available, type hints may be used:
@xl_func
def strlen(x: str) -> int:
return len(x)
"""
# xl_func may be called with no arguments as a plain decorator, in which
# case the first argument will be the function it's applied to.
if signature is not None and callable(signature):
return signature
# or it will eturn a dectorator.
def dummy_decorator(func):
return func
return dummy_decorator
def xl_arg_doc(arg_name, docstring):
"""
Decorator for documenting a function's named parameters.
Must be applied before xl_func.
eg:
@xl_func("int a, int b: int")
@xl_arg_doc("a", "this is the docstring for a")
@xl_arg_doc("b", "this is the docstring for b")
def my_xl_function(a, b):
return a + b
Alternatively if no docstrings are explicitly supplied
and the function has a docstring, PyXLL will try and
find parameter documentation in the docstring.
@xl_func("int a, int b: int")
def my_xl_function(a, b):
'''
return a + b
a : this is the docstring for a
b : this is the docstring for b
'''
return a + b
"""
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def xl_macro(signature=None,
allow_abort=None,
arg_descriptions=None,
name=None,
shortcut=None,
transpose=False):
"""
Decorator for exposing python functions as macros.
Macros are used like VBA macros and can be assigned to buttons.
They take no arguments the return value is not used.
Macros may call macro sheet functions and may call back
into Excel like menu items.
eg:
@xl_macro
def my_macro():
win32api.MessageBox(0, "my_macro", "my_macro")
A signature may be applied to the function, e.g.:
@xl_macro("string x: int")
def strlen(x):
return len(x)
Or where possible, type hints may be used:
@xl_macro
def strlen(x: str) -> int:
return len(x)
"""
# xl_macro may be called with no arguments as a plain decorator, in which
# case the first argument will be the function it's applied to.
if signature is not None and callable(signature):
return signature
# or it will eturn a dectorator.
def dummy_decorator(func):
return func
return dummy_decorator
[docs]def xl_license_notifier(func):
"""
Decorator for callbacks to notify user code of the current state of
the license.
The decorated function must be of the form:
def callback(string name, datetime.date expdate, int days_left, bool is_perpetual)
All registered callbacks are called only once when the license is
checked at the time pyxll is first loaded.
If the license is perpetual, expdate will be end date of the maintenance contract
and days_left will be the days between the pyxll build date and expdate.
"""
return func
[docs]def xl_on_close(func):
"""
Decorator for callbacks that should be called when Excel is about
to be closed.
Even after this function has been called, it's possible Excel won't
actually close as the user may veto it.
The function should take no arguments.
"""
return func
[docs]def xl_on_reload(func):
"""
Decorator for callbacks that should be called after a reload is
attempted.
The callback takes a list of tuples of three three items:
(modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info
is the exception information (exc_type, exc_value, exc_traceback).
"""
return func
[docs]def xl_on_open(func):
"""
Decorator for callbacks that should be called after PyXLL has
been opened and the user modules have been imported.
The callback takes a list of tuples of three three items:
(modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info
is the exception information (exc_type, exc_value, exc_traceback).
"""
return func
_async_call_warning_enabled = True
def async_call(func, *args, **kwargs):
"""
Schedule a function to be called after the current Excel
calculation cycle has completed.
The function is called in an Excel macro context with automatic
calculation disabled, so it is safe to use :py:func:`xl_app` and
other COM and macro functions.
This can be used by worksheet functions that need to modify the worksheet
where calling back into Excel would fail or cause a deadlock.
NOTE: In the stubs version (not embedded in PyXLL) the function
is called immediately.
:param func: Function or callable object to call in an Excel macro context
at some time in the near future.
"""
global _async_call_warning_enabled
if _async_call_warning_enabled:
_log.warning("pyxll.async_call is deprecated and will be removed in a future release. "
"Use pyxll.schedule_call instead.")
_async_call_warning_enabled = False
func(*args, **kwargs)
[docs]def schedule_call(func, *args, **kwargs):
"""
Schedule a function to be called after the current Excel
calculation cycle has completed.
The function is called in an Excel macro context with automatic
calculation disabled, so it is safe to use :py:func:`xl_app` and
other COM and macro functions.
This can be used by worksheet functions that need to modify the worksheet
where calling back into Excel would fail or cause a deadlock.
From Python 3.7 onwards when called from the PyXLL asyncio event loop and
'nowait' is not set this function returns an asyncio.Future. This future
can be awaited on to get the result of the call.
NOTE: In the stubs version (not embedded in PyXLL) the function
is called immediately.
:param func: Function or callable object to call in an Excel macro context
at some time in the near future.
:param *args: Arguments to be passed to the the function.
:param delay: Delay in seconds to wait before calling the function.
:param nowait: Do not return a Future even if called from the asyncio event loop.
:param retries: Integer number of times to retry.
:param retry_delay: Time in seconds to wait between retries.
:param retry_backoff: Multiplier to apply to 'retry_delay' after each retry. This
can be used to increase the time between each retry by setting
'retry_backoff' to > 1.0.
param retry_filter: Callable that received the exception value in the case of an
error. It should return True if a retry should be attempted
or False otherwise.
.. warning::
This function doesn't allow passing keyword arguments to the schedule function.
To do that, use functools.partial().::
# Will schedule "print("Hello", flush=True)"
schedule_call(functools.partial(print, "Hello", flush=True))
"""
delay = kwargs.pop("delay", 0)
nowait = kwargs.pop("nowait", False)
retries = int(kwargs.pop("retries", 0))
retry_delay = float(kwargs.pop("retry_delay", 0))
retry_backoff = float(kwargs.pop("retry_backoff", 1.0))
retry_filter = kwargs.pop("retry_filter", None)
for kw in kwargs:
raise TypeError("'%s' is an invalid keyword argument for schedule_call()" % kw)
# In the stubs package the function gets called immediately, regardless
# of the delay passed in or what thread we might be running in.
result = func(*args)
if not nowait:
# Check to see if we are running in an asyncio event loop
try:
import asyncio
loop = asyncio.get_running_loop()
except (ImportError, AttributeError, RuntimeError):
return
# We are so return the result in a Future
future = loop.create_future()
future.set_result(result)
return future
_ribbon_xml = None
_default_ribbon_xml = """<?xml version="1.0" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" loadImage="pyxll.load_image">
<ribbon><tabs/></ribbon>
</customUI>
"""
def _xml_to_dom(xml):
"""Takes xml as a string or dom and returns a document object"""
if not isinstance(xml, minidom.Document):
return minidom.parseString(xml)
return xml
def _validate_ribbon_xml(xml):
"""Tests the ribbon xml to make sure it looks reasonable.
Returns validated xml as text.
"""
xml = _xml_to_dom(xml)
if not xml.documentElement:
raise AssertionError("Ribbon XML is missing a root document element")
if not xml.documentElement.tagName == "customUI":
raise AssertionError("Ribbon XML document element is expected to be 'customUI' "
"(not '%s'" % xml.documentElement.tagName)
def _assert_unique_attr(element, attr, error, values={}):
elements = [element]
while elements:
element = elements[0]
elements = elements[1:] + list(element.childNodes or [])
attributes = element.attributes or {}
value = getattr(attributes.get(attr, None), "value", None)
if value:
if value in values:
raise AssertionError(error % {"attr": attr, "value": value})
values[value] = None
_assert_unique_attr(xml.documentElement,
"id",
"Duplicate %(attr)s attribute '%(value)s' found.")
def _assert_at_most_one(element, tag_name, error):
children = {}
for child in element.childNodes:
if child.nodeType != child.ELEMENT_NODE:
continue
children.setdefault(child.tagName, [])
children[child.tagName].append(child)
tag_children = children.get(tag_name, [])
if len(tag_children) > 1:
raise AssertionError(error)
if tag_children:
return tag_children[0]
ribbon = _assert_at_most_one(xml.documentElement,
"ribbon",
"'customUI' element in ribbon XML should have a single ribbon node")
if ribbon:
_assert_at_most_one(ribbon,
"tabs",
"'ribbon' element in ribbon XML should have a single tabs node)")
return xml.toxml()
[docs]def set_ribbon_tab(xml, tab_id=None, reload=True):
"""
Sets a tab in the ribbon using an xml fragment.
:param xml: Ribbon xml as a string containing at least one tab element.
:param tab_id: Id of tab to add if multiple tabs exist in the xml.
:param reload: If true the ribbon will be refreshed immediately.
"""
xml = _xml_to_dom(xml)
if tab_id:
tab_id = str(tab_id)
for new_tab in xml.getElementsByTagName("tab"):
if str(new_tab.getAttribute("id")) == tab_id:
break
else:
raise KeyError("Tab '%s' not found" % tab_id)
else:
new_tabs = xml.getElementsByTagName("tab")
if not new_tabs:
raise RuntimeError("No 'tab' elements found")
new_tab = new_tabs[0]
tab_id = str(new_tab.getAttribute("id") or "")
new_xml = get_ribbon_xml()
if not new_xml:
new_xml = _default_ribbon_xml
new_xml = _xml_to_dom(new_xml)
tabs = new_xml.getElementsByTagName("tabs")
if tabs:
tabs = tabs[0]
else:
ribbon = new_xml.getElementsByTagName("ribbon")
if ribbon:
ribbon = ribbon[0]
else:
ribbon = new_xml.createElement("ribbon")
new_xml.documentElement.appendChild(ribbon)
tabs = new_xml.createElement("tabs")
ribbon.appendChild(tabs)
if tab_id:
for tab in tabs.childNodes:
if tab.nodeType == tab.ELEMENT_NODE \
and str(tab.tagName) == "tab" \
and str(tab.getAttribute("id")) == tab_id:
tabs.replaceChild(new_tab, tab)
break
else:
tabs.appendChild(new_tab)
else:
tabs.appendChild(new_tab)
set_ribbon_xml(new_xml, reload=reload)
[docs]def remove_ribbon_tab(tab_id, reload=True):
"""
Removes a tab previously added to the ribbon.
:param tab_id: Id of tab to remove
:param reload: If true the ribbon will be refreshed immediately.
:return: True if a tab was removed, False otherwise.
"""
new_xml = get_ribbon_xml()
if not new_xml:
return False
new_xml = minidom.parseString(new_xml)
tabs = new_xml.getElementsByTagName("tab")
if not tabs:
return False
updated = False
tab_id = str(tab_id)
for tab in tabs:
if str(tab.getAttribute("id")) == tab_id:
tab.parentNode.removeChild(tab)
updated = True
if not updated:
return False
set_ribbon_xml(new_xml, reload=reload)
[docs]def get_ribbon_xml():
"""Returns the ribbon XML currenly in use by PyXLL."""
return _ribbon_xml
[docs]def set_ribbon_xml(xml, reload=True):
"""
Sets the XML used by PyXLL for customizing the ribbon.
:param xml: XML document to set as the current ribbon
:param reload: If true the ribbon will be refreshed immediately.
"""
global _ribbon_xml
_ribbon_xml = _validate_ribbon_xml(xml)
if reload:
# com_reload only reloads the COM part of the addin, which is
# responsible for the ribbon. This doesn't reload the rest of
# the PyXLL addin so no functions will get re-registered.
com_reload()
[docs]class ErrorContext:
"""Context object passed to the PyXLL error handler."""
[docs] class Type:
"""Type to indicate the origination of the error."""
UDF = "udf"
MACRO = "macro"
MENU = "menu"
RIBBON = "ribbon"
IMPORT = "import"
def __init__(self, error_type, function_name=None, import_errors=None):
self.error_type = error_type
self.function_name = function_name
self.import_errors = import_errors
def error_handler(context, exc_type, exc_value, exc_traceback):
"""Standard PyXLL error handler."""
# For UDFs return a preview of the error as a single line
if context.error_type in (ErrorContext.Type.UDF, ErrorContext.Type.MACRO):
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
# For menus and ribbon functions display an error message.
if context.error_type in (ErrorContext.Type.MENU, ErrorContext.Type.RIBBON):
message = ""
if context.function_name:
message = "Error calling '%s'\n\n" % context.function_name
elif context.error_type == ErrorContext.Type.RIBBON:
message = "PyXLL Ribbon Error\n\n"
exc_type_name = getattr(exc_type, "__name__", "Exception")
message += str(exc_type_name) + (str(exc_value) and (": " + str(exc_value)) or "") + "\n\n"
exc_instance = exc_value
if not isinstance(exc_instance, BaseException):
try:
exc_instance = exc_type(exc_value)
except:
exc_instance = None
message += "".join(traceback.format_exception(exc_type, exc_instance, exc_traceback))
caption = "PyXLL: " + ((context.error_type == ErrorContext.Type.MENU and "Menu") or "Ribbon") + " Error"
# MB_SETFOREGROUND | MB_ICONHAND | MB_HELP
message_box(message, caption, 0x00010000 | 0x00000010 | 0x00004000)
return
if context.error_type == ErrorContext.Type.IMPORT:
lines = ["Error importing Python modules"]
if context.import_errors:
lines.append("")
for modulename, exc_info in context.import_errors:
exc_type, exc_value, exc_traceback = exc_info
lines.append("Error importing '%s': %s" % (modulename, exc_value))
message = "\n".join(lines)
# MB_SETFOREGROUND | MB_ICONHAND | MB_HELP
message_box(message, "PyXLL: Error importing Python modules", 0x00010000 | 0x00000010 | 0x00004000)
return
def error_handler_quiet(context, exc_type, exc_value, exc_traceback):
"""PyXLL error handler that doesn't display any error dialogs"""
# For UDFs return a preview of the error as a single line
if context.error_type in (ErrorContext.Type.UDF, ErrorContext.Type.MACRO):
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
# for backwards compatibility with older pyxll.cfg files
error_to_string = error_handler
def _wrap_iunknown(unk, no_dynamic_dispatch=False):
"""Wrap a pythoncom PyIUnknown object into a win32com.Dispatch"""
# Get the PyIDispatch object
import pythoncom
disp = unk.QueryInterface(pythoncom.IID_IDispatch)
# Try to wrap using the existing wrapper classes (or generate them if
# they don't exist)
try:
from win32com.client.gencache import EnsureDispatch
return EnsureDispatch(disp)
except:
if no_dynamic_dispatch:
raise
pass
# If we can't use a generated wrapper then use a dynamic Dispatch wrapper.
# This will work but won't add constants to win32com.client.constants.
import win32com
_log.warning("win32com.client.Dispatch failed. win32com.client.dynamic.Dispatch will be used instead.")
_log.warning("Delete your gen_py folder '%s' as it may have become corrupted" % win32com.__gen_path__)
from win32com.client.dynamic import Dispatch as DynamicDispatch
return DynamicDispatch(disp)
class BaseFormatter(object):
"""Base class used for formatting Excel ranges.
"""
@classmethod
def __get_numpy(cls):
try:
return cls.__np
except AttributeError:
try:
import numpy
cls.__np = numpy
except ImportError:
cls.__np = None
return cls.__np
@classmethod
def __is_sequence(cls, x):
np = cls.__get_numpy()
if np:
return isinstance(x, (list, tuple, np.ndarray))
return isinstance(x, (list, tuple))
@classmethod
def __resize_array(cls, value, cell, datatype, datatype_ndim, transpose):
# If the data type is object and it's not an array, don't check if the object
# is a sequence just convert it to a 1x1 array.
if datatype == "object" and datatype_ndim == 0:
value = [[value]]
# If the data type is a 1d array convert it to 1xN row array
if datatype_ndim == 1:
value = [value]
# Make sure the value is a 2d array
if not cls.__is_sequence(value):
value = [value]
if len(value) == 0:
value = [[None]]
if not cls.__is_sequence(value[0]):
value = [value]
# Transpose the array before matching it to the cell range
if transpose:
value = list(zip(*value))
# Pad or trim the array to match the cell range
num_rows = cell.rows
num_cols = cell.columns
new_value = [None] * num_rows
for r in range(num_rows):
row = []
if r < len(value):
row = value[r]
if len(row) > num_cols:
row = row[:num_cols]
if len(row) < num_cols:
row = list(row) + ([None] * (num_cols - len(row)))
new_value[r] = row
return new_value
def apply(self, cell, value=None, datatype=None, datatype_ndim=0, datatype_kwargs={}, transpose=False):
"""Apply formatting to a cell or range of cells.
:param cell: XLCell instance to by formatted.
:param value: Value being set on the cell(s).
:param datatype: Data-type as specified as the return type when using @xl_func.
:param datatype_ndim: Number of dimensions if the datatype is an array.
:param datatype_kwargs: Data-type keyword arguments.
:param transpose: True if the value should be transposed before styling.
"""
if self.apply_cell is not BaseFormatter.apply_cell:
value = self.__resize_array(value, cell, datatype, datatype_ndim, transpose)
for i, row_value in enumerate(value):
for j, cell_value in enumerate(row_value):
self.apply_cell(cell.cell(i, j),
cell_value,
datatype=datatype,
datatype_kwargs=datatype_kwargs)
@staticmethod
def apply_cell(cell, value=None, datatype=None, datatype_kwargs={}):
"""Called for each cell to apply any formatting.
For most Formatter classes this is the only method that needs to
be overridden.
:param cell: XLCell instance to by formatted.
:param value: Value being set on the cell(s).
:param datatype: Data-type as specified as the return type when using @xl_func.
:param datatype_kwargs: Data-type keyword arguments.
"""
pass
@staticmethod
def clear(cell):
"""Clears any formatting from a cell or range of cells."""
r = cell.to_range()
r.Style = "Normal"
@staticmethod
def rgb(red, green, blue):
"""Return a color number from RGB values.
Note Excel uses 'BGR' instead of the more usual RGB.
"""
return (red & 0xff) | ((green & 0xff) << 8) | ((blue & 0xff) << 16)
@staticmethod
def apply_style(cell, style):
"""Apply a formatting style dictionary to a cell."""
if not style:
return
r = cell.to_range()
interior_color = style.get("interior_color", None)
if interior_color is not None:
r.Interior.Color = interior_color
text_color = style.get("text_color", None)
if text_color is not None:
r.Font.Color = text_color
bold = style.get("bold", None)
if bold is not None:
r.Font.Bold = bold
italic = style.get("italic", None)
if italic is not None:
r.Font.Italic = italic
font_size = style.get("font_size", None)
if font_size is not None:
r.Font.Size = font_size
number_format = style.get("number_format", None)
if number_format is not None:
r.NumberFormat = number_format
auto_fit = style.get("auto_fit", False)
if auto_fit:
r.AutoFit()
class FormatterCollection(BaseFormatter):
def __init__(self, formatters=[]):
self.__formatters = formatters
def apply(self, cell, value=None, datatype=None, datatype_ndim=0, datatype_kwargs={}, transpose=False):
for formatter in self.__formatters:
formatter.apply(cell,
value,
datatype=datatype,
datatype_ndim=datatype_ndim,
datatype_kwargs=datatype_kwargs,
transpose=transpose)
def clear(self, cell):
# Only call clear once unless implemented differently from the base class
cleared = False
for formatter in self.__formatters:
if not cleared or formatter.clear is not BaseFormatter.clear:
formatter.clear(cell)
cleared = True
def __add__(self, other):
if isinstance(other, dict):
other = Formatter(**other)
if not isinstance(other, BaseFormatter):
raise TypeError("Can't add non-formatter object to a formatter.")
return FormatterCollection(self.__formatters + [other])
def __iadd__(self, other):
if isinstance(other, dict):
other = Formatter(**other)
if not isinstance(other, BaseFormatter):
raise TypeError("Can't add non-formatter object to a formatter.")
self.__formatters.append(other)
return self
CTPDockPositionLeft = 0
CTPDockPositionTop = 1
CTPDockPositionRight = 2
CTPDockPositionBottom = 3
CTPDockPositionFloating = 4
[docs]class CTPBridgeBase:
"""Base class of bridges between the Python UI toolkits
and PyXLL's Custom Task Panes.
"""
[docs] def __init__(self, control):
self.control = control
[docs] def get_hwnd(self):
raise NotImplemented()
[docs] def get_title(self):
pass
[docs] def pre_attach(self, hwnd):
pass
[docs] def post_attach(self, hwnd):
pass
[docs] def on_close(self):
pass
[docs] def on_window_closed(self):
pass
[docs] def on_window_destroyed(self):
pass
[docs] def process_message(self, hwnd, msg, wparam, lparam):
pass
[docs] def translate_accelerator(self, hwnd, msg, wparam, lparam, modifier):
pass
[docs] def on_timer(self):
pass
[docs]def create_ctp(control, title=None, width=None, height=None, position=CTPDockPositionRight):
"""Creates a Custom Task Pane from a UI control object.
The control object can be any of the following:
- tkinter.Toplevel
- PyQt5.QtWidgets.QWidget
- PySide2.QtWidgets.QWidget
- wx.Frame
:param control: UI control of one of the supported types.
:param title: Title of the custom task pane to be created.
:param width: Initial width of the custom task pane.
:param height: Initial height of the custom task pane.
:param position: Where to display the custom task pane. Can be any of:
- CTPDockPositionLeft
- CTPDockPositionTop
- CTPDockPositionRight
- CTPDockPositionBottom
- CTPDockPositionFloating
"""
raise Exception("Not supported when running outside of Excel")
[docs]class PlotBridgeBase:
[docs] def __init__(self, figure):
self.figure = figure
[docs] def can_export(self, format):
"""Return True if the figure can be exported in a specific format.
Formats are svg and png.
"""
return False
[docs] def get_size_hint(self, dpi):
"""Return (width, height) tuple the figure should be exported as
or None. Width and height are in points (72th of an inch)."""
return None
[docs] def export(self, width, height, dpi, format, filename, **kwargs):
"""Export the figure to a file as a given size and format."""
raise NotImplementedError()
[docs]def plot(figure=None,
name=None,
width=None,
height=None,
top=None,
left=None,
sheet=None,
allow_svg=True,
bridge_cls=None,
**kwargs):
"""Plots a figure to Excel as an embedded image.
The figure can be any of the following:
- A matplotlib Figure object
:param figure: Figure to plot. This can be an instance of any of the following:
- matplotlib.figure.Figure
- plotly.graph_objects.Figure
- bokeh.models.plots.Plot
If none, the active matplotlib.pyplot figure is used.
:param name: Name of Picture object in Excel. If this is None then
a name will be chosen, and if called from a UDF then
repeated calls with re-use the same name.
:param width: Width of the picture in Excel, in points.
If set then height must also be set.
If None the width will be taken from the figure.
:param height: Height of the picture in Excel, in points.
If set then width must also be set.
If None the height will be taken from the figure.
:param top: Location of the top of the plot in Excel, in points.
If set then left must also be set.
If None, the picture will be placed below the current or selected cell.
:param left: Location of the left of the plot in Excel, in points.
If set then top must also be set.
If None, the picture will be placed below the current or selected cell.
:param sheet: Name of the sheet to add the picture to. If none, the current sheet is used.
:param allow_svg: Some figures may be rendered as SVG, if the version of Excel allows.
This can be disabled by setting this option to False.
:param kwargs: Additional arguments will be called to the implementation specific
method for exporting the figure to an image.
"""
raise Exception("Not supported when running outside of Excel")
