private void btnRun_Click(object sender, EventArgs e)
{
try
{
Excel.Worksheet thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
int orgCount = thisList.ListColumns.Count;
foreach (string t in this.checkedListBox1.CheckedItems)
{
int lcCount = thisList.ListColumns.Count;
int position = this.checkedListBox1.Items.IndexOf(t) + (lcCount - orgCount) + 2;
string colName = t + "(" + this.comboBox2.SelectedItem.ToString() + ")";
string formula = "=" + ExcelHelpers.CreateValidFormulaName(t) + "*" + this.textBox1.Text + "*" + this.textBox2.Text;
string stylename = "Comma";
Excel.ListColumn newColumn = thisList.ListColumns.Add(position);
newColumn.Name = colName;
newColumn.DataBodyRange.Formula = formula;
newColumn.DataBodyRange.Style = stylename;
//set old column property to not show up in regression/actual options
}
this.checkedListBox1.Items.Clear();
AddColumnNames();
}
catch
{
//raise error
}
}
internal string addProductionColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string colName = "";
string formula = "";
Excel.ListObject dataList = ExcelHelpers.GetListObject(WS, dataListName);
object[,] headers = dataList.HeaderRowRange.Value2 as object[, ];
string dttbl = dataListName;
string dtyearcol = ExcelHelpers.GetListColumn(dataList, EnPIResources.yearColName).Name;
string yearcol = ExcelHelpers.GetListColumn(LO, EnPIResources.yearColName).Name;
int i = headers.GetLowerBound(0);
for (int j = headers.GetLowerBound(1); j <= headers.GetUpperBound(1); j++)
{
if (DataHelper.isProduction(headers[i, j].ToString()))
{
formula += "SUM(" + dttbl + ExcelHelpers.CreateValidFormulaName(headers[i, j].ToString()) + " " +
DataHelper.RowRangebyMatch(dttbl, dtyearcol, "[[#This Row]," + yearcol + "]", dataSheetName)
+ ")" + "+";
}
}
if (formula != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(2);
colName = "Production";
newCol.Name = colName;
newCol.DataBodyRange.Formula = "=" + formula.Substring(0, formula.Length - 1);
newCol.DataBodyRange.Style = "Comma [0]";
}
return(colName);
}
internal void addEIColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string formula = "";
string newname = rsc.GetString("buildingEnPIColName");
string tbl = LO.Name;
string bsqfcol = rsc.GetString("buildingSQFColName");
string colhat = rsc.GetString("totalAdjValuesColName");
string that; //adjusted current year energy consumption
string bsqf; //building square feet
that = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(colhat) + "]";
bsqf = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(bsqfcol) + "]";
formula = "=IFERROR(" + that + "/" + bsqf + ",0)";
if (bsqfcol != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(missing);
newCol.Name = newname;
newCol.DataBodyRange.Formula = formula;
newCol.DataBodyRange.Style = "Comma";
}
}
