How to Read & Write Data in Excel Files with C#.NET 2005

Have you ever tried to read and write data in any Excel file with C#.NET 2005 or higher. The following code shows how to read and write with an Excel file having the following structure:

Structure of Excel File

SrNo

Ename

Salary

1. For Reading the Data from Excel File.(Format .xls/.xlss)

//Code for reading the contents of Excel file and display the same on datagridview

private void button1_Click(object sender, EventArgs e)

{

//FOR READING THE EXCEL FILE

OleDbConnection vConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\rohit.xls;Extended Properties=Excel 12.0");

//Reading the Content from a sheet named Employees

String strSQL = "SELECT * FROM [Employees$]";

OleDbCommand cmd = new OleDbCommand(strSQL, vConn);

DataSet ds = new DataSet();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

da.Fill(ds,"emp");

//Displaying the data read from Excel and displayed on datagridview.

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "emp";

vConn.Close();

}

2. For Writing the Data into Excel File.(Format .xls/.xlss)

private void button2_Click(object sender, EventArgs e)

{

//WRITING TO THE EXCEL FILE

OleDbConnection vConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\rohit.xls;Extended Properties=Excel 12.0");

// Write to Excel file

vConn.Open();

String vSrno="100";

String vEname="Oh My God";

String vSalary="90000";

String strSQL = "insert into [Employees$] ([SRNO], [ENAME], [SALARY]) values( '" + vSrno + "', '" + vEname + "','"+vSalary+"')";

OleDbCommand cmd = new OleDbCommand(strSQL, vConn);

cmd.ExecuteNonQuery();

MessageBox.Show("inserted");

vConn.Close();

}

The above code simplifies the process of reading and writing into the excel file.