将Excel文件数据库导入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();
 }