//ham bao cao doanh thu theo khach hang
public void AllTariffByMonth(string Start, string End, string Area)
{
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);
int area = 0;
if (!string.IsNullOrEmpty(Area))
{
area = Int32.Parse(Area);
}
FileInfo template = new FileInfo(HostingEnvironment.MapPath("~/Content/template1.xlsx"));
ExcelPackage EP = new ExcelPackage(template);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelWorksheet Sheet = EP.Workbook.Worksheets[0];
List <VT_GetCustomerWithTransaction_Result> list = db.VT_GetCustomerWithTransaction(start, end, area).ToList();
int row = 5;
int sum = 0;
Sheet.Cells[string.Format("B2")].Value = "SẢN LƯỢNG HÀNG VẬN CHUYỂN TỪ " + Start + "-" + End;
foreach (var item in list)
{
Sheet.Cells[string.Format("B{0}", row)].Value = row - 4;
Sheet.Cells[string.Format("C{0}", row)].Value = item.Name;
Sheet.Cells[string.Format("D{0}", row)].Value = db.VT_GetCntr20(start, end, item.ID);
Sheet.Cells[string.Format("E{0}", row)].Value = db.VT_GetCntr40(start, end, item.ID);
Sheet.Cells[string.Format("F{0}", row)].Value = db.VT_GetCntr45(start, end, item.ID);
Sheet.Cells[string.Format("H{0}", row)].Value = item.Total;
sum += (int)item.Total;
row++;
}
Sheet.Cells[string.Format("H{0}", row)].Value = sum;
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");
Response.BinaryWrite(EP.GetAsByteArray());
Response.End();
}