public TestStep(
            Excel.Workbook workbook,
            Excel.Worksheet worksheet,
            Excel.ListRow listRow,
            ITestCommand command,
            string target,
            string value)
        {
            if (null == workbook)
            {
                throw new ArgumentNullException("workbook");
            }

            if (null == worksheet)
            {
                throw new ArgumentNullException("worksheet");
            }

            if (null == listRow)
            {
                throw new ArgumentNullException("listRow");
            }

            if (null == command)
            {
                throw new ArgumentNullException("command");
            }

            this.Workbook  = workbook;
            this.Worksheet = worksheet;
            this.ListRow   = listRow;
            this.Command   = command;
            this.Target    = target;
            this.Value     = value;
        }
        public ProtoContact(Excel.ListColumns columns, Excel.ListRow data)
        {
            IEnumerator   columnEnumerator = columns.GetEnumerator();
            StringBuilder address          = new StringBuilder();

            foreach (Excel.Range c in data.Range)
            {
                columnEnumerator.MoveNext();
                Excel.ListColumn col = (Excel.ListColumn)columnEnumerator.Current;
                switch (col.Name)
                {
                case "First":
                    First = ((String)c.Value ?? "").Trim();
                    break;

                case "Last":
                    Last = ((String)c.Value ?? "").Trim();
                    break;

                case "Birthday":
                    Birthday = c.Value;
                    break;

                case "Home":
                    HomePhone = ((String)c.Value ?? "").Trim();
                    break;

                case "Cell":
                    CellPhone = ((String)c.Value ?? "").Trim();
                    break;

                case "Email":
                    Email = ((String)c.Value ?? "").Trim();
                    break;

                case "Work Email":
                    WorkEmail = ((String)c.Value ?? "").Trim();
                    break;

                case "Address 1":
                    Address1 = ((String)c.Value ?? "").Trim();
                    address.Append(Address1);
                    break;

                case "Address 2":
                    Address2 = ((String)c.Value ?? "").Trim();
                    if (Address2 != "")
                    {
                        address.Append('\n');
                        address.Append(Address2);
                    }
                    break;

                default:
                    throw new Exception("Unknown Table Column " + col.Name);
                }
            }
            Address = address.ToString();
        }
        private void recorder_CommandRecording(object sender, Recorder.CommandRecorderEventArgs e)
        {
            var testCase = this.GetActiveWorkbookContext().GetActiveTestCase();

            Excel.ListObject listObject = testCase.ListObject;
            Excel.ListRow    listRow    = ListObjectHelper.GetEmptyRow(listObject);
            ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Command, e.Command);
            ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Target, e.Target);
            ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Value, e.Value);
        }
Exemple #4
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="worksheet"></param>
        /// <param name="listObject"></param>
        /// <param name="listRow"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public TestStep CompileFromListRow(
            Excel.Workbook workbook,
            Excel.Worksheet worksheet,
            Excel.ListObject listObject,
            Excel.ListRow listRow,
            Dictionary <string, string> data)
        {
            if (null == listObject)
            {
                throw new ArgumentNullException("listObject");
            }

            if (null == listRow)
            {
                throw new ArgumentNullException("listRow");
            }

            ExcelHelper.SetColor(listRow.Range, Constants.ColorNone);
            ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Error, string.Empty);

            string name = ListRowHelper.Get(listRow, ListRowHelper.ColumnIndex.Command);

            if (string.IsNullOrWhiteSpace(name))
            {
                return(null);
            }

            try
            {
                var command = TestCommandFactory.CreateCommand(name);
                var target  = this.GetValue(data, ListRowHelper.Get(listRow, ListRowHelper.ColumnIndex.Target));
                var value   = this.GetValue(data, ListRowHelper.Get(listRow, ListRowHelper.ColumnIndex.Value));

                this.SyntaxCheck(command, target, value);

                var step = new TestStep(
                    workbook,
                    worksheet,
                    listRow,
                    command,
                    target,
                    value);

                return(step);
            }
            catch (Exception ex)
            {
                Log.Logger.Warn(ex);
                ExcelHelper.SetColor(listRow.Range, Constants.ColorPink);
                Excel.Range range = ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Error, ex.Message);
                //ExcelHelper.AddComment(range, ex.Message);
                throw;
            }
        }
Exemple #5
0
        public static string Get(Excel.ListRow listRow, ColumnIndex columnIndex)
        {
            if (null == listRow)
            {
                throw new ArgumentNullException("listRow");
            }

            Excel.Range range = listRow.Range[1, columnIndex];
            string      text  = range.Text;

            return(text.Trim());
        }
Exemple #6
0
 //Added By Suman TFS Ticket: 68479
 private void AddNewRowToSummaryData(Excel.ListObject SummaryData, string rowName, string rowValue, string stylename, string format)
 {
     Excel.ListRow newRow = SummaryData.ListRows.Add();
     newRow.Range.Value2 = "=" + rowValue;
     ((Excel.Range)newRow.Range[1, 1]).Value2 = rowName;
     newRow.Range.Style = stylename;
     ((Excel.Range)newRow.Range).Cells.Interior.Color       = 0xBCE4D8;
     ((Excel.Range)newRow.Range[1, 1]).Cells.Interior.Color = 0x28624F;
     ((Excel.Range)newRow.Range[1, 1]).Cells.Font.Color     = 0xFFFFFF;
     ((Excel.Range)newRow.Range[1, 1]).Cells.Font.Bold      = true;
     newRow.Range.NumberFormat = format;
     ((Excel.Range)newRow.Range[1, 1]).NumberFormat = "General";
 }
        public static Excel.ListRow AddRow(Excel.ListObject listObject, bool isTextFormat = true)
        {
            Excel.ListRow listRow = listObject.ListRows.AddEx();

            if (isTextFormat)
            {
                listRow.Range.NumberFormatLocal = "@";
            }

            listRow.Range.ColumnWidth = DEFAULT_COLUMN_WIDTH;
            listRow.Range.WrapText    = true;

            return(listRow);
        }
Exemple #8
0
        public static Excel.ListRow GetListRow(Excel.ListObject LO)
        {
            Excel.ListRow thisRow = null;
            if (LO == null)
            {
                return(null);
            }

            foreach (Excel.ListRow i in LO.ListRows)
            {
                if (i.Index.Equals(1))
                {
                    thisRow = i;
                }
            }
            return(thisRow);
        }
Exemple #9
0
        internal void addUnadjustedEnergyIntensity()
        {
            Excel.ListRow prodEnIntenRow = SummaryData.ListRows.Add();

            string formula = "";

            for (int j = 0; j < DS.ProductionVariables.Count(); j++)
            {
                formula += SubtotalRowFormula("SUMIF", DS.ProductionVariables[j].ToString()) + "+";
            }
            if (formula != "")
            {
                string name = Globals.ThisAddIn.rsc.GetString("unadjustedEnergyIntensColName");
                prodEnIntenRow.Range.Value2 = "=" + "OFFSET(" + SummaryData.Name + "[#Headers], MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") + "\"," + SummaryData.Name + "[[#All],[ ]],0)-1,0)" + "/"
                                              + "OFFSET(" + SummaryData.Name + "[#Headers], MATCH(\"Total Production Output\"," + SummaryData.Name + "[[#All],[ ]],0)-1,0)";
                prodEnIntenRow.Range.Style = "Comma [0]";
                ((Excel.Range)prodEnIntenRow.Range[1, 1]).Value2 = name;
            }
            prodEnIntenRow.Range.NumberFormat = "###,##0.000";
        }
        public static void ForEach(Excel.ListObject listObject, Func <Excel.ListRow, bool> action)
        {
            if (null == listObject)
            {
                throw new ArgumentNullException("listObject");
            }

            int count = listObject.ListRows.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ListRow listRow = listObject.ListRows[i];

                if (!action(listRow))
                {
                    break;
                }
                ;
            }
        }
Exemple #11
0
        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);
        }
Exemple #12
0
        internal void addUnadjustedBuildingEnergyInten()
        {
            Excel.ListRow buildEnIntenRow = SummaryData.ListRows.Add();

            string formula = "";

            for (int j = 0; j < DS.BuildingVariables.Count(); j++)
            {
                formula += SubtotalRowFormula("AVERAGEIF", DS.BuildingVariables[j].ToString()) + "+";
            }
            if (formula != "")
            {
                string name = Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName");
                buildEnIntenRow.Range.Value2 = "=" + "OFFSET(" + SummaryData.Name + "[#Headers], MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") + "\"," + SummaryData.Name + "[[#All],[ ]],0)-1,0)" + "/"
                                               + formula.Substring(0, formula.Length - 1);
                buildEnIntenRow.Range.Style = "Comma [0]";
                ((Excel.Range)buildEnIntenRow.Range[1, 1]).Value2 = name;
            }

            buildEnIntenRow.Range.NumberFormat = "###,##0.000";
        }
        private void ExecuteInternal()
        {
            var workbookContext = App.Context.GetActiveWorkbookContext();

            Excel.Worksheet worksheet = this.GetReportSheet(workbookContext.Workbook);

            Excel.ListObject listObject = ListObjectHelper.AddListObject(worksheet);
            listObject.Name = "TestList";
            listObject.ListColumns[1].Name = Properties.Resources.ListColumnName_Command;
            listObject.ListColumns[1].Range.EntireColumn.AutoFit();

            ListObjectHelper.AddColumn(listObject, Properties.Resources.ReportColumnTestCase);
            ListObjectHelper.AddColumn(listObject, Properties.Resources.ReportColumnTestData);
            ListObjectHelper.AddColumn(listObject, Properties.Resources.ReportColumnResult);

            foreach (var testCase in App.Context.GetActiveWorkbookContext().TestCases)
            {
                Excel.ListRow row = ListObjectHelper.AddRow(listObject, true);
                ExcelHelper.SetText(worksheet, row.Range[1, 1], 1, testCase.DisplayName);
            }
        }
Exemple #14
0
        internal string VaryingRowFormula(int modelIndex, Excel.ListRow row, string before, string after)
        {
            string beforeModel = "=" + before;
            string afterModel  = "=" + after;
            string formula     = "";

            if (AdjustmentMethod[1, 1].Equals(Globals.ThisAddIn.rsc.GetString("adjustmentModel")))
            {
                formula += afterModel;
            }
            else if (AdjustmentMethod[(AdjustmentMethod.Length / 2) - 1, 1].Equals(Globals.ThisAddIn.rsc.GetString("adjustmentModel")))
            {
                formula += beforeModel;
            }
            else
            {
                bool modelSwitch = false;
                foreach (Excel.ListColumn LC in SummaryData.ListColumns)
                {
                    if (LC.Index.Equals(modelIndex))
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = beforeModel;
                        modelSwitch = true;
                    }
                    //after model
                    else if (modelSwitch)
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = afterModel;
                    }
                    //before model
                    else
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = beforeModel;
                    }
                }
            }

            return(formula);
        }
Exemple #15
0
        public static Excel.Range Set(Excel.ListRow listRow, ColumnIndex columnIndex, string value)
        {
            if (null == listRow)
            {
                throw new ArgumentNullException("listRow");
            }

            Excel.Range range = listRow.Range[1, columnIndex];

            if (string.IsNullOrWhiteSpace(value))
            {
                range.Clear();
                range.Formula = string.Empty;
            }
            else
            {
                range.NumberFormatLocal = "@";
                range.Value             = value;
                range.WrapText          = true;
            }

            return(range);
        }
        public static Excel.ListRow GetEmptyRow(Excel.ListObject listObject)
        {
            Excel.ListRow theListRow = null;

            ForEach(listObject, (listRow) =>
            {
                var s = ListRowHelper.Get(listRow, ListRowHelper.ColumnIndex.Command);
                if (string.IsNullOrWhiteSpace(s))
                {
                    theListRow = listRow;
                    return(false);
                }

                return(true);
            });

            if (null != theListRow)
            {
                return(theListRow);
            }

            return(ListObjectHelper.AddRow(listObject, true));
        }
Exemple #17
0
        public int ParseExcelFile(string wb_path)
        {
            Excel.Workbook wb = app.Workbooks.Open(wb_path, ReadOnly: true);

            Excel._Worksheet ws = wb.ActiveSheet;

            string OSV, KFO, partner, contract;

            double?debet, kredit;

            int ContractCountPerKFO = 0;

            OSV     = "";
            KFO     = "";
            partner = "";

            bool ReadMode = false;

            foreach (Excel.Range row in ws.UsedRange.Rows)
            {
                string firstCellValue = row.Cells[1, 1].Text;
                //string firstCellValue = (string)(row.Cells[1, 1] as Excel.Range).Value;

                if (firstCellValue == "Итого")
                {
                    break;
                }

                if (firstCellValue == "Договоры")
                {
                    ReadMode = true;
                    continue;
                }

                if (ReadMode)
                {
                    switch (row.Cells[1, 1].IndentLevel)
                    {
                    case 0:
                        OSV = firstCellValue;
                        break;

                    case 2:
                        KFO = firstCellValue;
                        break;

                    case 4:
                        partner = firstCellValue;
                        break;

                    case 6:
                        contract = firstCellValue;
                        debet    = (double?)(row.Cells[1, 19] as Excel.Range).Value;
                        kredit   = (double?)(row.Cells[1, 21] as Excel.Range).Value;

                        if (debet != null || kredit != null)
                        {
                            Excel.ListRow destRow = table.ListRows.Add();
                            //destRow.Range.Cells[1, 1] = Convert.ToString(OSV);
                            destRow.Range.Cells[1, 1].NumberFormatLocal = @"@";
                            destRow.Range.Cells[1, 1] = OSV;
                            destRow.Range.Cells[1, 2] = KFO;
                            destRow.Range.Cells[1, 3] = partner;
                            destRow.Range.Cells[1, 4] = contract;
                            destRow.Range.Cells[1, 5] = (debet == null) ? 0 : debet;
                            destRow.Range.Cells[1, 6] = (kredit == null) ? 0 : kredit;

                            ContractCountPerKFO++;
                        }

                        break;
                    }
                }
            }

            wb.Close();
            return(ContractCountPerKFO);
        }
Exemple #18
0
        internal void AddSubtotalColumns()
        {
            Globals.ThisAddIn.Application.AutoCorrect.AutoFillFormulasInLists = false;

            string stylename = "Comma";
            //Unadjusted Data--------------------------
            //Unadjusted Fuel
            bool firstRow = true;

            foreach (Utilities.EnergySource es in DS.EnergySources)
            {
                Excel.ListRow newRow = SummaryData.ListRows.Add();
                //for some reason, adding the first row to the ListRows added two rows...
                if (firstRow)
                {
                    SummaryData.ListRows[2].Delete();
                }
                firstRow = false;
                string name = es.Name.Replace(((char)13).ToString(), "").Replace(((char)10).ToString(), "");
                newRow.Range.Value2 = "=" + SubtotalRowFormula("SUMIF", name);
                ((Excel.Range)newRow.Range[1, 1]).Value2 = name;
                newRow.Range.Style = stylename;
                ((Excel.Range)newRow.Range).Cells.Interior.Color       = 0xBCE4D8;
                ((Excel.Range)newRow.Range[1, 1]).Cells.Interior.Color = 0x28624F;
                ((Excel.Range)newRow.Range[1, 1]).Cells.Font.Color     = 0xFFFFFF;
                ((Excel.Range)newRow.Range[1, 1]).Cells.Font.Bold      = true;
                newRow.Range.NumberFormat = "###,##0";
            }
            //Added By Suman TFS Ticket: 68479
            foreach (Utilities.EnergySource es in DS.EnergySources)
            {
                string name = es.Name.Replace(((char)13).ToString(), "").Replace(((char)10).ToString(), "");
                if (!name.Contains("TOTAL"))// Need to find out a way to eliminate total column here
                {
                    AddNewRowToSummaryData(SummaryData, name + " Annual Savings", SubtotalRowFormula("SUMIF", "Energy Savings: " + name), stylename, "###,##0");
                    if (Globals.ThisAddIn.fromEnergyCost)
                    {
                        //TFS Ticket 68851 :Modified By Suman
                        AddNewRowToSummaryData(SummaryData, name + " Estimated Cost Savings ($)", SubtotalRowFormula("SUMIF", "Cost Savings ($): " + name), stylename, "_($* #,##0.00_);_($* (#,##0.00);_($* " + " -" + "??_);_(@_)");
                    }
                }
            }

            if (DS.ProductionVariables.Count > 0)
            {
                Excel.ListRow prodOutput = SummaryData.ListRows.Add();
                string        range      = "";
                foreach (string prod in DS.ProductionVariables)
                {
                    if (DS.ProductionVariables.IndexOf(prod).Equals(DS.ProductionVariables.Count - 1))
                    {
                        range += SubtotalRowFormula("SUMIF", prod);
                    }
                    else
                    {
                        range += SubtotalRowFormula("SUMIF", prod) + " + ";
                    }
                }
                string name5 = "Total Production Output";
                prodOutput.Range.Value2 = "=" + range;
                ((Excel.Range)prodOutput.Range[1, 1]).Value2 = name5;
                prodOutput.Range.NumberFormat = "###,##0";
            }


            if (DS.ProductionVariables.Count > 0)
            {
                addUnadjustedEnergyIntensity();
            }

            if (DS.BuildingVariables.Count > 0)
            {
                addUnadjustedBuildingEnergyInten();
            }

            AdjustmentArraySetup(Utilities.ExcelHelpers.getYears(DS.SourceData));

            //calculate Cumulative Improvement
            Excel.ListRow cumulativeImprovRow = SummaryData.ListRows.Add(System.Type.Missing);
            string        ciName = EnPIResources.totalImprovementColName;

            if (DS.ProductionVariables.Count > 0)
            {
                cumulativeImprovRow.Range.Value2 = "=(INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedEnergyIntensColName") + "\"," + SummaryData.Name + "[[ ]],0),2)-INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedEnergyIntensColName") + "\"," + SummaryData.Name + "[[ ]],0),COLUMN()))/(INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedEnergyIntensColName") + "\"," + SummaryData.Name + "[[ ]],0),2))";
            }
            else if (DS.BuildingVariables.Count > 0)
            {
                cumulativeImprovRow.Range.Value2 = "=(INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName") + "\"," + SummaryData.Name + "[[ ]],0),2)-INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName") + "\"," + SummaryData.Name + "[[ ]],0),COLUMN()))/(INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName") + "\"," + SummaryData.Name + "[[ ]],0),2))";
            }
            else
            {
                cumulativeImprovRow.Range.EntireRow.Hidden = true;
            }
            cumulativeImprovRow.Range.Style = "Percent";
            ((Excel.Range)cumulativeImprovRow.Range[1, 1]).Value2 = ciName;
            ((Excel.Range)cumulativeImprovRow.Range[1, 2]).Value2 = 0;
            cumulativeImprovRow.Range.NumberFormat = "0.00%";

            //calculate Annual Improvement
            Excel.ListRow annualImprovRow = SummaryData.ListRows.Add(System.Type.Missing);
            string        aiName          = EnPIResources.annualImprovementColName;

            if (DS.ProductionVariables.Count > 0 || DS.BuildingVariables.Count > 0)
            {
                annualImprovRow.Range.Value2 = "=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0,1,1)-OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,-1,1,1)";
            }
            else
            {
                annualImprovRow.Range.EntireRow.Hidden = true;
            }
            annualImprovRow.Range.Style = "Percent";
            ((Excel.Range)annualImprovRow.Range[1, 1]).Value2 = aiName;
            ((Excel.Range)annualImprovRow.Range[1, 2]).Value2 = 0;
            annualImprovRow.Range.NumberFormat = "0.00%";

            //Calculate Annual Savings
            Excel.ListRow annualSavingsRow = SummaryData.ListRows.Add(System.Type.Missing);
            annualSavingsRow.Range.Value2 = "=(INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") + "\"," + SummaryData.Name + "[[ ]],0),2)-INDEX(" + SummaryData.Name + ",MATCH(\"" + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") + "\"," + SummaryData.Name + "[[ ]],0),COLUMN()))";
            annualSavingsRow.Range.Style  = "Comma";
            ((Excel.Range)annualSavingsRow.Range[1, 1]).Value2               = "Total Savings Since Baseline Year (MMBtu/Year)";
            ((Excel.Range)annualSavingsRow.Range).Cells.Interior.Color       = 0xFFFFFF;
            ((Excel.Range)annualSavingsRow.Range[1, 1]).Cells.Interior.Color = 0x28624F;
            ((Excel.Range)annualSavingsRow.Range[1, 2]).Value2               = 0;
            annualSavingsRow.Range.NumberFormat = "###,##0";

            //Calculate Cumulative savings
            Excel.ListRow cumulativeSavingsRow = SummaryData.ListRows.Add(System.Type.Missing);
            cumulativeSavingsRow.Range.Value2 = "=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0,1,1)-OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,-1,1,1)";
            cumulativeSavingsRow.Range.Style  = "Comma";
            ((Excel.Range)cumulativeSavingsRow.Range[1, 1]).Value2               = "New Energy Savings for Current Year (MMBtu/year)";
            ((Excel.Range)cumulativeSavingsRow.Range).Cells.Interior.Color       = 0xFFFFFF;
            ((Excel.Range)cumulativeSavingsRow.Range[1, 1]).Cells.Interior.Color = 0x28624F;
            ((Excel.Range)cumulativeSavingsRow.Range[1, 2]).Value2               = 0;
            cumulativeSavingsRow.Range.NumberFormat = "###,##0";

            //Added By Suman:TFS Ticket 68479
            //Estimated Annual cost savings
            if (Globals.ThisAddIn.fromEnergyCost)
            {
                string estimatedAnnualCostSavingsFormula = string.Empty;
                foreach (Utilities.EnergySource es in DS.EnergySources)
                {
                    if (!es.Name.Contains("TOTAL")) // Need to find out a way to eliminate total column here
                    {
                        estimatedAnnualCostSavingsFormula += "INDEX(" + SummaryData.Name + ",MATCH(\"" + es.Name + " Estimated Cost Savings ($)" + "\"," + "[[ ]],0),COLUMN())+";
                    }
                }
                estimatedAnnualCostSavingsFormula = estimatedAnnualCostSavingsFormula.Remove(estimatedAnnualCostSavingsFormula.Length - 1, 1);
                AddNewRowToSummaryData(SummaryData, "Estimated Annual Cost Savings", estimatedAnnualCostSavingsFormula, stylename, "_($* #,##0_);_($* (#,##0);_($* " + " -" + "??_);_(@_)");
            }

            //Added By Suman: TFS Ticket 68998
            //CO2 Avoided Emissions
            if (Globals.ThisAddIn.fromCO2Emission)
            {
                string co2EmissionFormula = string.Empty;
                foreach (Utilities.EnergySource es in DS.EnergySources)
                {
                    if (!es.Name.Contains("TOTAL")) // Need to find out a way to eliminate total column here
                    {
                        string emissionFactor;
                        Globals.ThisAddIn.CO2EmissionFactors.TryGetValue(es.Name, out emissionFactor);
                        //just in case not to break the code
                        emissionFactor      = ((!string.IsNullOrEmpty(emissionFactor) ? emissionFactor : "1"));
                        co2EmissionFormula += "INDEX(" + SummaryData.Name + ",MATCH(\"" + es.Name + " Annual Savings" + "\"," + "[[ ]],0),COLUMN())*" + emissionFactor + "/1000+"; //TFS Ticket: 68853
                    }
                }
                co2EmissionFormula = co2EmissionFormula.Remove(co2EmissionFormula.Length - 1, 1);
                AddNewRowToSummaryData(SummaryData, "Avoided CO2 Emissions (Metric Ton/year)", co2EmissionFormula, stylename, "###,##0"); //TFS Ticket: 70385
            }
        }
Exemple #19
0
        internal string SEnPI(int modelPosition, Excel.ListRow row, int modelIndex)
        {
            string beforeModel = "IFERROR((INDEX("
                                 + SummaryData.Name +
                                 ",MATCH(\""
                                 + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") +
                                 "\","
                                 + SummaryData.Name +
                                 "[[ ]],0),"
                                 + modelIndex +
                                 ")/INDEX("
                                 + SummaryData.Name +
                                 ",MATCH(\""
                                 + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") +
                                 "\","
                                 + SummaryData.Name +
                                 "[[ ]],0),))*((INDEX("
                                 + SummaryData.Name +
                                 ",MATCH(\""
                                 + Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName") +
                                 "\","
                                 + SummaryData.Name +
                                 "[[ ]],0),)/INDEX("
                                 + SummaryData.Name +
                                 ",MATCH(\""
                                 + Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName") +
                                 "\","
                                 + SummaryData.Name +
                                 "[[ ]],0),"
                                 + modelIndex +
                                 ")" + ")),1)";;
            string afterModel = "IFERROR((INDEX("
                                + SummaryData.Name +
                                ",MATCH(\""
                                + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") +
                                "\","
                                + SummaryData.Name +
                                "[[ ]],0),)/INDEX("
                                + SummaryData.Name +
                                ",MATCH(\""
                                + Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName") +
                                "\","
                                + SummaryData.Name +
                                "[[ ]],0),"
                                + modelIndex +
                                "))*((INDEX("
                                + SummaryData.Name +
                                ",MATCH(\""
                                + Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName") +
                                "\","
                                + SummaryData.Name +
                                "[[ ]],0),"
                                + modelIndex +
                                ")/INDEX("
                                + SummaryData.Name +
                                ",MATCH(\""
                                + Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName") +
                                "\","
                                + SummaryData.Name +
                                "[[ ]],0),)" + ")),1)";;

            string SEnPI = "";

            switch (modelPosition)
            {
            //Model = Baseline
            case 1:
                SEnPI = afterModel;
                break;

            //other
            case 2:
                bool modelSwitch = false;
                foreach (Excel.ListColumn LC in SummaryData.ListColumns)
                {
                    if (LC.Index.Equals(modelIndex))
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = "=" + beforeModel;
                        modelSwitch = true;
                    }
                    //after model
                    else if (modelSwitch)
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = "=" + afterModel + " * OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-" + (LC.Index - 2).ToString() + ",1,1)";
                    }
                    //before model
                    else
                    {
                        ((Excel.Range)row.Range[1, LC.Index]).Value2 = "=" + beforeModel;
                    }
                }
                break;

            //Model = Last reporting year
            case 3:
                SEnPI = beforeModel;
                break;
            }

            return(SEnPI);
        }
        private void Import(string path)
        {
            if (string.IsNullOrWhiteSpace(path))
            {
                return;
            }

            using (var reader = new StreamReader(path, Encoding.UTF8))
            {
                using (var sgmlReader = new SgmlReader()
                {
                    DocType = "HTML",
                    CaseFolding = CaseFolding.ToLower,
                    IgnoreDtd = true,
                    InputStream = reader
                })
                {
                    XDocument  xml = XDocument.Load(sgmlReader);
                    XNamespace ns  = "http://www.w3.org/1999/xhtml";

                    var profile = xml.Descendants(ns + "head").Attributes("profile").First().Value;

                    if (profile != "http://selenium-ide.openqa.org/profiles/test-case")
                    {
                        throw new InvalidOperationException(Properties.Resources.ImportTestcaseNoSuchProfile);
                    }

                    string testcaseName = xml.Descendants(ns + "thead").Descendants(ns + "td").First().Value;
                    string baseUrl      = this.GetBaseUrl(ns, xml);

                    var trs = xml.Descendants(ns + "tbody").Descendants(ns + "tr");

                    var workbookContext = App.Context.GetActiveWorkbookContext();
                    workbookContext.BaseUrl = baseUrl;

                    Excel.Workbook  workbook  = workbookContext.Workbook;
                    Excel.Worksheet worksheet = ExcelHelper.WorksheetAdd(workbook);
                    ExcelHelper.WorksheetActivate(worksheet);

                    string newName = ListObjectHelper.NewTestCaseName(workbook) + "_" + testcaseName;
                    worksheet.Name = newName;

                    Excel.ListObject listObject = ListObjectHelper.AddListObject(worksheet);
                    listObject.Name = newName;

                    listObject.ListColumns[1].Name = Properties.Resources.ListColumnName_Command;
                    listObject.ListColumns[1].Range.EntireColumn.AutoFit();

                    ListObjectHelper.AddColumn(listObject, Properties.Resources.ListColumnName_Target);
                    ListObjectHelper.AddColumn(listObject, Properties.Resources.ListColumnName_Value);
                    ListObjectHelper.AddColumn(listObject, Properties.Resources.ListColumnName_Result);
                    ListObjectHelper.AddColumn(listObject, Properties.Resources.ListColumnName_ErrorMessage);
                    ListObjectHelper.AddColumn(listObject, Properties.Resources.ListColumnName_Evidence);

                    foreach (var tr in trs)
                    {
                        var td      = tr.Elements(ns + "td");
                        var command = td.ElementAt(0).Value;
                        var target  = td.ElementAt(1).Value;
                        var value   = td.ElementAt(2).Value;

                        Excel.ListRow listRow = ListObjectHelper.AddRow(listObject);
                        ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Command, command);
                        ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Target, target);
                        ListRowHelper.Set(listRow, ListRowHelper.ColumnIndex.Value, value);
                    }

                    ListObjectHelper.SelectCell(listObject, 2, 1);
                    App.Context.Update();
                    ExcelHelper.WorksheetActivate(worksheet);
                }
            }
        }