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
}
}
}
}
}