Resources » .NET programming » ASP.NET/Web Applications

How to read EXCEL data file in asp.net


Posted Date: 06-May-2008  Last Updated:   Category: ASP.NET/Web Applications    
Author: Member Level: Gold    Points: 20


This time, its about how to read a set of data or specific data from EXCEL file using C#.Net. Infact, we can use SQL querying in the EXCEL file data itself.



Hi all,
This time, its about how to read a set of data or specific data from EXCEL file using C#.Net. Infact, we can use SQL querying in the EXCEL file data itself.

Step-1: Include the connection string for the EXCEL file containing the filename and Provider settings.


string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("Filename.xls") + ";" + "Extended Properties=Excel 8.0;";


Step-2: Create a new connection object and open it for processing.


OleDbConnection objConn = new OleDbConnection(connectionString);
objConn.Open();


Step-3: Create a command object for querying and pass that command with the connection object created.


String strConString = "SELECT firstColumnName FROM [Sheet1$]";
//where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);


Step-4: Here, in this example i am going to use DataSet for reading and holding the data, for which i am creating a Data Adapter Object.


// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();


Step-5: Now, fill the data from the EXCEL file by querying with the command object and store them into the Dataset created.


// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData");


Step-6: Process the data for displaying with the Dataset, which you can set as datasource for many ASP.NET controls.


for (int i = 0; i < objDataset1.Tables[0].Rows.Count; i++)
{
document.Write("Data: " + objDataset1.Tables[0].Rows[i].ItemArray[0].ToString() + "
");
}


Step-7: As we are at the end, important thing is to Close the connection.


// Clean up objects.
objConn.Close();


To conclude


This is a simple Data reading from an EXCEL file, which doesnt classify the name for the columns to read about. We provide or create an EXCEL file having the Column name as the first row of data.

So our example may contain that as the column name and the sheet name as the table name.

NOTE: See the usage of Sheetname with "[" and "]" for specifying the query syntax properly.


Did you like this resource? Share it with your friends and show your love!

Responses to "How to read EXCEL data file in asp.net"
Author: Sebastian    13 Jun 2008Member Level: Gold   Points : 1
This is very informative. Thanks for sharing the details.


Author: Kolan    05 Mar 2010Member Level: Gold   Points : 0
good article, keep on posting some good stuff


Author: Syed Arif Murshed    05 Mar 2010Member Level: Gold   Points : 1
It is very good
You have sent very good information.
It is very useful
Keep it up.



Author: satyendra kr. Pandit    08 Mar 2010Member Level: Silver   Points : 1
This is very user friendly code and any one can use it. who worked on dataset using Sql server or any database connection.




Author: Lion    18 Mar 2010Member Level: Gold   Points : 0
This is a Best Example..


Author: Naveen Kumar    22 Mar 2010Member Level: Gold   Points : 0
thank you sir


Author: Arunkumar B    07 Oct 2010Member Level: Silver   Points : 1
Kudos Mr. Pravin.. Really I have spent more than 2 hours in finding code for excel data read... ur post is really helped me lot



Guest Author: abc     06 Mar 2012
hey i'm getting an exception "SEHException UnHandled : External component has thrown an exception" at objconn.open() :( pls pls help.


Guest Author: polarzbearz     04 Apr 2012
hi. i got error at step 6, i cant get the "document". how do i go around to fix this?


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Rameshkumar
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India