public ActionResult DownloadExcel(DateTime startDate, DateTime endDate, string portal) { int portalId = 0; int.TryParse(portal, out portalId); SubscriptionHistoryRepository subscriptionHistoryRepository = new SubscriptionHistoryRepository(); //get data from database var subscriptionHistory = subscriptionHistoryRepository.GetSubscriptionHistory (portalId, startDate, endDate); FileInfo template = new FileInfo( Server.MapPath("~/ExcelTemplate/8eSieugameClubreport.xlsx")); using (ExcelPackage pck = new ExcelPackage(template, true)) { ExcelWorksheet wsDaily = pck.Workbook.Worksheets["8e Sieugame-Daily"]; int startRow = 3; foreach (SubscriptionHistory subHistory in subscriptionHistory) { int columnIndex = 1; wsDaily.Cells[startRow, columnIndex++].Value = subHistory .DateLogged .DayOfWeek .ToString(); wsDaily.Cells[startRow, columnIndex++].Value = subHistory .DateLogged .ToString("MM/dd/yyyy"); wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Visit; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Active; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Billed; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.New; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Pending; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Renewals; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Suspended; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Total; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Canceled; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Unsubscribed; wsDaily.Cells[startRow, columnIndex++].Value = subHistory.Downloads; columnIndex++;//blank column wsDaily.Cells[startRow, columnIndex++].Value = subHistory.PaidDownloadRevenue; columnIndex++;//blank column wsDaily.Cells[startRow, columnIndex++].Value = subHistory.TurnoverAmount; startRow++; } Response.Clear(); pck.SaveAs(Response.OutputStream); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("content-disposition", "attachment; filename=8eSieugameClubreport.xlsx"); } return new EmptyResult(); }
private SubscriptionHistoryList GetSubscriptionHistory(string portal, DateTime startDate, DateTime endDate, int? page) { int portalId = 0; int.TryParse(portal, out portalId); if (page == null) page = 0; SubscriptionHistoryRepository subscriptionHistoryRepository = new SubscriptionHistoryRepository(); var subscriptionHistory = subscriptionHistoryRepository.GetSubscriptionHistory (portalId, startDate, endDate); var paginatedSubscriptionHistory = new PaginatedList<SubscriptionHistory> (subscriptionHistory, page.Value, Settings.PageSize); SubscriptionHistoryList model = new SubscriptionHistoryList(); model.StartDate = startDate; model.EndDate = endDate; model.PortalId = portalId; model.PaginatedList = paginatedSubscriptionHistory; return model; }