如何将数据从excel导入到SQL Server [英] How to import data from excel to sql server

问题描述

您好，

如果员工在sqltable中存在，我要求我需要从excel填写一个coloumn数量到sql server。请告诉我该怎么办...

我现在尝试的内容粘贴在下面..

Hello,
I have requirement that i need to fill an amount coloumn from excel to sql server if employee exist in sqltable..please tell me how can i do this ..
What i have tried now has pasted below..

protected void UploadExcel(object sender,EventArgs e)
  {
      string Constr = System.Configuration.ConfigurationManager.ConnectionStrings["HRGold"].ConnectionString;
      string path = fplUploadExcel.PostedFile.FileName;
      string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
      OleDbConnection excelConnection = new OleDbConnection(ExcelConstr);
      OleDbCommand oldbcmd = new OleDbCommand("Select [EmployeeId],[EmployeeName],[Amount] From [Sheet1$] where EmployeeReferenceId='" + ddlEmployeeId.SelectedItem.Value + "'", excelConnection);
      excelConnection.Open();
      OleDbDataReader reader = oldbcmd.ExecuteReader();
      SqlBulkCopy sqlBulk = new SqlBulkCopy(Constr);

      while (reader.Read())
      {
          if (ddlEmployeeId.SelectedItem.Value.Equals(reader))
          {
              sqlBulk.DestinationTableName = "PFDummyExcel";
              sqlBulk.WriteToServer(reader);
          }
          else
          {
              lblError.Text = "Employee Not Found";
          }
      }
      excelConnection.Close();
  }

推荐答案

其中EmployeeReferenceId =' + ddlEmployeeId.SelectedItem.Value + < span class =code-string> '，excelConnection）;
excelConnection.Open（）;
OleDbDataReader reader = oldbcmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（Constr）;

while （reader.Read（））
{
if （ddlEmployeeId.SelectedItem.Value.Equals（reader））
{
sqlBulk.DestinationTableName = PFDummyExcel;
sqlBulk .WriteToServer（reader）;
}
else
{
lblError.Text = Employe e Not Found;
}
}
excelConnection.Close（）;
}

where EmployeeReferenceId='" + ddlEmployeeId.SelectedItem.Value + "'", excelConnection); excelConnection.Open(); OleDbDataReader reader = oldbcmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(Constr); while (reader.Read()) { if (ddlEmployeeId.SelectedItem.Value.Equals(reader)) { sqlBulk.DestinationTableName = "PFDummyExcel"; sqlBulk.WriteToServer(reader); } else { lblError.Text = "Employee Not Found"; } } excelConnection.Close(); }

我几个星期前在我的项目中完成了这个，我的项目的一些代码在Table.I中上传excel数据希望我的代码可以帮到你。

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\"";
             }

             //code to get list of all worksheets in excel file
             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;

         //1- SQL Bulk Copy  OK
         try
         {
             string excelConString = System.Configuration.ConfigurationManager.ConnectionStrings["NewConnection"].ConnectionString;

             SqlBulkCopy bulkcopy = new SqlBulkCopy(excelConString);
             {
                 bulkcopy.DestinationTableName = tableName;
                 bulkcopy.BulkCopyTimeout = 120;
                 bulkcopy.WriteToServer(reader);
             }
         }

         //2 - when bulk copy failed
         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;
             //check coulmn name is same or not
             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>

尝试以下代码

Try this below code

string Connectionstring = string Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file_path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";// Change the connection string acording to ur excel version
OleDbConnection myXLConnection = new OleDbConnection(Connectionstring);
OleDbCommand myXLCommand = new OleDbCommand("select * from [Sheet1

这篇关于如何将数据从excel导入到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！