How to read/write or select/insert data/rows to/from datasets/datatables to Excel

using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb; //important

string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\New.xls;Extended Properties=""Excel 8.0;HDR=Yes;ImportedMixedTypes=Text;""";

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = connStr;

OleDbCommand cmd = new OleDbCommand("insert into [Sheet1$] (ID, Name) values ('1', 'abc')", conn);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Make a blank .xls file in c:\, name it New.xls. In its first row, in the first two cells, write "ID" and "Name", this becomes the column names of first and second columns respectively.

Note: You have to use the connection string as it is. Just copy paste and change file path to your excel file. A few things to note:

"Data Source" has space in between. Its not "DataSource".

FilePath has to have double slash. Its "C:\\New.xls", not "C:\xls".

There is a space in "Extended Properties". Its not "ExtendedProperties".

There is a space in "Excel 8.0". Its not "Excel8.0".

Omit "IMEX=1" entirely or you would get "use updateable query error". I don't know the default value of IMEX so omit it altogether.

Use "HDR=Yes" to use your column names. Setting column names in excel file is explained above.

After

Extended Properties=

This solution work for .xls files only. It not work for .xlsx so don't try that.

there are two quotes and at the end there are three quotes (two to close Extended Properties and one to close the entire connection string).

In the query itself, enclose the sheet name in square brackets and use a $ sign after name of the sheet (the dollar sign is inside the square bracket). Use sheet name as you use table name in sql.

All values stored in excel are strings. So I had to put 1 in single quotes even though i meant it to be a numeric value.