private void WriteBestModel()
{
WS.Range["A1"].EntireRow.Hidden = true;
Utilities.Model bestmodel = Source.BestModel();
Globals.ThisAddIn.SelectedSourcesBestModelFormulas.Add(new KeyValuePair <string, string>(Source.Name, bestmodel.Formula()));
Excel.Range target = BottomCell().get_Offset(1, 0).get_Resize(1, 5);
target.Merge(true);
target.Value2 = Globals.ThisAddIn.rsc.GetString("bestModel") + bestmodel.ModelNumber.ToString();
if (!bestmodel.Valid())
{
target = BottomCell().get_Offset(1, 0).get_Resize(1, 5);
target.Merge(true);
target.Value2 = Globals.ThisAddIn.rsc.GetString("noValidModel");
}
target.EntireRow.Hidden = true;
}
public void WriteResultsTable(int n, bool top, bool isRead)
{
// put the model results data into an object array
//object[,] amodel ;
Excel.Range header = BottomCell().get_Offset(2, 0);
string start = header.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing);
ArrayList lst = this.Source.Models.ModelSort();
int first = top ? 0 : Math.Min(n, lst.Count);
int last = top ? Math.Min(n, lst.Count) : lst.Count;
if (first == last)
{
return;
}
// write the column headers
//{ ModelNo, ModelValid, IVNames, IVCoefficients, IVses, IVpVals, R2, adjR2, pVal, RMSError, Residual, AIC, Formula };
foreach (Utilities.Constants.ModelOutputColumns col in System.Enum.GetValues(typeof(Utilities.Constants.ModelOutputColumns)))
{
string label = Globals.ThisAddIn.rsc.GetString("label" + col.ToString()) ?? col.ToString();
header.Value2 = label;
header = header.get_Offset(0, 1);
}
object[,] row;
int rowct = 1;
for (int j = first; j < last; j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
int varct = mdl.VariableNames.Length;
rowct += varct + 1;
Excel.Range target = BottomCell().get_Offset(1, 0).get_Resize(varct + 1, 13);
row = new object[1, 13];
row[0, 0] = mdl.ModelNumber;
row[0, 1] = mdl.Valid();
row[0, 6] = mdl.R2();
row[0, 7] = mdl.AdjustedR2();
row[0, 8] = mdl.ModelPValue();
row[0, 9] = mdl.RMSError;
row[0, 10] = mdl.ResidualSS();
row[0, 11] = mdl.AICFormula();
row[0, 12] = mdl.Formula();
target.get_Resize(1, 13).Value2 = row;
if (top)
{
Excel.Hyperlink hl = (Excel.Hyperlink)WS.Hyperlinks.Add(target.get_Resize(1, 1), "",
target.get_Resize(1, 1).get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing), System.Type.Missing, System.Type.Missing);
hl.ScreenTip = "Graph model " + mdl.ModelNumber.ToString();
}
row = new object[varct + 1, 4];
for (int i = 0; i < varct; i++)
{
row[i, 0] = mdl.VariableNames[i];
row[i, 1] = mdl.Coefficients[i];
row[i, 2] = mdl.StandardErrors()[i];
row[i, 3] = mdl.PValues()[i];
}
//row[varct, 0] = "(Intercept)";
row[varct, 1] = mdl.Coefficients[varct];
target.get_Offset(0, 2).get_Resize(varct + 1, 4).Value2 = row;
// put a box around
target.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, System.Type.Missing);
// highlight the best model
if (Source.BestModel() != null)
{
if (mdl.ModelNumber == Source.BestModel().ModelNumber)
{
if (mdl.Valid())
{
target.Font.Color = 0x0000AA;
//target.Style = Globals.ThisAddIn.rsc.GetString("bestModelStyle");
target.Font.Bold = true;
}
else
{
target.Font.Color = 0x0000AA;
//target.Style = "Bad";
target.Font.Bold = true;
}
}
}
}
Excel.Range tbl = WS.get_Range(start, System.Type.Missing).get_Resize(rowct, 13);
Excel.ListObject LO = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tbl, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
LO.TableStyle = "TableStyleLight8";
LO.ShowTableStyleRowStripes = false;
((Excel.Range)LO.Range[0, 4]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 5]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 10]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 11]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 12]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 6]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 7]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 8]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 9]).EntireColumn.NumberFormat = "0.0000";
}