CODEWITHSUNDEEP
c#asp.net-mvcepplus
Pisit Koolplukpol
Pisit Koolplukpol

Reputation: 33

EPPlus not downloading excel file with templates

When I use Epplus to export the excel it not downloading the excel file here are my code

public ActionResult ExportDataTableToExcel(string searchBox)
{
    FileStream fs = null;
    var fileName = "xxxReport";

    fs = new FileStream(Server.MapPath(@"~/Content/Templates/xxxReport.xlsx"), FileMode.Open, FileAccess.Read);

    var result = new List<xxxReport_Result>();
    using (var db = new xxxEntities())
    {
        if (searchBox != "" && searchBox != null)
        {
            result = db.xxxReport()
                    .Where(r => r.Match.Contains(searchBox)
                    || r.MD.Contains(searchBox)
                    || r.SBU.Contains(searchBox)
                    || r.Project.Contains(searchBox)).ToList();
        }
        else
        {
            result = db.xxxReport().ToList();
        }
    }

    var results = Mapper.Map<List<ReportModel>>(result);

    Debug.WriteLine("End : {0}", DateTime.Now.ToString("HH:mm:ss"));

    using (var excel = new ExcelPackage(fs))
    {
        var workSheet = excel.Workbook.Worksheets.FirstOrDefault();

        int i = 11;
        results.ToList().ForEach(s =>
            {
                int col = 2;
                workSheet.Cells[i, col++].Value = s.NO;
                workSheet.Cells[i, col++].Value = s.Match;
                workSheet.Cells[i, col++].Value = s.MD;
                workSheet.Cells[i, col++].Value = s.SBU;
                workSheet.Cells[i, col++].Value = s.Project;
                workSheet.Cells[i, col++].Value = s.BlockUnit;
                i++;
            });

        Response.Clear();
        string saveAsFileName = string.Format("{0}_{1:d}.xlsx", fileName, DateTime.Now).Replace("/", "-");
        Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", saveAsFileName));
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.BinaryWrite(excel.GetAsByteArray());
        Response.End(); 
    }
    return JavaScript("Export Complete");
}

When I hit export button I get notthig, I try to debug and there are no error in code but when use console.log to check the results I get something I like this in my results.

���ʮ�����O�&�ת�����o����Tҵ��B*��� ]��(S���d�Ŗ��)��Oe���Ť񡘿p>^����4aπT|����4��_�

This is how I call function

function ExportToExcel() {
        $.post("@Url.Action("ExportDataTableToExcel", "xxx")",
        {
            searchBox1: $("#searchBox1").val(),
            searchBox2: $("#searchBox2").val(),
            searchBox3: $("#searchBox3").val(),
            searchBox4: $("#searchBox4").val()
        }).done(function (result) {
            console.log(result);
        });
    }

Upvotes: 2

Views: 1859

Answers (2)

Pisit Koolplukpol
Pisit Koolplukpol

Reputation: 33

I fix it by creating another function to download results file by using "GET" method

Upvotes: 0

jtate
jtate

Reputation: 2696

With MVC you should stay away from directly interacting with the Response object unless you absolutely need to. With that in mind, this should work:

public ActionResult ExportDataTableToExcel(string searchBox)
{
    var fileName = "xxxReport";
    byte[] excelContent;

    // *** code that pulls your data ***

    // always try to use a 'using' statement when you can for disposable objects
    using (var fs = new FileStream(Server.MapPath(@"~/Content/Templates/xxxReport.xlsx"), FileMode.Open, FileAccess.Read))
    using (var excel = new ExcelPackage(fs))
    {
        // *** manipulate your worksheet here ***

        excelContent = excel.GetAsByteArray();
    }

    string saveAsFileName = string.Format("{0}_{1:d}.xlsx", fileName, DateTime.Now).Replace("/", "-");
    return File(excelContent, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", saveAsFileName);
}

MVC controller functions return an ActionResult object that many other classes inherit from, including System.Web.Mvc.FileContentResult which is what the File() function returns (this method is built into System.Web.Mvc.Controller)

Upvotes: 3

Related Questions