我想要一个基于mvc中的一些sql的Excel文件来下载

Question

我正在处理一个类项目，我正在尝试实现一个简单的工资流程，用户输入开始日期和结束日期，然后点击获取报告，然后下载Excel文件。我还是ASP.NET MVC的新手，我发现它下载了一些代码，但不是我所期望的。

这是我的简单视图

@model IEnumerable<RazorCab.Models.Payroll>
@{
    ViewBag.Title = "Index";
   string strMessage = ViewBag.Message; 
}
<h2>Payroll</h2>
@using (Html.BeginForm("Index", "Payrolls", FormMethod.Get))
{
 <table>
<tr>
      <td>Select Dates:</td>
      <td><input type="date" name="dteStartDate" id="dteStartDate"       placeholder="YYYY/MM/DD" /></td>
    <td>To</td>
    <td><input type="date" name="dteEndDate" id="dteEndDate" placeholder="YYYY/MM/DD" /></td>
</tr>
<tr>
    <td><input type="submit" value="Get Report" /></td>
</tr>
@if (strMessage != "")
{
 <tr>
<td><p>@strMessage</p></td>
</tr>
}
</table>
}

这是我的控制器

public class PayrollsController : Controller
{
    private RazorCab02Entities db = new RazorCab02Entities();  
    // GET: Payrolls
    public ActionResult Index(DateTime? dteStartDate, DateTime? dteEndDate)
    {
        if (dteStartDate != null && dteEndDate != null)
        {
            var @StartDate = new SqlParameter("@StartDate", dteEndDate);
            var @EndDate = new SqlParameter("@EndDate", dteEndDate);
            string query = "SELECT E.EMPLID, E.FIRSTNAME, E.LASTNAME, E.WAGE, H.CLOCKINTIME, H.COCKOUTTIME, DATEDIFF(HOUR, H.CLOCKINTIME, H.CLOCKOUTTIME) AS HOURS_WORKED, E.WAGE * DATEDIFF(HOUR, H.CLOCKINTIME, H.CLOCKOUTTIME) AS DAILY_EARNINGS "
                + "FROM EMPLOYEE E "
                + "JOIN HOURLYWAGEMGMT H "
                + "ON H.EMPLID = E.EMPLID "
                + "WHERE E.POSITION = 'Driver' "
                + "AND H.CLOCKINDATE BETWEEN @StartDate AND @EndDate "
                + "AND H.CLOCKOUTDATE BETWEEN @StartDate AND @EndDate ";
            var grid = new GridView();
            grid.DataSource = query;
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-diposition", "attachment; filename=PayrollExcelFile.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grid.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return View();
        }
        else
        {
            return View();
        }
    }
}

这是我的型号：

public class Payroll
{
    public int EmplID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public decimal Wage { get; set; }
    public DateTime ClockInTime { get; set; }
    public DateTime? ClockOutTime { get; set; }
    public int HoursWorked { get; set; }
    public decimal DailyEarnings { get; set; }
}

任何帮助都将不胜感激，我查看了控制器代码，它对做这件事的人来说很好，但我不确定我做错了什么。

Answer 1

我最终实现了Epplus，我删除了那个代码，并在我的控制器中写了这个代码——它可以工作，除了我的日期被转换成excel中的序列号，所以我想这就是我对日期的计算不起作用的原因。有人知道如何使我的日期格式正确吗？

 public ActionResult Index(DateTime? dteStartDate, DateTime? dteEndDate)
    {
        if (dteStartDate != null && dteEndDate != null)
           {
                //var @StartDate = new SqlParameter("@StartDate", dteEndDate);
               // var @EndDate = new SqlParameter("@EndDate", dteEndDate);

            string query = "SELECT E.EMPLID, E.FIRSTNAME, E.LASTNAME, E.WAGE, H.CLOCKINTIME, H.CLOCKOUTTIME, DATEDIFF(HOUR, H.CLOCKINTIME, H.CLOCKOUTTIME) AS HOURS_WORKED, E.WAGE * DATEDIFF(HOUR, H.CLOCKINTIME, H.CLOCKOUTTIME) AS DAILY_EARNINGS "
                + "FROM EMPLOYEE E "
                + "JOIN HOURLYWAGEMGMT H "
                + "ON H.EMPLID = E.EMPLID "
                + "WHERE E.POSITION = 'Driver' "
                + "AND H.CLOCKINDATE BETWEEN '" + dteStartDate + "' AND '" + dteEndDate + "' "
                + "AND H.CLOCKOUTDATE BETWEEN '" + dteStartDate + "' AND '" + dteEndDate + "' ";


            var data = db.Database.SqlQuery<Payroll>(query);
            var pck = new OfficeOpenXml.ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("Payroll");
            ws.Cells["A2"].LoadFromCollection(data, false);
            ws.Cells["A1"].Value = "EmployeeID";
            ws.Cells["B1"].Value = "FirstName";
            ws.Cells["C1"].Value = "LastName";
            ws.Cells["D1"].Value = "Wage";
            ws.Cells["E1"].Value = "ClockInTime";
            ws.Cells["F1"].Value = "ClockOutTime";
            ws.Cells["G1"].Value = "HoursWorked";
            ws.Cells["H1"].Value = "DailyEarnings";
            using (ExcelRange rng = ws.Cells["A1:H1"])
            {
                rng.Style.Font.Bold = true;
                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
                rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
            }
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=Payroll.xlsx");
            Response.BinaryWrite(pck.GetAsByteArray());

        Response.End();
        return View();
         }
        else
        {
          return View();
         }
    }
}

}