Interactive Python graphics/visualisation with Excel

Making Excel users benefit of the quick and powerful Python GUI tools was an idea that looked pretty interesting.

The two main arguments for that are :

  1. Python code is easily maintainable, tested and integrated compared to what can be inside of an Excel sheet
  2. Python has some powerful libraries for numerical computing, 2D/3D visualisation, etc.

For the last London Financial PUG meeting (March 11), Travis and I had the idea of making Chaco playing with Excel thanks to a cool tool named pyxll and the pywin32 extension. The example allows the user to select a range of columns in Excel, send them to a Chaco regression tool where the user can select points. A Chaco tool does lively compute a regression on those points and update the Excel sheets.

The result is pretty interesting as shown on the screenshot here below. (I will most probably post a video showing how interactive it is).

Pyxll Chaco interactive session

Pyxll is a very interesting library allowing you to very easily make your Python function available within Excel (either as menu or functions). Thanks a lot to Tony Roberts for his excellent pieces of advice on using pyxll for the demo.

Chaco Python plotting application toolkit that facilitates writing plotting applications at all levels of complexity, from simple scripts with hard-coded data to large plotting programs with complex data interrelationships and a multitude of interactive tools. Chaco is part of the Enthought Tool Suite and available under the BDS license

Implementation details

All what it required was :

  1. A call in an Excell sheet to a function made available by Pyxll : =regression_tool(B1:C23)”
  2. One Python function to start the regression tool decorated by pyxll. Because we want to interact with the Excel sheet during the usage of the regression tool, we need to start the GUI stuff within an independant thread.
class RegressionThread(threading.Thread):
    """ Simple thread just starting the GUI code
    def __init__(self, x, y):     
        self.interactive_tool = RegressionTool(x=x, y=y)
    def run(self):        

@xl_func("numpy_array<float> ary: str")
def regression_tool(ary):
    """ GUI regression tool allowing interactive evaluation of a selected 2D array of data
    if x.shape[1] != 2:
        # FIXME : should be more clever that that
        return "Input data must have two columns !"
        # Starting the thread needed because we want to have callbacks to Excel
        d = RegressionThread(ary[:,0], ary[:,1])
    return "Refresh to data to start the GUI"

The RegressionTool class is the very same as the regression demo of Chaco with a small addition. After the regression_tool is created, we had a trait listener on it :

regression.on_trait_change(_update_excel_on_selection_change, "selection_changed")

The function _update_excel_on_selection_change is defined here below. The callback to Excel has to be done using the pywin32 extension.

def _update_excel_on_selection_change(obj, name, oldvalue, newvalue):
    When regression selection is changed, update the array of selected values in Excel and the fitting parameters    
    indices = obj.selection_datasource.metadata["selection"]
    if any(indices):
        # retrieve the (x,y) values
        x = compress(indices, obj.component.index.get_data())
        y = compress(indices, obj.component.value.get_data())
        selection_len = len(x)
        xy = concatenate((x[:,newaxis],y[:,newaxis]), axis=1)        
        # get the Excel application object
        xl = Dispatch("Excel.Application")
        # FIXME : harcoded destination - should find a better way to do that
        base_column_index = 5
        # set (x,y) values 
        rng1 = xl.Range(xl.Cells(1, base_column_index),
                        xl.Cells(selection_len, base_column_index+1))
        rng1.Value = xy        
        # reset the other rows to Nothing
        empty_cells = xl.Range(xl.Cells(selection_len+1, base_column_index),
                        xl.Cells(obj.max_len, base_column_index+1))
        empty_cells.Value = ""

        # select the destination for the fit parameters
        fitp = xl.Range(xl.Cells(1, base_column_index+3),
                        xl.Cells(2, base_column_index+3))    
        if obj.fit_params is not None:        
            fitp.Value = [[obj.fit_params[0]],
        else :
            fitp.Value = [[None], [None]]
About these ads

One Response to Interactive Python graphics/visualisation with Excel

  1. Excel how to tables says:

    Valuable info. Fortunate me I discovered your website by accident, and I’m stunned why this accident didn’t
    came about earlier! I bookmarked it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: