C
COHENMARVIN
I have some code that exports gridviews from asp.net pages to
spreadsheets. This is useful for my customers who want to manipulate
data in the spreadsheet, or just present it as a report. The code
works fine, except when the gridview has a blank cell. In that case,
the corresponding spreadsheet cell contains a capital 'A' with a tilde
on top. I posted this question earlier, and one person said that
maybe there was a special character in those blank cells in the
gridview. I looked at the source, and found in some of those
cells. So I thought that all I had to do was remove these with a
string.replace, but I don't find (see code below) an opportunity to do
that. Since I could not do that, I decided to find the unicode code
for 'A' with a tilde and replace that in the result by blanks. I
thought I found that code, (I think it was 194 decimal), but when I do
a search in my converted gridview, the search finds no instances of
this code.
If anyone has time to try the code below out, and comes up with a
solution, I will donate three million dollars to their favorite
charity (just kidding). Seriously, any help is appreciated.
-- Marv
Protected Sub ExportToExcel()
Dim strWriteControls As IO.StringWriter
Dim htmlWrite As HtmlTextWriter
Dim frm As HtmlForm = New HtmlForm()
Dim GVPrivate As New GridView
Dim strSQL As String
Dim strReportName As String
Dim strIntro As String
Dim DT As New Data.DataTable
Dim HaveData As Boolean = False
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename= '" & strReportName & "'.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-xls"
Controls.Add(frm)
Try
strSQL = "Exec GetCaseloadReport '" &
Session("AgencyName") & "','" & _
Session("WorkerName") & "'"
DT = ClassUtilities.GetData(strSQL)
If DT.Rows.Count <> 0 Then
GVPrivate.DataSource = DT
GVPrivate.DataBind()
Me.Controls.Add(GVPrivate) ' Ask Sean: WHY DO THIS,
FRM ADDS LATER!!!!
frm.Controls.Add(GVPrivate)
HaveData = True
End If
If HaveData Then
strWriteControls = New IO.StringWriter
htmlWrite = New HtmlTextWriter(strWriteControls)
frm.RenderControl(htmlWrite)
Response.Write(Server.HtmlDecode(strWriteControls.ToString()))
Else
Response.Write("<table><tr><td>No clients found.</td></
tr></table>")
End If
Response.End()
Catch ex As Exception
Me.lbldisplay.Text = ex.Message
End Try
End Sub
spreadsheets. This is useful for my customers who want to manipulate
data in the spreadsheet, or just present it as a report. The code
works fine, except when the gridview has a blank cell. In that case,
the corresponding spreadsheet cell contains a capital 'A' with a tilde
on top. I posted this question earlier, and one person said that
maybe there was a special character in those blank cells in the
gridview. I looked at the source, and found in some of those
cells. So I thought that all I had to do was remove these with a
string.replace, but I don't find (see code below) an opportunity to do
that. Since I could not do that, I decided to find the unicode code
for 'A' with a tilde and replace that in the result by blanks. I
thought I found that code, (I think it was 194 decimal), but when I do
a search in my converted gridview, the search finds no instances of
this code.
If anyone has time to try the code below out, and comes up with a
solution, I will donate three million dollars to their favorite
charity (just kidding). Seriously, any help is appreciated.
-- Marv
Protected Sub ExportToExcel()
Dim strWriteControls As IO.StringWriter
Dim htmlWrite As HtmlTextWriter
Dim frm As HtmlForm = New HtmlForm()
Dim GVPrivate As New GridView
Dim strSQL As String
Dim strReportName As String
Dim strIntro As String
Dim DT As New Data.DataTable
Dim HaveData As Boolean = False
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename= '" & strReportName & "'.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-xls"
Controls.Add(frm)
Try
strSQL = "Exec GetCaseloadReport '" &
Session("AgencyName") & "','" & _
Session("WorkerName") & "'"
DT = ClassUtilities.GetData(strSQL)
If DT.Rows.Count <> 0 Then
GVPrivate.DataSource = DT
GVPrivate.DataBind()
Me.Controls.Add(GVPrivate) ' Ask Sean: WHY DO THIS,
FRM ADDS LATER!!!!
frm.Controls.Add(GVPrivate)
HaveData = True
End If
If HaveData Then
strWriteControls = New IO.StringWriter
htmlWrite = New HtmlTextWriter(strWriteControls)
frm.RenderControl(htmlWrite)
Response.Write(Server.HtmlDecode(strWriteControls.ToString()))
Else
Response.Write("<table><tr><td>No clients found.</td></
tr></table>")
End If
Response.End()
Catch ex As Exception
Me.lbldisplay.Text = ex.Message
End Try
End Sub