none
Problem when exporting datagridview to csv RRS feed

  • Question

  • hi

    I have a problem when exporting datagridview to a csv file .
    Where the datagridview, the second column contains an encoded font name "AlmaFont"
    The export is fine, but the font does not appear in the csv file

    1- What I want is the font change in the csv file = "AlmaFont"
    2-Is there a possibility to set a style for the head columns when opening the file in an Excel format?

    my code to export to csv
       Dim thecsvfile As String = String.Empty
            For Each column As DataGridViewColumn In DATAG_CLIEN.Columns
                thecsvfile = thecsvfile & column.HeaderText & ","
            Next
            thecsvfile = thecsvfile.TrimEnd(",")
            thecsvfile = thecsvfile & vbCr & vbLf
            'get the rows
            For Each row As DataGridViewRow In DATAG_CLIEN.Rows
                'get the cells
                For Each cell As DataGridViewCell In row.Cells
                    thecsvfile = thecsvfile & cell.FormattedValue.replace(",", "") & ","
                Next
                'trim the last comma
                thecsvfile = thecsvfile.TrimEnd(",")
                'Add the line to the output
                thecsvfile = thecsvfile & vbCr & vbLf
            Next
            My.Computer.FileSystem.WriteAllText("D:\New folder\export.csv", thecsvfile, False)




    • Edited by monemas Tuesday, December 17, 2019 5:53 PM
    Tuesday, December 17, 2019 5:52 PM

Answers

  • Hello,

    I have a language extension for you to try in the following GitHub repository

    https://github.com/karenpayneoregon/GroupingVisualBasic/blob/master/WinFormsLanguageExtensions/DataGridViewExtensions.vb#L28

    eg. SomeDataGridView.ExportRows("YourFileName.csv")

    BTW .csv don't have fonts by their very nature which is a text file

    If you want this for Excel use EPPlus or SpreadSheetLight.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, December 17, 2019 6:29 PM
    Moderator
  • Hi,

    CVS is a text file format. It cannot be saved after adjustment. If you want to save it, save it as another file format. It is recommended that you export to an excel file. Converted to a csv file, the font information is lost, and the csv file is equivalent to the text form.

    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Reflection
    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Dim path As String = "D:\Student.xls"
        Dim constr As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + path + ";Extended Properties=Excel 8.0;"
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using conn As New OleDbConnection(constr)
                conn.Open()
                Using cmd As New OleDbDataAdapter("select * from [Sheet1$]", conn)
                    Dim ds As New DataSet()
                    cmd.Fill(ds, "Table1")
                    Me.DataGridView1.DataSource = ds
                    Me.DataGridView1.DataMember = "Table1"
                End Using
            End Using
            DataGridView1.Columns(1).DefaultCellStyle.Font = New Font("Microsoft YaHei", 12, FontStyle.Bold)
        End Sub
    
    
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            If DataGridView1.Rows.Count = 0 Then Return
            Dim excel As Excel.Application = New Excel.Application()
            Dim workbook As Excel.Workbook = excel.Workbooks.Add(True)
            Dim worksheet As Excel.Worksheet
            worksheet = CType(workbook.ActiveSheet, Excel.Worksheet) ' = workbooks.ActiveSheet
            excel.Visible = False
    
            worksheet.Columns(2).Font.Name = "Microsoft YaHei"
            worksheet.Columns(2).Font.size = 12
            worksheet.Columns(2).Font.FontStyle = FontStyle.Bold
    
            For i As Integer = 0 To DataGridView1.ColumnCount - 1
                excel.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText
            Next
    
            For i As Integer = 0 To DataGridView1.RowCount - 1 - 1
    
                For j As Integer = 0 To DataGridView1.ColumnCount - 1
    
                    If DataGridView1(j, i).ValueType = GetType(String) Then
                        excel.Cells(i + 2, j + 1) = "'" & DataGridView1(j, i).Value.ToString()
                    Else
                        excel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                    End If
                Next
            Next
    
            workbook.Saved = True
            workbook.SaveCopyAs("D:\export.xls")
            workbook = Nothing
            excel.Quit()
            GC.Collect()
            MsgBox("Successful export!")
    
        End Sub
    End Class

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Wednesday, December 18, 2019 12:05 PM
    Wednesday, December 18, 2019 3:46 AM
    Moderator

All replies

  • Hello,

    I have a language extension for you to try in the following GitHub repository

    https://github.com/karenpayneoregon/GroupingVisualBasic/blob/master/WinFormsLanguageExtensions/DataGridViewExtensions.vb#L28

    eg. SomeDataGridView.ExportRows("YourFileName.csv")

    BTW .csv don't have fonts by their very nature which is a text file

    If you want this for Excel use EPPlus or SpreadSheetLight.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, December 17, 2019 6:29 PM
    Moderator
  • Hi,

    CVS is a text file format. It cannot be saved after adjustment. If you want to save it, save it as another file format. It is recommended that you export to an excel file. Converted to a csv file, the font information is lost, and the csv file is equivalent to the text form.

    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Reflection
    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Dim path As String = "D:\Student.xls"
        Dim constr As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + path + ";Extended Properties=Excel 8.0;"
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using conn As New OleDbConnection(constr)
                conn.Open()
                Using cmd As New OleDbDataAdapter("select * from [Sheet1$]", conn)
                    Dim ds As New DataSet()
                    cmd.Fill(ds, "Table1")
                    Me.DataGridView1.DataSource = ds
                    Me.DataGridView1.DataMember = "Table1"
                End Using
            End Using
            DataGridView1.Columns(1).DefaultCellStyle.Font = New Font("Microsoft YaHei", 12, FontStyle.Bold)
        End Sub
    
    
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            If DataGridView1.Rows.Count = 0 Then Return
            Dim excel As Excel.Application = New Excel.Application()
            Dim workbook As Excel.Workbook = excel.Workbooks.Add(True)
            Dim worksheet As Excel.Worksheet
            worksheet = CType(workbook.ActiveSheet, Excel.Worksheet) ' = workbooks.ActiveSheet
            excel.Visible = False
    
            worksheet.Columns(2).Font.Name = "Microsoft YaHei"
            worksheet.Columns(2).Font.size = 12
            worksheet.Columns(2).Font.FontStyle = FontStyle.Bold
    
            For i As Integer = 0 To DataGridView1.ColumnCount - 1
                excel.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText
            Next
    
            For i As Integer = 0 To DataGridView1.RowCount - 1 - 1
    
                For j As Integer = 0 To DataGridView1.ColumnCount - 1
    
                    If DataGridView1(j, i).ValueType = GetType(String) Then
                        excel.Cells(i + 2, j + 1) = "'" & DataGridView1(j, i).Value.ToString()
                    Else
                        excel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                    End If
                Next
            Next
    
            workbook.Saved = True
            workbook.SaveCopyAs("D:\export.xls")
            workbook = Nothing
            excel.Quit()
            GC.Collect()
            MsgBox("Successful export!")
    
        End Sub
    End Class

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Wednesday, December 18, 2019 12:05 PM
    Wednesday, December 18, 2019 3:46 AM
    Moderator