Click here to Skip to main content
13,147,050 members (68,750 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


26 bookmarked
Posted 6 Jan 2012

Read Excel in ASP.NET

, 6 Jan 2012
Rate this:
Please Sign up or sign in to vote.
Read Excel in ASP.NET
Read Data From an Excel File (.xlsx) in ASP.NET

In this article, we will see how to display data from an Excel spreadsheet using ASP.NET.
We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a GridView.

Let us get started:
Step 1: Open Visual Studio > File > New >Website > Under Templates, click ASP.NET WebSite and choose either Visual C# or Visual Basic as the language.
Select a location and click Ok.

Step 2: We will create two Excel sheets and add them to the project.
One Excel sheet will be created in Office 2003(.xls) and
the other one using Office 2007(.xlsx).
Add 4 columns called ID, Name, Age and City to the ‘Sheet1’.
Also add some data into the columns.
Once these Excel files are created, add them to your project.
To add them to the project, right click project > Add Existing Item > Add the two Excel files.

Step 3: We will now create a web.config file to store the connection string information.
Right click project > Add New Item > Web.config. Add the following entries to the file:
            <add name="xls" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0" />
            <add name="xlsx" connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0" />

As you can observe, the connection string for xlsx (Excel 2007) contains 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.

Step 4: Add a GridView to the Default.aspx page. We will extract data from the Excel file and bind it to the GridView.

Step 5: Let us now create a connection to the Excel file and extract data from it. Before that, add a reference to System.Data.OleDb;

C# code:
protected void Page_Load(object sender, EventArgs e)
        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
            // Open connection
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Customer");
            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            // Close connection

All set!! Run the application and see the data getting displayed in the GridView. If you want to target the Excel 2007 sheet, just change xls to xlsx in the ConfigurationManager.ConnectionString.

I hope this article was useful and I thank you for viewing it.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

You may also be interested in...


Comments and Discussions

QuestionSuggestion regarding connection string. Pin
Lad Kunal11-May-15 0:38
memberLad Kunal11-May-15 0:38 
GeneralMy vote of 2 Pin
Marcelo Lujan [El Bebe.Net ]4-May-12 15:11
memberMarcelo Lujan [El Bebe.Net ]4-May-12 15:11 
GeneralRe: My vote of 2 Pin
chetan virkar4-May-12 21:20
memberchetan virkar4-May-12 21:20 
GeneralRe: My vote of 2 Pin
Sangram Nandkhile28-Aug-15 4:50
memberSangram Nandkhile28-Aug-15 4:50 
GeneralThank You for this simple example Pin
King Coffee2-May-12 10:40
memberKing Coffee2-May-12 10:40 
GeneralRe: Thank You for this simple example Pin
chetan virkar7-May-12 21:02
memberchetan virkar7-May-12 21:02 
Generalthank u! Pin
chetan virkar6-Jan-12 5:45
memberchetan virkar6-Jan-12 5:45 
GeneralReason for my vote of 5 Nice work. Pin
Richard MacCutchan6-Jan-12 0:48
mvpRichard MacCutchan6-Jan-12 0:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 6 Jan 2012
Article Copyright 2012 by chetan virkar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid