How to Import data from EXCEL to SQL Database in ASP.NET

Question

i am using this code for this solution.

 

using System;

using System.Data;

using System.IO;

using System.Data.OleDb;

using System.Configuration;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

OleDbConnection Econ;

SqlConnection con;

string constr,Query,sqlconn;

protected void Page_Load(object sender, EventArgs e)

{

}

private void ExcelConn(string FilePath)

{

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);

Econ = new OleDbConnection(constr);

}

private void connection()

{

sqlconn = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;

con = new SqlConnection(sqlconn);

}

private void InsertExcelRecords(string FilePath)

{

ExcelConn(FilePath);

Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");

OleDbCommand Ecom = new OleDbCommand(Query, Econ);

Econ.Open();

DataSet ds=new DataSet();

OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);

Econ.Close();

oda.Fill(ds);

DataTable Exceldt = ds.Tables[0];

connection();

//creating object of SqlBulkCopy

SqlBulkCopy objbulk = new SqlBulkCopy(con);

//assigning Destination table name

objbulk.DestinationTableName = "Employee";

//Mapping Table column

objbulk.ColumnMappings.Add("Name", "Name");

objbulk.ColumnMappings.Add("City", "City");

objbulk.ColumnMappings.Add("Address", "Address");

objbulk.ColumnMappings.Add("Designation", "Designation");

//inserting Datatable Records to DataBase

con.Open();

objbulk.WriteToServer(Exceldt);

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);

InsertExcelRecords(CurrentFilePath);

}

}

But i am getting this error

 

Server Error in '/InsertExcelFileRecords' Application.

---

The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Source Error:

Line 44:             OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ); 
Line 45:             Econ.Close(); 
Line 46:             oda.Fill(ds); 
Line 47:             DataTable Exceldt = ds.Tables[0]; 
Line 48:        connection();

Source File: c:\Users\ghg\Desktop\InsertExcelFileRecords\Default.aspx.cs Line: 46

I have already open vs in admin mode but not working.Anyone suggest me what should i do in this code.My excel sheet name is Sheet1