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 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 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 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