使用.Net MVC和日期范围过滤器从数据库导出Excel中的数据

Question

我正在创建一个简单的票务工具。我正在开发一个函数，在该函数中，我可以应用日期范围格式从DB导出数据。

使用我创建的代码，只有行被导出，但没有查看结果。请查看下面的代码并帮助修复此问题。

1. 所有门票视图

@using (Html.BeginForm("Filter", "Ticket", FormMethod.Post))
{
<input type="datetime" name="start" id="start" />
<input type="datetime" name="end" id="end" />
<input type="submit"/>
}

2. 筛选控制器以显示两个日期之间的数据

public ActionResult Filter(DateTime start, DateTime end)
{
using (Db db = new Db())
{
//Ticket dto = db.Tickets.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).FirstOrDefault();
var filterTicker = db.Tickets
.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).ToList();

return View(filterTicker);
}
}

3. 下载和导出数据的代码

public FileContentResult Download(Ticket model)
{
//Ticket model = new Ticket();
var fileDownloadName = String.Format("FileName.xlsx");
const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

// Pass your ef data to method
using (Db db = new Db())
{
var start = model.EscalatedOn;
var end = model.EscalatedOn;
ExcelPackage package = GenerateExcelFile(db.Tickets.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).ToList());

var fsr = new FileContentResult(package.GetAsByteArray(), contentType);
fsr.FileDownloadName = fileDownloadName;
return fsr;
}
}
private static ExcelPackage GenerateExcelFile(IEnumerable<Ticket> datasource)
{
ExcelPackage pck = new ExcelPackage();
//Create the worksheet 
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet 1");
// Sets Headers
ws.Cells[1, 1].Value = "Record Number";
ws.Cells[1, 2].Value = "Reviewer";
ws.Cells[1, 3].Value = "Resource";
ws.Cells[1, 4].Value = "Ticket Number";
ws.Cells[1, 5].Value = "Scenario";
ws.Cells[1, 6].Value = "Status";
//ws.Cells[1, 7].Value = "Escalated";
ws.Cells[1, 7].Value = "SLA Status";
ws.Cells[1, 8].Value = "Escalated On";
ws.Cells[1, 9].Value = "Was This Legit Esc";
ws.Cells[1, 10].Value = "Escalation Reason";
ws.Cells[1, 11].Value = "Can we de-escalate in future?";
ws.Cells[1, 12].Value = "How can we de-escalate?";
ws.Cells[1, 13].Value = "Username";
// Inserts Data
for (int i = 0; i < datasource.Count(); i++)
{
ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).RecNo;
ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).Reviewer;
ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).Resource;
ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).Number;
ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).Scenario;
ws.Cells[i + 2, 6].Value = datasource.ElementAt(i).Status;
//ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).Escalated;
ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).SLAStatus;
ws.Cells[i + 2, 8].Value = datasource.ElementAt(i).EscalatedOn;
ws.Cells[i + 2, 9].Value = datasource.ElementAt(i).WasThisLegitEsc;
ws.Cells[i + 2, 10].Value = datasource.ElementAt(i).EscalationReason;
ws.Cells[i + 2, 11].Value = datasource.ElementAt(i).DeEscalate;
ws.Cells[i + 2, 12].Value = datasource.ElementAt(i).HowCanWeDeEscalate;
ws.Cells[i + 2, 13].Value = datasource.ElementAt(i).Username;
}
// Format Header of Table
using (ExcelRange rng = ws.Cells["A1:M1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid 
rng.Style.Fill.BackgroundColor.SetColor(Color.Gold); //Set color to DarkGray 
rng.Style.Font.Color.SetColor(Color.Black);
}
return pck;
}

4. 筛选视图

@model IEnumerable<TicketTool.Models.VM.Ticket>
@{
ViewBag.Title = "Filter";
}
<h2>Filter</h2>
<a href="@Url.Action("Download","Ticket")" class="btn btn-sm btn-info">Export</a>
<table class="table">
<tr><th>
Ticket Number
</th>
<th>
Escalated On
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>

<td>
@Html.DisplayFor(modelItem => item.Number)
</td>
<td>
@Html.DisplayFor(modelItem => item.EscalatedOn)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.CaseId }) |
@Html.ActionLink("Details", "Details", new { id=item.CaseId }) |
@Html.ActionLink("Delete", "Delete", new { id=item.CaseId })
</td>
</tr>
}
</table>

非常感谢在这方面的任何帮助

Answer 1

在过滤操作中使用ViewBag

public ActionResult Filter(DateTime start, DateTime end)
{
using (Db db = new Db())
{
var filterTicker = db.Tickets
.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).ToList();

ViewBag.START = start;
ViewBag.END = end;
return View(filterTicker);
}
}

然后在过滤器视图中

<a href="@Url.Action("Download", "Ticket",new {start = ViewBag.START , end = ViewBag.END })" class="btn btn-sm btn-info">Export</a>

最后在下载动作

public FileContentResult Download(Ticket model,DateTime start, DateTime end)

Answer 2

以下是适用于我的的解决方案

1. 所有门票查看

{
<input type="datetime" name="start" id="start" />
<input type="datetime" name="end" id="end" />
<input type="submit"/>
}

2. 筛选控制器以显示两个日期之间的数据

public ActionResult Filter(DateTime start, DateTime end)
{
var st = start;
var en = end;
using (Db db = new Db())
{
var filterTicket = db.Tickets
.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).ToList();
ViewBag.START = st;
ViewBag.END = en;
return View(filterTicket);
}
}

3( 下载和导出数据的代码

public FileContentResult Download(DateTime start, DateTime end)
{
var fileDownloadName = String.Format("FileName.xlsx");
const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

// Pass your ef data to method

ExcelPackage package = GenerateExcelFile(db.Tickets.Where(x => x.EscalatedOn >= start && x.EscalatedOn <= end).ToList());

var fsr = new FileContentResult(package.GetAsByteArray(), contentType);
fsr.FileDownloadName = fileDownloadName;
return fsr;

}
private static ExcelPackage GenerateExcelFile(IEnumerable<Ticket> datasource)
{
ExcelPackage pck = new ExcelPackage();
//Create the worksheet 
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet 1");
// Sets Headers
ws.Cells[1, 1].Value = "Record Number";
ws.Cells[1, 2].Value = "Reviewer";
ws.Cells[1, 3].Value = "Resource";
ws.Cells[1, 4].Value = "Ticket Number";
ws.Cells[1, 5].Value = "Scenario";
ws.Cells[1, 6].Value = "Status";
//ws.Cells[1, 7].Value = "Escalated";
ws.Cells[1, 7].Value = "SLA Status";
ws.Cells[1, 8].Value = "Escalated On";
ws.Cells[1, 9].Value = "Was This Legit Esc";
ws.Cells[1, 10].Value = "Escalation Reason";
ws.Cells[1, 11].Value = "Can we de-escalate in future?";
ws.Cells[1, 12].Value = "How can we de-escalate?";
ws.Cells[1, 13].Value = "Username";
// Inserts Data
for (int i = 0; i < datasource.Count(); i++)
{
ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).RecNo;
ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).Reviewer;
ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).Resource;
ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).Number;
ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).Scenario;
ws.Cells[i + 2, 6].Value = datasource.ElementAt(i).Status;
//ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).Escalated;
ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).SLAStatus;
ws.Cells[i + 2, 8].Value = datasource.ElementAt(i).EscalatedOn;
ws.Cells[i + 2, 9].Value = datasource.ElementAt(i).WasThisLegitEsc;
ws.Cells[i + 2, 10].Value = datasource.ElementAt(i).EscalationReason;
ws.Cells[i + 2, 11].Value = datasource.ElementAt(i).DeEscalate;
ws.Cells[i + 2, 12].Value = datasource.ElementAt(i).HowCanWeDeEscalate;
ws.Cells[i + 2, 13].Value = datasource.ElementAt(i).Username;
}
// Format Header of Table
using (ExcelRange rng = ws.Cells["A1:M1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid 
rng.Style.Fill.BackgroundColor.SetColor(Color.Gold); //Set color to DarkGray 
rng.Style.Font.Color.SetColor(Color.Black);
}
return pck;
}

然后在过滤器视图中

<a href="@Url.Action("Download", "Ticket",new {start = ViewBag.START , end = ViewBag.END })" class="btn btn-sm btn-info">Export</a>

这对我来说很好