Here is how can do so.
private void button1_Click(object sender, EventArgs e)
{
try {
DataTable sheetTable = loadSingleSheet("C:\\excelFile.xls", "Sheet1$");
updateDataTable(sheetTable);
} catch (Exception ex) {
System.Response.Write(ex.Message);
}
}
public void updateDataTable(DataTable dt)
{
using (SqlConnection connection = new SqlConnection(connectionString)) {
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
adapter.Update(ds);
}
}
private OleDbConnection returnConnection(string fileName)
{
return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}
private DataTable loadSingleSheet(string fileName, string sheetName)
{
DataTable sheetData = new DataTable();
using (OleDbConnection conn = this.returnConnection(fileName)) {
conn.Open();
OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
sheetAdapter.Fill(sheetData);
}
return sheetData;
}
It might need some tuning but this is how you will do basically.