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