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