public SwitchModel New()
{
SwitchModel sModel = new SwitchModel();
int i = List.Add(sModel);
sModel.ModelNumber = i + 1;
return(sModel);
}
private string GetRangeForSelectedModel(SwitchModel sm, Excel.ListRow LR)
{
string address = LR.Range.Address;
int noOfVariables = 0;
for (int count = 0; count < sm.VariableNames.Length; count++)
{
if (!string.IsNullOrEmpty(sm.VariableNames[count]))
{
noOfVariables += 1;
}
}
string[] addr = address.Split(new char[] { '$', ':' });
int destinationAddress = Convert.ToInt32(addr[2]) + noOfVariables;
string targetAddress = "$" + addr[1] + addr[2] + ":" + "$" + addr[4] + destinationAddress.ToString();
//Excel.Range target = energySheet.get_Range(targetAddress);
return(targetAddress);
}
public void Add(SwitchModel sModel)
{
List.Add(sModel);
}
private void PopulateModels(Excel.ListObject DataLO)
{
Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
//
if (DataLO != null)
{
int bestModelNumber;
//check to see if model can be switched from current sheet.
try
{
//TFS Ticket 71242
string strBestModel = ((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString();
string[] strArry = strBestModel.Split(new string[] { EnPIResources.bestModel }, StringSplitOptions.RemoveEmptyEntries);
bestModelNumber = Convert.ToInt32(strArry[0]);
//bestModelNumber = Convert.ToInt32(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Substring(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Length - 1));
}
catch
{
this.label1.Text = "In order to switch a model, you must first be on the sheet that the model is listed on.";
this.comboBox1.Enabled = false;
return;
}
int currentModelNumber = 0;
int varCount = 1;
modelCount = 0;
bool bestModel = false;
foreach (Excel.ListRow row in DataLO.ListRows)
{
string[] variables = new string[DataLO.ListRows.Count];
double[] VarPvalue = new double[DataLO.ListRows.Count];
if (((Excel.Range)row.Range.Cells[1]).Value2 != null)
{
varCount = 1;
modelCount++;
//modelCount = row.Range.Count;
currentModelNumber = Convert.ToInt32(((Excel.Range)row.Range.Cells[1]).Value2.ToString());
if (currentModelNumber.Equals(bestModelNumber))
{
bestModel = true;
}
variables[0] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString();
VarPvalue[0] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString());
//SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString())
// , ((Excel.Range)row.Range.Cells[13]).Value2.ToString(), bestModel, thisSheet.Name, modelCount
// , VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString())
// , Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString()));
//Added by suman SEP Changes
SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString())
, ((Excel.Range)row.Range.Cells[14]).Value2.ToString(), bestModel, thisSheet.Name, modelCount
, VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString())
, Convert.ToDouble(((Excel.Range)row.Range.Cells[10]).Value2.ToString()));
sModCol.Add(sm);
bestModel = false;
}
else
{
if (((Excel.Range)row.Range.Cells[3]).Value2 != null)
{
sModCol.Item(sModCol.Count - 1).VariableNames[varCount] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString();
varCount++;
}
//if (((Excel.Range)row.Range.Cells[6]).Value2 != null)
//{
// sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString());
// varCount++;
//}
if (((Excel.Range)row.Range.Cells[7]).Value2 != null)
{
sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString());
varCount++;
}
}
}
}
this.label1.Text = "Select the model you wish to use to calculate the adjusted values on the EnPI Results and SEnPI Results sheets from the drop down below.";// +thisSheet.Name + ":";
int t = DataLO.ListColumns["Model is Appropriate for SEP"]._Default.Length;
}
private void UpdateEnPISheet(SwitchModel sm)
{
bool outputPresent = false;
Excel.Worksheet adjustedDataSheet = new Excel.Worksheet();
//Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; //TFS Ticket: 71242
Excel.Worksheet thisSheet = energySheet;
//Update the SEP Validation Check values
string sourceName = energySheet.Name;
sourceName = sourceName.Substring(1, sourceName.Length - 1).ToString().TrimStart();
Globals.ThisAddIn.SelectedSourcesBestModelFormulas[sourceName] = sm.Formula;
string adjustedDatasheetName = string.Empty;
foreach (GroupSheetCollection gsc in Globals.ThisAddIn.masterGroupCollection)
{
bool matchingCollection = false;
foreach (GroupSheet GS in gsc)
{
string GSname = GS.Name;
if (GS.WS.Equals(thisSheet))
{
matchingCollection = true;
}
if (GS.adjustedDataSheet && matchingCollection)
{
adjustedDataSheet = GS.WS;
adjustedDatasheetName = GS.WS.Name;
UpdateSEPValidationCheckList(adjustedDatasheetName);
outputPresent = true;
}
if (GS.outputSheet)
{
populateModelData(thisSheet, GS.WS, sm.ModelNumber);
}
}
}
if (outputPresent)
{
string adjDataSheetName = adjustedDataSheet.Name;
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[adjustedDataSheet.Name]).Select(Type.Missing);
thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
//Update the latest values on Validation Check Table of Model Sheet
try
{
for (int i = 0; i < Globals.ThisAddIn.lstSEPValidationValues.Count; i++)
{
Excel.Range sepValidationChk = thisSheet.get_Range("C13").get_Offset(0, i).get_Resize(1, 1);
sepValidationChk.Value2 = Globals.ThisAddIn.lstSEPValidationValues[i].SEPValidationCheck;
sepValidationChk.NumberFormat = "General";
}
}
catch (Exception ex)
{
}
Excel.ListObject LO = (Excel.ListObject)thisSheet.ListObjects.get_Item(1);
foreach (Excel.ListColumn col in LO.ListColumns)
{
//------------------------------
//modify substring so that regression runs past 9 can switch models
string shortColName = col.Name;
//since sheet names are limited to 29 characters -- see CreateValidWorksheetName in ExcelHelpers.cs
if (col.Name.Length >= 36)
{
shortColName = col.Name.Substring(0, 35);
}
string shtname = sm.SheetName.Substring(2);
if (shortColName.Equals("Modeled " + sm.SheetName.Substring(2)))
//------------------------------
{
col.DataBodyRange.Formula = "=" + sm.Formula;
//recalculate all of the List objects so numbers are updated.
foreach (Excel.Worksheet WS in WB.Worksheets)
{
string wsname = WS.Name;
foreach (Excel.ListObject ListObj in WS.ListObjects)
{
if (ListObj.DataBodyRange != null)
{
ListObj.DataBodyRange.Dirty();
ListObj.DataBodyRange.Calculate();
}
}
}
}
}
Excel.Range negativeMessageHeader = thisSheet.get_Range("A2");
Excel.Range negativeMessageDescription = thisSheet.get_Range("A3");
if (Globals.ThisAddIn.NegativeCheck(LO, Globals.ThisAddIn.modeledSourceIndex))
{
negativeMessageHeader.EntireRow.Hidden = false;
negativeMessageDescription.EntireRow.Hidden = false;
}
else
{
negativeMessageHeader.EntireRow.Hidden = true;
negativeMessageDescription.EntireRow.Hidden = true;
}
}
}
