locked
Populate Dataset with Excel Worksheet

    Question

  • Hey all,

    I was wondering what is the most efficient way to populate a dataset from an excel worksheet.

    I have tried many ways but most are very inconvenient.

    Any sugestions?

    Thanks.
    Monday, September 10, 2007 6:52 AM

Answers

  • Hi Nabeel,

     

    Based on your post, you need to populate a dataset from an excel worksheet. Here is the code snippet to populate the dataset from the excel files. Hope this helps.

    Code Snippet

    Dim cmd As OleDbDataAdapter

    Dim ds As New DataSet()

    Dim cn As OleDbConnection

    cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "data source=C:\test.xls;Extended Properties=Excel 8.0;")

    cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

    cn.Open()

    cmd.Fill(ds, "Table1")

    cn.Close()

    Me.dataGridView1.DataSource = ds

    Me.dataGridView1.DataMember = "Table1"

     

     

    Thanks.

    Tuesday, September 11, 2007 5:23 AM
    Moderator

All replies

  • Hi Nabeel,

     

    Based on your post, you need to populate a dataset from an excel worksheet. Here is the code snippet to populate the dataset from the excel files. Hope this helps.

    Code Snippet

    Dim cmd As OleDbDataAdapter

    Dim ds As New DataSet()

    Dim cn As OleDbConnection

    cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "data source=C:\test.xls;Extended Properties=Excel 8.0;")

    cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

    cn.Open()

    cmd.Fill(ds, "Table1")

    cn.Close()

    Me.dataGridView1.DataSource = ds

    Me.dataGridView1.DataMember = "Table1"

     

     

    Thanks.

    Tuesday, September 11, 2007 5:23 AM
    Moderator
  • Riquel Dong, thanks for your great reply. I have already tried that method but it doesn't allways populate the dataset correctly (sometimes the data is off by 1-2 columns or rows). I don't know why.

    I ended up creating a class that easily reads excel files.

    Here is the class for anyone else looking for this:

    I might add writing later. PM/E-Mail me if you need it.

    You will need the excel.dll and the excel interlop dll(s).

    If you need those, just email/pm me.

    Code Snippet

    ' Class Created By Nabeel Allana. All Rights Reserved. '
    '              Do Not Remove This Header!              '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'Usage:
    '
    'Dim emanager As New ExcelManager
    'emanager.FilePath = "smallworking.xls"
    'emanager.ShowExcelWindow = False
    'emanager.ExcelWorksheetIndex = 1
    'emanager.SetupWorksheet()
    'Console.WriteLine(emanager.GetValueAtCell("A",5)

    ''' <summary>
    ''' Class allowing excel file reading
    ''' </summary>
    ''' <remarks></remarks>
    Class ExcelManager
    #Region "Constructors"
        Private xlsApp As New Excel.Application
        Private xlsWB As Excel.Workbook
        Private xlsSheet As Excel.Worksheet
        Private xlsCell As Excel.Range
        Private _filepath As String = vbNull
        Private _excelsheet As Integer = 1
        Private _showwindow As Boolean = True
    #End Region
    #Region "Properties"
        ''' <summary>
        ''' The path to the Excel Doccument
        ''' </summary>
        ''' <value>Default Value: vbNull</value>
        ''' <returns>Currently Set File Path (String)</returns>
        ''' <remarks></remarks>
        Public Property FilePath() As String
            Get
                Return _filepath
            End Get
            Set(ByVal value As String)
                _filepath = value
            End Set
        End Property
        ''' <summary>
        ''' Gets or Sets the Index of the Selected Worksheet
        ''' </summary>
        ''' <value>Default Value: 1</value>
        ''' <returns>Currently selected Worksheet Index</returns>
        ''' <remarks></remarks>
        Public Property ExcelWorksheetIndex() As Integer
            Get
                Return _excelsheet
            End Get
            Set(ByVal value As Integer)
                _excelsheet = value
            End Set
        End Property
        ''' <summary>
        ''' Choose to show the excel window
        ''' </summary>
        ''' <value>Default Value: False</value>
        ''' <returns>Current Value</returns>
        ''' <remarks></remarks>
        Public Property ShowExcelWindow() As Boolean
            Get
                Return _showwindow
            End Get
            Set(ByVal value As Boolean)
                _showwindow = value
            End Set
        End Property
    #End Region
    #Region "Functions"
        ''' <summary>
        ''' Sets up the connection to the Excel Worksheet with the Properties Selected.
        ''' </summary>
        ''' <returns>Success or Failure. Throws Exception of Failure.</returns>
        ''' <remarks></remarks>
        Public Function SetupWorksheet() As Boolean
            Try
                xlsWB = xlsApp.Workbooks.Open(FilePath)
                xlsSheet = xlsWB.Worksheets(ExcelWorksheetIndex)
                xlsApp = New Excel.Application
                xlsApp.Visible = _showwindow
                'Returns true for success
                Return True
            Catch ex As Exception
                'Throw an exeption and Return False
                Throw New Exception("An Error Has Occured: " & vbCrLf & vbCrLf & ex.Message & vbCrLf & vbCrLf & "Help Link: " & ex.HelpLink)
                Return False
            End Try
        End Function
        ''' <summary>
        ''' Returns the value of the specified cell in the opened excel doccument.
        ''' </summary>
        ''' <param name="ch">The Character of the Column to Get Value</param>
        ''' <param name="num">The Row number to get value of</param>
        ''' <returns>Value at selected Cell (String)</returns>
        ''' <remarks></remarks>
        Public Function GetValueAtCell(ByVal ch As Char, ByVal num As Integer) As String
            xlsCell = xlsSheet.Range(ch & num)
            Return xlsCell.Text
        End Function
    #End Region
    End Class
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Wednesday, September 12, 2007 8:20 AM
  • Hi,

    you can easily populate DataSet from Excel file with this Excel C# / VB.NET library.

    Here is a sample Excel VB.NET code:

    Dim ef = New ExcelFile()
    ef.LoadXls("DataSet.xls")
    
    Dim dataSet = New DataSet()
    
    For Each ws As ExcelWorksheet In ef.Worksheets
    	Dim dataTable = dataSet.Tables.Add(ws.Name)
    
    	' Create columns from first row cells.
    dataTable.Columns.AddRange(ws.Rows(0).AllocatedCells.Cast(Of ExcelCell)().Select(Function(cell) New DataColumn(DirectCast(cell.Value, String))).ToArray())
    
    	' If excel cell value is not string, call ToString() on it.
    	ws.ExtractDataEvent += Function(sender, e) 
    	If e.ErrorID = ExtractDataError.WrongType Then
    		e.DataTableValue = e.ExcelValue.ToString()
    		e.Action = ExtractDataEventAction.Continue
    	End If
    
    End Function
    
    	ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows(1), ws.Columns(0))
    Next
    
    

    Thursday, April 07, 2011 10:19 AM