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