将Excel文件数据导入到SqlServer数据库的三种方案

雨点打透心脏的1/2处 2024-04-19 16:14 42阅读 0赞

最近在一个项目中需要用到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();  
    }

转载于:https://my.oschina.net/ZhangYaqing/blog/1549710