Export to excel file in MVC3 ASP.net

Question

I have the following code in a Controller and would like to save it all to an excel file, but I can't get the browser to show me the file save dialog.

public ContentResult Export(...) {
StringBuilder sb = new StringBuilder();
            sb.Append("<table border='" + "2px" + "'b>");
            //write column headings
            sb.Append("<tr>");
            foreach (System.Data.DataColumn dc in dt.Columns) {
                sb.Append("<td><b><font face=Arial size=2>" + dc.ColumnName + "</font></b></td>");
            }
            sb.Append("</tr>");

            //write table data
            foreach (System.Data.DataRow dr in dt.Rows) {
                sb.Append("<tr>");
                foreach (System.Data.DataColumn dc in dt.Columns) {
                    sb.Append("<td><font face=Arial size=" + "14px" + ">" + dr[dc].ToString() + "</font></td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");

            this.Response.AddHeader("Content-Disposition", "Employees.xls");
            this.Response.ContentType = "application/vnd.ms-excel";
            return this.Content(sb.ToString());
}

Thanks a lot in advance!

Answer 1

Try this:

public ActionResult Export(...) {
        StringBuilder sb = new StringBuilder();
        sb.Append("<table border='" + "2px" + "'b>");
        //write column headings
        sb.Append("<tr>");
        foreach (System.Data.DataColumn dc in dt.Columns) {
            sb.Append("<td><b><font face=Arial size=2>" + dc.ColumnName + "</font></b></td>");
        }
        sb.Append("</tr>");

        //write table data
        foreach (System.Data.DataRow dr in dt.Rows) {
            sb.Append("<tr>");
            foreach (System.Data.DataColumn dc in dt.Columns) {
                sb.Append("<td><font face=Arial size=" + "14px" + ">" + dr[dc].ToString() + "</font></td>");
            }
            sb.Append("</tr>");
        }
        sb.Append("</table>");

        this.Response.AddHeader("Content-Disposition", "Employees.xls");
        this.Response.ContentType = "application/vnd.ms-excel";
        byte[] buffer = System.Text.Encoding.UTF8.GetBytes(sb.ToString());
            return File(buffer, "application/vnd.ms-excel");
}

Answer 2

I'm not sure if it has something to do that you are using ContentResult. But recently I used an own class inheriting from FileResult, which helps to generate Excel-Exports (sorry, comments are in german):

/// <summary>
/// Generiert eine Excel-Datei
/// </summary>
public sealed class ExcelFileResult : FileResult
{
    private DataTable dt;
    private TableStyle tableStyle;
    private TableItemStyle headerStyle;
    private TableItemStyle itemStyle;

    /// <summary>
    /// Z.Bsp. "Exportdatum: {0}" (Standard-Initialisierung) - wenn leerer String, wird Exportdatum
    /// nicht angegeben.
    /// </summary>
    public string TitleExportDate { get; set; }
    /// <summary>
    /// Titel des Exports, wird im Sheet oben links ausgegeben
    /// </summary>
    public string Title { get; set; }


    /// <summary>
    /// Konstruktor
    /// </summary>
    /// <param name="dt">Die zu exportierende DataTable</param>
    public ExcelFileResult(DataTable dt)
        : this(dt, null, null, null)
    { }

    /// <summary>
    /// Konstruktor
    /// </summary>
    /// <param name="dt">Die zu exportierende DataTable</param>
    /// <param name="tableStyle">Styling für gesamgte Tabelle</param>
    /// <param name="headerStyle">Styling für Kopfzeile</param>
    /// <param name="itemStyle">Styling für die einzelnen Zellen</param>
    public ExcelFileResult(DataTable dt, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle)
        : base("application/ms-excel")
    {
        this.dt = dt;
        TitleExportDate = "Exportdatum: {0}";
        this.tableStyle = tableStyle;
        this.headerStyle = headerStyle;
        this.itemStyle = itemStyle;

        // provide defaults
        if (this.tableStyle == null)
        {
            this.tableStyle = new TableStyle();
            this.tableStyle.BorderStyle = BorderStyle.Solid;
            this.tableStyle.BorderColor = Color.Black;
            this.tableStyle.BorderWidth = Unit.Parse("2px");
        }
        if (this.headerStyle == null)
        {
            this.headerStyle = new TableItemStyle();
            this.headerStyle.BackColor = Color.LightGray;
        }
    }


    protected override void  WriteFile(HttpResponseBase response)
    {
        // Create HtmlTextWriter
        StringWriter sw = new StringWriter();
        HtmlTextWriter tw = new HtmlTextWriter(sw);

        // Build HTML Table from Items
        if (tableStyle != null)
            tableStyle.AddAttributesToRender(tw);
        tw.RenderBeginTag(HtmlTextWriterTag.Table);

        // Create Title Row
        tw.RenderBeginTag(HtmlTextWriterTag.Tr);
        tw.AddAttribute(HtmlTextWriterAttribute.Colspan, (dt.Columns.Count - 2).ToString());
        tw.RenderBeginTag(HtmlTextWriterTag.Td);
        tw.Write(Title);
        tw.RenderEndTag();
        tw.AddAttribute(HtmlTextWriterAttribute.Colspan, "2");
        tw.RenderBeginTag(HtmlTextWriterTag.Td);
        if (TitleExportDate != string.Empty)
            tw.WriteLineNoTabs(string.Format(TitleExportDate, DateTime.Now.ToString("dd.MM.yyyy")));
        tw.RenderEndTag();

        // Create Header Row
        tw.RenderBeginTag(HtmlTextWriterTag.Tr);
        DataColumn col = null;
        for (Int32 i = 0; i <= dt.Columns.Count - 1; i++)
        {
            col = dt.Columns[i];
            if (headerStyle != null)
                headerStyle.AddAttributesToRender(tw);
            tw.RenderBeginTag(HtmlTextWriterTag.Th);
            tw.RenderBeginTag(HtmlTextWriterTag.Strong);
            tw.WriteLineNoTabs(col.ColumnName);
            tw.RenderEndTag();
            tw.RenderEndTag();
        }
        tw.RenderEndTag();

        // Create Data Rows
        foreach (DataRow row in dt.Rows)
        {
            tw.RenderBeginTag(HtmlTextWriterTag.Tr);
            for (Int32 i = 0; i <= dt.Columns.Count - 1; i++)
            {
                if (itemStyle != null)
                    itemStyle.AddAttributesToRender(tw);
                tw.RenderBeginTag(HtmlTextWriterTag.Td);
                tw.WriteLineNoTabs(HttpUtility.HtmlEncode(row[i]));
                tw.RenderEndTag();
            }
            tw.RenderEndTag(); //  /tr
        }

        tw.RenderEndTag(); //  /table

        // Write result to output-stream
        Stream outputStream = response.OutputStream;
        byte[] byteArray = Encoding.Default.GetBytes(sw.ToString());
        response.OutputStream.Write(byteArray, 0, byteArray.GetLength(0));
    }
}

Then in your Controller:

    /// <summary>
    /// Excel-Export
    /// </summary>
    public ExcelFileResult ExportExcel()
    {
        // DataTable dt = -- > get your data
        ExcelFileResult actionResult = new ExcelFileResult(dt) { FileDownloadName = "yourFileName.xls" };
        return actionResult;
    }