Originally posted by: pentunia
Has anybody tried the 'Warehouse example' and managed to get it to work:
'Integrating OPL with Excel using Visual Studio Tools for Office'
http://publib.boulder.ibm.com/infocenter/odmeinfo/v3r3/index.jsp?topic=/ilog.odms.ide.odme.help/Content/Optimization/Documentation/ODME/_pubskel/ODME_pubskels/startALL_ODME_Eclipse_and_Xplatform_doc1279.html If you have, would you please paste your entire code, pleeeease :)
I'm suppose to learn these tutorials, but I'm having the hardest time :/
1. At this point I have managed to whittle my errors down to only 6- Visual Studio Output window reply after running Debug/Run Debugging:
code ThisAddIn.cs(56,17): error CS0103: The name 'MessageBox' does not exist in the current context
ThisAddIn.cs(56,56): error CS0103: The name 'MessageBoxButtons' does not exist in the current context
ThisAddIn.cs(56,78): error CS0103: The name 'MessageBoxIcon' does not exist in the current context
ThisAddIn.cs(173,21): error CS0103: The name 'MessageBox' does not exist in the current context
ThisAddIn.cs(173,119): error CS0103: The name 'MessageBoxButtons' does not exist in the current context
ThisAddIn.cs(177,21): error CS0103: The name 'MessageBox' does not exist in the current context
[/code]
2. Now if I add (near the top) 'using System.Windows.Forms;' I no longer receive the above errors. Instead:
A blank Excel Book1 opens... I wait and wait, nothing- I close Excel. VS Output window states:
code<snip>
A first chance exception of type 'System.Deployment.Application.DeploymentException' occurred in System.Deployment.dll
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
The thread '<No Name>' (0x1e84) has exited with code 0 (0x0).
<snip>
The thread 0x1e78 has exited with code 0 (0x0).
The program '
7804 excel.exe: Managed' has exited with code 0 (0x0).
[/code]
(If it's important I have pasted the entire VS Output text to here
http://pastebin.com/n8eMF1HN )
3. This is my complete code (I have already added the oplall.dll to the References):
code using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Windows.Forms;
using ILOG.Concert;
using ILOG.CPLEX;
using ILOG.OPL;
using Exception = System.Exception;
namespace ExcelWarehouse
{
public partial class ThisAddIn
{
private Office.CommandBar AddInMenuBar;
private Office.CommandBarButton SetupButton;
private Office.CommandBarButton SolveButton;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
CreateOplCommands();
}
private void CreateOplCommands()
{
try
{
// new command bar for OPL:
AddInMenuBar = Application.CommandBars.Add(
"OPL Commands", Office.MsoBarPosition.msoBarTop, missing, true);
// setup button:
SetupButton = (Office.CommandBarButton)AddInMenuBar.Controls.Add(
Office.MsoControlType.msoControlButton,
missing, missing, missing, true);
SetupButton.Caption = "OPL Warehouse Setup";
SetupButton.Style = Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption;
SetupButton.Click += new
Office._CommandBarButtonEvents_ClickEventHandler(
SetupButton_Click);
// solve button:
SolveButton = (Office.CommandBarButton)AddInMenuBar.Controls.Add(
Office.MsoControlType.msoControlButton,
missing, missing, missing, true);
SolveButton.Caption = "OPL Warehouse Solve";
SolveButton.Style = Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption;
SolveButton.Click += new
Office._CommandBarButtonEvents_ClickEventHandler(
SolveButton_Click);
AddInMenuBar.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
RemoveOPLCommands();
}
private void RemoveOPLCommands()
{
SolveButton.Delete(false);
SolveButton = null;
SetupButton.Delete(false);
SetupButton = null;
AddInMenuBar = null;
}
private void SolveButton_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
// respond to solve button click
Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveWorkbook.ActiveSheet;
System.IO.StringWriter errHandlerErrors = new System.IO.StringWriter();
try
{
setValue(sheet, "B9", "Computing...");
SolveProblem(sheet, errHandlerErrors);
setValue(sheet, "B9", "Success");
}
catch (Exception ex)
{
setValue(sheet, "B9", "Error:" + ex.ToString() + "(" + errHandlerErrors.ToString() + ")");
}
}
private void SetupButton_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
// respond to setup button click
Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveWorkbook.ActiveSheet;
setValue(sheet, "A1", "Data used by the OPL model:");
setValue(sheet, "A2", "nbWarehouses:");
setValue(sheet, "B2", "5");
setValue(sheet, "A3", "nbStores:");
setValue(sheet, "B3", "10");
setValue(sheet, "A4", "fixed:");
setValue(sheet, "B4", "30");
setValue(sheet, "A5", "disaggregate:");
setValue(sheet, "B5", "1");
setValue(sheet, "A8", "Result computed by the OPL model:");
setValue(sheet, "A9", "State:");
setValue(sheet, "A10", "Objective:");
}
private void SolveProblem(Excel.Worksheet sheet, System.IO.StringWriter errHandlerErrors)
{
// actually solve the problem with OPL
OplFactory.DebugMode = true;
OplFactory oplF = new OplFactory();
OplErrorHandler errHandler = oplF.CreateOplErrorHandler(errHandlerErrors);
OplModelSource modelSource = oplF.CreateOplModelSourceFromString(GetModelText(), "warehouse");
OplSettings settings = oplF.CreateOplSettings(errHandler);
OplModelDefinition def = oplF.CreateOplModelDefinition(modelSource, settings);
Cplex cplex = oplF.CreateCplex();
OplModel opl = oplF.CreateOplModel(def, cplex);
OplDataSource dataSource = new MyParams(oplF, sheet);
opl.AddDataSource(dataSource);
opl.Generate();
if (cplex.Solve())
{
setValue(sheet, "B10", "" + opl.Cplex.ObjValue);
}
else
{
setValue(sheet, "B10", "No solution");
}
oplF.End();
}
/**
* This class feeds data to the OPL model from the appropriate cells of the
* input Excel worksheet.
*/
class MyParams : CustomOplDataSource
{
private Excel.Worksheet _xlDataSheet;
public MyParams(OplFactory oplF, Excel.Worksheet xlDataSheet)
: base(oplF)
{
_xlDataSheet = xlDataSheet;
}
public override void CustomRead()
{
OplDataHandler handler = this.DataHandler;
try
{
handler.StartElement("nbWarehouses");
handler.AddIntItem(Int16.Parse(getValue(_xlDataSheet, "B2")));
handler.EndElement();
handler.StartElement("nbStores");
handler.AddIntItem(Int16.Parse(getValue(_xlDataSheet, "B3")));
handler.EndElement();
handler.StartElement("fixed");
handler.AddIntItem(Int16.Parse(getValue(_xlDataSheet, "B4")));
handler.EndElement();
handler.StartElement("disaggregate");
handler.AddIntItem(Int16.Parse(getValue(_xlDataSheet, "B5")));
handler.EndElement();
}
catch (System.FormatException ex)
{
MessageBox.Show("Error in the data, please check that you're using integers only.", "Data Error", MessageBoxButtons.OK);
}
catch (Exception ex)
{
MessageBox.Show("Exception in custom data source:" + ex.ToString());
}
}
}
/**
* This is the warehouse OPL model:
*/
static String GetModelText()
{
String model = "";
model += "int fixed = ...;";
model += "int nbWarehouses = ...;";
model += "int nbStores = ...;";
model += "int disaggregate = ...;";
model += "assert nbStores > nbWarehouses;";
model += "range Warehouses = 1..nbWarehouses;";
model += "range Stores = 1..nbStores;";
model += "int capacity
w in Warehouses = nbStores div nbWarehouses + w mod (nbStores div nbWarehouses);";
model += "int supplyCost
s in Storesw in Warehouses = 1+((s+10*w) mod 100);";
model += "dvar float open
Warehouses in 0..1;";
model += "dvar float supply
StoresWarehouses in 0..1;";
model += "minimize ";
model += "sum(w in Warehouses) fixed * open[w] +";
model += "sum(w in Warehouses, s in Stores) supplyCost[s][w] * supply[s][w];";
model += "constraints {";
model += " forall(s in Stores)";
model += " sum(w in Warehouses) supply[s][w] == 1;";
model += " forall(w in Warehouses)";
model += " sum(s in Stores) supply[s][w] <= open[w]*capacity[w];";
model += " if (disaggregate == 1) {";
model += " forall(w in Warehouses, s in Stores)";
model += " supply[s][w] <= open[w];";
model += " }";
model += "}";
return model;
}
static private void setValue(Excel.Worksheet sheet, String cell, String value)
{
sheet.get_Range(cell, cell).set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, value);
}
static private String getValue(Excel.Worksheet sheet, String cell)
{
return (String)sheet.get_Range(cell, cell).Text;
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
[/code]
(I'm using Windows XP SP3, MS Office 2007 SP2 (running all from computer; not CD including Primary Interop Assemblies), Visual Studios 2008 SP1, VSTO 3.0, OPL version 6.3, .NET.
I do have: C:\WINDOWS\system32\Com\comadmin.dll & C:\WINDOWS\system32\dllcache\comadmin.dll
I do have 'mscorlib.dll' (9 locations) and
'System.Deployment' is registered in my: C:\WINDOWS\assembly\
and is located in 2 places:
C:\WINDOWS\assembly\GAC_MSIL\System.Deployment\2.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Deployment.dll
)
I have rebooted and retried- same issue.
I have in VS menu- Debug/Exceptions/ searched for 'InvalidDeploymentException' / Then selected it/OK - same issue.
Thank you :)
#CPLEXOptimizers#DecisionOptimization