如何使用EPPlus和MVC从服务器下载.xslx文件

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.

我有一个Excel文件，通过使用EPPlus文件在服务器上生成并使用一个窗口。位置在本地机器上工作得很好，但是在部署到服务器时什么也不做。我试图通过MVC控制器返回FileStreamResult，但我认为它不工作。我正在使用ajax调用来访问控制器方法，但是当方法运行时，它无法输入.done。

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

我一直在看ASP。NET MVC EPPlus为我的c#引用下载Excel文件。

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

EPPlus方法

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?

获取该文件的最简单方法是什么?

3 个解决方案

#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.

在设置了excel表之后，无需保存或添加到MemoryStream中，只需将字节数组设置为packge.GetAsByteArray()，然后从操作中将其作为文件返回，而不是FileStreamResult。

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

#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.

EPPLUS真让我头疼。我对MVC不太熟悉，但我假设您希望按照直接写入输出响应的方式进行操作。在这种情况下，我使用如下内容。我在评论中看到，内存流也可以写。这与您所要求的更接近，但是我目前没有在代码中使用它。所以买家当心,.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();

#3

The answer that DougY has post probably works fine, however i did find a solution before he posted.

DougY已经发布的答案可能很有效，但是我在他发布之前找到了解决方案。

I won't mark this as answered because i'm sure there is a better way, if anyone wants to post or comment on what's the best way to do it ill mark an answer then.

我不会把这个标记为已回答，因为我确信有更好的方法，如果有人想发表或评论什么是最好的方法，我会标记一个答案。

thanks for the response DougY

谢谢你的回复，杜吉

The 2 methods of the controller could probably be combined, but this is just how it ended up.

控制器的两种方法可以组合，但这就是它的最终结果。

Controller

控制器

public static string ContentType { get; set; }
public static string FilePath { get; set; }
public static string FileName { get; set; }
public static byte[] Bytes { get; set; }

public void ExportToExcel(int BatchID,string BatchName)//is called first to set the variables
    {
        string contentType;
        byte[] bytes;
        string ret = DataAccess.ExportUtility.CreateExcelFile(BatchID, BatchName,out contentType, out bytes);
        ContentType = contentType;
        Bytes = bytes;

        FileName = ret[1];




    }
    public ActionResult DownloadExcelFile()//is then called to download the file
    {

        return File(Bytes, ContentType, FileName);

    }

ExportUtility class

ExportUtility类

public static string[] CreateExcelFile(int batchid,string batchName,out string ContentType, out byte[] Bytes)
    {

        string fileName = batchName + " Reason_Code_Export.xlsx";

        var serverPath = HttpContext.Current.Server.MapPath("~/ExportFiles/");
        DirectoryInfo outputDir = new DirectoryInfo(serverPath);
        byte[] bytes;
        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();
        ExcelPackage package;
        using (package = new ExcelPackage(newfile))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(batchName);

            worksheet.Cells["A1"].Value = batchName + " Reason_Code_Export";
            worksheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:B1"].Merge = true;
            worksheet.Cells["A1:B1"].Style.Font.Bold = true;

            worksheet.Cells["A2"].Value = "Reason Code";
            worksheet.Cells["B2"].Value = "Number of Reason Codes Selected";
            worksheet.Cells["A2:B2"].Style.Font.Bold = true;
            int row = 3;
            int col = 1;
            foreach (KeyValuePair<string,int> MA in MAData)
            {
                worksheet.Cells[row, col].Value = MA.Key;
                worksheet.Cells[row, col + 1].Value = MA.Value;
                row++;

            }
            worksheet.Column(1).Width = 34.29;
            worksheet.Column(2).Width = 34.29;

            package.Workbook.Properties.Title = batchName + " Reason_Code_Export";
            package.Workbook.Properties.Author = "Intranet Application: Unclaimed Properties";
            package.Workbook.Properties.Company = "Assurity Life 2013";
            Bytes = package.GetAsByteArray();
            //package.SaveAs(newfile);//MS);

        }
        MS.Position = 0;
        var rl = serverPath + fileName;
        var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        ContentType = contentType;
        FileStreamResult FSR = new FileStreamResult(MS, contentType);
        FSR.FileDownloadName = fileName;
        string[] ret = new string[2];
        ret[0] = serverPath;
        ret[1] = fileName;


        return ret;
    }