Export <templatefield to Excel

[jamie_pattison]

I am using a HttpContext to write all gridview data to Excel.

The problem i have is with any templatefields that are bound their data is not exported i.e.

<asp:TemplateField HeaderText="Value"> 
<ItemTemplate> 
<asp:LinkButton ID="Completevalue" runat="server" Text='<%# Eval("SalesValue") %>' OnClick="GetSalesTotal" CommandArgument='<%# Eval("ID") %>'>
</asp:LinkButton> 
</ItemTemplate> 
</asp:TemplateField>

They dont export across. Im using a For each loop for the column names and rows to iterate through the rows which are successfully exported, i then wrote some code to convert any LinkButton to a literal control i.e.

If TypeOf Currentctrl Is LinkButton Then                 
control.Controls.Remove(current)                 
control.Controls.AddAt(i, New LiteralControl(DirectCast(currentCtrl, LinkButton).Text))

but the same issue remains which is all template fields with a value is not exported across but other fields are.

Could anyone advise how i could get the value?

Thanks

 

[nayanpatel]

GridView with BoundField or TemplateField with Label can be exported easily and also displays properly. Problem occurs when the GridView with TemplateField Column contains controls like HyperLink, TextBox, Button, LinkButton, RadioButton or CheckBox controls. When such case occurs we need to convert remove these controls and replace them with Label or Literal controls.

Ex: In the GridView I have made use of TemplateField Columns with different controls like HyperLink, TextBox, Button, LinkButton, RadioButton and CheckBox for illustration purposes.

<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"

RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:HyperLink ID="lnkCity" runat="server" NavigateUrl="#" Text='<%# Eval("City") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Text = "CheckBox Control" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:RadioButton ID="RadioButton1" runat="server" Text = "RadioButton Control" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />

You will need to import the following namespaces:

Imports System.IO
Imports System.Data
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections.Generic

Below is the code to bind the GridView with records from the Customers table of the Northwind database:

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindGrid()
End If
End Sub

Private Sub BindGrid()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(strConnString)
Using cmd As New SqlCommand("SELECT * FROM Customers")
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
End Using
End Using
End Using
End Using
End Sub

The OnPageIndexChanging event handles the Pagination in the GridView:

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGrid()
End Sub

Below is the code to Export GridView to Excel file with Paging enabled. Firstly the GridView is again populated with data from database after setting AllowPaging to false.
Then a loop is executed on all rows of the GridView and the colors of the Row and the Alternating Row are applied to their individual cells. If this is not done then the color will spread on all cells of the Excel sheet for each row.Class textmode is applied to all cells so that they are rendered as text as per mso number format, doing this prevents large numbers from getting converted to exponential values.

Protected Sub ExportToExcel(sender As Object, e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Using sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)

'To Export all pages
GridView1.AllowPaging = False
Me.BindGrid()

GridView1.HeaderRow.BackColor = Color.White
For Each cell As TableCell In GridView1.HeaderRow.Cells
cell.BackColor = GridView1.HeaderStyle.BackColor
Next
For Each row As GridViewRow In GridView1.Rows
row.BackColor = Color.White
For Each cell As TableCell In row.Cells
If row.RowIndex Mod 2 = 0 Then
cell.BackColor = GridView1.AlternatingRowStyle.BackColor
Else
cell.BackColor = GridView1.RowStyle.BackColor
End If
cell.CssClass = "textmode"
Dim controls As New List(Of Control)()

'Add controls to be removed to Generic List
For Each control As Control In cell.Controls
controls.Add(control)
Next

'Loop through the controls to be removed and replace then with Literal
For Each control As Control In controls
Select Case control.GetType().Name
Case "HyperLink"
cell.Controls.Add(New Literal() With { _
.Text = TryCast(control, HyperLink).Text _
})
Exit Select
Case "TextBox"
cell.Controls.Add(New Literal() With { _
.Text = TryCast(control, TextBox).Text _
})
Exit Select
Case "LinkButton"
cell.Controls.Add(New Literal() With { _
.Text = TryCast(control, LinkButton).Text _
})
Exit Select
Case "CheckBox"
cell.Controls.Add(New Literal() With { _
.Text = TryCast(control, CheckBox).Text _
})
Exit Select
Case "RadioButton"
cell.Controls.Add(New Literal() With { _
.Text = TryCast(control, RadioButton).Text _
})
Exit Select
End Select
cell.Controls.Remove(control)
Next
Next
Next

GridView1.RenderControl(hw)

'style to format numbers to string
Dim style As String = "<style> .textmode { } </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