Export multiple Excel sheets from Razor view

Question

The following code exports some data, downloading it in an XLS file.

What I want is to give all the three list and export each list of data in a separate sheet. How can I do that?

My code looks like this:

public bool ExportQuestionSet(int QuestionSetNo)
{
    ExportResponse response = new ExportResponse();
    QuestionSetTbl questionSetTbl = _questionSetDAO.GetQuestionSetByQuestionSetNo(QuestionSetNo);
    QuestionSetContract questionSetContract = GetQuestionSetByQuestionSetNo(QuestionSetNo);
    GridView gv = new GridView();
    gv.DataSource = questionSetContract.QuestionsInfoList;
    gv.DataBind();
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=QuestionSet.xls");
    Response.ContentType = "application/ms-excel";
    Response.Charset = "";
    StringWriter sw = new StringWriter();
    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
    gv.RenderControl(htw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
    return response.TaskComplete;
}

score 1 · Answer 1 · answered Jan 27 '15 at 11:39

You can't output HTML and call it Excel. This works well for a reasonably formatted table, but it isn't an Excel file and will never be recognized as such. The file can be read, but it isn't possible to export multiple sheets.

Use a library to create a proper Excel file.

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

I would suggest using OpenXml, as it's quite fast and will create the correct Excel file. Using the sample in this thread: https://stackoverflow.com/a/11812551/1361993 you can just create a dataset with multiple datatables, which will in turn be outputted to the respective sheets in the file.

edited May 23 '17 at 11:57

Community

1
1

answered Jan 27 '15 at 11:57

Andrej Kikelj

800
7
11

Export multiple Excel sheets from Razor view

2 Answers2