public List <WarehouseMonthReportVM> GetMonthReport(WarehouseMonthFilterVM filter) { DateTime filterDate; if (filter.Date.Length == 11) { filterDate = DateTime.ParseExact(filter.Date, "dd.MM.yyyy.", null); } else { filterDate = DateTime.ParseExact(filter.Date, "MM.yyyy.", null); } var list = _context.Inventory .Include(x => x.Branch) .Include(x => x.ItemSize) .ThenInclude(x => x.Item) .Include(x => x.ItemSize) .ThenInclude(x => x.Size) .Where(x => x.BranchID == filter.BranchId) .Select ( x => new WarehouseMonthReportVM { Branch = x.Branch.Name, ItemSizeName = x.ItemSize.Item.Name + " - " + x.ItemSize.Size.Name, Sold = _context.Warehouse.Where(y => y.Date.Month == filterDate.Month && y.ItemSizeID == x.ItemSizeID && y.Date.Year == filterDate.Year) .Select(y => y.Sold).Sum() } ).ToList(); return(list); }
public IActionResult ExcelMonth(string Date, int BranchId) { using (var workbook = new XLWorkbook()) { WarehouseMonthFilterVM filterVM = new WarehouseMonthFilterVM { Date = Date, BranchId = BranchId }; if (Date.Length == 11) { Date = Date.Substring(3); } var worksheet = workbook.Worksheets.Add($"Report {Date}"); worksheet.Cell(1, 1).Value = $"Report for {Date}"; var list = _warehouseService.GetMonthReport(filterVM); worksheet.Column("A").Width = 30; worksheet.Column("B").Width = 30; worksheet.Column("C").Width = 10; worksheet.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; worksheet.Cell(1, 1).Style.Font.Bold = true; worksheet.Range("A1:C1").Merge(); worksheet.Cell(2, 1).InsertTable(list); using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Report {Date}.xlsx")); } } }