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 :
- Python code is easily maintainable, tested and integrated compared to what can be inside of an Excel sheet
- 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 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
All what it required was :
- A call in an Excell sheet to a function made available by Pyxll : =regression_tool(B1:C23)”
- 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): threading.Thread.__init__(self) self.interactive_tool = RegressionTool(x=x, y=y) def run(self): self.interactive_tool.configure_traits() @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 != 2: # FIXME : should be more clever that that return "Input data must have two columns !" else: # Starting the thread needed because we want to have callbacks to Excel d = RegressionThread(ary[:,0], ary[:,1]) d.start() 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 :
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") gencache.EnsureDispatch(xl) # 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], [obj.fit_params]] else : fitp.Value = [[None], [None]]