Read and Write Excel data using C#

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);

}

}

}

 enj......................