Brief background
Our client wanted to provide us with some data on their season ticket holders. They use all sorts of new and old systems to hold this information, with the main list of numbers being in Excel. We needed to load this data into our database but without needing a complex SSIS / BizTalk transformation process... so we started to look around.
Libraries ahoy!...
There's lots of third party libraries to do this sort of thing, but they're heavy and hard to learn... We didn't like these, we found that they take too long to get working / work around the issues... we just needed access to the source data in a simple format and found that Microsoft support opening Excel as a data source using ADO and .NET.]
Driver to the cab
To allow you to open the Excel file in the correct format, you'll need to get hold of the OLEDB drivers for the relevant version of Microsoft Office. I found that the latest 2010 drivers worked for the relevant file formats. You just need to pick which one you need for your CPU - i.e.: the 32 or 64 bit versions.
On my development PC, I have the 32 bit installed. Our production server is a 64-bit O/S - so needed the 64bit version installing.. it doesn't matter if you code with one and release with the other providing your Visual Studio compile output is any-CPU. If it isn't then obviously, you need the one to match your compilation process.
Links are on the following microsoft download page: http://www.microsoft.com/download/en/details.aspx?id=13255
Also, these do not need a reboot when you install them, so you don't necessarily need to wait for a maintenance window to install on a server.
Coding for an XLSX File
Ok, so to the code!... firstly - you need to get at the data in ADO as you would an old fashioned database..
Dim stConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" + m_stSourceFile + "; Extended Properties=Excel 12.0;"
Dim obDataAdaptor As OleDb.OleDbDataAdapter
obDataAdaptor = New OleDb.OleDbDataAdapter("SELECT * FROM Season_Data", stConnectionString)
These lines define a connection string to an Excel file where the file name is in the m_stSourceFile variable name (e.g.: c:\data\excel-data.xlsx). The second and third lines define an OLEDB adaptor to read data from the worksheet 'Season_Data' (The worksheet name is the name on the tabs in the Excel document... if you're referring to them in this manner they don't need to be in order - but the names need to match. You can use the Data structure querying features of OLEDB to determine the worksheet names as they're presented as tables, but thats a different blog in itself).
Dim dsValues As New DataSet()
Dim dtTable As DataTable
obDataAdaptor.Fill(dsValues, "DataValues")
dtTable = dsValues.Tables("DataValues")
The lines above prepare a populated ADO style table of the data held in the spreadsheet (being accessed through the data adaptor). So we now have a connection to Excel, and a table containing the data from one of the worksheets. The data held in the table can then be accessed using the a Rows and Columns style process - Rows().Item() method, for example:
Dim stCellValue As String = CStr(dtTable.Rows(1).Item(0))
Reads the cell value of the first column, second row of the table. As with all arrays, both .Rows and .Item are zero index based.
