Hi, I'm trying to export data from excel sheet to my grid, I'm using the foll code, but I get an error at DataGridView1.Rows.Add(), saying rows cannot be added programmatically.

    Private Sub btnexport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexport.Click

        Dim xlsApp As Microsoft.Office.Interop.Excel.Application
        Dim xlsbook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim m_Rw As Integer
        Dim i As Integer
        Dim j As Integer

        xlsApp = CreateObject("Excel.Application")
        xlsApp.Workbooks.Open("C:\Users\finance and accounts\Desktop\sample.xlsx")
        xlsbook = xlsApp.ActiveWorkbook
        xlsSheet = xlsbook.Sheets(1)

        ' insert column headers...
        'DataGridView1.ColumnCount = xlsSheet.UsedRange.Columns.Count
        'DataGridView1.ColumnHeadersVisible = True
        For i = 0 To xlsSheet.UsedRange.Columns.Count - 1
            DataGridView1.Columns(i).Name = xlsSheet.Cells(1, i + 1).Value.ToString

        ' insert rows and data to its cells....
        Dim var1(xlsSheet.UsedRange.Columns.Count) As String ' array for consisting cell value
        m_Rw = 0
        For i = 2 To xlsSheet.UsedRange.Rows.Count - 1
            DataGridView1.AllowUserToAddRows = True
            For j = 0 To xlsSheet.UsedRange.Columns.Count - 1

                If xlsSheet.Cells(i, j + 1).Value = Nothing Then
                    var1(j) = ""
                    var1(j) = xlsSheet.Cells(i, j + 1).Value.ToString
                End If

                DataGridView1.Item(j, m_Rw).Value = var1(j)

            Next j
            m_Rw = m_Rw + 1
        Next i

        xlsSheet = Nothing  'disconnect from the Worksheet
        xlsbook = Nothing
        xlsApp.Quit()       'Close (and disconnect from) Excel
        xlsApp = Nothing

        Me.Cursor = Cursors.Default

    End Sub

Recommended Answers

All 3 Replies

you have to first add columns to your grid in order to add rows to it , first add columns then add rows


ther are columns present in the grid... Am I supposed to mention the columns in the above code as well?? If yes, then how?

you can add column values line by line by doing this.

'this will add rows to your grid try this.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.