Export to Excel in c#

Question

I have a page with gridview. I need to download that gridview data in the excel format. I have used the following code to download into the excel format.

public static void Export(string fileName, GridView gv)
{
    string style = @"<style> .text { mso-number-format:\@;text-align:right; } </style> ";
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
    HttpContext.Current.Response.ContentType = "application/ms-excel";

    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {
            HttpContext.Current.Response.Write(style);

            //  Create a table to contain the grid      
            System.Web.UI.WebControls.Table table = new System.Web.UI.WebControls.Table();

            //  include the gridline settings  
            table.GridLines = gv.GridLines;

            //  add the header row to the table 
            if (gv.HeaderRow != null)
            {
                PrepareControlForExport(gv.HeaderRow);
                table.Rows.Add(gv.HeaderRow);
            }

            //  add each of the data rows to the table   
            foreach (GridViewRow row in gv.Rows)
            {
                // add numeric style for each cell   
                foreach (TableCell cell in row.Cells)
                {
                    cell.Attributes.Add("class", "text");
                }
                PrepareControlForExport(row);
                table.Rows.Add(row);
            }

            //  add the footer row to the table  
            if (gv.FooterRow != null)
            {
                PrepareControlForExport(gv.FooterRow);
                table.Rows.Add(gv.FooterRow);
            }

            //  render the table into the htmlwriter  
            table.RenderControl(htw);

            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
}

/// <summary>
/// Replace any of the contained controls with literals        
/// </summary>       
/// <param name="control"></param> 
private static void PrepareControlForExport(Control control)
{
    for (int i = 0; i < control.Controls.Count; i++)
    {
        Control current = control.Controls[i];

        if (current is LinkButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
        }
        else if (current is ImageButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
        }
        else if (current is HyperLink)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
        }
        else if (current is DropDownList)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
        }
        else if (current is CheckBox)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
        }
        if (current.HasControls())
        {
            PrepareControlForExport(current);
        }
    }
}

As soon as download the file it is download the css files of the master page also. Can you please let me know, how to resolve this.

Answer 1

        Response.ClearContent();

        Response.Buffer = true;

        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "excelname.xls"));

        Response.ContentType = "application/ms-excel";

        //which row you dont want in gridview
        GridView5.Columns[0].Visible = false;

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);

        GridView5.AllowPaging = false;

        GridView5.RenderControl(htw);

        Response.Write(sw.ToString());

        Response.End();

Answer 2

try my working method:

void ExportToExcel(GridView grdData, string filename)
{
    grdData.BorderStyle = BorderStyle.Solid;
    grdData.BorderWidth = 1;
    grdData.BackColor = Color.WhiteSmoke;
    grdData.GridLines = GridLines.Both;
    grdData.Font.Name = "Verdana";
    grdData.Font.Size = FontUnit.XXSmall;
    grdData.HeaderStyle.BackColor = Color.DimGray;
    grdData.HeaderStyle.ForeColor = Color.White;
    grdData.RowStyle.HorizontalAlign = HorizontalAlign.Left;
    grdData.RowStyle.VerticalAlign = VerticalAlign.Top;

    HttpResponse response = HttpContext.Current.Response;
    response.Clear();
    response.Charset = "";
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename+ "\"");

    using (var sw = new StringWriter())
    {
        using (var htw = new HtmlTextWriter(sw))
        {
            grdData.RenderControl(htw);
            response.Write(sw.ToString());
            response.End();
        }
    }
} 

Answer 3

The easiest way to create true Excel files from C# is to use the ClosedXML library: ClosedXML. ClosedXML is also available as a NuGet package to make it easy to get started.