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 and used in day-to-day activity, but is often messed up in some section of the code.

Overview

Normally when there is a requirement for accessing data from an 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 that has some disadvantages such as using it over the web (Check this link for more details Issues). So the alternate option is 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 data from and import data to the Excel sheet and the data is available in a dataset or a data table. We can use the following code snippets to get the data passed to and from Excel.
The following 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','[email protected]')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO TABLE [EmpTable$](EmpFirstName ,EmpLastName ,EmpDept ) VALUES('Arun','Kumar','[email protected]')";
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 how to do a small manipulation of reading and writing Excel data using C# which we normally require in our day-to-day coding.