I need to read and write the values from excel in C#?

Hi,

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

}

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

}

Reference :

http://www.c-sharpcorner.com/UploadFile/ae35ca/read-and-write-excel-data-using-C-Sharp/

http://www.aspdotnet-suresh.com/2010/06/read-data-from-excel-sheet-and-bind-it.html

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Arul R10y ago
Accepted answer
Kindly refer the below links, Hope this will solve your problem.

http://www.gemboxsoftware.com/support/articles/read-xls-xlsx-ods-csv-html-net

http://www.codeproject.com/Tips/801032/Csharp-How-To-Read-xlsx-Excel-File-With-Lines-of

http://codereview.stackexchange.com/questions/47227/reading-data-from-excel-sheet-with-exceldatareader

http://stackoverflow.com/questions/657131/how-to-read-data-of-an-excel-file-using-c

http://www.c-sharpcorner.com/UploadFile/ae35ca/read-and-write-excel-data-using-C-Sharp/

Let me know for any thing more....
If your problem solved means kindly Accept my answer
0
Ankur Mistry10y ago
check : http://www.c-sharpcorner.com/UploadFile/ae35ca/read-and-write-excel-data-using-C-Sharp/
0
Rajeesh Menoth10y ago
Hi,

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

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

}

Reference :

http://www.c-sharpcorner.com/UploadFile/ae35ca/read-and-write-excel-data-using-C-Sharp/

http://www.aspdotnet-suresh.com/2010/06/read-data-from-excel-sheet-and-bind-it.html
0