将Excel 表快速导入sqlserver

   /// <summary>
        /// 从Exce表中直接导入到数据库中
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void barButtonItem从Excel导入_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            killAllProcess();
             tz = false;
            barButtonItem查询_ItemClick(null, null);
            int n = 0;
            string tableName = "";
            string str = "";
            openFileDialog1.FileName = "配件编码表";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                str = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
                DataTable tb =GetExcelTableName(openFileDialog1.FileName) ;
                checkform checkform1 = new checkform(tb, "选择Excel表名");
                DialogResult result = checkform1.ShowDialog();
                OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1;/"");
                string connectionString = "Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;";
                if (result == DialogResult.OK)
                {
                    tableName = checkform1.ExcelName.Replace("$","");
                    try
                    {
                        DateTime t = DateTime.Now;
                        con.Open();
                        //string sql = "select * from [" + tableName + "]";
                        //--------------------------------------------------------------------------------------------------
                        //用bcp导入数据
                        TimeSpan sp = new TimeSpan();
                        TransferData(openFileDialog1.FileName, tableName, Conn.sqlstr);

                        //------------------------------------------------------------------------------------
                        //OleDbCommand cmd = new OleDbCommand(sql, con);
                        //OleDbDataReader read = cmd.ExecuteReader();
                        ////do
                        ////{
                        //while (read.Read())
                        //{
                        //    if (tz)
                        //    {
                        //        read.Close();
                        //        break;
                        //    }
                            //if (!DbHelperSQL.Exists("select count(*) from fittingscode where 配件编码=" + read["配件编码"]))
                            //{
                            //    bandedGridView1.AddNewRow();
                            //    DataRow row = bandedGridView1.GetDataRow(bandedGridView1.FocusedRowHandle);
                            //    for (int i = 0; i < bandedGridView1.Columns.Count; i++)
                            //    {
                            //        string strs = bandedGridView1.Columns[i].Caption;
                            //        try
                            //        {
                            //            row[strs] = read[strs];
                            //        }
                            //        catch (Exception)
                            //        {
                            //        }
                            //    }
                            //    //row["录入时间"] = DbHelperSQL.getservertime();
                            //    //row["录入人员"] = MainForm.UserName;
                            //    //row["录入部门"] = MainForm.UserDepartment;
                            //    n += 1;
                            //    Application.DoEvents();
                            //    bandedGridView1.CloseEditor();
                            //    bandedGridView1.UpdateCurrentRow();
                            //    sqlbuildAdapter.Update(ds);
                            //}

 

                        //}
                        //} while (read.NextResult());
                        //read.Close();
                        //con.Close();
                        //bandedGridView1.Bands[0].Caption = openFileDialog1.FileName + "-" + tableName;
                        sp = DateTime.Now - t;
                        MessageBox.Show("已经成功打开Excel表" + n + "条记录;耗时" + sp.TotalSeconds + "秒", "获取数据成功");
                    }
                    catch (OleDbException)
                    {
                        con.Close();
                        MessageBox.Show("至少有一个参数未指定");
                    }
                    catch (Exception ex)
                    {
                        con.Close();
                        MessageBox.Show(ex.ToString());
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            bandedGridView1.Bands[0].Caption = "配件编码表";
        }
        private void TransferData(string excelFile, string sheetName, string connectionString)
        {
            DataSet ds = new DataSet();
            try
            {
                //获取全部数据  
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);
                //如果目标表不存在则创建     
                string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim() + ")";
                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }
                //用bcp导入数据  
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    bcp.BatchSize = 100;//每次传输的行数       
                    bcp.NotifyAfter = 100;//进度提示的行数   
                    bcp.DestinationTableName = sheetName;//目标表  
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }      
        //进度显示 
       private void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
        {
            this.Text = e.RowsCopied.ToString();
            this.Update();
        }

 

 

        /// <summary>  
        /// 获取EXCEL的表名   
        /// </summary>  
        /// <param name="p_ExcelFile">Excel文件</param>  
        /// <returns>数据表</returns>  
        private static DataTable GetExcelTableName(string p_ExcelFile)
        {
            try
            {
                if (System.IO.File.Exists(p_ExcelFile))
                {
                    OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=/"Excel 8.0/";Data Source=" + p_ExcelFile);
                    _ExcelConn.Open();
                    DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null) as DataTable;
                    _ExcelConn.Close();
                    return _Table;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }
        private static void killAllProcess() // 杀掉所有winword.exe进程
        {
            System.Diagnostics.Process[] myPs;
            myPs = System.Diagnostics.Process.GetProcesses();
            foreach (System.Diagnostics.Process p in myPs)
            {
                if (p.Id != 0)
                {
                    string myS = "EXCEL.EXE" + p.ProcessName + " ID:" + p.Id.ToString();
                    try
                    {
                        if (p.Modules != null)
                            if (p.Modules.Count > 0)
                            {
                                System.Diagnostics.ProcessModule pm = p.Modules[0];
                                myS += "/n Modules[0].FileName:" + pm.FileName;
                                myS += "/n Modules[0].ModuleName:" + pm.ModuleName;
                                myS += "/n Modules[0].FileVersionInfo:/n" + pm.FileVersionInfo.ToString();
                                if (pm.ModuleName.ToLower() == "excel.exe")
                                    p.Kill();
                            }
                    }
                    catch
                    { }
                    finally
                    {
                    }
                }
            }
        }
        private void barButtonItem停止导入_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            tz = true;
        }