0 Replies - 2632 Views - Last Post: 13 October 2014 - 11:02 AM

#1 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6447
  • View blog
  • Posts: 26,070
  • Joined: 12-December 12

Populate DataGridView with Excel Data

Posted 13 October 2014 - 11:02 AM

This code populates a DataGridView from an Excel worksheet, where the data does not start from A1. Note that this approach does not require looping through rows and columns.

To achieve this, a button first opens the workbook and creates a Defined Name for the relevant data-range. In my case, it starts from the first worksheet (it doesn't have to) and from cell A5 onwards.



If your data starts in Sheet1 from A1 then you don't need this extra step, just use "SELECT * FROM [Sheet1$]".



It uses CurrentRegion, which is equivalent to clicking in a cell and pressing Ctrl-A. "HDR=YES" in the code means that the data-range includes column-headings, which become column-headers for the DGV.
Imports Excel = Microsoft.Office.Interop.Excel  'Add Reference
'Microsoft Excel 14.0 Object Library

Public Class frmExcel

    Private Sub btnNameRange_Click(sender As Object, e As EventArgs) Handles btnNameRange.Click
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open("C:\Users\Andrew\Documents\AndysData.xlsx")
        Dim xlSheet As Excel.Worksheet = xlBook.Worksheets(1)    'or use worksheet-name
        xlSheet.Range("A5").CurrentRegion.Name = "staffData"
        xlBook.Close(True)      'save changes
        xlApp.Quit()
        ReleaseObject(xlSheet)
        ReleaseObject(xlBook)
        ReleaseObject(xlApp)

    End Sub

    Private Sub btnFillExcel_Click(sender As Object, e As EventArgs) Handles btnFillExcel.Click
        Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Andrew\Documents\AndysData.xlsx;"
        sConn &= "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        Using conn As New OleDb.OleDbConnection(sConn)
            Dim dSet As New DataSet

            Dim adapt As New OleDb.OleDbDataAdapter("SELECT * FROM [staffData]", conn)
            adapt.Fill(dSet, "tblStaff")
            dgvExcel.DataSource = dSet.Tables("tblStaff")
        End Using
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        'http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class


To test it requires a Form with two Buttons and a DataGridView, and Add a Reference for Excel. Click the Button to name the range first, then the Button to fill the DGV.

You will, of course, need to change the file name and path, worksheet name (or number) and the cell (A5) where your data starts from. The data needs to be in a single, disconnected, block. Open the file and use Ctrl-A to confirm this.

Attached Image



The much better option is that the Excel data should start from cell A1, and that the (single) worksheet contains no other data or formulas other than the table.

This post has been edited by andrewsw: 14 October 2014 - 06:58 AM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1