Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Solver Platform SDK - Loading and Solving Excel Workbooks

Loading and Solving Excel Workbooks

Solver Platform SDK has the ability to load Excel workbooks with optimization and simulation models (created using one of our Excel products) and solve them anywhere, from desktop to server to cloud.  Excel is required to create your model, but it's not required on the target system; everything you need is contained with SolverSDK.dll.  Examples range from a .NET application running under Windows Server 2008 or SQL Server to a CGI for an Apache Web server under Linux.

In workbooks that will be solved via the SDK, you can use almost any Excel formula or function (even new Excel 2010 functions) -- but you can't use functions written in VBA (check with us for other ways to accomplish this).  Generally speaking, if your model runs with the PSI Interpreter in Risk Solver Platform or Premium Solver Platform, it will run in Solver Platform SDK.  When run in the SDK, problem setup is usually faster, and solution runs at the same speed or faster than in Excel.

Your Excel workbook must be in the new XML-based (.xlsx) file format , used in Excel 2010 and Excel 2007.  If you're using Excel 2003, you'll need to install the Microsoft Office Compatibility Pack (a free download from Microsoft's Website) so you can save files in this format.

Changing Your Input Data

When you load your workbook, you can update the input data of the model (replacing numbers that originally appeared in worksheet cells), and hence solve a new problem instance.  To do this, in Risk Solver Platform or Premium Solver Platform you simply "mark" a cell range for updating in the SDK with a call to the new PsiInput() function -- for example in the Product Mix model included with the SDK, we use =PsiInput(D17:F17) to mark the Profit per Product values for updating.

When you load the Excel workbook in your SDK application program -- with a single statement such as problem.Load(filename, File_Format.XLStd) -- the SDK will automatically create InputData objects corresponding to the cell ranges marked with PsiInput() calls.  You can reference these cell ranges like arrays, with syntax such as problem.InputData["D17:F17"].Value[i,j], or use a defined name instead of D17:F17.

Getting Started with Examples

In the SDK Examples folder installed on your PC, you'll find dozens of examples written in all of the supported languages.  To see a complete example of loading an Excel workbook, updating its data, and solving an optimization model, open the Product Mix example in Visual Studio, and build and run the application  To see an example of loading an Excel workbook and running a Monte Carlo simulation model, open the College Fund example.  We've included versions of these models in C#, VB.NET, C++, VB6, Java, and even procedural C, with project files for old and new versions of Visual Studio.

Here is sample code in C#, which writes to the console for simplicity.  It creates a Problem object, and calls its Load method, passing in a workbook filename.  That's all it takes to define the model -- the code immediately calls Problem.Solver.Optimize to solve the problem.  Then, in a few simple lines of C#, it accesses and displays the optimal values of the decision variables and the objective.

try
{
    using (Problem problem = new Problem())
    {
    problem.Load("MyWorkbook.xlsx",
     File_Format.XLStd);
    problem.Solver.Optimize();

    _textConsole.Write("Optimize Status = " +
     problem.Solver.OptimizeStatus + "\r\n");

    for (i = 0; i < problem.VarDecision.Size; i++)
    _textConsole.Write("x" + (i + 1) + " = " +
     problem.VarDecision.FinalValue[i] + "\r\n");

    _textConsole.Write("Objective = " +
     problem.FcnObjective.FinalValue[0] + "\r\n");
    }
}
catch (SolverException ex)
{
    MessageBox.Show(
    "Exception " + ex.ExceptionType + "\r\n" +
    "Exception " + ex.Message, "SDK Exception");
}


The Problem.Load method can be used to load an Excel workbook, or a LP format, MPS format, or OSiL format file; the second argument indicates the file type.  If you have a workbook with multiple worksheets, and you want to solve a model on a worksheet other than the active sheet, just append an exclamation point ! and the sheetname to the workbook filename.  Each Variable object and each Function object represents one contiguous cell range in the Excel workbook.  For example, you could write the expression problem.Variables["D9:F9"].FinalValue[i] to access the final values of a specific block of decision variables.

< Back to Solver Platform SDK Product Overview