/// <summary>
///
/// </summary>
/// <param name="wsSheet"></param>
/// <param name="excelPackage"></param>
/// <param name="movementsModel"></param>
/// <param name="categories"></param>
/// <param name="sheetYear"></param>
/// <param name="sheetTableName"></param>
/// <param name="justExtrations"></param>
/// <param name="startCell"></param>
/// <returns></returns>
public static List <ExcelTable> CreateExcelMonthSummaryTableFromMovementsViewModel(ExcelWorksheet wsSheet, List <MovementsViewModel> movementsModel, IEnumerable <string> categories,
int sheetYear = 0, string sheetTableName = null, bool justExtrations = true, string startCell = null)
{
int minYear;
int maxYear;
if (sheetYear > 0)
{
minYear = sheetYear;
maxYear = sheetYear;
}
else
{
minYear = movementsModel.Min(mov => mov.DateTime.Year);
maxYear = movementsModel.Max(mov => mov.DateTime.Year);
}
IEnumerable <string> newColumns = new[] { "Month", "Total" };
categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
categories = categories.OrderBy(c => c);
// and the new columns to the category
categories = newColumns.Concat(categories);
var categoriesUpdated = categories as string[] ?? categories.ToArray();
string startTableCell = startCell ?? _startCell;
// Create Excel table Header
var endTableCellAddress = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
var tableName = sheetTableName ?? "Tanble-";
List <ExcelTable> excelTables = new List <ExcelTable>();
for (int year = minYear; year <= maxYear; year++)
{
//give table Name
tableName = string.Concat(tableName, year);
//calculate Table sizes
endTableCellAddress = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
var excelTable = CreateExcelTable(wsSheet, tableName, categoriesUpdated, startTableCell, endTableCellAddress, true);
// Set Excel table content
for (int month = 1; month <= 12; month++)
{
for (int i = 0; i < categoriesUpdated.Length; i++)
{
switch (categoriesUpdated[i])
{
case "Month":
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = monthName;
break;
case "Total":
double totalCategory = ModelConverter.CategoriesMonthYearTotal(movementsModel, year, month, justExtrations);
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = totalCategory;
break;
default:
//Get summ for category
var tablecell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i);
double totalCategory1 = ModelOperation.GetTotalforCategory(movementsModel, categoriesUpdated[i], year, month, justExtrations);
excelTable.WorkSheet.Cells[tablecell].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
//add value tu excel cell
wsSheet.Cells[tablecell].Value = totalCategory1;
//AddExcelCellValue(row, tableStartColumn, totalCategory1, wsSheet);
break;
}
}
}
startTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12 + 5, 0);
excelTable.WorkSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
excelTables.Add(excelTable);
}
return(excelTables);
}
