got problem with exportGridview Data into Excel format

[Dhananjay]

Hi all ,

I have got problem when i am tring to exportGridview Data into Excel
format.
It is going into text format ,but what i want is if the field is
number/currency then it should go into number/currency format
itself .Data exported to excel are all exported as text.
Export to excel should maintain the formatting like numbers and money
should be numbers and money in excel .

Here is the code provided, please someone correct my code.currently i
am getting correct ouput but thing is i want exported data into
particular format.

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e
As EventArgs)
Dim fromDate As String
Dim toDate As String
fromDate = txtFromEffectiveDate.Text.ToString()
toDate = txtToEffectiveDate.Text.ToString()

If PanelGVEffectiveDate.Visible = True Then
ExportToExcelFromTo(SqlDataSource1, "", fromDate, toDate)
ElseIf PanelGVDateAdded.Visible = True Then
ExportToExcelFromTo(SqlDataSource3, "", fromDate, toDate)
Else
ExportToExcel(SqlDataSource5, "")
End If

End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As
Control)

End Sub


Public Sub ExportToExcel(ByVal dataSrc As SqlDataSource, ByVal
fileName As String)
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename=FileName.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.xls"

'GET Data From Database
Dim cn As New SqlConnection(dataSrc.ConnectionString)
Dim query As String =
dataSrc.SelectCommand.Replace(ControlChars.Cr + ControlChars.Lf, "
").Replace(ControlChars.Tab, " ")

Dim cmd As New SqlCommand(query, cn)

cmd.CommandTimeout = 999999
cmd.CommandType = CommandType.Text

Try
cn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim sb As New StringBuilder()
'
'Add Header
'

Dim count As Integer
For count = 0 To dr.FieldCount - 1
If Not (dr.GetName(count) Is Nothing) Then
sb.Append(dr.GetName(count))
End If
If count < dr.FieldCount - 1 Then
sb.Append(ControlChars.Tab)
End If
Next count
Response.Write((sb.ToString() + ControlChars.Lf))
Response.Flush()
'
'Append Data
'
While dr.Read()
sb = New StringBuilder()

Dim col As Integer
For col = 0 To (dr.FieldCount - 1) - 1
If Not dr.IsDBNull(col) Then

sb.Append(dr.GetValue(col).ToString().Replace(",", " "))
End If
sb.Append(ControlChars.Tab)
Next col
If Not dr.IsDBNull((dr.FieldCount - 1)) Then
sb.Append(dr.GetValue((dr.FieldCount -
1)).ToString().Replace(",", " "))
End If
Response.Write((sb.ToString() + ControlChars.Lf))
Response.Flush()
End While
dr.Dispose()




Catch ex As Exception
Response.Write(ex.Message)
Finally
cmd.Connection.Close()
cn.Close()
End Try
Response.End()
End Sub

Public Sub ExportToExcelFromTo(ByVal dataSrc As SqlDataSource,
ByVal fileName As String, ByVal FromEffectiveDate As String, ByVal
ToEffectiveDate As String)
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename=FileName.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.xls"
Dim sb As New StringBuilder()

'GET Data From Database
Dim cn As New SqlConnection(dataSrc.ConnectionString)
Dim query As String =
dataSrc.SelectCommand.Replace(ControlChars.Cr + ControlChars.Lf, "
").Replace(ControlChars.Tab, " ")

Dim cmd As New SqlCommand(query, cn)
'sb.Append(query.ToString())

cmd.CommandTimeout = 999999
cmd.CommandType = CommandType.Text


cmd.Parameters.Add(New SqlParameter("@FromEffectiveDate",
CDate(FromEffectiveDate.ToString())))
cmd.Parameters.Add(New SqlParameter("@ToEffectiveDate",
CDate(ToEffectiveDate.ToString())))
cmd.Parameters.Add(New SqlParameter("@FromDateAdded",
CDate(FromEffectiveDate.ToString())))
cmd.Parameters.Add(New SqlParameter("@ToDateAdded",
CDate(ToEffectiveDate.ToString())))
''FromEffectiveDate = txtFromEffectiveDate.Text.ToString()
''ToEffectiveDate = txtToEffectiveDate.Text.ToString()

Try
cn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
'Dim sb As New StringBuilder()
'
'Add Header
'


Dim count As Integer
For count = 0 To dr.FieldCount - 1
If Not (dr.GetName(count) Is Nothing) Then
sb.Append(dr.GetName(count))
End If
If count < dr.FieldCount - 1 Then
sb.Append(ControlChars.Tab)
End If
Next count
Response.Write((sb.ToString() + ControlChars.Lf))
Response.Flush()
'
'Append Data
'
While dr.Read()
sb = New StringBuilder()

Dim col As Integer
For col = 0 To (dr.FieldCount - 1) - 1
If Not dr.IsDBNull(col) Then

sb.Append(dr.GetValue(col).ToString().Replace(",", " "))
End If
sb.Append(ControlChars.Tab)
Next col
If Not dr.IsDBNull((dr.FieldCount - 1)) Then
sb.Append(dr.GetValue((dr.FieldCount -
1)).ToString().Replace(",", " "))
End If
Response.Write((sb.ToString() + ControlChars.Lf))
Response.Flush()
End While
dr.Dispose()

Catch ex As Exception
Response.Write((sb.ToString() + ControlChars.Lf))
Response.Write(ex.Message)
'Response.Write(ex.Message)
Finally
cmd.Connection.Close()
cn.Close()
End Try
Response.End()
End Sub





Thanks in advance
Dhananjay