GuidesReturning a DataSet from an Excel Spreadsheet

Returning a DataSet from an Excel Spreadsheet

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

You won’t find many developers proclaiming Excel to be the future of data storage. Even if you’re storing only a small amount of data—perhaps settings or a selection list—it’s still worthwhile going with the Registry, XML files, or a simple Access database.

But some programmers swear by the effectiveness of Excel. Why? Because it’s almost as common as Word, and the majority of users seem able to find their way about pretty easily. So they make simple user-definable values or selection lists available through a simple workbook.

The following code snippet uses OLE DB code to access a named range within an Excel workbook and return it as a DataSet object, ready for binding to a Windows DataGrid, for example.

How do you set up a named range? Just select a region on an Excel worksheet (whether that be one cell, a number of cells, a column, or even the whole sheet), and then click in the Name Box to the top-left of the screen. Type in a new name for the range, and press Enter. (Use the Insert > Name menu to make further edits.) When finished, you’re all set to run the code.

And here it is, comments and all:

Public Function GetDataFromExcel(ByVal FileName As String, _
  ByVal RangeName As String) As System.Data.DataSet
    ' Returns a DataSet containing information from a named range
    ' in the passed Excel worksheet
    Try
        Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & FileName & "; _
                           Extended Properties=Excel 8.0;"
        Dim objConn _
            As New System.Data.OleDb.OleDbConnection(strConn)
        objConn.Open()
        ' Create objects ready to grab data
        Dim objCmd As New System.Data.OleDb.OleDbCommand( _
            "SELECT * FROM " & RangeName, objConn)
        Dim objDA As New System.Data.OleDb.OleDbDataAdapter()
        objDA.SelectCommand = objCmd
        ' Fill DataSet
        Dim objDS As New System.Data.DataSet()
        objDA.Fill(objDS)
        ' Clean up and return DataSet
        objConn.Close()
        Return objDS
    Catch
        ' Possible errors include Excel file already open and
        ' locked, et al.
        Return Nothing
    End Try
End Function

As an example, here’s how you might call the function, using a workbook called MyTestWorkbook.xls in the same directory as the executable, plus a named range called SampleNamedRange. It then takes the first table in the returned DataSet and binds it to our Windows DataGrid. Not bad for one line of code:

DataGrid1.DataSource = GetDataFromExcel(Application.StartupPath & _
   "MyTestWorkbook.xls", "SampleNamedRange").Tables(0)

Figure: My sample application, grabbing data straight from an Excel workbook

About the Author

Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book (ISBN 1-59059-106-2, $49.99), plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at www.karlmoore.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories