What you want to see on DotNetFunda.com ?
Go to DotNetFunda.com
Twitter TwitterLinkedIn
YouTubeGoogle
 Online : 2554 |  Welcome, Guest!   Register  Login
Home > Articles > ASP.NET > How to read MS Excel file and populate into GridView

How to read MS Excel file and populate into GridView

3 vote(s)
Rating: 4.67 out of 5
Article posted by Sheonarayan on 8/17/2012 | Views: 9748 | Category: ASP.NET | Level: Intermediate | Points: 250 red flag


Many a times we are in the need of reading the MS Excel file and populate them into the GridView or validating the data of the excel sheet. In this article we are going to see how to populate the MS Excel data into the GridView and loop through them.

To demonstrate how to read the MS Excel data and populate into GridView or loop through the records, I have created a sample MS Excel file and here is how it looks like.


In this excel we have two sheets, Sheet1 and Sheet2. I have taken few columns and data as displayed above.

Now, the next step is to have a connection string to connect to the MS Excel file and here is the appSettings into my web.config file.

<appSettings>

<add key="Excel2003OleDBConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1&quot;"/>

<add key="Excel2007OleDBConnection" value="Provider=Microsoft.ACE.

OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 12.0 Xml;HDR=YES;IMEX=1&quot;"/>

</appSettings>

Here, the first appSettings is for the file that is .xls and the second appSetting is for .xslx (latest version of MS Excel). If you notice this carefully, we have a placeholder for the Data Source ie {0} where we will fill the name of the file at the time of using this connection string.

Next, I have kept a GridView where I am going to populate the data retrieved from the Excel sheet.

<div>

<asp:GridView ID="GridView1" runat="server" EnableViewState="false" />

</div>

Next is to write the code that connect to the MS Excel file and bring the data and here it goes.

Namespace to be used

using System.Data;

using System.Configuration;

using System.Data.OleDb;

Below is the actual code that does the magic.

protected void Page_Load(object sender, EventArgs e)

{

string file = Server.MapPath("~/App_Data/SampleData.xls");

string connStr = string.Format(ConfigurationManager.AppSettings["Excel2003OleDBConnection"], file);

DataTable table = new DataTable();

 

using (OleDbConnection conn = new OleDbConnection(connStr))

{

string sheet = @"SELECT * FROM [Sheet2$]"; // to avoid error write the sheet name in square bracket

using (OleDbCommand cmd = new OleDbCommand(sheet, conn))

{

conn.Open();

 

using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))

{

ad.Fill(table);

}

conn.Close();

};

}

 

GridView1.DataSource = table;

GridView1.DataBind();

 

// to loop through the rows use foreach loop

foreach (DataRow row in table.Rows)

{

string firstName = row["FirstName"].ToString();

}

}

In the Page_Load event of the page, we have got the MS Excel file name to connect to into the file variable. Then we have formatted the connection string using string.Format to ensure that the file name is placed at the placeholder of the connection string.

Remember, that if you are using wrong appSettings, you may get error. For example, if you are trying to use second appSettings to read the .xlsx file for which the driver is not installed on your system, you will get following error.

System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not 
registered on the local machine
Till MS Excel 2010 version, you should be using the first appSettings with .xls extension of the MS Excel file or you need the latest provider installed on your system.

As we are using OledDb in our connection string, so we will need to use Oledb .NET provider. Use the same type of code that you use to retrieve the data from a RDBMS like Sql Server. Instead of database table name in the SQL statement, write the MS Excel file Sheet name. Note that I have written the sheet name in square bracke [] as you may get error if you failed to do so. You should also suffix the sheet name with $ to avoid errors listed below.

If you fail to suffix $ in the Sheet name
System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not 
find the object 'Sheet2'.  Make sure the object exists and that you spell its 
name and the path name correctly.
If you fail to keep the sheet name in square  bracket "[]"
System.Data.OleDb.OleDbException: Syntax error in FROM clause.
Calling the Fill method of the SqlDataAdapter object will give you records from the respective sheet into the DataTable that you can use to populate the GridView or loop through and check for any errors in the records.



Hope this article was useful, thanks for reading and do let me know your feedback.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.

Page copy protected against web site content infringement by Copyscape
Found interesting? Add this to:



Please Sign In to vote for this post.

About Sheo Narayan

Experience:8 year(s)
Home page:http://www.snarayan.com
Member since:Tuesday, July 08, 2008
Level:HonoraryPlatinum
Status: [Microsoft_MVP] [Administrator]
Biography:Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001.

Connect me on Facebook | Twitter | LinkedIn | Blog

 Responses
Posted by: Laurenrodriguez | Posted on: 25 Aug 2012 05:01:31 AM | Points: 25

I just wanted to comment your article and say that I really enjoyed reading your post here. It was very informative. Keep it up and I’ll be back to read more soon

Posted by: Under56 | Posted on: 13 Sep 2012 06:13:39 AM | Points: 25

Hi Sheo Narayan, very good article. Sheo i need your help badly. actually i want to display asp.net source code in webpage when running website and also retain their colors like above your article is having. but i don't know tha method how to do it. please help me with this.

>> Write Response - Respond to this post and get points
Related Posts

A tutorial video which explains step by step to enable your asp.net application to consume application services like Membership,Roles,Profiles etc.

By using this Article you can learn how to change BackGround Color of a Row in Gridview when user moves mouse on a particular selecting Row.

Transforming the report output into PDF is an easy task now.

I have tried to give the steps for using the MENU control.

Guys has anybody faced problems with the Key, value pair collections not sorting the collections based on values . As all the collections sort themself on keys . As i needed a collection of Key,Value pairs which does sorting on values rather than key, For that reason i have created my own collection class which dose sorting on the values. And this is working perfectly for me .

More ...
About Us | Contact Us | The Team | Advertise | Software Development | Write for us | Testimonials | Privacy Policy | Terms of Use | Link Exchange | Members | Go Top
General Notice: If you find plagiarised (copied) contents on this page, please let us know the original source along with your correct email id (to communicate) for further action.
Copyright © DotNetFunda.Com. All Rights Reserved. Copying or mimicking the site design and layout is prohibited. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks. | 5/23/2013 10:11:23 PM