public IActionResult GetTopProducts() { result = _statisticalRepository.GetTopProducts(); return(Ok(result)); }
public IActionResult Index(string startDate, string endDate) { var title = ""; if (startDate == null) { bills = _statisticalRepository.GetAllBills(); products = _statisticalRepository.GetTopProducts(); cataloges = _statisticalRepository.GetTopCataloges(); } else { if (startDate.Equals(endDate)) { title = " NGÀY " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy"); } else { title = " TỪ " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy") + " ĐẾN " + Convert.ToDateTime(endDate).ToString("dd-MM-yyyy"); } bills = _statisticalRepository.GetAllBillsByDate(startDate, endDate); products = _statisticalRepository.GetTopProductsByDate(startDate, endDate); cataloges = _statisticalRepository.GetTopCatalogesByDate(startDate, endDate); } byte[] fileContents; using (var package = new ExcelPackage()) { var ws = package.Workbook.Worksheets.Add("Sheet1"); var titlePosition = "B2:F3"; var datePosition = "B5:D5"; var tablePosition = 6; var tableFirstRow = 7; //===========================================Bang doanh thu================================================== ws.Cells["B" + tablePosition].LoadFromCollection(bills, true, TableStyles.Medium14); var tableLastRow = ws.Dimension.End.Row; var tbBillLastRow = tableLastRow + 1; ws.Cells[titlePosition].Merge = true; ws.Cells[titlePosition].Value = "THỐNG KÊ DOANH THU" + title; ws.Cells[titlePosition].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Cells[titlePosition].Style.Font.Size = 20; ws.Cells[titlePosition].Style.Font.Bold = true; ws.Cells[datePosition].Merge = true; ws.Cells[datePosition].Value = "Ngày tạo: " + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"); for (int i = tableFirstRow; i < tbBillLastRow; i++) { using (ExcelRange Rng = ws.Cells["D" + i]) { if (!string.IsNullOrEmpty(Rng.Text)) { ExcelComment cmd = Rng.AddComment(Rng.Value.ToString(), "Apt. Notes"); } } } ; ws.Column(5).Style.Numberformat.Format = "dd-MM-yyyy HH:mm:ss"; ws.Column(6).Style.Numberformat.Format = "#,##0 [$VND]"; ws.Cells["B" + tbBillLastRow].Value = "Tổng Cộng"; ws.Cells["F" + tbBillLastRow].Formula = "=SUM(F" + tableFirstRow + ":F" + (tbBillLastRow - 1) + ")"; ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(112, 173, 71)); ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Color.SetColor(Color.White); ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Bold = true; ws.Cells["B" + tablePosition].Value = "#"; for (int i = 0; i < bills.Count(); i++) { ws.Cells["B" + (tableFirstRow + i)].Value = i + 1; } //===========================================Bang cataloges================================================== var titleCataPosition = "H4:J4"; var tbCataLastRow = tablePosition + cataloges.Count(); var totalCata = tbCataLastRow + 1; ws.Cells["I" + tablePosition].LoadFromCollection(cataloges, true); ws.Cells["H" + tablePosition].Value = "#"; for (int i = 0; i < cataloges.Count(); i++) { ws.Cells["H" + (tableFirstRow + i)].Value = i + 1; } ws.Cells[titleCataPosition].Value = "TOP DANH MỤC"; ws.Cells[titleCataPosition].Style.Font.Size = 14; ws.Cells[titleCataPosition].Style.Font.Bold = true; ws.Cells[titleCataPosition].Merge = true; ExcelRange range = ws.Cells["H" + tablePosition + ":J" + tbCataLastRow]; ExcelTable tab = ws.Tables.Add(range, "TopCataloges"); tab.TableStyle = TableStyles.Light9; ws.Cells["H" + totalCata].Value = "Tổng Cộng"; ws.Cells["J" + totalCata].Formula = "=SUM(J" + tableFirstRow + ":J" + tbCataLastRow + ")"; ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(155, 194, 230)); ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Color.SetColor(Color.White); ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Bold = true; //===========================================Bang product================================================== var titleProductPosition = "L4:P4"; ws.Cells["M" + tablePosition].LoadFromCollection(products, true); ws.Cells["L" + tablePosition].Value = "#"; for (int i = 0; i < products.Count(); i++) { ws.Cells["L" + (7 + i)].Value = i + 1; } ws.Cells[titleProductPosition].Value = "TOP SẢN PHẨM"; ws.Cells[titleProductPosition].Style.Font.Size = 14; ws.Cells[titleProductPosition].Style.Font.Bold = true; ws.Cells[titleProductPosition].Merge = true; ExcelRange range2 = ws.Cells["L" + tablePosition + ":" + ws.Dimension.End.Address]; ExcelTable tab2 = ws.Tables.Add(range2, "TopProducts"); tab2.TableStyle = TableStyles.Medium4; ws.Column(14).Style.Numberformat.Format = "#,##0 [$VND]"; ws.Column(16).Style.Numberformat.Format = "#,##0 [$VND]"; //================================================================================================ ws.Cells["B" + tableFirstRow + ":B" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; ws.Cells["E" + tableFirstRow + ":E" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; ws.Row(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Row(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Cells[ws.Dimension.Address].AutoFitColumns(); for (int col = 1; col <= ws.Dimension.End.Column; col++) { ws.Column(col).Width = ws.Column(col).Width + 2; } ws.Cells["D7"].AutoFitColumns(30.00, 40.00); ws.Column(1).Width = 4; // Send to browser fileContents = package.GetAsByteArray(); } if (fileContents == null || fileContents.Length == 0) { return(NotFound()); } return(File( fileContents: fileContents, contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileDownloadName: "report.xlsx" )); }