/// <summary> /// /// </summary> /// <param name="excelTable"></param> /// <param name="columnName"></param> /// <param name="cellAdress"></param> public static void UpdateTableValues(ExcelTable excelTable, string columnName, string cellAdress) { var columnNameAdresse = ExcelHelpers.GetAdressFromColumnName(excelTable, columnName); for (int month = 1; month <= 12; month++) { var cellToUpdate = ExcelHelpers.AddRowAndColumnToCellAddress(columnNameAdresse, month, 0); var totalCellValue = ExcelHelpers.AddRowAndColumnToCellAddress(cellAdress, month, 0); excelTable.WorkSheet.Cells[cellToUpdate].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount"); excelTable.WorkSheet.Cells[cellToUpdate].Formula = totalCellValue; } }
/// <summary> /// /// </summary> /// <param name="categories"></param> /// <param name="workSheet"></param> /// <param name="tableName"></param> /// <param name="row"></param> /// <param name="excelTable"></param> /// <returns></returns> public static Dictionary <string, string> GetColumnsNameAdress(IEnumerable <string> categories, ExcelTable excelTable) { var dictionary = new Dictionary <string, string>(); foreach (var item in categories) { var cellAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, item); if (!string.IsNullOrEmpty(cellAdress)) { dictionary.Add(item, cellAdress); } } return(dictionary); }
public static void UpdateClassesTableValues(Dictionary <string, string> BudgetCategoriesAddressdictionary, Dictionary <string, string> ExpenseCategoriesAddressdictionary, ExcelTable excelTable) { //TODO check cell Value var date = (double)excelTable.WorkSheet.Cells["G1"].Value; var monthToFilter = DateTime.FromOADate(date).Month; // Get cell address var operatingAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "OPERATING"); var budgetAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "BUDGET"); var actualAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "ACTUAL"); var categories = BudgetCategoriesAddressdictionary.Where(ct => ExpenseCategoriesAddressdictionary.ContainsKey(ct.Key)).Select(ct => ct.Key).ToList(); var tableElement = excelTable.TableXml.DocumentElement; //tableElement.Attributes["ref"].Value = rng.Address; var ref1 = tableElement.Attributes["ref"].Value; var columnNode = tableElement["tableColumns"]; //columnNode.Attributes["count"].Value = rng.End.Column.ToString(); var count1 = columnNode.Attributes["count"].Value; for (int row = 0; row < categories.Count; row++) { //TODO Update formula =HLOOKUP([@OPERATING];'Expenses details'!$E$22:$AC$34;MONTH($G$1)+1;FALSE) string budgetCellAdress = BudgetCategoriesAddressdictionary[categories[row]]; string actualCellAdress = ExpenseCategoriesAddressdictionary[categories[row]]; string newBudgetCell = $"OFFSET({budgetCellAdress},MONTH($G$1),0)"; string newActualCell = $"OFFSET({actualCellAdress},MONTH($G$1),0)"; excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(operatingAdress, row + 1, 0)].Value = categories[row]; excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(budgetAdress, row + 1, 0)].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount"); excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(budgetAdress, row + 1, 0)].Formula = newBudgetCell; excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(actualAdress, row + 1, 0)].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount"); excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(actualAdress, row + 1, 0)].Formula = newActualCell; } }