def array_pyxll_function_5(x):
"""
return the input array with row and col numbers.
This example shows how to use xlfCaller to get the range
of the cells the array function is being called by.
"""
# get the size of the rect the array function was called over
# i.e. the size of the array to be returned
caller = xlfCaller()
width = caller.rect.last_col - caller.rect.first_col + 1
height = caller.rect.last_row - caller.rect.first_row + 1
# check the input array is the same size
assert len(x) == height
assert len(x[0]) == width
# construct the return value as a list of lists with the
# same dimensions as the calling cells.
result = []
for i in range(height):
row = []
for j in range(width):
row.append("%s (col=%d, row=%d)" % (x[i][j], j, i))
result.append(row)
return result
def automation_example(rows, cols, value):
"""copies value to a range of rows x cols below the calling cell"""
# Get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
# The update is done asynchronously so as not to block Excel by
# updating the worksheet from a worksheet function
def update_func():
# Get the Excel.Application COM object
xl = xl_app()
# Get an Excel.Range object from the XLCell instance
range = caller.to_range(com_package="win32com")
# get the cell below and expand it to rows x cols
range = xl.Range(range.Resize(2, 1), range.Resize(rows + 1, cols))
# and set the range's value
range.Value = value
# kick off the asynchronous call the update function
pyxll.async_call(update_func)
return address
def array_pyxll_function_5(x):
"""
return the input array with row and col numbers.
This example shows how to use xlfCaller to get the range
of the cells the array function is being called by.
"""
# get the size of the rect the array function was called over
# i.e. the size of the array to be returned
caller = xlfCaller()
width = caller.rect.last_col - caller.rect.first_col + 1
height = caller.rect.last_row - caller.rect.first_row + 1
# check the input array is the same size
assert len(x) == height
assert len(x[0]) == width
# construct the return value as a list of lists with the
# same dimensions as the calling cells.
result = []
for i in range(height):
row = []
for j in range(width):
row.append("%s (col=%d, row=%d)" % (x[i][j], j, i))
result.append(row)
return result
def mpl_plot_ewma_embedded(figname, xs, ys, span):
# create the figure and axes for the plot
fig = Figure(figsize=(8, 6), dpi=75, facecolor=(1, 1, 1), edgecolor=(0, 0, 0))
ax = fig.add_subplot(111)
# calculate the moving average
ewma_ys = ewma(ys, span=span)
# plot the data
ax.plot(xs, ys, alpha=0.4, label="Raw")
ax.plot(xs, ewma_ys, label="EWMA")
ax.legend()
# write the figure to a temporary image file
filename = os.path.join(os.environ["TEMP"], "xlplot_%s.png" % figname)
canvas = FigureCanvas(fig)
canvas.draw()
canvas.print_png(filename)
# Show the figure in Excel as a Picture object on the same sheet
# the function is being called from.
xl = xl_app()
caller = xlfCaller()
sheet = xl.Range(caller.address).Worksheet
# if a picture with the same figname already exists then get the position
# and size from the old picture and delete it.
for old_picture in sheet.Pictures():
if old_picture.Name == figname:
height = old_picture.Height
width = old_picture.Width
top = old_picture.Top
left = old_picture.Left
old_picture.Delete()
break
else:
# otherwise place the picture below the calling cell.
top_left = sheet.Cells(caller.rect.last_row+2, caller.rect.last_col+1)
top = top_left.Top
left = top_left.Left
width, height = fig.bbox.bounds[2:]
# insert the picture
# Ref: http://msdn.microsoft.com/en-us/library/office/ff198302%28v=office.15%29.aspx
picture = sheet.Shapes.AddPicture(Filename=filename,
LinkToFile=0, # msoFalse
SaveWithDocument=-1, # msoTrue
Left=left,
Top=top,
Width=width,
Height=height)
# set the name of the new picture so we can find it next time
picture.Name = figname
# delete the temporary file
os.unlink(filename)
return "[Plotted '%s']" % figname
def MiInputArray(array):
global inp
global arrayvals
arrayvals = array
inp=[]
for row in array:
for cell_value in row:
inp.append(cell_value)
global num_of_inp
num_of_inp = len(inp)
global inparraycell
inparraycell = xlfCaller()
return "Array of Input(s)"
def plot_to_excel(figure, name):
"""Plot a figure in Excel"""
# write the figure to a temporary image file
filename = os.path.join(os.environ["TEMP"], "xlplot_%s.png" % name)
canvas = FigureCanvas(figure)
canvas.draw()
canvas.print_png(filename)
# Show the figure in Excel as a Picture object on the same sheet
# the function is being called from.
xl = xl_app()
caller = xlfCaller()
sheet = xl.Range(caller.address).Worksheet
# if a picture with the same figname already exists then get the position
# and size from the old picture and delete it.
for old_picture in sheet.Pictures():
if old_picture.Name == name:
height = old_picture.Height
width = old_picture.Width
top = old_picture.Top
left = old_picture.Left
old_picture.Delete()
break
else:
# otherwise place the picture below the calling cell.
top_left = sheet.Cells(caller.rect.last_row + 2,
caller.rect.last_col + 1)
top = top_left.Top
left = top_left.Left
width, height = figure.bbox.bounds[2:]
# insert the picture
# Ref: http://msdn.microsoft.com/en-us/library/office/ff198302%28v=office.15%29.aspx
picture = sheet.Shapes.AddPicture(
Filename=filename,
LinkToFile=0, # msoFalse
SaveWithDocument=-1, # msoTrue
Left=left,
Top=top,
Width=width,
Height=height)
# set the name of the new picture so we can find it next time
picture.Name = name
# delete the temporary file
os.unlink(filename)
return "[Plotted '%s']" % name
def MiInput(cell):
global inp
global inpcell
global formstr
global current_inp
global arrayvals
inpcell = xlfCaller()
formstr = str(inpcell.formula)
#
# inp=[]
# for row in arrayvals:
# for cell_value in row:
# inp.append(cell_value)
# rect1 = inpcell.rect
# xlcAlert(str(rect1.first_row))
# xlcAlert(str(rect1.first_col))
return inp[current_inp]
def draw(arr):
"""Renders a one- or twodimensional scalar array."""
if not isinstance(arr, np.ndarray):
raise TypeError('Numpy ndarray expected.')
# get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
#=======================================================================
# the update is done asynchronously so as not to block some
# versions of Excel by updating the worksheet from a worksheet function
def update_func(x):
xl = xl_app()
if xl is not None:
range = xl.Range(address)
y = None
try:
if x.ndim == 1:
range = xl.Range(range.Resize(2, 1),
range.Resize(x.shape[0]+1, 1))
# we need to reshape a 1D vector into a 2D array
y = np.reshape(x, (x.shape[0], 1))
elif x.ndim == 2:
range = xl.Range(range.Resize(2, 1),
range.Resize(x.shape[0]+1, x.shape[1]))
y = x
else:
raise ValueError('Array rank must be 1 or 2.')
range.Value = y
except Exception, ex:
logger.info(ex)
def automation_example(rows, cols, value):
"""copies value to a range of rows x cols below the calling cell"""
# get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
# the update is done asynchronously so as not to block some
# versions of Excel by updating the worksheet from a worksheet function
def update_func():
xl = xl_app()
range = xl.Range(address)
# get the cell below and expand it to rows x cols
range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))
# and set the range's value
range.Value = value
# kick off the asynchronous call the update function
pyxll.async_call(update_func)
return address
def cached_object_return_func(x):
"""
custom return type for objects that should be cached for use as
parameters to other xl functions
"""
# this requires the function to be registered as a macro sheet equivalent
# function because it calls xlfCaller, hence macro=True in
# the xl_return_type decorator above.
#
# As xlfCaller returns the individual cell a function was called from, it's
# not possible to return arrays of cached_objects using the cached_object[]
# type in a function signature. allow_arrays=False prevents a function from
# being registered with that return type. Arrays of cached_objects as an
# argument type is fine though.
if _have_win32com:
# _setup_event_handler creates an event handler for Excel events to
# ensure the cache is kept up to date with cell changes
_setup_event_handler(_global_cache)
# get the calling cell in [book]sheet!address format
caller = xlfCaller()
address = caller.address
# split the cell up into workbook, sheet and cell
assert "!" in address, "Calling cell address not in [book]sheet!address format: %s" % address
wb_and_sheet, cell = address.split("!", 1)
wb_and_sheet = wb_and_sheet.strip("'")
assert wb_and_sheet.startswith("[") and "]" in wb_and_sheet, \
"Calling cell not in [book]sheet!address format: %s" % address
workbook, sheet = wb_and_sheet.strip("[").split("]", 1)
while "''" in sheet:
sheet = sheet.replace("''", "'")
# update the cache and return the cached object id
return _global_cache.update(workbook, sheet, cell, x)
def cached_object_return_func(x):
"""
Custom return type for objects that should be cached for use as
parameters to other xl functions.
"""
global _global_cache
# This requires the function to be registered as a macro sheet equivalent
# function because it calls xlfCaller, hence macro=True in
# the xl_return_type decorator above.
#
# As xlfCaller returns the individual cell a function was called from, it's
# not possible to return arrays of cached_objects using the cached_object[]
# type in a function signature. allow_arrays=False prevents a function from
# being registered with that return type. Arrays of cached_objects as an
# argument type is fine though.
if _have_win32com:
# _setup_event_handler creates an event handler for Excel events to
# ensure the cache is kept up to date with cell changes.
_setup_event_handler(_global_cache)
# Get the calling sheet and cell.
caller = xlfCaller()
sheet = caller.sheet_name
cell = (caller.rect.first_row, caller.rect.first_col)
# Check the function isn't being used as an array function.
assert cell == (caller.rect.last_row, caller.rect.last_col), \
"Functions returning objects should not be used as array functions"
# The sheet name will be in "[book]sheet" format.
workbook = None
if sheet.startswith("[") and "]" in sheet:
workbook, sheet = sheet.strip("[").split("]", 1)
# Update the cache and return the cached object id.
return _global_cache.update(workbook, sheet, cell, x)
def cached_object_return_func(x):
"""
custom return type for objects that should be cached for use as
parameters to other xl functions
"""
# this requires the function to be registered as a macro sheet equivalent
# function because it calls xlfCaller, hence macro=True in
# the xl_return_type decorator above.
#
# As xlfCaller returns the individual cell a function was called from, it's
# not possible to return arrays of cached_objects using the cached_object[]
# type in a function signature. allow_arrays=False prevents a function from
# being registered with that return type. Arrays of cached_objects as an
# argument type is fine though.
if _have_win32com:
# _setup_event_handler creates an event handler for Excel events to
# ensure the cache is kept up to date with cell changes
_setup_event_handler(_global_cache)
# get the calling cell in [book]sheet!address format
caller = xlfCaller()
address = caller.address
# split the cell up into workbook, sheet and cell
assert "!" in address, "Calling cell address not in [book]sheet!address format: %s" % address
wb_and_sheet, cell = address.split("!", 1)
wb_and_sheet = wb_and_sheet.strip("'")
assert wb_and_sheet.startswith("[") and "]" in wb_and_sheet, \
"Calling cell not in [book]sheet!address format: %s" % address
workbook, sheet = wb_and_sheet.strip("[").split("]", 1)
while "''" in sheet:
sheet = sheet.replace("''", "'")
# update the cache and return the cached object id
return _global_cache.update(workbook, sheet, cell, x)
def draw_table(tbl):
"""
Renders a table = list of rows = list of lists.
We assume the CALLER did the proper type conversions!!!
(We can handle strings, int32, and float64 colums.)
"""
if not isinstance(tbl, list):
raise TypeError('List expected.')
# get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
#=======================================================================
# the update is done asynchronously so as not to block some
# versions of Excel by updating the worksheet from a worksheet function
def update_func(x):
xl = xl_app()
if xl is not None:
range = xl.Range(address)
try:
header = x[0]
num_cols = len(header)
num_rows = len(x)
range = xl.Range(range.Resize(2, 1),
range.Resize(num_rows + 1, num_cols))
range.Value = x
except Exception, ex:
logger.info(ex)
def cached_object_return_func(x):
"""
Custom return type for objects that should be cached for use as
parameters to other xl functions.
"""
global _global_cache
# This requires the function to be registered as a macro sheet equivalent
# function because it calls xlfCaller, hence macro=True in
# the xl_return_type decorator above.
#
# As xlfCaller returns the individual cell a function was called from, it's
# not possible to return arrays of cached_objects using the cached_object[]
# type in a function signature. allow_arrays=False prevents a function from
# being registered with that return type. Arrays of cached_objects as an
# argument type is fine though.
if _have_win32com:
# _setup_event_handler creates an event handler for Excel events to
# ensure the cache is kept up to date with cell changes.
_setup_event_handler(_global_cache)
# Get the calling sheet and cell.
caller = xlfCaller()
sheet = caller.sheet_name
cell = (caller.rect.first_row, caller.rect.first_col)
# Check the function isn't being used as an array function.
assert cell == (caller.rect.last_row, caller.rect.last_col), \
"Functions returning objects should not be used as array functions"
# The sheet name will be in "[book]sheet" format.
workbook = None
if sheet.startswith("[") and "]" in sheet:
workbook, sheet = sheet.strip("[").split("]", 1)
# Update the cache and return the cached object id.
return _global_cache.update(workbook, sheet, cell, x)
def hc_plot(chart, control_name, theme=None):
"""
This function is used by the other plotting functions to render the chart as html
and display it in Excel.
"""
# add the theme if there is one
if theme:
chart.add_JSsource(
["https://code.highcharts.com/themes/%s.js" % theme])
# get the calling sheet
caller = xlfCaller()
sheet_name = caller.sheet_name
# split into workbook and sheet name
match = re.match("^\[(.+?)\](.*)$", sheet_name.strip("'\""))
if not match:
raise Exception("Unexpected sheet name '%s'" % sheet_name)
workbook, sheet = match.groups()
# get the Worksheet object
xl = xl_app()
workbook = xl.Workbooks(workbook)
sheet = workbook.Sheets(sheet)
# find the existing webbrowser control, or create a new one
try:
control = sheet.OLEObjects(control_name[:31])
browser = control.Object
except:
control = sheet.OLEObjects().Add(ClassType="Shell.Explorer.2",
Left=147,
Top=60.75,
Width=400,
Height=400)
control.Name = control_name[:31]
browser = control.Object
# set the chart aspect ratio to match the browser
if control.Width > control.Height:
chart.set_options(
"chart",
{"height": "%d%%" % (100. * control.Height / control.Width)})
else:
chart.set_options(
"chart",
{"width": "%d%%" % (100. * control.Width / control.Height)})
# get the html and add the 'X-UA-Compatible' meta-tag
soup = BeautifulSoup(chart.htmlcontent)
metatag = soup.new_tag("meta")
metatag.attrs["http-equiv"] = "X-UA-Compatible"
metatag.attrs['content'] = "IE=edge"
soup.head.insert(0, metatag)
# write out the html for the browser to render
fh = tempfile.NamedTemporaryFile("wt", suffix=".html", delete=False)
filename = fh.name
# clean up the file after 10 seconds to give the browser time to load
def on_timer(timer_id, time):
timer.kill_timer(timer_id)
os.unlink(filename)
timer.set_timer(10000, on_timer)
fh.write(soup.prettify())
fh.close()
# navigate to the temporary file
browser.Navigate("file://%s" % filename)
return "[%s]" % control_name
def hc_plot(chart, control_name, theme=None):
"""
This function is used by the other plotting functions to render the chart as html
and display it in Excel.
"""
# add the theme if there is one
if theme:
chart.add_JSsource(["https://code.highcharts.com/themes/%s.js" % theme])
# get the calling sheet
caller = xlfCaller()
sheet_name = caller.sheet_name
# split into workbook and sheet name
match = re.match("^\[(.+?)\](.*)$", sheet_name.strip("'\""))
if not match:
raise Exception("Unexpected sheet name '%s'" % sheet_name)
workbook, sheet = match.groups()
# get the Worksheet object
xl = xl_app()
workbook = xl.Workbooks(workbook)
sheet = workbook.Sheets(sheet)
# find the existing webbrowser control, or create a new one
try:
control = sheet.OLEObjects(control_name[:31])
browser = control.Object
except:
control = sheet.OLEObjects().Add(ClassType="Shell.Explorer.2",
Left=147,
Top=60.75,
Width=400,
Height=400)
control.Name = control_name[:31]
browser = control.Object
# set the chart aspect ratio to match the browser
if control.Width > control.Height:
chart.set_options("chart", {
"height": "%d%%" % (100. * control.Height / control.Width)
})
else:
chart.set_options("chart", {
"width": "%d%%" % (100. * control.Width / control.Height)
})
# get the html and add the 'X-UA-Compatible' meta-tag
soup = BeautifulSoup(chart.htmlcontent)
metatag = soup.new_tag("meta")
metatag.attrs["http-equiv"] = "X-UA-Compatible"
metatag.attrs['content'] = "IE=edge"
soup.head.insert(0, metatag)
# write out the html for the browser to render
fh = tempfile.NamedTemporaryFile("wt", suffix=".html", delete=False)
filename = fh.name
# clean up the file after 10 seconds to give the browser time to load
def on_timer(timer_id, time):
timer.kill_timer(timer_id)
os.unlink(filename)
timer.set_timer(10000, on_timer)
fh.write(soup.prettify())
fh.close()
# navigate to the temporary file
browser.Navigate("file://%s" % filename)
return "[%s]" % control_name
