public async Task <IActionResult> PostReport([FromBody] CreateReportDto createReportDto) { MonthReportModel model = new MonthReportModel { Month = createReportDto.DateReport.ToString("MMMM", CultureInfo.CreateSpecificCulture("en-US")), Year = createReportDto.DateReport.Year.ToString(), Procedures = await _reportService.GetPerformedProcedures(createReportDto.DateReport), Doctors = await _reportService.GetDoctors(), RentExpense = createReportDto.RentExpense, AdvertisingExpense = createReportDto.AdvertisingExpense, UtilitiesExpense = createReportDto.UtilitiesExpense }; byte[] bin = await _reportService.SaveExcelReportFile(model); //clear the buffer stream Response.Headers.Clear(); Response.Clear(); //set the correct contenttype Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //set the correct length of the data being send Response.Headers.Add("content-length", bin.Length.ToString()); //set the filename for the excel package Response.Headers.Add("content-disposition", "attachment; filename=\"MonthReport.xlsx\""); //send the byte array to the browser await Response.Body.WriteAsync(bin, 0, bin.Length); //cleanup await Response.CompleteAsync(); return(Ok()); }
public async Task <byte[]> SaveExcelReportFile(MonthReportModel model) { using (var package = new ExcelPackage()) { var workSheet = package.Workbook.Worksheets.Add($"{model.Month} , {model.Year}"); workSheet.Cells.Style.Font.Size = 14; workSheet.Cells["A1"].Value = "VETERINARY CLINIC"; workSheet.Cells["A1:D1"].Merge = true; workSheet.Row(1).Style.Font.Bold = true; workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells["A2"].Value = "Income statement"; workSheet.Cells["A2:D2"].Merge = true; workSheet.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells["A3"].Value = $"For the month ended {model.Month} , {model.Year}"; workSheet.Cells["A3:D3"].Merge = true; workSheet.Row(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells["A3:D3"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; workSheet.Cells["A4"].Value = "Revenues"; workSheet.Cells["A4:D4"].Merge = true; workSheet.Row(4).Style.Font.Bold = true; int proceduresCount = 0; if (model.Procedures != null && model.Procedures.Count > 0) { for (int i = 1; i <= model.Procedures.Count; i++) { workSheet.Cells["B" + (4 + i).ToString()].Value = $"{model.Procedures[i - 1].ProcedureName}: ({model.Procedures[i - 1].Count})"; workSheet.Cells["C" + (4 + i).ToString()].Value = model.Procedures[i - 1].Price * model.Procedures[i - 1].Count; proceduresCount++; } } workSheet.Cells["C" + (5 + proceduresCount).ToString()].Value = "Service revenue"; if (proceduresCount > 0) { workSheet.Cells["D" + (5 + proceduresCount).ToString()].Formula = "=SUM(C5:C" + (5 + proceduresCount - 1).ToString() + ")"; } else { workSheet.Cells["D" + (5 + proceduresCount).ToString()].Value = 0; } workSheet.Cells["D" + (5 + proceduresCount).ToString()].Style.Font.Bold = true; workSheet.Cells["A" + (6 + proceduresCount).ToString()].Value = "Expenses"; workSheet.Cells["A" + (6 + proceduresCount).ToString() + ":D" + (6 + proceduresCount).ToString()].Merge = true; workSheet.Row(6 + proceduresCount).Style.Font.Bold = true; int employeeCount = 0; if (model.Doctors != null && model.Doctors.Count > 0) { for (int i = 1; i <= model.Doctors.Count; i++) { workSheet.Cells["B" + (6 + (proceduresCount + i)).ToString()].Value = $"{model.Doctors[i - 1].FirstName} {model.Doctors[i - 1].LastName}"; workSheet.Cells["C" + (6 + (proceduresCount + i)).ToString()].Value = model.Doctors[i - 1].Salary; employeeCount++; } } workSheet.Cells["B" + (7 + proceduresCount + employeeCount).ToString()].Value = "Salaries expense"; if (employeeCount > 0) { workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Formula = "=SUM(C" + (7 + proceduresCount).ToString() + ":C" + (6 + proceduresCount + employeeCount).ToString() + ")"; } else { workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Value = 0; } workSheet.Cells["C" + (7 + proceduresCount + employeeCount).ToString()].Style.Font.Bold = true; int totalCount = proceduresCount + employeeCount; workSheet.Cells["B" + (8 + totalCount).ToString()].Value = "Rent expense"; workSheet.Cells["C" + (8 + totalCount).ToString()].Value = model.RentExpense != null ? model.RentExpense : 0; workSheet.Cells["C" + (8 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["B" + (9 + totalCount).ToString()].Value = "Advertising expense"; workSheet.Cells["C" + (9 + totalCount).ToString()].Value = model.AdvertisingExpense != null ? model.AdvertisingExpense : 0; workSheet.Cells["C" + (9 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["B" + (10 + totalCount).ToString()].Value = "Utilities expense"; workSheet.Cells["C" + (10 + totalCount).ToString()].Value = model.UtilitiesExpense != null ? model.UtilitiesExpense : 0; workSheet.Cells["C" + (10 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["C" + (11 + totalCount).ToString()].Value = "Total expense"; workSheet.Cells["D" + (11 + totalCount).ToString()].Formula = "=SUM(C" + (7 + totalCount).ToString() + ":C" + (10 + totalCount).ToString() + ")"; workSheet.Cells["D" + (11 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["A" + (12 + totalCount).ToString()].Value = "Net income"; workSheet.Cells["A" + (12 + totalCount).ToString() + ":" + "C" + (12 + totalCount).ToString()].Merge = true; workSheet.Cells["A" + (12 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["D" + (12 + totalCount).ToString()].Formula = "=D" + (5 + proceduresCount).ToString() + "-D" + (11 + totalCount).ToString(); workSheet.Cells["D" + (12 + totalCount).ToString()].Style.Font.Bold = true; workSheet.Cells["D" + (12 + totalCount).ToString()].Style.Border.BorderAround(ExcelBorderStyle.Thin); ExcelRange rg = workSheet.Cells[1, 1, workSheet.Dimension.End.Row, workSheet.Dimension.End.Column]; rg.Style.Border.BorderAround(ExcelBorderStyle.Thick); double minimumSize = 12; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns(minimumSize); return(await package.GetAsByteArrayAsync()); } }