How to download .xslx File from server using EPPlus and MVC

Question

I have an Excel file generated on the server by using EPPlus the file is correctly made and using a window.location works fine on the local machine but does nothing when its deployed to a server. I'm trying to return a FileStreamResult back through the MVC controller but i don't think it's working. i'm using an ajax call to access the controller method but it fails to enter .done when the method is run through.

i have been looking at ASP.NET MVC EPPlus Download Excel File for my C# reference.

Script

 function exportToExcel() {
    var batchName = $("#batchDateSelect option:selected").text();
    var bID = $("#batchDateSelect").val();
    var params = {
        BatchID: bID,
        BatchName: batchName
    };
    $.post(path + "Export/ExportToExcel", params)
    .done(function (Data, textStatus, jqXHR) {
        var fileName = "";

        ////window.location = path + "ExportFiles/"+fileName;
    });

}

Controller

public ActionResult ExportToExcel(int BatchID,string BatchName)
{

    FileStreamResult FSR = DataAccess.ExportUtility.CreateExcelFile(BatchID, BatchName);

    return FSR;

}

EPPlus Method

public static FileStreamResult CreateExcelFile(int batchid,string batchName)
    {

        string fileName = batchName + " Reason_Code_Export.xlsx";
        var serverPath = HttpContext.Current.Server.MapPath("~/ExportFiles/");
        DirectoryInfo outputDir = new DirectoryInfo(serverPath);

        FileInfo newfile = new FileInfo(outputDir.FullName + fileName);
        if (newfile.Exists)
        {
            newfile.Delete();
            newfile = new FileInfo(outputDir.FullName + fileName);
        }
        Dictionary<string,int> MAData = PolicyDataAccess.GetMatchActionData(batchid);
        MemoryStream MS = new MemoryStream();
        using (ExcelPackage package = new ExcelPackage(newfile))
        {
            ..........
            ........

            package.SaveAs(MS);
        }
        MS.Position = 0;
        var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        FileStreamResult FSR = new FileStreamResult(MS, contentType);
        FSR.FileDownloadName = fileName;



        return FSR;
    }

What is the easiest way to Get that file?

Solution 1

I'm late to this question but may be it will be helpful for others.

After you setup the excel sheet then without saving or adding it to the MemoryStream, just make array of bytes as packge.GetAsByteArray() and from your action return it as a File instead of FileStreamResult.

var FileBytesArray = packge.GetAsByteArray();
return File(FileBytesArray, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename + ".xlsx");

Solution 2

    EPPLUS has given me some real headaches.
And I'm not so familiar with MVC, but i assume you want to do something along the lines of writing directly to the output response. In that case, I use something like what's below.
and I see i left in the comments that the memory stream write works as well. that's closer to what you are asking to do but i do not currently use it in my code. so buyer beware.

.cheers.

        Response.Clear();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        Response.AddHeader( "Content-Disposition", "attachment; filename=ProposalRequest-" + fileName + ".xslx" );
        Response.BinaryWrite( pck.GetAsByteArray() );
        // myMemoryStream.WriteTo(Response.OutputStream); //works too
        Response.Flush();
        Response.Close();