D
DavidC
I have an aspx web page that outputs the contents of a GridView to Excel and
it works great. However, instead of the cell contents I would like to export
a formula in the footer Row like "=SUM(M2:M357)" instead of a calculated
total that I am now exporting. Below are the 2 subroutines to export. Can
someone show me where and how to export the formula in the Footer row?
Thanks.
Private Shared Sub ExportFundingFile(ByVal fileName As String, ByVal gv
As GridView)
gv.AllowPaging = False
gv.DataBind()
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition",
String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
' Create a form to contain the grid
Dim table As Table = New Table
table.GridLines = gv.GridLines
' add the header row to the table
If (Not (gv.HeaderRow) Is Nothing) Then
PrepareControlForExport(gv.HeaderRow, False)
table.Rows.Add(gv.HeaderRow)
End If
' add each of the data rows to the table
For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row, False)
table.Rows.Add(row)
Next
' add the footer row to the table
If (Not (gv.FooterRow) Is Nothing) Then
PrepareControlForExport(gv.FooterRow, True)
table.Rows.Add(gv.FooterRow)
End If
' render the table into the htmlwriter
table.RenderControl(htw)
' render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.End()
End Sub
' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Control,
ByVal bolFooter As Boolean)
Dim i As Integer = 0
Do While (i < control.Controls.Count)
Dim current As Control = control.Controls(i)
If (TypeOf current Is LinkButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
LinkButton).Text))
ElseIf (TypeOf current Is ImageButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
ImageButton).AlternateText))
ElseIf (TypeOf current Is HyperLink) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
HyperLink).Text))
ElseIf (TypeOf current Is DropDownList) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
DropDownList).SelectedItem.Text))
ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
CheckBox).Checked))
'TODO: Warning!!!, inline IF is not supported ?
End If
If current.HasControls And current.Visible Then
PrepareControlForExport(current, bolFooter)
End If
i = (i + 1)
Loop
End Sub
it works great. However, instead of the cell contents I would like to export
a formula in the footer Row like "=SUM(M2:M357)" instead of a calculated
total that I am now exporting. Below are the 2 subroutines to export. Can
someone show me where and how to export the formula in the Footer row?
Thanks.
Private Shared Sub ExportFundingFile(ByVal fileName As String, ByVal gv
As GridView)
gv.AllowPaging = False
gv.DataBind()
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition",
String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
' Create a form to contain the grid
Dim table As Table = New Table
table.GridLines = gv.GridLines
' add the header row to the table
If (Not (gv.HeaderRow) Is Nothing) Then
PrepareControlForExport(gv.HeaderRow, False)
table.Rows.Add(gv.HeaderRow)
End If
' add each of the data rows to the table
For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row, False)
table.Rows.Add(row)
Next
' add the footer row to the table
If (Not (gv.FooterRow) Is Nothing) Then
PrepareControlForExport(gv.FooterRow, True)
table.Rows.Add(gv.FooterRow)
End If
' render the table into the htmlwriter
table.RenderControl(htw)
' render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.End()
End Sub
' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Control,
ByVal bolFooter As Boolean)
Dim i As Integer = 0
Do While (i < control.Controls.Count)
Dim current As Control = control.Controls(i)
If (TypeOf current Is LinkButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
LinkButton).Text))
ElseIf (TypeOf current Is ImageButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
ImageButton).AlternateText))
ElseIf (TypeOf current Is HyperLink) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
HyperLink).Text))
ElseIf (TypeOf current Is DropDownList) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
DropDownList).SelectedItem.Text))
ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current,
CheckBox).Checked))
'TODO: Warning!!!, inline IF is not supported ?
End If
If current.HasControls And current.Visible Then
PrepareControlForExport(current, bolFooter)
End If
i = (i + 1)
Loop
End Sub