Export Gridview Data to Excel - Change the header name

Hi is there a chance that I change the header name of the exported data from gridview. Below is my code and its working fine. my only problem is I can't change the header name

My code

Dim strFilename As String = Now.Date.Month.ToString + Now.Date.Day.ToString + Now.Hour.ToString + Now.Minute.ToString + Now.Second.ToString

Response.AddHeader("content-disposition", "attachment;filename=" & strFilename & ".xls")
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"

Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter()
Dim htmlWrite As System.We开发者_开发技巧b.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(stringWrite)
Dim dg As System.Web.UI.WebControls.DataGrid = New System.Web.UI.WebControls.DataGrid()

dg.DataSource = dt
dg.DataBind()
dg.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.End()

The result is

 ID  Fname
 1   aaa
 2   bbb
 2   ccc

I want like this

Employee ID  First Name
1            aaa
2            bbb 
3            ccc

Below is a class I have used to export a GridView that I ripped off from here. It can handle template columns and image controls (displays the alternate text instead).

You may want to review how PrepareControlForExport() handles some controls. You also may need add in handling of more controllers into PrepareControlForExport(), as it is by no means complete.

public class ExportGridView
{
    public static void Export(String fileName, GridView gridView)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        //  Create a table to contain the grid
        Table table = new Table();

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

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

        //  add each of the data rows to the table
        foreach (GridViewRow row in gridView.Rows)
        {
            PrepareControlForExport(row);
            table.Rows.Add(row);
        }

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

        using (StringWriter stringWriter = new StringWriter())
        {
            using (HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter))
            {
                //  render the table into the htmlwriter
                table.RenderControl(htmlWriter);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(stringWriter.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control c = control.Controls[i];

            if (c is LinkButton)
            {
                control.Controls.Remove(c);
                control.Controls.AddAt(i, new LiteralControl((c as LinkButton).Text));
            }
            else if (c is ImageButton)
            {
                control.Controls.Remove(c);
                control.Controls.AddAt(i, new LiteralControl((c as ImageButton).AlternateText));
            }
            else if (c is HyperLink)
            {
                control.Controls.Remove(c);
                control.Controls.AddAt(i, new LiteralControl((c as HyperLink).Text));
            }
            else if (c is Image)
            {
                control.Controls.Remove(c);
                control.Controls.AddAt(i, new LiteralControl((c as Image).AlternateText));
            }
            else if (c is DropDownList)
            {
                control.Controls.Remove(c);
                control.Controls.AddAt(i, new LiteralControl((c as DropDownList).SelectedItem.Text));
            }
            else if (c is CheckBox)
            {
                control.Controls.Remove(c);
                if ((c as CheckBox).Checked)
                {
                    control.Controls.AddAt(i, new LiteralControl("True"));
                }
                else
                {
                    control.Controls.AddAt(i, new LiteralControl("False"));
                }
            }
            else if (c is HiddenField)
            {
                control.Controls.Remove(c);
            }

            if (c.HasControls())
            {
                PrepareControlForExport(c);
            }
        }
    }
}

Here is my original DataGrid answer just for completeness sake...

You haven't defined any columns on your DataGrid, so it has generated them automatically, resulting the following HTML (with excel headers):

<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
    <tr>
        <td>ID</td><td>FName</td>
    </tr>
    <tr>
        <td>1</td><td>aaa</td>
    </tr>
    <tr>
        <td>2</td><td>bbb</td>
    </tr>
    <tr>
        <td>3</td><td>ccc</td>
    </tr>
</table>

If you add some columns to the DataGrid you can achieve your desired output (don't forget to include dg.AutoGenerateColumns = false):

Response.AddHeader("content-disposition", "attachment;filename=tets.xls");
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();

BoundColumn column;

column = new BoundColumn();
column.DataField = "ID";
column.HeaderText = "Employee ID";
dg.Columns.Add(column);

column = new BoundColumn();
column.DataField = "FName";
column.HeaderText = "First Name";
dg.Columns.Add(column);

dg.AutoGenerateColumns = false;
dg.DataSource = Data.BuildData();
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

You haven't defined any columns on your GridView, so it has generated them automatically, resulting the following HTML (with excel headers):

<div>
    <table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
        <tr>
            <th scope="col">Employee ID</th><th scope="col">First Name</th>
        </tr>
        <tr>
            <td>1</td>
            <td>aaa</td>
        </tr>
        <tr>
            <td>2</td>
            <td>bbb</td>
        </tr>
        <tr>
            <td>3</td>
            <td>ccc</td>
        </tr>
    </table>
</div>

If you add some columns to the GridView you can achieve your desired output (don't forget to include gv.AutoGenerateColumns = false):

    Response.AddHeader("content-disposition", "attachment;filename=tets.xls");
    Response.Clear();
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
    System.Web.UI.WebControls.GridView gv = new System.Web.UI.WebControls.GridView();

    BoundField column;

    column = new BoundField();
    column.DataField = "ID";
    column.HeaderText = "Employee ID";
    gv.Columns.Add(column);

    column = new BoundField();
    column.DataField = "FName";
    column.HeaderText = "First Name";
    gv.Columns.Add(column);

    gv.AutoGenerateColumns = false;
    gv.DataSource = Data.BuildData();
    gv.DataBind();
    gv.RenderControl(htmlWrite);
    Response.Write(stringWrite.ToString());
    Response.End();