通过OleDB方式将Excel数据导入Sql数据库

 

通过OleDB方式将Excel数据导入Sql数据库，通过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());
    }
}