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