Read and Write Excel Data Using C#

In this post 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.
LogMeIn Remote PC Access - Free Trial

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','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); } } }