How to read MS Excel file and populate into GridView

Sheonarayan
Posted by in ASP.NET category on for Intermediate level | Points: 250 | Views : 29393 red flag
Rating: 4.67 out of 5  
 3 vote(s)

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> <!-- for .xls --> <add key="Excel2003OleDBConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1&quot;"/> <!-- for .xlsx --> <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.
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Laurenrodriguez on: 8/25/2012 | 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 on: 9/13/2012 | 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.
Posted by: Antisd on: 3/11/2015 | Points: 25
Kindly provide the entire web config file for this code. Want to know the connection settings that you've mentioned in it.
Posted by: Sheonarayan on: 3/11/2015 | Points: 25
The web.config connection string is already there in this article. Look at the first code snippet at the top of this article.

Hope this helps.

Thanks
Sheo Narayan

Login to post response

Comment using Facebook(Author doesn't get notification)