Floating FB popout byF5debug

Read and Write Excel data using C#

 

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.

[more]

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter