In this post I will show you with a hands-on example on how to accompish a common task. We will use an asp.net application to read data from an excel file. I have excel 2007 installed and I have several .xlsx files with data.

A lot of people believe that most of the data nowdays are stored in databases. You cannot imagine how much data is saved in excel files.

We will connect to the excel file with the appropriate provider. 

I will be using VS 2010 Ultimate edition and VB.Net to create a simple asp.net application.

1) Launch Visual Studio 2010/2008.Express edition will suffice.

2) Create a web site with an appropriate name. Use VB.Net as the development language.

3) Create an excel file ( I assume you have excel 2007 or 2003 ) and give it an appropriate name. Insert some data in it.

4) Open your web.config file and type in the  <connectionStrings> section and type

<connectionStrings>		

<add name="excel2003" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:\Users\fofo\Desktop\test.xls;Extended Properties=Excel 8.0"/>

<add name="excel2007" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=C:\Users\fofo\Desktop\test.xlsx;Extended Properties=Excel 12.0"/>	

</connectionStrings>

5) As you can see we have information in our <connectionStrings> section that will help us read from an .xls or .xlsx file.I have a file that is called test.xls and another one called test.xlsx

6) Add a GridView web server control in the default.aspx page and a button. Name them as you like.

7) Click the button and in the event handling routine type

 Dim myconstring As String = _

 ConfigurationManager.ConnectionStrings("excel2003").ConnectionString
       
 Dim oledbConnection As OleDbConnection = New OleDbConnection(myconstring)
 
 
 Try
 
 oledbConnection.Open()
 
            
 Dim mycommand As OleDbCommand _

 = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConnection)
 
            
 Dim oledadapter As OleDbDataAdapter = New OleDbDataAdapter()
 
 oledadapter.SelectCommand = mycommand
 
 Dim ds As DataSet = New DataSet()
 
           
 oledadapter.Fill(ds, "Marks")
 
          
 GridView1.DataSource = ds.Tables(0).DefaultView
 GridView1.DataBind()
 Catch ex As Exception
 
 Label1.Text = ex.Message()
 
 Finally
 
 oledbConnection.Close()
 End Try

 8) I will try to explain what I am doing in the code.

• I find the connection string using the ConfigurationManager class

• I create the connection object

• I put my main bit of code in a Try Catch statement

• I open the connection

• I create a command object and select the data

• I create an adapter object and then I create a dataset

• I fill in the Dataset and bind the data to the GridView

• Then I close the connection
  

9) Run your application and see the data from the excel file appearing in the GridView control. If you have Excel 2007 installed just change this line of code.

 Dim myconstring As String = _

 ConfigurationManager.ConnectionStrings("excel2007").ConnectionString

Email me if you need the code.

Hope it helps!!!!!