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?
Best Answer
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: