Export GridView to Excel with dynamic HyperLink using C# and VB.Net

Hi tonyb2264,

I have created sample. Please refer the below code. You need to simply add dummy gridview in the html.

HTML

<asp:GridView ID="gvDummy" runat="server" AutoGenerateColumns="False">
    <Columns>
    </Columns>
</asp:GridView>

Then create class with the name GridViewTemplate. Then write the below code.

VB.Net

Imports Microsoft.VisualBasic

Public Class GridViewTemplate
    Implements ITemplate
    'A variable to hold the type of ListItemType.
    Public _templateType As ListItemType

    'A variable to hold the column name.
    Public _columnName As String

    'Constructor where we define the template type and column name.
    Public Sub New(type As ListItemType, colname As String)
        'Stores the template type.
        _templateType = type
        'Stores the column name.
        _columnName = colname
    End Sub

    Public Sub ITemplate_InstantiateIn(container As System.Web.UI.Control) Implements ITemplate.InstantiateIn
        Select Case _templateType
            Case ListItemType.Header
                'Creates a new label control and add it to the container.
                Dim lbl As New Label()
                'Allocates the new label object.
                lbl.Text = _columnName
                'Assigns the name of the column in the lable.
                container.Controls.Add(lbl)
                'Adds the newly created label control to the container.
                Exit Select

            Case ListItemType.Item
                'Creates a new text box control and add it to the container.
                Dim lbl1 As New Label()
                'Allocates the new text box object.
                AddHandler lbl1.DataBinding, AddressOf DataBinding
                'lbl1.DataBinding += New EventHandler(AddressOf DataBinding)
                'Attaches the data binding event.
                container.Controls.Add(lbl1)
                'Adds the newly created textbox to the container.
                Exit Select

            Case ListItemType.EditItem
                'As, I am not using any EditItem, I didnot added any code here.
                Exit Select

            Case ListItemType.Footer
                Dim chkColumn As New CheckBox()
                chkColumn.ID = Convert.ToString("Chk") & _columnName
                container.Controls.Add(chkColumn)
                Exit Select
        End Select
    End Sub

    ''' <summary>
    ''' This is the event, which will be raised when the binding happens.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Public Sub DataBinding(sender As Object, e As EventArgs)
        Dim lbl As Label = DirectCast(sender, Label)
        Dim container As GridViewRow = DirectCast(lbl.NamingContainer, GridViewRow)
        Dim dataValue As Object = DataBinder.Eval(container.DataItem, _columnName)
        If dataValue IsNot DBNull.Value Then
            lbl.Text = dataValue.ToString()
        End If
    End Sub
End Class

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI;

/// <summary>
/// Summary description for GridViewTemplate
/// </summary>
public class GridViewTemplate : ITemplate
{
    //A variable to hold the type of ListItemType.
    ListItemType _templateType;

    //A variable to hold the column name.
    string _columnName;

    //Constructor where we define the template type and column name.
    public GridViewTemplate(ListItemType type, string colname)
    {
        //Stores the template type.
        _templateType = type;
        //Stores the column name.
        _columnName = colname;
    }

    void ITemplate.InstantiateIn(System.Web.UI.Control container)
    {
        switch (_templateType)
        {
            case ListItemType.Header:
                //Creates a new label control and add it to the container.
                Label lbl = new Label();            //Allocates the new label object.
                lbl.Text = _columnName;             //Assigns the name of the column in the lable.
                container.Controls.Add(lbl);        //Adds the newly created label control to the container.
                break;

            case ListItemType.Item:
                //Creates a new text box control and add it to the container.
                Label lbl1 = new Label();                                //Allocates the new text box object.
                lbl1.DataBinding += new EventHandler(DataBinding);   //Attaches the data binding event.
                container.Controls.Add(lbl1);                            //Adds the newly created textbox to the container.
                break;

            case ListItemType.EditItem:
                //As, I am not using any EditItem, I didnot added any code here.
                break;

            case ListItemType.Footer:
                CheckBox chkColumn = new CheckBox();
                chkColumn.ID = "Chk" + _columnName;
                container.Controls.Add(chkColumn);
                break;
        }
    }

    /// <summary>
    /// This is the event, which will be raised when the binding happens.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void DataBinding(object sender, EventArgs e)
    {
        Label lbl = (Label)sender;
        GridViewRow container = (GridViewRow)lbl.NamingContainer;
        object dataValue = DataBinder.Eval(container.DataItem, _columnName);
        if (dataValue != DBNull.Value)
        {
            lbl.Text = dataValue.ToString();
        }
    }
}

Then write the below code in the page.

VB.Net

Imports System.IO
Imports System.Data
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class VB
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        loadDynamicGridWithTemplateColumn(PopulateData())
        ExportToExcel()
    End Sub

    Private Function PopulateData() As DataTable
        Dim dt As New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("Column 1"), New DataColumn("Column 2"), New DataColumn("Column 3")})
        dt.Rows.Add("Cascading DropDownList", 638896, "<a href='http://www.aspforums.net/Threads/638896/638896' target='_blank'>638896</a>")
        dt.Rows.Add("GridView Row details", "<a href='http://www.aspforums.net/Threads/149842/149842' target='_blank'>149842</a>", 149842)
        dt.Rows.Add("Total and Grand Total", 922260, "<a href='http://www.aspforums.net/Threads/922260/922260' target='_blank'>922260</a>")
        dt.Rows.Add("Prevent sales", "<a href='http://www.aspforums.net/Threads/109916/109916' target='_blank'>109916</a>", 109916)
        dt.Rows.Add("Read spread sheet", 111322, "<a href='http://www.aspforums.net/Threads/111322/111322' target='_blank'>111322</a>")
        ' Get dt from database using your procedure
        Return dt
    End Function

    Private Sub loadDynamicGridWithTemplateColumn(dt As DataTable)
        For Each col As DataColumn In dt.Columns
            Dim bfield As New TemplateField()
            bfield.HeaderTemplate = New GridViewTemplate(ListItemType.Header, col.ColumnName)
            'Initialize the HeaderText field value.
            bfield.ItemTemplate = New GridViewTemplate(ListItemType.Item, col.ColumnName)
            'Add the newly created bound field to the GridView.
            gvDummy.Columns.Add(bfield)
        Next
        gvDummy.DataSource = dt
        gvDummy.DataBind()
    End Sub

    Protected Sub ExportToExcel()
        Using sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)
            gvDummy.AllowPaging = False
            'gvDummy.DataBind();
            gvDummy.HeaderRow.BackColor = Color.White
            For Each cell As TableCell In gvDummy.HeaderRow.Cells
                cell.BackColor = gvDummy.HeaderStyle.BackColor
            Next
            For Each row As GridViewRow In gvDummy.Rows
                row.BackColor = Color.White
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = gvDummy.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = gvDummy.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                Next
            Next
            gvDummy.RenderControl(hw)
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
            Response.Charset = ""
            Response.ContentType = "application/vnd.ms-excel"
            Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        End Using
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered 

    End Sub

End Class

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;

public partial class CS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        loadDynamicGridWithTemplateColumn(PopulateData());
        ExportToExcel();
    }

    private DataTable PopulateData()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("Column 1"), new DataColumn("Column 2"), new DataColumn("Column 3") });
        dt.Rows.Add("Cascading DropDownList", 638896, "<a href='http://www.aspforums.net/Threads/638896/638896' target='_blank'>638896</a>");
        dt.Rows.Add("GridView Row details", "<a href='http://www.aspforums.net/Threads/149842/149842' target='_blank'>149842</a>", 149842);
        dt.Rows.Add("Total and Grand Total", 922260, "<a href='http://www.aspforums.net/Threads/922260/922260' target='_blank'>922260</a>");
        dt.Rows.Add("Prevent sales", "<a href='http://www.aspforums.net/Threads/109916/109916' target='_blank'>109916</a>", 109916);
        dt.Rows.Add("Read spread sheet", 111322, "<a href='http://www.aspforums.net/Threads/111322/111322' target='_blank'>111322</a>");
        // Get dt from database using your procedure
        return dt;
    }

    private void loadDynamicGridWithTemplateColumn(DataTable dt)
    {
        foreach (DataColumn col in dt.Columns)
        {
            TemplateField bfield = new TemplateField();
            bfield.HeaderTemplate = new GridViewTemplate(ListItemType.Header, col.ColumnName);
            //Initialize the HeaderText field value.
            bfield.ItemTemplate = new GridViewTemplate(ListItemType.Item, col.ColumnName);
            //Add the newly created bound field to the GridView.
            gvDummy.Columns.Add(bfield);
        }
        gvDummy.DataSource = dt;
        gvDummy.DataBind();
    }

    protected void ExportToExcel()
    {
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            gvDummy.AllowPaging = false;
            //gvDummy.DataBind();
            gvDummy.HeaderRow.BackColor = Color.White;
            foreach (TableCell cell in gvDummy.HeaderRow.Cells)
            {
                cell.BackColor = gvDummy.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in gvDummy.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = gvDummy.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = gvDummy.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                }
            }
            gvDummy.RenderControl(hw);
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
}

Screenshot