473,217 Members | 2,121 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,217 software developers and data experts.

got problem with exportGridview Data into Excel format

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
Jan 24 '08 #1
2 2089
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
Jan 24 '08 #2
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
Jan 24 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet...
9
by: Ksenia Marasanova | last post by:
Hi, I have a little problem with encoding. Was hoping maybe anyone can help me to solve it. There is some amount of data in a database (PG) that must be inserted into Excel sheet and emailed....
1
by: Richard Holliingsworth | last post by:
Hello: Thanks for your quick response. I'm trying to import a new Excel file into an A2K table and it's truncating the data. One of the Excel columns is a text field that can be up to 2000...
0
by: acharyaks | last post by:
Hi life saver, I am using excel component for the development. The purpose is to connect to excel through the odbc connection string. Then through the connection extract data into a dataset and...
1
by: huseinv | last post by:
Hi. I have a search tool which gives data in listing format and grid format (based on selection) on the same asp page. If a user select grid, the result is listed in grid format. Now how i can...
3
by: maylee21 | last post by:
hi, anyone can help me figure out how to read data from a text file like this: 10980012907200228082002 and extract the data according to this kind of format: Record type 1 TY-RECORD ...
4
by: wellscrambled | last post by:
Folks, Probably don't have the right forum here, but this is all a bit new to me. I have a web site that sends me a daily email with an excel spreadsheet attachment that contains some customer...
0
by: Dhananjay | last post by:
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...
6
by: and1 | last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.