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

[=?Utf-8?B?RmFtaWx5IFRyZWUgTWlrZQ==?=]

As far as I know, the text format (comma separated format really) is setup to
do what you are seeing. It does not support column formating. I don't even
think you could save a formula and have Excel recognize it as such.

If you really want to create an Excel output which supports column
definitions, you would need to use Excel objects. That said, I do 99% of my
stuff in desktop apps, and the constant advice I see to people is not to use
Office objects in a server application.

"Dhananjay" wrote:

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

[Andrew Morton]

Family Tree Mike wrote:

As far as I know, the text format (comma separated format really) is
setup to do what you are seeing. It does not support column
formating. I don't even think you could save a formula and have
Excel recognize it as such.

FWIW, you can: a file containing

1,2,3,=a1+b1+c1
=pi()/2,=sin(a2)

evaluates the formuale when opened in Excel.

Andrew