public IActionResult DownloadExcel(string reportSql, string connectKey, string reportName) { var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName); Response.Headers.Add("content-disposition", "attachment; filename=" + reportName + ".xlsx"); Response.ContentType = "application/vnd.ms-excel"; return(File(excel, "application/vnd.ms-excel", reportName + ".xlsx")); }
public void DownloadExcel(string reportSql, string connectKey, string reportName) { var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName); Context.Response.ClearContent(); Context.Response.AddHeader("content-disposition", "attachment; filename=" + reportName + ".xlsx"); Context.Response.ContentType = "application/vnd.ms-excel"; Context.Response.BinaryWrite(excel); Context.Response.End(); }
public ActionResult DownloadExcel(string reportSql, string connectKey, string reportName, bool allExpanded, string expandSqls, string columnDetails = null) { var columns = columnDetails == null ? new List <ReportHeaderColumn>() : JsonConvert.DeserializeObject <List <ReportHeaderColumn> >(columnDetails); var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName, allExpanded, expandSqls.Split(',').ToList(), columns); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=" + reportName + ".xlsx"); Response.ContentType = "application/vnd.ms-excel"; Response.BinaryWrite(excel); Response.End(); return(View()); }
async Task IJob.Execute(IJobExecutionContext context) { var apiUrl = ConfigurationManager.AppSettings["dotNetReport.apiUrl"]; var accountApiKey = ConfigurationManager.AppSettings["dotNetReport.accountApiToken"]; var databaseApiKey = ConfigurationManager.AppSettings["dotNetReport.dataconnectApiToken"]; var fromEmail = ConfigurationManager.AppSettings["email.fromemail"]; var fromName = ConfigurationManager.AppSettings["email.fromname"]; var mailServer = ConfigurationManager.AppSettings["email.server"]; var mailUserName = ConfigurationManager.AppSettings["email.username"]; var mailPassword = ConfigurationManager.AppSettings["email.password"]; var clientId = ""; // you can specify client id here if needed // Get all reports with schedule and run the ones that are due using (var client = new HttpClient()) { var response = await client.GetAsync($"{apiUrl}/ReportApi/GetScheduledReports?account={accountApiKey}&dataConnect={databaseApiKey}&clientId={clientId}"); response.EnsureSuccessStatusCode(); var content = await response.Content.ReadAsStringAsync(); var reports = JsonConvert.DeserializeObject <List <ReportWithSchedule> >(content); foreach (var report in reports) { foreach (var schedule in report.Schedules) { try { var chron = new CronExpression(schedule.Schedule); var lastRun = !String.IsNullOrEmpty(schedule.LastRun) ? Convert.ToDateTime(schedule.LastRun) : DateTimeOffset.UtcNow.AddMinutes(-10); var nextRun = chron.GetTimeAfter(lastRun); schedule.NextRun = (nextRun.HasValue ? nextRun.Value.ToLocalTime().DateTime : (DateTime?)null); if (schedule.NextRun.HasValue && DateTime.Now >= schedule.NextRun && (!String.IsNullOrEmpty(schedule.LastRun) || lastRun <= schedule.NextRun)) { // need to run this report var dataFilters = new { }; // you can pass global data filters to apply as needed https://dotnetreport.com/kb/docs/advance-topics/global-filters/ response = await client.GetAsync($"{apiUrl}/ReportApi/RunScheduledReport?account={accountApiKey}&dataConnect={databaseApiKey}&scheduleId={schedule.Id}&reportId={report.Id}&localRunTime={schedule.NextRun.Value.ToShortDateString()} {schedule.NextRun.Value.ToShortTimeString()}&clientId={clientId}&dataFilters={(new JavaScriptSerializer()).Serialize(dataFilters)}"); response.EnsureSuccessStatusCode(); content = await response.Content.ReadAsStringAsync(); var reportToRun = JsonConvert.DeserializeObject <DotNetReportModel>(content); response = await client.GetAsync($"{apiUrl}/ReportApi/LoadReportColumnDetails?account={accountApiKey}&dataConnect={databaseApiKey}&reportId={report.Id}&clientId={clientId}"); response.EnsureSuccessStatusCode(); content = await response.Content.ReadAsStringAsync(); var columnDetails = JsonConvert.DeserializeObject <List <ReportHeaderColumn> >(content); var excelFile = DotNetReportHelper.GetExcelFile(reportToRun.ReportSql, reportToRun.ConnectKey, reportToRun.ReportName, columns: columnDetails); // send email var mail = new MailMessage { From = new MailAddress(fromEmail, fromName), Subject = report.Name, Body = $"Your scheduled report is attached.<br><br>{report.Description}", IsBodyHtml = true }; mail.To.Add(schedule.EmailTo); var attachment = new Attachment(new MemoryStream(excelFile), report.Name + ".xlsx"); mail.Attachments.Add(attachment); using (var smtpServer = new SmtpClient(mailServer)) { smtpServer.Port = 587; smtpServer.Credentials = new System.Net.NetworkCredential(mailUserName, mailPassword); //smtpServer.EnableSsl = true; smtpServer.Send(mail); } } } catch (Exception ex) { // could not run, ignore error } } } } }