problem with exporting text to excel using StreamWriter

  • Thread starter Thread starter Dan
  • Start date Start date
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()
 
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
 
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
Click to expand...
 
Back
Top