problem with exporting text to excel using StreamWriter


D

Dan

Hi,

i want to export text from fields in a table to an excel sheet.
This code below works. The problem is that when a field contains text with
'return' in it, the text is put in 2 (or more) rows in excel instead of one
row.
Is there a way to solve this, e.g. by removing the 'returns' (but how)
before sending the text to excel, or ...
Thanks for help
Dan.

Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter
objFileStream = New FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)

connection.Open()
Dim sql As String = "SELECT * FROM mytable"
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
dr = cmd.ExecuteReader()

While dr.Read()
For i = 1 To dr.FieldCount - 1
strLine = strLine & dr.GetValue(i) & Chr(9)
Next
objStreamWriter.WriteLine(strLine)
strLine = ""
End While

dr.Close()
connection.Close()
objStreamWriter.Close()
objFileStream.Close()
 
Ad

Advertisements

D

Dan

Thanks for replying.

i tried this: (assume the string in the table is: "firstpart + enter-key
from the user + secondpart")

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine.Replace(Chr(13), " ")
strLine = strLine.Replace(Chr(10), " ")
strLine = strLine & dr.GetString(i) & Chr(9)
Next
End While


But i still get in excel the string cut into two cells like this:
firstpart
secondpart
 
Ad

Advertisements

D

Dan

i found it:

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine & dr.GetString(i) & Chr(9)
Next
strLine = strLine.Replace(Chr(13), " ")
strLine = strLine.Replace(Chr(10), " ")
objStreamWriter.WriteLine(strLine)
strLine = ""
End While

Dan said:
Thanks for replying.

i tried this: (assume the string in the table is: "firstpart + enter-key
from the user + secondpart")

While dr.Read()
For i = 0 To dr.FieldCount - 1
strLine = strLine.Replace(Chr(13), " ")
strLine = strLine.Replace(Chr(10), " ")
strLine = strLine & dr.GetString(i) & Chr(9)
Next
End While


But i still get in excel the string cut into two cells like this:
firstpart
secondpart
 

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

Top