Introduction
Most of the time you will use SQL, Oracle, Access or some other database to store data. But it is possible to use Excel spreadsheet much like a database to stored data. This article and code will explain how to do this in C#.
Spreadsheet Setup
The first step to using an Excel spreadsheet as a place to store data and possibley update/delete/add data, is to put the data in the spreadsheet. Most of the time you will have column headers for you data and this will be the field names used when writing queries. After populating the spreadsheet with data (you may already have a spreadsheet you want to use, which is fine) the next step is to Define the Names in the workbook. A Name in the Excel workbook is a section of data that will be given a name and a range. The name that you give it will be much like a table name in a database. Here is how to create a name:
- Open the Excel spreadsheet and select the menu choices of "Insert|Name|Define" as shown below.
- After selecting this a dialog box will come up called "Define Name". You will need to type in a name for the data you want to define. Then you will select the data in the "Refers to:" section at the bottom of the dialog box. Once you have the data set up and named your ready to go.
At this point you Excel spreadsheet setup is complete, so save the spreadsheet.
Database
To connect to the spreadsheet database using ADO.NET you will need a connection string, the one below will do the trick.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
You will need to add a reference to the "System.Data.OleDb" namespace also. This will allow you to connect and query data from the spreadsheet.
Example Program
There is an example program with this that will show you how to connect to the spreadsheet and query data. It is possible to have more than one spreadsheet in an Excel workbook that you can query using SQL joins. This assumes that you have set up each spreadsheet with a "Name" as described in the Spreadsheet Setup section above.
The example program had examples of querying a single "Name" (or table if that is the way you want to think about it) , two spreadsheets with both spreadsheets having a "Name" associated with it, inserting data into a spreadsheet and updating data in a spreadsheet. The basic steps necessary for using the data is fairly simple and just like using SQL or any other database. First you make a connection to the spreadsheet using the connection string listed above. Second you write a SQL query using the spreadsheet "Name" (table name) that you defined before in setting up the spreadsheet. Third you execute the query and if necessary return records. And finally display the data, assuming that is what you want to do with it.
The code is fairly well commented so I am not going to go into much detail about how it is done but you can look at the code to get a good idea of how to do this. Once you get past the Excel spreadsheet setup from above, the rest is just like connecting to any other database that you may have used before.
Conclusion
Using Excel as a database for most purposes is probably not a good idea but there may come a time when data is sent to you on a regular basis in a spreadsheet, to be updated or imported into other process and this may come in handy for that process.