My answer is a little more lenghty but here it is. It has it's advantages along with disadvantages.
It assumes a class named
FieldsHere that
maps the excel columns, and the DB columns as well. Of course you need
to adapt it. You could also use an OleDbDataAdapter to fill a DataSet.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + FileNameHere + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\";";
OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT Field1Here,Field2Here,Field3Here "+
"FROM [sheetNameHere$] ORDER BY Field1Here ASC";
OleDbDataReader dr = null;
try
{
con.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
c = new FieldsHerre();
c.Field1Here= dr.IsDBNull(0) ? "Empty" : dr.GetString(0);
c.Field2Here= dr.IsDBNull(1) ? "Empty" : dr.GetString(1);
c.Field3Here= dr.IsDBNull(2) ? "Empty" : dr.GetString(2);
datas.Add(c);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (!dr.IsClosed)
{
dr.Close();
}
dr.Dispose();
if (con.State != ConnectionState.Closed)
{
con.Close();
}
con.Dispose();
con = null;
}
return datas;
}