Ref:
Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net
Html will be same.
You need to check the column count if its equal then use foreach loop. Dont compare the Name of Excel DataTable column. Now you need to check the DataType of column for both DataTable.
Please refer this C# code.
Namespaces
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
C#
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
DataTable sqlTable = new DataTable();
DataTable dtExcelData = new DataTable();
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Salary",typeof(decimal)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
//***************Code to get the SQL Table in DataTable Start*************//
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * From tblPersons", conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(sqlTable);
}
}
}
//***************Code to get the SQL Table in DataTable End*************//
if (sqlTable.Columns.Count == dtExcelData.Columns.Count)
{
int columnCount = sqlTable.Columns.Count;
int counter = 0;
foreach (DataColumn col in sqlTable.Columns)
{
foreach (DataColumn col1 in dtExcelData.Columns)
{
if (col.DataType == col1.DataType)
{
counter++;
}
}
}
if (columnCount == counter)
{
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPersons";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
}
}