Microsoft .Net provides various methods to interact with Microsoft Office programs through the use of ADO.Net. This tutorial will look at the methods and techniques for reading data stored in Excel spreadsheets.

Using 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.

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.

Excel OLEDB

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.

The only problem I am having with reading an Excel file this way, is that it always returns my rows & fields in alphabetical order! I want the select statement to return everything in the order it was entered in the Excel file. Is there any way to prevent the provider from "automatically" ordering the columns & rows in alphabetical order? To see what I'm talking about, change the Account Number in your first row from "00001" to "10000" and watch it appear at the end of the result set 'all of a sudden'.

If you want to read data from a .xlsx file, you have to use Microsoft.ACE.OLEDB.12.0 as the provider. This is the new Access database engine OLE DB driver and is also capable of reading Excel 2003. Use it to read xlsx (Excel 2007) data.

Get more info on Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET here...

Sheet1$ refers to the sheet name. If you have changed the sheet name then you must update the code accordingly. Also, I have not tried this with Excel 2007 which uses a different file format. If you are not doing so already you may wish to try exporting as Excel 2000.

viranjuda, It's difficult to diagnose a problem without seeing the code.

There are several things that may cause this error including file permissions, file location (local or network), are you calling the code from a console application or ASP.Net? What is the query you are trying to execute?

If you could provide more information we may be better able to assist.

This code was built and compiled on Windows XP SP3 with Visual Studo 2005 and Office 2000 installed. It has also been tested on a fresh copy of Windows XP with SP2 and .Net framework 2, MS Office is not installed.

Create a standard console application with the above code, no additional references are required, System, System.Data and System.Xml automatically referenced.

@anonymous, please can you provide any more details of your error (project type, references, visual studio version etc..)

@Pratap, please could you also provide some more details, such as the error message?

Error 2 The type 'System.ComponentModel.Component' is defined in an assembly that is not referenced. You must add a reference to assembly 'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. D:IdeasCSExplorerExplorer.cs 38 13 Explorer.

I'm getting this error message, hat .Net environment you are supose to have, what refrences, etc. very amateurish and not useful at all.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.

Save my name, email, and website in this browser for the next time I comment.

About the Author

Tim Trott

Tim Trott is a creative photographer, traveller, astronomer and software engineer with a passion for self-growth and a desire for personal challenge.

Hi, I'm Tim Trott. I'm a creative photographer, traveller, astronomer and software engineer with a passion for self-growth and a desire for personal challenge.

This is my website, a place for me to share my experiences, knowledge and photography. I love to help people by writing articles and tutorials about my hobbies that I'm most passionate about. I hope you enjoy reading my articles as much as I enjoy writing them.