C# Excel导入到sql

将Excel文件数据库导入SQL Server的三种方案//方案一： 通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQL Server

openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
     FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
     string filePath = fileInfo.FullName;
     string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
    
     try
     {
         OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
         oleDbConnection.Open();
        
         //获取excel表
         DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

         //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
         string tableName = dataTable.Rows[0][2].ToString().Trim();
         tableName = "[" + tableName.Replace("‘","") + "]";

         //利用SQL语句从Excel文件里获取数据
         //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
         string query = "SELECT 日期,开课城市,讲师,课程名称,持续时间 FROM " + tableName;
         dataSet = new DataSet();

         //OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection);
         //OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
         OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,connExcel);
        
         oleAdapter.Fill(dataSet,"gch_Class_Info");

         //dataGrid1.DataSource = dataSet;
         //dataGrid1.DataMember = tableName;
         dataGrid1.SetDataBinding(dataSet,"gch_Class_Info");

         //从excel文件获得数据后，插入记录到SQL Server的数据表
         DataTable dataTable1 = new DataTable();
        
         SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate,
classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1);
        
         SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
        
         sqlDA1.Fill(dataTable1);

         foreach(DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows)
         {
             DataRow dataRow1 = dataTable1.NewRow();
            
             dataRow1["classDate"] = dataRow["日期"];
             dataRow1["classPlace"] = dataRow["开课城市"];
             dataRow1["classTeacher"] = dataRow["讲师"];
             dataRow1["classTitle"] = dataRow["课程名称"];
             dataRow1["durativeDate"] = dataRow["持续时间"];

             dataTable1.Rows.Add(dataRow1);
         }

         Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
         sqlDA1.Update(dataTable1);
        
         oleDbConnection.Close();

     }
     catch(Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }
}

//方案二： 直接通过SQL语句执行SQL Server的功能函数将Excel文件转换到SQL Server数据库

OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";

SqlConnection sqlConnection1 = null;

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
     string filePath = openFileDialog.FileName;

     sqlConnection1 = new SqlConnection();
     sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";

     //import excel into SQL Server 2000
     /*string importSQL = "SELECT * into live41 FROM OpenDataSource" + 
         "(‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source=" + "\"" + "E:\\022n.xls" + "\"" + 
         "; User ID=;Password=; Extended properties=Excel 5.0‘)...[Sheet1$]";*/

     //export SQL Server 2000 into excel
     string exportSQL = @"EXEC master..xp_cmdshell
‘bcp Library.dbo.live41 out " + filePath + "-c -q -S" + "\"" + "\"" +
         " -U" + "\"" + "\"" + " -P" + "\"" + "\"" + "\‘";
    
     try
     {
         sqlConnection1.Open();
        
         //SqlCommand sqlCommand1 = new SqlCommand();
         //sqlCommand1.Connection = sqlConnection1;
         //sqlCommand1.CommandText = importSQL;
         //sqlCommand1.ExecuteNonQuery();
         //MessageBox.Show("import finish!");
        
         SqlCommand sqlCommand2 = new SqlCommand();
         sqlCommand2.Connection = sqlConnection1;
         sqlCommand2.CommandText = exportSQL;
         sqlCommand2.ExecuteNonQuery();
         MessageBox.Show("export finish!");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.ToString());
     }
}

if(sqlConnection1!=null)
{
     sqlConnection1.Close();
     sqlConnection1 = null;
}

//方案三： 通过到入Excel的VBA dll，通过VBA接口获取Excel数据到DataSet

OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel files(*.xls)|*.xls";

ExcelIO excelio = new ExcelIO();

if(openFile.ShowDialog()==DialogResult.OK)
{
     if(excelio!=null)
         excelio.Close();

     excelio = new ExcelIO(openFile.FileName);
     object[,] range = excelio.GetRange();
     excelio.Close();

    
     DataSet ds = new DataSet("xlsRange");

     int x = range.GetLength(0);
     int y = range.GetLength(1);

     DataTable dt = new DataTable("xlsTable");
     DataRow dr;
     DataColumn dc;
    
     ds.Tables.Add(dt);

     for(int c=1; c<=y; c++)
     {
         dc = new DataColumn();
         dt.Columns.Add(dc);
     }
    
     object[] temp = new object[y];
    
     for(int i=1; i<=x; i++)
     {
         dr = dt.NewRow();

         for(int j=1; j<=y; j++)
         {
             temp[j-1] = range[i,j];
         }
        
         dr.ItemArray = temp;
         ds.Tables[0].Rows.Add(dr);
     }

     dataGrid1.SetDataBinding(ds,"xlsTable");
    
     if(excelio!=null)
         excelio.Close();
}

C# Excel导入到sql （转）,古老的榕树,5-wow.com