I done this few weeks ago in my project,some code of my project to upload excel data in Table.I hope my code help you.
public class RecordImport
{
List<string> ErrorList = new List<string>();
public string UploadFile(string tPath, int tId)
{
string extnsn = Path.GetExtension(tPath).ToString().ToUpper().Trim();
string connString = "";
string strFileType = ".xlsx";
string path = tPath;
string query = "";
OleDbConnection conn;
if (extnsn == ".CSV")
{
connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", Path.GetDirectoryName(tPath));
query = "SELECT * FROM [" + Path.GetFileName(tPath) + "]";
}
else
{
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
conn = new OleDbConnection(connString);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); ;
string[] excelSheets = { };
if (dt != null)
{
excelSheets = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
}
conn.Close();
query = "SELECT * FROM [" + excelSheets[0].ToString().Trim() + "]";
}
conn = new OleDbConnection(connString);
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader reader = cmd.ExecuteReader();
var context = new MountSinai_SysEntities1();
string tableName = context.Table.Find(tId).tableName;
var tableFieldList = from a in context.TablesField
where a.tableId == tId
select a.fieldName;
var fieldListData = from b in context.TablesField
where b.tableId == tId
select b;
try
{
string excelConString = System.Configuration.ConfigurationManager.ConnectionStrings["NewConnection"].ConnectionString;
SqlBulkCopy bulkcopy = new SqlBulkCopy(excelConString);
{
bulkcopy.DestinationTableName = tableName;
bulkcopy.BulkCopyTimeout = 120;
bulkcopy.WriteToServer(reader);
}
}
catch (Exception e)
{
reader.Dispose();
reader.Close();
ErrorList.Add("SQL Bulk Copy Fail : " + e.Message.ToString());
ArrayList validList = new ArrayList();
ArrayList validListDataTypeId = new ArrayList();
var reader1 = cmd.ExecuteReader();
int b = 0;
foreach (var field in fieldListData)
{
string xlsColumnName = reader1.GetName(b).ToUpper().Trim();
if (field.fieldName != "Id")
{
if (field.fieldName.ToString().ToUpper() == xlsColumnName)
{
validList.Add(field.fieldName.ToString().ToUpper().Trim());
validListDataTypeId.Add(field.displayFieldTypeId.ToString().ToUpper().Trim());
}
else
{
return "column name or number not Match";
}
b++;
}
}
string cellValue;
while (reader1.Read())
{
int keyFlag = 0;
StringBuilder queryBuilder = new StringBuilder("Insert into " + tableName + " Values (");
StringBuilder queryBuilderCopy = new StringBuilder("Insert into " + tableName + "_Temp Values (");
for (int a = 0; a < validList.Count; a++)
{
cellValue = reader1[a].ToString().Trim();
if (cellValue != "")
{
if ( Convert.ToInt32( validListDataTypeId[a]) == 21)
{
if (keyFlag == 0)
{
queryBuilder.Insert(0, "OPEN SYMMETRIC KEY WDCSKey DECRYPTION BY CERTIFICATE WDCSCert ");
keyFlag = 1;
}
queryBuilder.Append("ENCRYPTBYKEY(KEY_GUID('WDCSKey'), '" + cellValue + "'),");
}
else
{
queryBuilder.Append("'"+cellValue+"',");
}
}
}
queryBuilder.Remove(queryBuilder.Length - 1, 1);
queryBuilder.Append(")");
try
{
var Dbcontext = new DBEntities();
var returnValue = Dbcontext.Database.SqlQuery<int32>(queryBuilder.ToString()).First();
queryBuilder.Clear();
}
catch(Exception ex)
{
queryBuilder.Clear();
}
}</int32></string></string>