Delen via

Deploy OML model from Excel to Visual Studio

  • Artikel

Deploy-to-C# is a new feature we shipped in Solver Foundation v2.0 Excel Add-in. The idea is to simplify the process of deploying an OML model written in the Add-in into a Visual Studio project that builds the same model using Solver Foundation Services APIs. With this new feature, users who are comfortable in writing and debugging their models in Excel/OML environment now can produce an equivalent C# model by a click of a button. Once the C# model is generated, users can further embed the model code into their applications. It is also possible now to solve the model using multiple directives or use plug-in solvers.

In this post, we will show how to use the Deploy functionality to convert an OML model into a Visual Studio project. To repeat the following steps yourself, please make sure you have Microsoft Office 2007, Visual Studio 2008, and Solver Foundation v2.0 installed. Furthermore, to try out the plug-in solver you have, please update the solver registration section in the config file accordingly.

In Solver Foundation v2.0 release, we include a sample on the supply chain planning. It can be found under <your documents folder>\Microsoft Solver Foundation\Samples\Excel\LP. Let us start with this sample.

Once we load this Excel file, we can see the data are stored in the spreadsheet and the OML model is presented in our new modeling pane.

We can solve the model in Excel by clicking the Solve button. Here is the result.

As we can see from the log, Solver Foundation simplex solver is used with the default setting to solve this model.

Now let us start deploying the model into C#. We notice the new Deploy button in the Ribbon.

Let us click on it. The following window pops up, where we can specify the C# file name for the model to be deployed. Let's call it SupplyChainPlanning.cs.

Now we have two files created under the target folder: SupplyChainPlanning.cs and SupplyChainPlanning.xml. The first file contains the equivalent model written in C# and the second xml file contains the data from the spreadsheet. The model now can be embedded into any Visual Studio C# project.

Let us now open Visual Studio 2008 and create a new C# console project. Notice that Solver Foundation v2.0 installation creates two project templates in Visual Studio 2008. Let us pick the Solver Foundation Console Application template.

After the project is created, let us add the SupplyChainPlanning.cs and SupplyChainPlanning.xml into the project. We can delete the sample model file created automatically by the template because we are not going to use it. We make sure that the xml file will be always copied to the output directory during compilation since it contains the data we need.

Next, we need to write some glue code to bind the data from XML file to the model. This part is not automatically done at this moment. However, the data binding code is pretty straightforward. We use LINQ to XML in this case.

  class Program {

    private ExportedModel _model;

    public Program() {

      _model = new ExportedModel();

    }

    public void Run() {

      BindData();

      var solution = _model.Context.Solve();

      Console.WriteLine(solution.GetReport().ToString());

    }

    private void BindData() {

      var data = XElement.Load("SupplyChainPlanning.xml");

      var manufactureLoads =

          from load

          in data.Descendants("manufactureLoads")

          select new

          {

            Product = load.Element("Product").Value,

            Load = Convert.ToDouble(load.Element("ManufactureLoads").Value)

          };

      var factoryCapacities =

          from cap

          in data.Descendants("factoryCapacity")

          select new

          {

            Factory = cap.Element("Factory").Value,

            Capacity = Convert.ToDouble(cap.Element("FactoryCapacity").Value)

          };

      var unitManufactureCosts =

          from cost

          in data.Descendants("unitManufactureCost")

          select new

          {

            Product = cost.Element("Product").Value,

            Cost = Convert.ToDouble(cost.Element("UnitManufactureCost").Value)

          };

      var transports =

          from tran

          in data.Descendants("transport")

          select new

          {

            Product = tran.Element("Product").Value,

            Area = tran.Element("Geography").Value,

            Factory = tran.Element("Factory").Value,

            Transport = Convert.ToDouble(tran.Element("Transport").Value)

          };

      var demandForecastPrices =

          from demand

          in data.Descendants("demandForecastPrice")

          where demand.Elements().Count() == 4

          select new

          {

            Product = demand.Element("Product").Value,

            Area = demand.Element("Geography").Value,

  Promotion = demand.Element("Promotion").Value,

            Price = Convert.ToDouble(demand.Element("demandForecastPrice").Value)

          };

      var demandForecastUnits =

          from demand

          in data.Descendants("demandForecastUnits")

          where demand.Elements().Count() == 4

          select new

          {

            Product = demand.Element("Product").Value,

            Area = demand.Element("Geography").Value,

            Promotion = demand.Element("Promotion").Value,

            Units = Convert.ToDouble(demand.Element("demandForecastUnits").Value)

          };

      _model.manufactureLoads.SetBinding(manufactureLoads, "Load", "Product");

      _model.factoryCapacity.SetBinding(factoryCapacities, "Capacity", "Factory");

      _model.unitManufactureCost.SetBinding(unitManufactureCosts, "Cost", "Product");

      _model.transport.SetBinding(transports,

                                  "Transport", "Product", "Area", "Factory");

      _model.demandForecastPrice.SetBinding(demandForecastPrices,

                                            "Price", "Product", "Area", "Promotion");

      _model.demandForecastUnits.SetBinding(demandForecastUnits,

                                            "Units", "Product", "Area", "Promotion");

    }

    static void Main(string[] args) {

      var program = new Program();

      program.Run();

    }

  }

Note that we have a where clause in building demandForecastPrices and demandForecastUnits. We leave to the readers to experiment why the where clause is needed.

Now let's run the console app. Here is the output. For simplicity, we skip the decision values. We can verify that the objective function value is the same as we have seen in Excel.

===Solver Foundation Service Report===

Datetime: 10/30/2009 12:03:57

Model Name: Default

Capabilities Requested: LP

Solve Time (ms): 515

Total Time (ms): 911

Solve Completion Status: Optimal

Solver Selected: Microsoft.SolverFoundation.Solvers.SimplexSolver

Directives:

Microsoft.SolverFoundation.Services.Directive

Algorithm: Primal

Arithmetic: Hybrid

Variables: 154 -> 154 + 77

Rows: 104 -> 77

Nonzeros: 416

Eliminated Slack Variables: 0

Pricing (exact): SteepestEdge

Pricing (double): SteepestEdge

Basis: Slack

Pivot Count: 114

Phase 1 Pivots: 0 + 0

Phase 2 Pivots: 114 + 0

Factorings: 6 + 1

Degenerate Pivots: 82 (71.93 %)

Branches: 0

===Solution Details===

Goals:

Profit: 9158191.66666667

 

Now to illustrate a more advance usage of Solver Foundation in this C# project, let's include a solver registration and use a plug-in solver to solve it.

To use a plug-in solver with Solver Foundation, please make sure you have all the needed DLLs from the plug-in solver vendor. In this example, we will use Gurobi solver to solve this LP model.

First we need to add a config file to the project so that we can register the plug-in solvers. Here is what the app.config file looks like

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <configSections>

    <section name="MsfConfig"

             type="Microsoft.SolverFoundation.Services.MsfConfigSection, Microsoft.Solver.Foundation, Version=2.0.2.8632, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

        allowLocation="true"

             allowDefinition="Everywhere"

             allowExeDefinition="MachineToApplication"

             restartOnExternalChanges="true"

             requirePermission="true" />

  </configSections>

  <MsfConfig>

    <MsfPluginSolvers>

      <MsfPluginSolver

              capability="LP"

              assembly="GurobiPlugin.dll"

              solverclass="SolverFoundation.Plugin.Gurobi.GurobiSolver"

              directiveclass="SolverFoundation.Plugin.Gurobi.GurobiDirective"

              parameterclass="SolverFoundation.Plugin.Gurobi.GurobiParams"/>

    </MsfPluginSolvers>

  </MsfConfig>

</configuration>

Notice that we register Gurobi solver as an LP solver here, with Gurobi solver's class, directive class, and parameter class names.

Next, we need to include necessary DLLs into the project so that Solver Foundation can find them. Here is what the project looks like now.

Here we reference GurobiPlugin.dll (plug-in solver wrapper) and add the gurobi20.dll (unmanaged code DLL for the actual solver) to the project. Notice that we set the "Copy to Output Directory" property of gurobi20.dll to "Copy Always" too. These two DLLs are shipped with Solver Foundation v2.0 and can be found under <your program files folder>\ Microsoft Solver Foundation\2.0.2.8632\Plugins.

Now let's run the application again. This time, we will see from the report that Gurobi solver is used and the same objective function value is returned.

===Solver Foundation Service Report===

Datetime: 10/30/2009 13:48:28

Model Name: Default

Capabilities Requested: LP

Solve Time (ms): 110

Total Time (ms): 522

Solve Completion Status: Optimal

Solver Selected: SolverFoundation.Plugin.Gurobi.GurobiSolver

Directives:

Microsoft.SolverFoundation.Services.Directive

Algorithm: Dual

Arithmetic: Double

Variables: 154 -> 258 + 104

Rows: 104 -> 104

Nonzeros: 416

Eliminated Slack Variables: 0

Pricing (double): Automatic

Pivot Count: 36

Phase 1 Pivots: -1 + 0

Phase 2 Pivots: -1 + 0

Factorings: -1 + 0

Degenerate Pivots: -1 (-2.78 %)

Branches: 0

===Solution Details===

Goals:

Profit: 9158191.66666666

To tweak the settings of Gurobi solver, we can further create a Gurobi solver directive and change the settings there. Then we pass the directive instance into Solve call. I will leave this to the readers to experiment.

This sample goes through all steps in deploying an OML model from Excel to Visual Studio. I hope it helps in your application development. Thank you for your time.

Lengning

Comments

  • Anonymous
    April 14, 2010
    Lenging, I have an OML model in Excel and want to export into C# but i still want to have an Excel GUI when i run it. I want to have my constraints in as radio buttons for tick on and off.How can i go about it.