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); }
/// <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; } }
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()); }
//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); }
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); }
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; } ; } }
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); }
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); } }
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); }
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)); }
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); }
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 } }
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); } } }