Returning a DataSet from an Excel Spreadsheet
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.