2 Replies - 1548 Views - Last Post: 12 November 2011 - 05:42 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Read from Excel sheet and populate a DataSet

Posted 08 November 2007 - 04:53 PM

Description:

Make sure to import the System.Data.OleDb Namespace.

Pass the function the name (and path) of your spreadsheet and it returns a DataSet populated from that spreadsheet.I use this snippet to read from an Excel spreadsheet, then take the data from the sheet and populate a DataSet with it.

This uses the System.Data.OleDb Namespace

''' <summary>
''' Function to read from an Excel spreadsheet and populate a
''' DataSet with the data in the sheet
''' </summary>
''' <param name="file">Excel sheet to read from</param>
''' <returns>A populated DataSet</returns>
''' <remarks></remarks>
Public Function ReadExcelIntoDataset(ByVal file As String) As Data.DataSet
    'Create the DataSet that will hold the data from the sheet
    Dim dsExcel As New Data.DataSet
    'First name sure the file exists
    If Not IO.File.Exists(file) Then
        MessageBox.Show("The file provided doesn't exist. Please check the name and try again")
    Else
        Try
            'Declare the variables needed for the reading of the Excel file
            Dim sOleDbString As New String("SELECT * FROM [sheet1$]")
            'Create our connection to the Excel sheet
            Dim cnExcel As New Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source='" & _
            file & " '; " & "Extended Properties=Excel 11.0;")
            'Create our command object
            Dim cmdExcel As New Data.OleDb.OleDbDataAdapter(sOleDbString, cnExcel)
            'Now we try to fill the DataSet with the data from the Excel file
            Try
                'Fill was successful
                cmdExcel.Fill(dsExcel)
                'Catch any errors that have occurred
            Catch ex As Exception 'Fill failed
                MessageBox.Show(ex.Message)
                dsExcel = Nothing
                'Finally close the connection
            Finally
                cnExcel.Close()
            End Try
            'Catch any errors that may have occurred
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            dsExcel = Nothing
        End Try
        Return dsExcel
    End If
End Function

This post has been edited by AdamSpeight2008: 11 January 2015 - 08:54 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Read from Excel sheet and populate a DataSet

#2 fgruhlke   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 0
  • Joined: 23-November 09

Re: Read from Excel sheet and populate a DataSet

Posted 23 November 2009 - 07:05 PM

Thanks for the code, however I am just starting out in VB.Net and need help with the instructions.. How exactly do I import the "name space" and tell the function the location and name of the excell file... Thanks again for your help!!
Was This Post Helpful? 0
  • +
  • -

#3 Beach_Coder   User is offline

  • D.I.C Head
  • member icon

Reputation: 17
  • View blog
  • Posts: 123
  • Joined: 10-November 11

Re: Read from Excel sheet and populate a DataSet

Posted 12 November 2011 - 05:42 PM

Is this a better way to import data from an Excel file than simply launching an instance of Excel and grabbing the information that way? If yes, why? Right away I was thinking this would be much less of a drain on resources and perhaps even be just flat out quicker. Is that correct? In what sort of case would I absolutely need to or want to use this method over the way I've been doing ... forever?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1