Generating an Excel File, then Downloading it From

2019-09-16 20:08发布

问题:

So I have this page on my application where the user can download a "hard copy" of a rendered view, which represents Skills and their Requirements on Roles within a Project, showing the fulfilment of said Skills by any people on the Role.

I have the functionality working with a csv file, as I can just use a StringBuilder for creating a comma-delimited file.

However, before approaching the Excel approach, where I want some light formatting, I realise I cannot acheive this the same way.

I have used Interop for generating Excel files before, but how would I be able to create one that can be downloaded after it's generation?

Here is the working code to generate and return a CSV file:

public ActionResult DownloadSQEPToCSV(int projectID)
{
    //source my data

    StringBuilder sBuilder = new StringBuilder();

    sBuilder.Append("SQEPMatrix, For Project," + data.First().Project.ContractNumber);

    foreach (var role in data)
    {
        sBuilder.Append("\r\nRole:," + role.First().Title.Name);
        sBuilder.Append("\r\nSkill,Requirement");
        foreach (var person in role.Distinct(uCom))
        {
            sBuilder.Append("," + person.User.UserDetail.Name);
        }
        foreach (var skill in role.Distinct(uCom))
        {
            //More stuff to generate what I want
        }
        sBuilder.Append("\r\n");
    }

    //Attach file to the header 
    Response.Clear();
    Response.AddHeader("Content-Disposition", "attachment;filename=SQEPMatrix for " + data.First().Project.ContractNumber + ".csv");
    Response.ContentType = "text/csv";
    Response.Write(sBuilder);
    Response.End();

    return SetTitleAndID("SQEP","dl_sqep_csv");
}

This code is invoked by the following script:

function download(id) {
    window.location.href = '../../Project/DownloadSQEPExcel?projectID=' + id;
}

So my question, is how can I generate an Excel spreadsheet, and return the generated file in a manner similar to how I return my .csv file?

回答1:

If you use something like DocumentFormat.OpenXml you can create the Excel file in a memory stream and then return the stream using a FileStreamResult:

var theStreamContainingSpreadsheet = CreateSpreadsheet();
theStreamContainingSpreadsheet.Position = 0;
return new FileStreamResult(theStreamContainingSpreadsheet, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                   {FileDownloadName = "Export.xlsx"};