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

Question

我有一个使用EPPlus在服务器上生成的Excel文件，文件正确生成，在本地机器上使用window.location可以正常工作，但在部署到服务器时却无法工作。我试图通过MVC控制器返回一个FileStreamResult，但我认为它没有起作用。我正在使用ajax调用来访问控制器方法，但当该方法运行时，它无法进入.done。

我一直在参考ASP.NET MVC EPPlus Download Excel File进行C#编程。

 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;
    });

}

控制器

public ActionResult ExportToExcel(int BatchID,string BatchName)
{

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

    return FSR;

}

< p > 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;
    }

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

- JosephK

Answer 1

我对这个问题来得有点晚，但也许对其他人有帮助。

在设置了Excel表格之后，不要保存或将其添加到MemoryStream中，只需将其转换为字节数组，方法是使用packge.GetAsByteArray()，然后从您的操作返回它，作为File而不是FileStreamResult。

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

- Shahzad

Answer 2

EPPLUS 给我带来了一些真正的头痛。
虽然我对 MVC 不是很熟悉，但我认为你想要做的是直接写入输出响应。在这种情况下，我使用类似下面的方法。
我看到我留下了内存流写入也可以的注释。那更接近于你所要求的，但我目前没有在我的代码中使用它。所以买家自负。

.干杯。

        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();

- dougY

---

谢谢，这有助于解决使用epplus编辑模板时出现“损坏”文件的问题。 - blfuentes

Answer 3

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

我不会将其标记为已回答，因为我相信有更好的方法，如果有人想要发布或评论最佳方法，我会标记一个答案。

感谢DougY的回复。

控制器的两种方法可能可以合并，但这只是它的最终形式。

控制器

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);

    }

导出工具类

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;
    }

- JosephK