TonyW TonyW - 4 months ago 31
Vb.net Question

Line breaks lost when exporting datagrid to excel in CSV format WPF

This works fine, assuming there are no line breaks in certain cells.

dgvResults.SelectAllCells()
dgvResults.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader
ApplicationCommands.Copy.Execute(Nothing, dgvResults)
Dim result As [String] = DirectCast(Clipboard.GetData(DataFormats.CommaSeparatedValue), String)
Clipboard.Clear()
dgvResults.UnselectAllCells()

Try
Dim file As New System.IO.StreamWriter("c:\export.csv")
file.WriteLine(result)
file.Close()
Process.Start("c:\export.csv")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
End Try


This is how I add line breaks

Dim x As New List(Of String)
For Each item In res.Properties("proxyaddresses")
x.Add(item)
Next
AllSMTPAddresses = String.Join(ControlChars.Lf, x)


When I export this, it doesn't take into consideration there are line breaks, and completely ignores them... so the excel formatting is a little wonky. I've tried: Environment.NewLine, vbCrLf, and now ControlChars.Lf. I think excel doesn't know what to do with the line breaks, so it just does w.e it wants, and creates new rows with them.

Any idea on how I would attack this?

Updated results @Jimmy

enter image description here

This is what it's supposed to look like...

enter image description here

Answer

I don't believe this will be possible without modifying those rows prior to the export. I found some example code that may help,

  Public Sub writeCSV(grid1 As Object, outputFile As String)
    ' Create the CSV file to which grid data will be exported.
    Dim sw As New StreamWriter(outputFile)
    ' First we will write the headers.
    Dim dt As DataTable = DirectCast(grid1.DataSource, DataSet).Tables(0)

    Dim iColCount As Integer = dt.Columns.Count
    For i As Integer = 0 To iColCount - 1
        sw.Write(dt.Columns(i))
        If i < iColCount - 1 Then
            sw.Write(",")
        End If
    Next
    sw.Write(sw.NewLine)
    ' Now write all the rows.
    For Each dr As DataRow In dt.Rows
        For i As Integer = 0 To iColCount - 1
            sw.Write("""") 'lets encapsulate those fields in quotes, quoted csv file!
            If Not Convert.IsDBNull(dr(i)) Then
                sw.Write(dr(i).ToString())
            End If
            sw.Write("""")
            If i < iColCount - 1 Then
                sw.Write(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator)
            End If
        Next
        sw.Write(sw.NewLine)
    Next
    sw.Close()
End Sub

modified from here