SolverStudio & IronPython

SolverStudio contains a full IronPython interpreter; this is selected under the language “PuLP (IronPython)”.

SolverStudio creates Python floats, strings, dictionaries, tuples and lists corresponding to the user defined DataItems, and injects these into the user’s scope so they can be accessed directly using the DataItem’s name. We recommend you look at the “Python Examples.xlsx” spreadsheet to see how items on a sheet are mapped to Python objects. Note that SolverStudio traps all writes to these variables, and updates its internal store for that DataItem. This can sometimes result in non-Pythonic behaviour in thatif two variables refer to the same Data Item, they can both have their values changed by a single assignment statement.

SolverStudio allows direct access to the Excel API functions and methods using the three pre-defined objects

Application: The standard Excel application object,
ActiveSheet: The currently active sheet,
ActiveWorkbook: The currently active workbook.

An an example, you can use the following code to access a named range on the active sheet:

print ActiveSheet.Range("Test").Value2

Please also see the VBA documentation for these (using the links above). More IronPython-specific details are available on using Using IronPython with Excel, but note that we have already set up the application, active sheet and activer workbook objects ready to use (and some of this seems out of date now).

Advanced Note: Code written for IronPython using “Application“, “ActiveSheet” and “ActiveWorkbook” will often work under both IronPython and CPython. However, in some cases, IronPython requires a trailing (). We have used .Value2 above because this works identically under both IronPython and CPython. However, if we use .Value, then it must be ActiveSheet.Range(“Test”).Value in CPython and ActiveSheet.Range(“Test”).Value() (with brackets) in IronPython. This is because .Value can take an argument. Thus .Value(10) works, and .Value[10] also works, where 10 is “xlRangeValueDefault” and the [] notation is IronPython’s way of distinguishing between multiple versions of a call.

As an example, the following code example shows how to directly access a range on a sheet as a .Net array of Variants. (This can be faster than using the more typical SolverStudio approach of a double-indexed dictionary of values. Note that the blank cells come through as “None” in the direct access. As the code shows, SolverStudio creates a list of tuples for this Data Item, but this excludes any blank cells.

DirectSheetAccessExample

The code is as follows, and can be copied into a “PuLP (IronPython)” model in SolverStudio.

print "Named Range Test=",ActiveSheet.Range("Test").Address()
print "Test=",Test
FasterTest = ActiveSheet.Range("Test").Value2
for i in (0,1,2):
    for j in (0,1): 
       print "FasterTest[",i,j,"]=",FasterTest[i,j]

SolverStudio also supports using an external Python installed by the user; learn more here.

Leave a Reply

Your email address will not be published. Required fields are marked *