SQL Server中导Excel数据三种非常好的方案 -技术开发专区

SQL Server中导Excel数据三种非常好的方案

作者：dlb 编辑：胡铭娅 2010-08-05 08:54 来源：dlb的�

【IT168 技术应用】最近在一个项目中需要用到Excel文件导入数据库的功能，本人很懒，所以到网上搜了一堆方法，但是通过对比，觉得一下三种是比较好用或者不是很常见的方法，希望对大家有所帮助。

　　方案一：通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQL Server，这种方法的优点是非常的灵活，可以对Excel表中的各个单元格进行用户所需的操作。

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");
        //从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();
}

　　当然还有其他一些方法，如遍历Excel文件中的数据然后构造sql语句，直接利用sql操作Excel文件导入数据库等，这些都是很常见的方法，因此就不再做收录了。最后说明下，以上的方法是我从网上找的源码并做了一定的修改。

关注我们