public static void UpdateClassesTableValues(Dictionary <string, string> BudgetCategoriesAddressdictionary, Dictionary <string, string> ExpenseCategoriesAddressdictionary, int year,
ExcelWorksheet workSheet, string tableName)
{
//TODO check if dictionary have key and if Table have column name
var addressDictionary = ExcelHelpers.GetTableStartAdress(workSheet, tableName);
//TODO check cell Value
var date = (double)workSheet.Cells["G1"].Value;
var monthToFilter = DateTime.FromOADate(date).Month;
// Get cell address
var OperatingAdress = ExcelHelpers.GetColumnNameAdress("OPERATING", workSheet, tableName);
var BudgetAdress = ExcelHelpers.GetColumnNameAdress("BUDGET", workSheet, tableName);
var ActualAdress = ExcelHelpers.GetColumnNameAdress("ACTUAL", workSheet, tableName);
//Get Row and Colum Index
var OperatingIndex = ExcelHelpers.GetRowAndColumIndex(OperatingAdress);
var BudgetIndex = ExcelHelpers.GetRowAndColumIndex(BudgetAdress);
var ActualIndex = ExcelHelpers.GetRowAndColumIndex(ActualAdress);
//int budgetCategories = BudgetCategoriesAddressdictionary.Count();
//int expenseCategories = ExpenseCategoriesAddressdictionary.Count();
var categories = BudgetCategoriesAddressdictionary.Where(ct => ExpenseCategoriesAddressdictionary.ContainsKey(ct.Key)).Select(ct => ct.Key).ToList();
//List<string> categories = budgetCategories > expenseCategories ? new List<string>(ExpenseCategoriesAddressdictionary.Keys) : new List<string>(BudgetCategoriesAddressdictionary.Keys);
if (addressDictionary.Any())
{
var i = 1;
foreach (var category in categories)
{
//TODO Update formula =HLOOKUP([@OPERATING];'Expenses details'!$E$22:$AC$34;MONTH($G$1)+1;FALSE)
string budgetCellAdress = BudgetCategoriesAddressdictionary[category];
string actualCellAdress = ExpenseCategoriesAddressdictionary[category];
string newBudgetCell = $"OFFSET({budgetCellAdress},MONTH($G$1),0)";
string newActualCell = $"OFFSET({actualCellAdress},MONTH($G$1),0)";
AddExcelCellValue(OperatingIndex["row"] + i, OperatingIndex["column"], category, workSheet);
workSheet.Cells[BudgetIndex["row"] + i, BudgetIndex["column"]].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellFormula(BudgetIndex["row"] + i, BudgetIndex["column"], newBudgetCell, workSheet);
workSheet.Cells[ActualIndex["row"] + i, ActualIndex["column"]].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellFormula(ActualIndex["row"] + i, ActualIndex["column"], newActualCell, workSheet);
i++;
}
}
}
public static void UpdateYearTableValues(Dictionary <string, string> categoriesAddressdictionary, int year,
ExcelWorksheet workSheet, string tableName, string columnName, string dictionaryKey)
{
//TODO check if dictionary have key and if Table have column name
var addressDictionary = ExcelHelpers.GetTableStartAdress(workSheet, tableName);
// Get cell address
var columnNameAdress = ExcelHelpers.GetColumnNameAdress(columnName, workSheet, tableName);
var dictionaryKeyAddress = categoriesAddressdictionary[dictionaryKey];
//Get Row and Colum Index
var columNamecellIndex = ExcelHelpers.GetRowAndColumIndex(columnNameAdress);
if (addressDictionary.Any())
{
for (int month = 1; month <= 12; month++)
{
string newCellAdress = ExcelHelpers.AddRowAndColumnToCellAddress(categoriesAddressdictionary[dictionaryKey], month, 0);
workSheet.Cells[columNamecellIndex["row"], columNamecellIndex["column"]].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellFormula(columNamecellIndex["row"] + month, columNamecellIndex["column"], newCellAdress, workSheet);
}
}
}