CODEWITHSUNDEEP
wpfvb.netdatagridview
TonyW
TonyW

Reputation: 786

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

Upvotes: 1

Views: 748

Answers (1)

Jimmy Smith
Jimmy Smith

Reputation: 2451

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

Upvotes: 1

Related Questions