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