Output a formula

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
474,037
Messages
2,570,371
Members
47,013
Latest member
JewellChes

Latest Threads

Top