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);
}
}
}
private static void SetStartRowAndColum(string cellAdress)
{
if (!string.IsNullOrEmpty(cellAdress))
{
var column = string.Empty;
var row = string.Empty;
foreach (char c in cellAdress)
{
if (char.IsLetter(c))
{
column += c;
}
if (char.IsNumber(c))
{
row += c;
}
}
int rowNumber;
int.TryParse(row, out rowNumber);
_startRow = rowNumber;
_startColumn = ExcelHelpers.GetColumnIndex(column);
}
}
public static void AddExcelCellValue(int row, int column, object value, ExcelWorksheet wsSheet, Color?color = null)
{
var cellAddress = $"{ExcelHelpers.GetColumnName(column)}{row}";
AddExcelCellValue(cellAddress, value, wsSheet, color);
}
public static void AddExcelCellFormula(int row, int column, string formula, ExcelWorksheet wsSheet, Color?color = null)
{
var cellAddress = $"{ExcelHelpers.GetColumnName(column)}{row}";
AddExcelCellFormula(cellAddress, formula, wsSheet, color);
}
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 CreateExcelMonthSummaryTableFromMovementsViewModel(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
int sheetYear = 0, string sheetTableName = null, bool justExtrations = true)
{
int minYear;
int maxYear;
IEnumerable <string> TemExcelColumn;
if (sheetYear > 0)
{
minYear = sheetYear;
maxYear = sheetYear;
//add month ant total column to Ienumeration
TemExcelColumn = new[] { "Month", "Total" };
}
else
{
minYear = movementsModel.Min(mov => mov.DateTime.Year);
maxYear = movementsModel.Max(mov => mov.DateTime.Year);
//add month column to Ienumeration
TemExcelColumn = new[] { "Month" };
}
if (justExtrations)
{
categories = ModelClassServices.GetExtractionCategories(categories, movementsModel);
}
else
{
categories = ModelClassServices.GetIncomsCategories(categories, movementsModel);
}
categories = categories.OrderBy(c => c);
// and the new columns to the category
var newExcelColumn = TemExcelColumn.Concat(categories);
// Calculate size of the table
var endRow = _startRow + 12;
var endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
int startRow = _startRow;
int startColumn = _startColumn;
//var row = _startRow + 1;
var row = _startRow;
for (int year = minYear; year <= maxYear; year++)
{
//give table Name
var tableName = sheetTableName ?? string.Concat("Table-", year);
// add Headers to table
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
var tableStartColumn = _startColumn;
row++;
// Set Excel table content
for (int month = 1; month <= 12; month++)
{
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
AddExcelCellValue(row, tableStartColumn, monthName, wsSheet);
if (sheetYear > 0)
{
//Get summ for category
double totalCategory = ModelClassServices.CategoriesMonthYearTotal(movementsModel, year, month, justExtrations);
AddExcelCellValue(row, tableStartColumn + 1, totalCategory, wsSheet);
}
foreach (var category in newExcelColumn)
{
if (category != "Month" && category != "Total")
{
//Get summ for category
double totalCategory = ModelClassServices.TotalforCategory(movementsModel, category, year, month, justExtrations);
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
//add value tu excel cell
AddExcelCellValue(row, tableStartColumn, totalCategory, wsSheet);
}
tableStartColumn++;
}
tableStartColumn = _startColumn;
row++;
}
row = row + 2;
startRow = row;
endRow = row + 12;
}
//var noko = dict2.Keys.Except(dict.Keys);
//var noko2 = dict.Keys.Except(dict2.Keys);
wsSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
}
public static void CreateCategoriesAverage(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
int year = 0, int month1 = 0, bool justExtrations = true)
{
if (justExtrations)
{
categories = ModelClassServices.GetExtractionCategories(categories, movementsModel);
}
else
{
categories = ModelClassServices.GetIncomsCategories(categories, movementsModel);
}
categories = categories.OrderBy(c => c);
var columns = new[] { "Type" };
var newAverageColumn = new[] { $"Year({year})", "Day" };
var newExcelColumn = columns.Concat(categories);
// Calculate size of the table
var endRow = _startRow + newAverageColumn.Count();
var endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
int startRow = _startRow;
int startColumn = _startColumn;
var row = _startRow;
var tableStartColumn = _startColumn;
// Set Excel table content
//give table Name
var tableName = "CategoriesAverage";
// Add table Headers
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
row++;
foreach (var item in newAverageColumn)
{
foreach (var category in newExcelColumn)
{
if (category == "Type")
{
AddExcelCellValue(row, tableStartColumn, item, wsSheet);
}
else
{
double categoryAverage = 0;
if (item == $"Year({year})")
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, year, null, justExtrations);
}
if (item == $"Day")
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, null, null, justExtrations);
}
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellValue(row, tableStartColumn, categoryAverage, wsSheet);
}
tableStartColumn++;
}
tableStartColumn = _startColumn;
row++;
}
row = row + 3;
// Calculate size of the table
endRow = row + 12;
endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
startRow = row;
startColumn = _startColumn;
tableStartColumn = _startColumn;
// Set Excel table content
//give table Name
tableName = "CategoriesMonthAverage";
// Add table Headers
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
row++;
tableStartColumn = _startColumn;
for (int month = 1; month <= 12; month++)
{
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
AddExcelCellValue(row, tableStartColumn, monthName, wsSheet);
foreach (var category in newExcelColumn)
{
double categoryAverage = 0;
if (category == "Type")
{
tableStartColumn++;
continue;
}
else
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, null, month, justExtrations);
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellValue(row, tableStartColumn, categoryAverage, wsSheet);
tableStartColumn++;
}
}
tableStartColumn = _startColumn;
row++;
}
}