将Excel文件数据库导入SQL Server的三种方案

        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 Librar

凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务，公司网站：http://www.lingzhong.cn 为了给广大客户了解更多的技术信息，本技术文章收集来源于网络,凌众科技尊重文章作者的版权，如果有涉及你的版权有必要删除你的文章，请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!