本文共 4508 字，大约阅读时间需要 15 分钟。

将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();}

 

 

转载地址：https://linuxstyle.blog.csdn.net/article/details/1536901 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！