Results 1 to 10 of 10

Thread: Write DataGridView Headers to excel

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Write DataGridView Headers to excel

    Hello:

    My code is below. I have no trouble reading in the first row as a header for a datagridview, but when I write it out it keeps truncating them.

    Why can't I just somehow say the first row is a header that needs to be written??

    Code:
        Private Sub WriteDB(UserDB As String, dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i, j As Integer
    
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlBook = xlApp.Workbooks.Add(misValue)
            xlSheet = xlBook.Sheets("Sheet1")
    
    
            For h As Integer = 0 To dgv.ColumnCount - 1
                xlSheet.Cells(0, h) = dgv.Columns(h).HeaderText
                ' MessageBox.Show(dgv.Columns(h).HeaderText)
    
            Next
    
            For i = 1 To dgv.RowCount - 1
                For j = 0 To dgv.ColumnCount - 1
                    xlSheet.Cells(i + 1, j + 1) = dgv(j, i).Value.ToString()
    
                Next
    
            Next
    
            xlSheet.SaveAs(dbf)
            xlBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlBook)
            releaseObject(xlSheet)
    
    
        End Sub
    Last edited by ssabc; May 20th, 2019 at 04:27 PM.

  2. #2
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Write DataGridView Headers to excel

    Why can't I just somehow say the first row is a header that needs to be written??
    you could if you were using ADO.Net /OleDb

  3. #3
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Write DataGridView Headers to excel

    Hello,

    There is a free Excel library named SpreadSheetLight which has a method to export a DataTable to Excel with or without column headers. In the following code sample I show how to get a DataGridView rows into a DataTable then pass the DataTable to SpreadSheetLight which will import the DataTable into Excel and allow you to provide the sheet name. The best part is there is no worries for cleaning up automation objects as SpreadSheetLight is not based on automation and will even work without Excel being installed.

    DataGridView unbound to either Excel or Text file (VB.NET)

    See this section in the code sample about for setting up: Setting up for using SpreadSheetLight (and note SpreadSheetLight can be installed via NuGet package manager in Visual Studio).

    Downside, more code, upside the code is very easy and pain free.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Write DataGridView Headers to excel

    Hello:

    I am converting this to ADODB as suggested.

    It is frustrating because I date a datagridview that I wish to update to an Excel spreadsheet. Whenever I change the size of the dgv, the spreadsheet seems to want the same number of records. So I tried an rs.AddNew() if the recordcount was not the same. There are also options to delete, which affect things, as I'm sure is intended.

    I would be fine with wiping out the entire spread and rewriting the datagridview every time. This has to be simple??

    Code below:

    Code:
        Private Sub WriteDB(UserDB As String, dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
    
            Dim cn As String = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source= " & dbf & "; Extended Properties = " & Chr(34) & "Excel 12.0; HDR = Yes; IMEX = 1" & Chr(34) & "; "
            Dim sql As String = "SELECT * FROM [Sheet1$]"
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            rs.Open(sql, cn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)
            rs.MoveFirst()
    
            For i As Integer = 0 To dgv.Rows.Count - 2
                If rs.EOF Then
                    rs.AddNew()
    
                End If
    
                rs.Fields(0).Value = dgv.Rows(i).Cells(0).Value
                rs.Fields(1).Value = dgv.Rows(i).Cells(1).Value
                rs.Fields(2).Value = dgv.Rows(i).Cells(2).Value
    
                rs.Update()
    
                rs.MoveNext()
    
            Next
    
            rs.Close()
    
        End Sub

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Write DataGridView Headers to excel

    Nono, not ADODB... ADO.Net

    it looks more like this
    Code:
            Dim XLSFile As String = "C:\DATA\xlfile.xlsx"
            Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
        "Data Source=" & XLSFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
            Using XLSXConn As New OleDbConnection(connstring)
                Using XlsDA As New OleDbDataAdapter("SELECT * FROM [sheet1$]", XLSXConn)
                    Dim XlDtable As New DataTable
                    XlsDA.Fill(XlDtable)
                    DataGridView1.DataSource = XlDtable
                End Using
            End Using

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Write DataGridView Headers to excel

    Thanks, Reading it that way doesn't lock down the file when trying to wrote back!

    The difficulty is it binds the data... I want to keep things unbound because I am adding rows to the datagrids and updating. This, this may not be a good solution for me.

    At this point, I want to just write out the dgv like this, but it thinks the file is in use from the read, and nothing seems to kill the process.

    Code:
        Private Sub WriteDB2(UserDB As String, dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
    
            My.Computer.FileSystem.DeleteFile(dbf)
    
            Dim xlapp As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
            Dim xlbook As Microsoft.Office.Interop.Excel._Workbook = xlapp.Workbooks.Add(Type.Missing)
            Dim xlsheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
    
            xlapp.Visible = True
    
            xlsheet = xlbook.Sheets("Sheet1")
            xlsheet = xlbook.ActiveSheet
    
            For i As Integer = 1 To dgv.Columns.Count + 1 - 1
                xlsheet.Cells(1, i) = dgv.Columns(i - 1).HeaderText
    
            Next
    
            For i As Integer = 0 To dgv.Rows.Count - 1 - 1
    
                For j As Integer = 0 To dgv.Columns.Count - 1
                    xlsheet.Cells(i + 2, j + 1) = dgv.Rows(i).Cells(j).Value.ToString()
    
                Next
    
            Next
    
            xlbook.SaveAs(dbf)
            xlapp.Quit()
    
        End Sub
    Any other thoughts?
    Last edited by ssabc; May 21st, 2019 at 11:31 AM.

  7. #7
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Write DataGridView Headers to excel

    I am pretty confused by what your problem is. I am going to use the keyword "unbound" and ignore the rest of what you said.
    Here is a working sample how to add data from an XLSX to a datagridview without a datatable or datagridview.datasource

    Code:
    Public Class FormExcel
    
        Private Sub ButtonLoadXLSX_Click(sender As Object, e As EventArgs) Handles ButtonLoadXLSX.Click
            Dim XLSFile As String = "C:\DATA\test.xlsx"
    
            Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
        "Data Source=" & XLSFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    
            Using XLSXConn As New OleDb.OleDbConnection(connstring)
                XLSXConn.Open()
                Using XLSXRdr As OleDb.OleDbDataReader = New OleDb.OleDbCommand("SELECT * FROM [sheet1$]", XLSXConn).ExecuteReader
                    If XLSXRdr.HasRows Then
                        For Each DtRow As DataRow In XLSXRdr.GetSchemaTable.Rows
                            DataGridView1.Columns.Add(New DataGridViewTextBoxColumn With {
                                                      .Name = DtRow("ColumnName").ToString,
                                                      .ValueType = Type.GetType(DtRow("DataType").ToString)})
                        Next
    
                        
                        While XLSXRdr.Read
                            Dim Vals(XLSXRdr.FieldCount - 1) As String
                            For i As Integer = 0 To XLSXRdr.FieldCount - 1
                                Vals(i) = Convert.ToString(XLSXRdr(i))
                            Next
                            DataGridView1.Rows.Add(Vals)
                        End While
    
                    End If
    
                End Using
    
            End Using
        End Sub
    End Class
    Last edited by kpmc; May 21st, 2019 at 02:42 PM.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Write DataGridView Headers to excel

    The problem is in the writing of data back to excel, not reading it in.

    If it is not read a certain way, the write excludes the headers.

    Code to ReadXL:

    Code:
        Private Sub ReadDB3(UserDB As String, dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
            Dim cn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbf & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    
            Using xlsxCn As New OleDb.OleDbConnection(cn)
                xlsxCn.Open()
                Using xlsxRdr As OleDb.OleDbDataReader = New OleDb.OleDbCommand("SELECT * FROM [sheet1$]", xlsxCn).ExecuteReader
                    If xlsxRdr.HasRows Then
                        For Each DtRow As DataRow In xlsxRdr.GetSchemaTable.Rows
                            dgv.Columns.Add(New DataGridViewTextBoxColumn With {
                                                      .Name = DtRow("ColumnName").ToString,
                                                      .ValueType = Type.GetType(DtRow("DataType").ToString)})
    
                        Next
    
                        While xlsxRdr.Read
                            Dim Vals(xlsxRdr.FieldCount - 1) As String
                            For i As Integer = 0 To xlsxRdr.FieldCount - 1
                                Vals(i) = Convert.ToString(xlsxRdr(i))
    
                            Next
                            dgv.Rows.Add(Vals)
    
                        End While
    
                    End If
    
                End Using
    
            End Using
    
        End Sub
    Code to WriteXL:
    Code:
        Private Sub WriteDB2(UserDB As String, dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
    
            ' KillExcel()
            My.Computer.FileSystem.DeleteFile(dbf)
    
            Dim xlapp As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
            Dim xlbook As Microsoft.Office.Interop.Excel._Workbook = xlapp.Workbooks.Add(Type.Missing)
            Dim xlsheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
    
            xlapp.Visible = True
    
            xlsheet = xlbook.Sheets("Sheet1")
            xlsheet = xlbook.ActiveSheet
    
            For i As Integer = 1 To dgv.Columns.Count + 1 - 1
                xlsheet.Cells(1, i) = dgv.Columns(i - 1).HeaderText
    
            Next
    
            For i As Integer = 0 To dgv.Rows.Count - 1 - 1
                For j As Integer = 0 To dgv.Columns.Count - 1
                    xlsheet.Cells(i + 2, j + 1) = dgv.Rows(i).Cells(j).Value.ToString()
    
                Next
    
            Next
    
            xlbook.SaveAs(dbf)
            xlapp.Quit()
    
        End Sub
    Last edited by ssabc; May 22nd, 2019 at 08:29 AM.

  9. #9
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Write DataGridView Headers to excel

    You can use the OledbConnection to write changes back to the excel file. The only thing you cant do is delete.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Write DataGridView Headers to excel

    Thanks for the reply. I got it working. I'm just deleting and rewriting the whole thing, its not that big. The initial issue, for whatever reason had to do with the write removing the column headers. It seems depending on how it's read in, this can happen.

    Code:
        Private Sub WriteDB3(UserDB As String, ByVal dgv As DataGridView)
            Dim dbf As String = "\\w2012\users\Steve A\VS_Projects\PeerReviewDB\" & UserDB & ".xlsx"
    
            Dim rowsTotal, colsTotal As Short
            Dim I, j, iC As Short
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application
            Try
                Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add
                Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
                xlApp.Visible = True
    
                rowsTotal = dgv.RowCount
                colsTotal = dgv.Columns.Count - 1
    
                With xlSheet
                    .Cells.Select()
                    .Cells.Delete()
                    For iC = 0 To colsTotal
                        .Cells(1, iC + 1).Value = dgv.Columns(iC).HeaderText
    
                    Next
    
                    For I = 0 To rowsTotal - 1
                        For j = 0 To colsTotal
                            .Cells(I + 2, j + 1).value = dgv.Rows(I).Cells(j).Value
    
                        Next j
                    Next I
                    .Rows("1:1").Font.FontStyle = "Bold"
                    .Rows("1:1").Font.Size = 12
    
                    .Cells.Columns.AutoFit()
                    .Cells.Select()
                    .Cells.EntireColumn.AutoFit()
                    .Cells(1, 1).Select()
    
                End With
    
                xlBook.SaveAs(dbf)
                xlApp.Quit()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            Finally
                'RELEASE ALLOACTED RESOURCES
                System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
                xlApp = Nothing
    
            End Try
    
        End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width