If you wish to add a new language to SolverStudio, or write an IronPython model that does tricky stuff, then this page might be of interest.
SolverStudio supports multiple languages such as AMPL that are managed using Python files. The language “PuLP (IronPython)” is different in that SolverStudio directly runs the content of this Python file. The languages Gurobi (Python) and Python (External) are also different in that they are managed via IronPython files that pass on the user’s code to a standard CPython interpretor, and collect the results afterwards.
SolverStudio supports languages (eg AMPL, AMPLNEOS etc) via two IronPython files, an “init.py” file that is run when a language is first used in SolverStudio (eg as a result of a user opening a model in that language or manually selecting that language), and a run file such as “RunAMPL.py” that is executed whenever a user clicks Solve. Note that you can make changes to any “RunXXX.py” file (such as “RunAMPL.py”) while Excel is still open, and SolverStudio will automatically compile & run the new file when you click “Solve”. However, the “init.py” is only compiled and run once; you have to re-start Excel if you make changes to this file.
Note that we recommend you add support for any new language by modifying the support files for an existing language (such as AMPL). Then, when you have it working, please contact us to add this language into the next SolverStudio release, at which point we add your new language to the Language menu, and add your support files in a new language folder. Also note that you can test snippets of your code by running them in SolverStudio under the language “PuLP (IronPython)”, and then copying these snippets into the “init.py” or “RunXXXX.py” file.
When running a model in the “PuLP (IronPython)” language, or executing an “init.py” or “RunXXXX.pY file, the global namespace will contain a “SolverStudio” object that helps support interactions with the SolverStudio environment. You can use this object in any PuLP model running in IronPython (with care!), and (for language developers) in any language support file such as init.py and RunAMPL.py. (This object is not accessible from CPython).
The SolverStudio object has the following members that give access to the user’s DataItems:
SolverStudio.DataItems # A dictionary of all the data items (as objects keyed by their escaped name) SolverStudio.DictionaryDataItems # As for 'DataItems', but only those data items that are Dictionaries SolverStudio.ListDataItems # As for 'DataItems', but only those data items that are Lists SolverStudio.SingletonDataItems # As for 'DataItems', but only those data items that are singletons (i.e. a double or a string or none)
For example, the following code (which you can run in SolverStudio under the “PuLP (IronPython)” language) lists all the names of the data items:
for key in SolverStudio.DataItems: print key, SolverStudio.DataItems[key]
Note that SolverStudio traps writes to items, and updates the underlying data stored internally. Because each item has multiple “names” (eg DataItems[“Test”] and “Test” refer to the same internal storage), changes using one name are reflected in all other names. For example, assigning a new value to Test using, for example “Test=Test+1” will result in DataItems[“Test”] also having a new value. This is not standard Python behaviour.
We suggest you look at the “Python Examples” sample spreadsheet to see how data items are translated into Python objects.
The SolverStudio object also exposes the following calls. These methods can be called via, eg, SolverStudio.ChangeToLanguageDirectory(). Note that the following code is in C#, but works with IronPython thanks to the magic of .Net. Note that you can run a “Pulp (IronPython)” model containing “print dir(SolverStudio)” to list all these methods.
public string ChangeToLanguageDirectory() {
string oldCurrentDirectory = Directory.GetCurrentDirectory();
string path = _supportDirectory;
Directory.SetCurrentDirectory(path);
return oldCurrentDirectory;
}
public string ChangeToWorkingDirectory() {
string oldCurrentDirectory = Directory.GetCurrentDirectory();
string path = Globals.ThisAddIn.GetWorkingDirectory();
Directory.SetCurrentDirectory(path);
return oldCurrentDirectory;
}
public string LanguageDirectory() {
return _supportDirectory;
}
public string WorkingDirectory() {
return Globals.ThisAddIn.GetWorkingDirectory();
}
// Get the path to the SolverStudio.VSTO file. (This will not be the cached VSTo file, but always the one downloaded by the user). Includes
public string SolverStudioVSTOPath() {
return InstallTools.VSTORegistryFullPath;
}
// unused
public void SetCurrentDirectory(string s) {
if (!string.IsNullOrEmpty(s)) {
Directory.SetCurrentDirectory(s);
}
}
// Allow the Python file to run a Python method within a dialog
public void RunInDialog(string startPrompt = "", PythonRunnerDlg.WorkerMethod workerMethod = null, bool startAutomatically = false, bool pauseAtEnd = true, bool allowCancel = true) {
string oldDirectory = ChangeToLanguageDirectory();
NativeWindow parent = null;
try {
Globals.ThisAddIn._pythonRunnerDlg.SetStartup(startPrompt, workerMethod, startAutomatically, pauseAtEnd, allowCancel, this);
IntPtr hwndExcel = new IntPtr(Globals.ThisAddIn.Application.Hwnd);
parent = new NativeWindow();
parent.AssignHandle(hwndExcel);
Globals.ThisAddIn._pythonRunnerDlg.ShowDialog(parent);
} catch (Exception e) {
ReportException(e);
} finally {
if (parent != null) parent.ReleaseHandle(); // Must always undo the subclass or you can crash!
}
SetCurrentDirectory(oldDirectory);
}
public void ReportException(Exception ex) {
ExceptionOperations eo = _engine.GetService<ExceptionOperations>();
string error = eo.FormatException(ex);
MessageBox.Show("An error occurred while running a language support routine in the file " + _fileName + ": " + error, "SolverStudio Python Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
/// <summary>
/// Seatrch for a specified file (.exe or other) in one of the directories in the system %PATH%,
/// and return the full path for a specified file
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public string FindFileOnSystemPath(string fileName) {
return Globals.ThisAddIn.FindFileOnSystemPath(fileName);
}
/// Find file in the same directory as some other file; returns null if file not found
///
public string GetFileInSameDirectory(string baseFilePath, string fileName) {
return Globals.ThisAddIn.GetFileInSameDirectory(baseFilePath, fileName);
}
/// <summary>
/// This search all likely locations for a named .exe file, including the system (environment) PATH variable, and the 32 and 64
/// bit program files directories. It does not look in any of the SolverStudio folders.
/// </summary>
/// <param name="exeName">May include wild cards, such as 'AMPL*.exe'</param>
/// <param name="subDirectories">May incldue wild cards</param>
/// <param name="mustExist">If false, an exception is generated if the file is not found</param>
/// <returns></returns>
public string GetEXEPath(string exeName, string exeDirectory, bool mustExist = true) {
return Globals.ThisAddIn.FindFileInExeSubDirectory(exeName, exeDirectory, mustExist);
}
/// <summary>
/// Python support to run the specified executable, capturing the output within SolverStudio's window
/// </summary>
/// <param name="exeFilePath"></param>
/// <param name="exeArguments"></param>
/// <returns>true if the EXE returned a 0 exit code (meaning success)</returns>
public static bool RunExecutable(string exeFilePath, string exeArguments, bool addExeDirectoryToPath = false, bool addSolverPath = true,
bool addAtStartOfPath = false, bool killChildProcesses = false,
int timeOut = System.Threading.Timeout.Infinite, bool useLoopingWaitForExit = false) {
try {
return ExeRunner.RunEXE(exeFilePath, exeArguments, sendOutputToTaskPane: true, addExeDirectoryToPath: addExeDirectoryToPath, addSolverPath: addSolverPath,
addAtStartOfPath: addAtStartOfPath, killChildProcesses: killChildProcesses,
timeOut: timeOut, useLoopingWaitForExit: useLoopingWaitForExit);
} catch (System.Threading.ThreadAbortException ex) {
// User has cancelled
ex.Data.Add("ExeFilePath", exeFilePath); // Add more info, but as of 20140115 we don't show this.
ex.Data.Add("ExeArguments", exeArguments);
// We don't show any message; just pass on the abort exception which is specifically handled by the caller without any dialogs
throw ex;
} catch (Exception ex) {
ex.Data.Add("ExeFilePath", exeFilePath);
ex.Data.Add("ExeArguments", exeArguments);
throw new Exception("An error occurred when running the executable:\n" + exeFilePath + " " + exeArguments + "\n\n" + ex.Message, ex);
// We cannot show a massge as we may have a modal dialog showing; throw an exception. (Fixed AJM 20130121)
// MessageBox.Show("An error occurred when executing:\n" + exeFilePath + " " + exeArguments + "\n\n" + ex.Message);
//return false;
}
}
//public static int TestAMPL() {
// Process cmd = new Process();
// using (cmd) {
// cmd.StartInfo.FileName = "c:\\Ampl-Cplex-Gurobi-2013\\ampl.exe";
// cmd.StartInfo.Arguments = "c:\\temp\\blank.run";
// cmd.StartInfo.UseShellExecute = false;
// cmd.StartInfo.CreateNoWindow = true;
// cmd.StartInfo.RedirectStandardOutput = true;
// cmd.Start();
// string s = cmd.StandardOutput.ReadToEnd();
// cmd.WaitForExit(-1);
// return cmd.ExitCode;
// }
//}
/// <summary>
/// Run an exectuable, and returns its output as a string. Throws an exception if the exe does not complete within specified ms timeout
/// </summary>
/// <param name="exeFilePath"></param>
/// <param name="exeArguments"></param>
/// <param name="timeOut"></param>
/// <param name="useLoopingWaitForExit"> Set true when calling the AMPL license mgr </param>
/// <returns></returns>
public static string RunEXE_GetOutput(string exeFilePath, string exeArguments, int timeOut = System.Threading.Timeout.Infinite,
bool useLoopingWaitForExit = false) {
return ExeRunner.RunEXE_GetOutput(exeFilePath, exeArguments, timeOut: timeOut, useLoopingWaitForExit: useLoopingWaitForExit);
}
/// <summary>
/// Another simpler (more reilable? way to run an exectuable, and returns its output as a string
/// </summary>
/// <param name="exeFilePath"></param>
/// <param name="exeArguments"></param>
/// <returns></returns>
public static string RunEXE_GetOutput2(string exeFilePath, string exeArguments, int timeOut = System.Threading.Timeout.Infinite) {
return ExeRunner.RunEXE_GetOutput2(exeFilePath, exeArguments, timeOut);
}
//public static string RunEXE_GetOutput3(string exeFilePath, string exeArguments) {
// return ExeRunner.RunEXE_GetOutput3(exeFilePath, exeArguments);
//}
/// <summary>
/// Start a new executable; do not wait for it to finish
/// </summary>
/// <param name="exeFilePath"></param>
/// <param name="exeArguments"></param>
/// <param name="addAtStartOfPath"></param>
/// <returns></returns>
public static void StartEXE(string exeFilePath, string exeArguments, bool addExeDirectoryToPath = false,
bool addSolverPath = false, bool addAtStartOfPath = false, bool createWindow = true) {
var process = ExeRunner.StartEXE(exeFilePath, exeArguments, addExeDirectoryToPath, addSolverPath, addAtStartOfPath, createWindow);
process.Close();
}
/// <summary>
/// Look for a user specified exe file
/// </summary>
/// <param name="exeFilePath"></param>
/// <param name="extraFolderToCheck"></param>
/// <returns>Returns the full directoryPath to the named AMPL solver, or NULL</returns>
public static string FindPathForSolver(string exeFilePath, string extraFolderToCheck, bool mustExist = true) {
return Globals.ThisAddIn.FindPathForSolver(exeFilePath, extraFolderToCheck, mustExist);
}
/// <summary>
/// Allows a working file to be displayed with a dedicated ActiveTaskPane control; any changes to the file are also shown
/// Only 1 file can be displayed at a time
/// </summary>
/// <returns></returns>
public void ShowTextFile(string fileName) {
Globals.ThisAddIn.ShowTextFile(fileName);
}
/// <summary>
/// Get the dictionary of settings, if any, associated with the current stored model file. May return null.
/// </summary>
public Dictionary<string, object> ActiveModelSettings {
get {
WorkBookMgr activeMgr = WorkBookMgr.ActiveWorkBookMgr;
if (activeMgr == null) return null;
StoredFile activeStoredFile = activeMgr.ActiveStoredFile;
if (activeStoredFile == null) return null;
return activeStoredFile.ModelSettings;
}
}
/// <summary>
/// Get the full path to the IronPython directory (not the ipy.exe file)
/// Throws an exception if not found and mustExit == true.
/// </summary>
/// <returns>The full absolute CPython path ending with python.exe</returns>
public string GetIronPythonDirectoryPath(bool mustExist = true) {
return Globals.ThisAddIn.GetIronPythonDirectoryPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to our own ipy.exe directory (the ipy.exe file in our IronPython folder)
/// Throws an exception if not found and mustExit==true.
/// </summary>
/// <returns>The full absolute CPython path ending with python.exe</returns>
public string GetSolverStudioIPyPath(bool mustExist = true) {
return Globals.ThisAddIn.GetSolverStudioIPyPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to our own ipyw.exe directory (the ipyw.exe file in our IronPython folder)
/// Throws an exception if not found and mustExit==true.
/// </summary>
/// <returns>The full absolute CPython path ending with python.exe</returns>
public string GetSolverStudioIPyWPath(bool mustExist = true) {
return Globals.ThisAddIn.GetSolverStudioIPyWPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to the CPython executable, including any student version in the SolverStudio folder.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute CPython path ending with python.exe</returns>
public string GetCPythonPath(bool mustExist = true) {
return Globals.ThisAddIn.GetCPythonPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to the AMPL executable, including any student version in the SolverStudio folder.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute AMPL path ending with AMPL.exe</returns>
public string GetGLPSolPath() {
return Globals.ThisAddIn.GetGLPSolPath();// May throw an exception if path not found
}
/// <summary>
/// Get the full path to the CMPL executable, including any student version in the SolverStudio folder.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute CMPL path ending with CMPL.exe</returns>
public string GetCMPLPath() {
return Globals.ThisAddIn.GetCMPLPath();// May throw an exception if path not found
}
/// <summary>
/// Get the full path to the AMPL executable, including any student version in the SolverStudio folder.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute AMPL path ending with AMPL.exe</returns>
public string GetAMPLPath(bool preferSolverStudioAMPL, bool mustExist) {
return Globals.ThisAddIn.GetAMPLPath(preferSolverStudioAMPL, mustExist); // May throw an exception if path not found
}
public string GetExternalAMPLPath() {
// Look for a version of AMPL installed on the system outside SolverStudio (i.e. in the path etc)
// Returns null if none found
return Globals.ThisAddIn.GetExternalAMPLPath();
}
public string GetSolverStudioAMPLPath() {
// Look for a user-installed student version in the SolverStudio directory udner AMPL or AMPL/amplcl
// Returns null if none found
return Globals.ThisAddIn.GetSolverStudioAMPLPath();
}
/// <summary>
/// Get the directoryPath of any ampl_lic.exe file in the same directory as the specified ampl.exe
/// </summary>
/// <param name="AMPLpath"></param>
/// <returns></returns>
public string GetAssociatedAMPLLicenseMgr(string AMPLpath) {
return Globals.ThisAddIn.GetAssociatedAMPLLicenseMgr(AMPLpath);
}
public string GetRunningAMPLLicenseMgrPath() {
// Get the directoryPath of any running ampl_lic.exe file. None if no license mgr running
return Globals.ThisAddIn.GetRunningAMPLLicenseMgrPath();
}
// Start up the AMPL license mgr, which will either create a new process, or eventually quit.
// Returns true if succeeded.
// Try to get an error message if it fails.
public bool StartAMPLLicenseMgr(string AMPLLicenseMgrPath, string arguments, out string errorMessage) {
return Globals.ThisAddIn.StartAMPLLicenseMgr(AMPLLicenseMgrPath, arguments, out errorMessage);
}
/// <summary>
/// Get the full path to the GAMS executable.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute GAMS path ending with AMPL.exe</returns>
public string GetGAMSPath(bool mustExist = true) {
return Globals.ThisAddIn.GetGAMSPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to the Gurobi.bat file.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute Gurobi path ending with gurobi.bat</returns>
public string GetGurobiPath(bool mustExist = true) {
return Globals.ThisAddIn.GetGurobiPath(mustExist); // May throw an exception if path not found
}
public bool Is64BitProcess() {
try {
return Environment.Is64BitProcess;
} catch {
return false;
}
}
/// <summary>
/// Get the full path to the pyomo.exe file.
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute Pyomo path ending with pyomo.exe</returns>
public string GetPYOMOPath(bool mustExist = true) {
return Globals.ThisAddIn.GetPYOMOPath(mustExist); // May throw an exception if path not found
}
/// <summary>
/// Get the full path to the PuLP file to include for Python
/// Throws an exception if not found.
/// </summary>
/// <returns>The full absolute path to the SolverStudio PuLP directory</returns>
public string GetPuLPPath() {
string path = string.Empty;
path = Environment.GetEnvironmentVariable("SolverStudio_PULP_PATH");
if (path != null) {
return path;
} else {
return Globals.ThisAddIn.GetPuLPPath(); // May throw an exception if path not found
}
}
/// <summary>
/// Get the full path to the PuLP directory within SolverStudio's PuLP directory to include for Python
/// </summary>
/// <returns>The full absolute path to the PuLP directory</returns>
public string GetPuLPSourcePath(bool mustExist = true) {
string path = string.Empty;
path = Environment.GetEnvironmentVariable("SolverStudio_PULP_PATH");
if (path != null) {
return path + "\\src";
} else {
return Globals.ThisAddIn.GetPuLPSourcePath(mustExist);
}
}
/// <summary>
/// Provides access to the Languages menu to allow add-ins to add their own menu items
/// </summary>
/// <returns></returns>
public System.Windows.Forms.ToolStripMenuItem LanguageToolStripMenuItem() {
return Globals.ThisAddIn.ActiveTaskPaneControl.LanguageToolStripMenuItem;
}
/// <summary>
/// Get the full directoryPath to the GAMS DLL directory within SolverStudio
/// </summary>
/// <returns>The full absolute directoryPath to the GAMS DLL directory</returns>
/// UNUSED now (as GAMS DLL's handled by adding directories to the SolverStudio's process directoryPath)
//public string GetGAMSDLLPath(bool mustExist = true) {
// return Globals.ThisAddIn.LocateSupportDirectory("GAMS");
//}
/// <summary>
/// Get the full paths to the Solvers directories (common, 32 bit and 64 bit) within SolverStudio
/// </summary>
/// <returns>The full absolute paths to the Solvers directories</returns>
public string[] GetSolversPaths(bool mustExist = true) {
return Globals.ThisAddIn.GetSolversPaths(mustExist);
}
// Get a setting from the Registry for SolverStudio
public object GetRegistrySetting(string keyName, string settingName, object defaultValue = null) {
return Globals.ThisAddIn.GetRegistrySetting(keyName, settingName, defaultValue);
}
// Write a setting in the Registry for SolverStudio
public void SetRegistrySetting(string keyName, string settingName, object value) {
Globals.ThisAddIn.SetRegistrySetting(keyName, settingName, value);
}
/// <summary>
/// Appends string to the Taskpane output
/// </summary>
/// <returns></returns>
public void AppendToTaskPane(string msg) {
Globals.ThisAddIn.ActiveTaskPaneControl.AppendToOutput(msg);
}
/// <summary>
/// Gets the model stored with the active sheet
/// </summary>
/// <returns>The active model as a single string</returns>
public string GetActiveModelText() {
WorkBookMgr activeMgr = WorkBookMgr.ActiveWorkBookMgr;
if (activeMgr != null) {
return activeMgr.ActiveStoredFile.FileText;
} else {
return "No ActiveWorkBookMgr";
}
}
/// <summary>
/// Sets the model stored with the active sheet
/// </summary>
/// <returns></returns>
public int SetActiveModelText(string modelText) {
WorkBookMgr activeMgr = WorkBookMgr.ActiveWorkBookMgr;
if (activeMgr != null) {
activeMgr.ActiveStoredFile.SetFileText(modelText);
activeMgr.UpdateModelFromFile();
return 0;
} else {
return 1;
}
}
/// <summary>
/// Opens a GDX for writing
/// </summary>
/// <returns></returns>
public int OpenGDX(string path, bool read = true, bool append = false) {
GamsSupport gs = new GamsSupport();
return gs.OpenGDX(path, read, append);
}
/// <summary>
/// Closes a GDX file
/// </summary>
/// <returns></returns>
public void CloseGDX(int PGX) {
GamsSupport gs = new GamsSupport();
gs.CloseGDX(PGX);
}
/// <summary>
/// Writes a set to a GDX file
/// </summary>
/// <returns></returns>
public void WriteSetToGDX(int PGX, string name, ManagedDataItems DataItems, string description = "") {
GamsSupport gs = new GamsSupport();
gs.WriteSet(PGX, name, DataItems, description);
}
/// <summary>
/// Writes an indexed parameter to a GDX file
/// </summary>
/// <returns></returns>
public void WriteParamToGDX(int PGX, string name, ManagedDataItems DataItems, string description = "") {
GamsSupport gs = new GamsSupport();
gs.WriteParam(PGX, name, DataItems, description);
}
/// <summary>
/// Reads a parameter from a GDX file
/// </summary>
/// <returns></returns>
public void ReadSymbolFromGDX(int PGX, string name, int level, ref ManagedDataItems DataItems) {
GamsSupport gs = new GamsSupport();
gs.ReadGDXSymbol(PGX, name, level, ref DataItems);
}
/// <summary>
/// Imports a GDX file
/// </summary>
/// <returns></returns>
public void ImportGDX(int PGX, string filepath, bool VarOnly, bool Group) {
GamsSupport gs = new GamsSupport();
gs.ImportGDX(PGX, filepath, VarOnly, Group);
}
public static string EscapedDataItemName(string s) {
// Convert a variable name in a model, such as RHS1.up, into a valid Python name, eg RHS1_up
// We encode a "." as a "_", and a "_" as "_0"
return Globals.ThisAddIn.EscapedDataItemName(s);
}
public static string UnEscapedDataItemName(string s) {
// Convert an escaped (ie encoded) Python variable name in a model back into the original unescaped name
// We encode a "." as a "_", and a "_" as "__"
return Globals.ThisAddIn.UnEscapedDataItemName(s);
}