.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> Azure >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Read and Write Excel data using C#

Posted By:Karthikeyan Anbarasan       Posted Date: June 28, 2011    Points: 200    Category: Azure    URL: http://www.dotnetspark.com  

In this article we are going to see how to use an excel sheet as a source to load data in windows application or a web application
 

Introduction:

In this article we are going to see how to use an excel sheet as a source to load data in windows application or a web application and use the same to export back to the excel sheet using C# and VB.net. This requirement is straight forward used in day-to-day activity, but most of the time end up messing in some section of the code.

Overview:

Normally when requirement comes with accessing the data from the excel sheet, we first choose to start with the office interop assemblies (Office Automation Assemblies) and make a connection to the excel sheet and start processing but it has some disadvantages using it over the web(Check this link for more details Issues). So the alternate option was to use the OLEDB Provider to read the data and use it for the front end with an additional parameter of passing the connection string to the excel sheet.

Now we consider that we have things ready to export and import data to the excel sheet and the data is available in a dataset or a data table. We can use the below code snippet's to get the data passed to and fro to the excel.

The Below code snippet will be used to export the data from local to EXCEL Sheet.

Code : Writing data to Excel sheet

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=D:\MySamplefile.xls; Extended Properties=Excel 8.0;"
 
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "CREATE TABLE [EmpTable$](EmpFirstName Char(100), EmpLastName char(100), EmpDept char(250))";
command.ExecuteNonQuery();
}
//Add values to the table (EMPTable) in the Worksheet
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "INSERT INTO TABLE [EmpTable$](EmpFirstName ,EmpLastName ,EmpDept ) VALUES('Karthik','Anbu','karthik.Anbu@xyz.com')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO TABLE [EmpTable$](EmpFirstName ,EmpLastName ,EmpDept ) VALUES('Arun','Kumar','Arun.Kumar@xyz.com')";
command.ExecuteNonQuery();
}
 
 

Code : Reading data from Excel sheet

DataTable dt;

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=D:\MySamplefile.xls; Extended Properties=Excel 8.0;"
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "SELECT * FROM [EmpTable]";
using(OleDbDataAdapter adapter =new OleDbDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(dt);
}
}
}
 
 

Conclusion:

So in this article we have seen on how to do small manipulation of reading and writing excel data using C# which we normally require in our day-to-day coding.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend