//ham bao cao doanh thu van chuyen theo khach hang public void RevenueByCustomer(string CustomerName, string CustomerId, string Start, string End) { DateTime start = DateTime.ParseExact(Start + " 00:00", "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture); DateTime end = DateTime.ParseExact(End + " 23:59", "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture); FileInfo template = new FileInfo(HostingEnvironment.MapPath("~/Content/template.xlsx")); ExcelPackage EP = new ExcelPackage(template); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; ExcelWorksheet Sheet = EP.Workbook.Worksheets[0]; int id = Int32.Parse(CustomerId); List <VT_RevenueByCustomer_Result> list = db.VT_RevenueByCustomer(id, start, end).ToList(); int row = 16; Sheet.Cells[string.Format("D6")].Value = CustomerName; foreach (var item in list) { int sum = 0; Sheet.Cells[string.Format("A{0}", row)].Value = item.Type; Sheet.Cells[string.Format("C{0}", row)].Value = item.doc; Sheet.Cells[string.Format("D{0}", row)].Value = item.TruckNo; Sheet.Cells[string.Format("E{0}", row)].Value = item.CntrNo; Sheet.Cells[string.Format("F{0}", row)].Value = item.Name; Sheet.Cells[string.Format("G{0}", row)].Value = item.CreateDate; if (item.CntrSize.Contains("20")) { Sheet.Cells[string.Format("H{0}", row)].Value = "1"; Sheet.Cells[string.Format("K{0}", row)].Value = item.FeeAmount; sum += Int32.Parse(item.FeeAmount.ToString()); } else if (item.CntrSize.Contains("40")) { Sheet.Cells[string.Format("I{0}", row)].Value = "1"; Sheet.Cells[string.Format("L{0}", row)].Value = item.FeeAmount; sum += Int32.Parse(item.FeeAmount.ToString()); } else if (item.CntrSize.Contains("45")) { Sheet.Cells[string.Format("J{0}", row)].Value = "1"; Sheet.Cells[string.Format("M{0}", row)].Value = item.FeeAmount; sum += Int32.Parse(item.FeeAmount.ToString()); } if (db.VT_GetLiftingFee(item.TransID, item.CntrSize).FirstOrDefault() != null) { if (item.CntrSize.Contains("20")) { Sheet.Cells[string.Format("N{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize); } else if (item.CntrSize.Contains("40")) { Sheet.Cells[string.Format("O{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize); } else if (item.CntrSize.Contains("45")) { Sheet.Cells[string.Format("P{0}", row)].Value = db.VT_GetLiftingFee(item.TransID, item.CntrSize); } sum += Int32.Parse(db.VT_GetLiftingFee(item.TransID, item.CntrSize).FirstOrDefault().ToString()); } Sheet.Cells[string.Format("Q{0}", row)].Value = sum; Sheet.Cells[string.Format("R{0}", row)].Value = item.Note; row++; } Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx"); Response.BinaryWrite(EP.GetAsByteArray()); Response.End(); }