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;
}
internal void newChart(int n)
{
Excel.Range chartText1 = BottomCell().get_Offset(2, 0).get_Resize(1, 3);
Excel.Range chartText2 = chartText1.get_Offset(0, 6).get_Resize(1, 8);
((Excel.Range)chartText1[1, 1]).Value2 = "The plots below show the actual energy consumption versus the independent variables for the model year.";
((Excel.Range)chartText2[1, 1]).Value2 = "The line labeled \"Actuals\" in the plot below has not been adjusted. This is the original data entered by the user. The line labeled \"Model\" is the predicted energy consumption using the model selected above.";
chartText1.EntireRow.RowHeight = 75;
chartText1.EntireRow.Font.Bold = true;
chartText1.EntireRow.WrapText = true;
chartText1.Merge();
chartText2.Merge();
Excel.Range start = BottomCell().get_Offset(1, 0);
double topleft;
if (!double.TryParse(start.Top.ToString(), out topleft))
{
topleft = 0;
}
start.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
ArrayList lst = this.Source.Models.ModelSort();
string[] variables = new string[] {};
int longest = 0;
foreach (Utilities.Model model in lst)
{
if (model.VariableNames.Length > longest)
{
longest = model.VariableNames.Length;
variables = model.VariableNames;
}
}
object[] sources = new object[variables.Length];
int k = 0;
for (int i = 0; i < variables.Length; i++)
{
double[] temp = new double[Source.knownXs.Rows.Count];
foreach (System.Data.DataRow row in Source.knownXs.Rows)
{
temp[k] = Convert.ToDouble(row.ItemArray[i]);
k++;
}
k = 0;
sources[i] = temp;
}
for (int i = 0; i < variables.Length; i++)
{
if (i.Equals(0))
{
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
else
{
Excel.Range afterFirst = BottomCell().get_Offset(1, 0);
afterFirst.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
double topleftinner;
if (!double.TryParse(afterFirst.Top.ToString(), out topleftinner))
{
topleftinner = 0;
}
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleftinner, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
}
for (int j = 0; j < Math.Min(n, lst.Count); j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(400, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Placement = Excel.XlPlacement.xlMove;
CO.Name = "Model " + mdl.ModelNumber;
CO.Visible = false;
CO.Chart.ChartType = Excel.XlChartType.xlLineMarkers;
CO.Chart.ChartStyle = 5;
Excel.Axis CharObj2Yaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
CharObj2Yaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Yaxis.HasTitle = true;
CharObj2Yaxis.AxisTitle.Text = "Input Interval";
Excel.Axis CharObj2Xaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
CharObj2Xaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Xaxis.HasTitle = true;
CharObj2Xaxis.AxisTitle.Text = WS.Name;
//Modified by suman: TFS ticket :69333
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.Name = "Actuals";
Excel.Series modelSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
modelSeries.Values = mdl.PredictedYs();
modelSeries.Name = "Model " + mdl.ModelNumber;
newSeries.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkRed);// 0x4177B8;//12089153; // Calculated from color calculator
newSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
newSeries.MarkerBackgroundColorIndex = (Microsoft.Office.Interop.Excel.XlColorIndex) 9;
newSeries.Format.Line.Transparency = 1.0f;
if (mdl == this.Source.BestModel())
{
CO.Visible = true;
}
CO.Chart.AutoScaling = true;
CO.Chart.Refresh();
CO.Chart.HasTitle = true;
CO.Chart.ChartTitle.Text = "Comparison of Actual and Model Data";
CO.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;
}
}
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";
}
