Example #1
0
        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;
        }
Example #2
0
        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;
            }
        }
Example #3
0
        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";
        }