You can use the below approach for inserting xls data into database.
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
path = openFileDialog.InitialDirectory + openFileDialog.FileName;
}
string constring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0 Xml";
OleDbConnection con = new OleDbConnection(constring);
try
{
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
for (int j = 0; j < excelSheets.Length; j++)
{
string query = "SELECT * FROM [" +excelSheets[j]+ "]; ";
OleDbDataAdapter adp = new OleDbDataAdapter(query, constring);
DataSet ds = new DataSet();
string hCode = string.Empty;
string description = string.Empty;
adp.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
hCode = Convert.ToString(dr[1]);
description = Convert.ToString(dr[2]);
if (!(string.IsNullOrEmpty(hCode) && string.IsNullOrEmpty(description)))
{
dboject.InsertSiteNameIntoDatabase(hCode, description);
}
}
}
lblDisplay.Text = "Inserted successfully";
}
catch (Exception ex)
{
dboject.VMSLog(ex.Message);
}
finally
{
con.Close();
}