Hi,
Use this below code and read the data from EXCEL and convert to Dataset and then do the process of inserting data from the Dataset to the SQL Server DataBase.
using System.Data.OleDb;
using System.IO;
using System.Data;
private DataSet GenerateExcelData()
{
DataSet ds = new DataSet();
try
{
string path = System.IO.Path.GetFullPath(Server.MapPath("Excel/InformationNew.xlsx"));
if (Path.GetExtension(path) == ".xls")
{
oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
}
else if (Path.GetExtension(path) == ".xlsx")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
}
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
return ds;
}
catch (Exception ex)
{
return ds;
}
finally
{
oledbConn.Close();
oledbConn.Dispose();
}
}