locked
display excel spreadsheet data in a DataGridView

    Question

  • hey all. Im having no luck finding any reference to do this.

    would anyone know how this is done, or point me at a good tutorial on how to display the data in an excel spreadsheet in a DataGridView?

    thanks heaps!

    Greg.

    Wednesday, May 12, 2010 6:22 PM

Answers

  • imports System.Data.OleDb
    imports System.IO
    
    
    Public Sub setExcelFileAsDataSourceToDataGridView(ByVal FileName As String)
      Dim objConn As OleDbConnection
      Dim oleDA As OleDbDataAdapter
      Dim ds As DataSet
      'Check Whether file is xls file or not 
      If Path.GetExtension(FileName) = ".xls" Then
        Try
          'Create a OLEDB connection for Excel file 
          Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + FileName & ";" & "Extended Properties=Excel 8.0;"
          objConn = New OleDbConnection(connectionString)
          oleDA = New OleDbDataAdapter("select * from [Sheet1$]", objConn)
          ds = New DataSet()
          'Fill the Data Set 
          oleDA.Fill(ds)
          'Set DataSource of DataGridView 
          dataGridView1.DataSource = ds.Tables(0)
          ds.Dispose()
          oleDA.Dispose()
          objConn.Dispose()
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
      Else
        MessageBox.Show("Please select Excel File")
        
      End If
    End Sub

    Gaurav Khanna
    • Marked as answer by GPalinkas Wednesday, May 12, 2010 7:14 PM
    Wednesday, May 12, 2010 6:44 PM

All replies

  • Thanks Khanna!

    but... do you know of similar code in VB? am a little lost lookng at the cod in the links :P

     

    Wednesday, May 12, 2010 6:35 PM
  • imports System.Data.OleDb
    imports System.IO
    
    
    Public Sub setExcelFileAsDataSourceToDataGridView(ByVal FileName As String)
      Dim objConn As OleDbConnection
      Dim oleDA As OleDbDataAdapter
      Dim ds As DataSet
      'Check Whether file is xls file or not 
      If Path.GetExtension(FileName) = ".xls" Then
        Try
          'Create a OLEDB connection for Excel file 
          Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + FileName & ";" & "Extended Properties=Excel 8.0;"
          objConn = New OleDbConnection(connectionString)
          oleDA = New OleDbDataAdapter("select * from [Sheet1$]", objConn)
          ds = New DataSet()
          'Fill the Data Set 
          oleDA.Fill(ds)
          'Set DataSource of DataGridView 
          dataGridView1.DataSource = ds.Tables(0)
          ds.Dispose()
          oleDA.Dispose()
          objConn.Dispose()
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
      Else
        MessageBox.Show("Please select Excel File")
        
      End If
    End Sub

    Gaurav Khanna
    • Marked as answer by GPalinkas Wednesday, May 12, 2010 7:14 PM
    Wednesday, May 12, 2010 6:44 PM
  • Thanks Khanna!

    that totally worked.

    I feel like such a noob now.

    Wednesday, May 12, 2010 7:14 PM