Why use Excel as a Database?
Here Mudassar Ahmed Khan has explained with an example, how to read (import) Excel file without using OLEDB or installing Microsoft Office Excel or Interop Library in C# and VB.Net. The Excel file will be read using ClosedXml and OpenXml DLLs which does not require OLEDB and does not require Microsoft Office Excel or Interop Library software to. Microsoft Excel workbooks are one type of OLE DB data source that you can query through SQL Server in this manner. This article describes the syntax that is necessary to configure an Excel data source as a linked server, as well as the syntax that is necessary to use a distributed query that queries an Excel data source.
Deskovery. Most developers know how to use back ends like Microsoft SQL server, Oracle, Microsoft Access or IBM DB2 as database back ends for their applications. Microsoft Excel in the other hand is able to perform many of the services these back ends provide.
The unique things about Microsoft Excel if contrasted with these back ends is it's simplicity which directly results in zero DDL effort along with many other advantages. Microsoft Excel is increasingly used as a database replacement as you can have your tables designed and created in zero time and you can start immediately enter data into them, a feature that is hard to obtain in all of the back ends mentioned above. We observe an increasing demand from several application producers (specially small and medium applications) to have Microsoft Excel as their database back end. This way they will have all the analysis / data storage / calculations / formatting free from excel and in the same time they can access every thing in a form of a database table from the applications they build so that they have everything with an affordable cost. This is all not to mention that the availability of Microsoft Excel is much higher if compared to any other database back end. This tutorial will teach you how to use Microsoft Excel table sheets from .NET applications as if they are database tables residing on a database server for example.
Using MS Excel with ADO.NET
.NET Framework supports using Excel as a backend database for .Net applications. You can accomplish this task by using ADO.NET the same way you use it when you need to manipulate other types of backend databases. With ADO.Net you can retrieve data from a Microsoft Excel workbook, modify or add data in an existing workbook, or create a new workbook and work sheets.
In the following sections of our tutorial we will explain - step by step - how to manipulate Excel sheets by giving a real working example.
Different SQL Query with MS Excel
Using ADO.NET with Excel sheets is somehow differing from using it with the other database types in the following issues:
Automatic Code Generation: You have to do the entire job yourself, no automatic data adapters, commands, and datasets generation. You will write all the code yourself.
Connection String: The connection string will have the following syntax:
The 'HDR' parameter tells if columns in the accessed worksheet have headers or not. Yes for have, No for have not.
Data Types: There is no direct way to specify the data types for columns in Excel tables. The OLE DB scans a number of columns (you can set it ) to guess the data type for the field.
Table Names: To reference a table in an Excel workbook you have to use one of the following syntaxes:
Oledb Excel Driver
Or
Note that the dollar sign is an indication that the sheet exists. If you are creating a new sheet or table, do not use it.
Manipulate Excel Workbook With .NET Application
Create new Excel Workbook and Datasheet
To create a new Excel datasheet use the 'Create Table' SQL command. If the workbook specified in the connection string dose not exists, it will be created too. If it exists then the command will create a datasheet with the specified name.
Open up your MS Visual Studio 2005 and create a new VB.NET application. Add some button controls to 'Form1' design surface as shown in the following figure.
In the most beginning of 'Form1.vb' code file import the following namespace.
In this example we will create a separate function to build the required connection string as follows:
This function takes a string represents an Excel workbook file path name like 'd:yBook1.xls' as the data source and returns the connection string that represents that data source.
Now double click the'Create New Datasheet' button to activate its click event handler. Write the following lines of code.
As you see in the above code, to create a new workbook or datasheet we have to define an OleDB connection object and an OleDB command object. Then we set the connection string by using the 'CreateConnString' described in the previous section. Set the command connection property to the used connection object. Set the command text property to the SQL create table command as shown in the above code. This command will create a new workbook named 'Book1.xls' and a new worksheet named 'MySheet' that have two columns 'F1' and 'F2' both of the same data type which is 'Char(255)'.
Press F5 to run the application, and click the 'Create New Data Sheet' button. Now browse to the 'D:K' folder and find the new created 'Book1.xls'.
Insert New Records
To insert new records into an existing data sheet use the 'INSERT INTO' SQL command. In our application double click the 'Insert New Record' button to activate its click event handler. Write the following lines of code:
Run the application and click the 'Insert New Record' button. Browse to the 'D:kBook1.xls' and open it. You will find the following:
Figure 3 - New Record is inserted into the MySheet
The record is added to 'MySheet'.
Retrieve Existing Records
To retrieve existing records in an existing excel workbook use one of the two famous ADO.NET techniques. OleDB Data Reader, or OleDB Data Adapters and Data Sets. We will use the first technique in our program sample with a pre created Excel workbook named 'MyBook.xls' as shown in the following figure.
Double click the 'Retrieve Records' button to activate its click event handler. Type the following lines of code:
In the above code we retrieve all the records in the 'Products' datasheet located in the 'MyBook.xls' workbook. Run the application, click the 'Retrieve Record' button, and note the result.
You can use Data Adapter and Data set objects to accomplish the same task, but you need to build all the commands needed in the Data Adapter Fill and Update operations yourself.
Oledb Excel Update Query
Update Existing Record
To update an existing record we will make use of the 'Update ' SQL command. Double click the 'Update Existing Record' button to activate its event handler and type the following lines of code:
In the above code we locate the record where 'Prod = 'Prod1' and change its name to 'ABC' in the 'Products' worksheet of the 'MyBooks.xls' workbook. Run the application and click the 'Update Existing Record' button. Browse to 'MyBook.xls' and open it. You will find the following:
The First record was updated.
Limitations of Using Excel as a Database System
The following is some limitations when using ADO.NET with Excel:
- You can not delete a record or an entire datasheet using ADO.NET.
- You can not insert formulas in cells using ADO.NET.
To download the complete working example, just click here.
For further information
Refer to the online copy of Microsoft Developers Network at http://msdn.microsoft.com or use your own local copy of MSDN.
Tutorial toolbar:Tell A Friend | Add to favorites | Feedback |
Please enable JavaScript to view the comments powered by Disqus.comments powered by DisqusUsing ADO.Net and OleDB Provider
We can use the inbuilt OleDB provider for .Net to access Excel .xls spreadsheets, but before we do so, they need a little preparation. You can optionally create a Named Range to identify the table name, we also need column headings so if the spreadsheet does not contain column headings then you will need to add them.
Oledb Excel Connection String
Creating a Named Range
- With the spreadsheet open, select the data you wish to include in the query, including headings.
- Select the Insert Menu, then Name and select Define...
- Enter a name for the table and click Add.
- Close the dialogue and save the workbook.
Accessing the Data
I am just going to create a simple console application that will run through all the customers and display them on screen. The code itself is pretty straight forward, instead of SqlConnection, SqlCommand etc.. we are using OleDbConnection and OleDbCommand.
Updating Spreadsheets and Inserting Data
You can update records and insert data using regular SQL commands and execute the command through OleDb.