席慕容的明镜:将exce数据l导入sqlser 完整源码

///////////////////////////////////////////////////////从excel导入sqlser 完整源码//////////////////////////////////////////////////
using System.Data.SqlClient;    //用于SqlServer数据库操作
using System.Data.OleDb;        //用于Excel数据库操作
#region Excel导入到Sqlserver

        //指定的Excel文件名
        private string strFileName;
        //是否已经获取到Excel文件的路径
        private bool hasFile;
        //是否已经把Excel读取到Datase中
        private bool hasContent;
        //从Excel中读取到内容
        private DataSet dsExcel;

        public ExcelOperate()
        {
            InitializeComponent();
            //初始化为false
            hasFile = false;
            hasContent = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {}

========================
Excel数据库访问操作读入ds   绑定显示控件、显示
========================        public void ShowExcelContent()
        {
            //下面是Excel数据库访问操作：
                //连接字符串
                string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + strFileName + "';Extended Properties=Excel 8.0";
                //要执行的sql语句
                string strSql = "select * from [Sheet1$]";
                //创建OleDb连接对象
                OleDbConnection oleDbCon = new OleDbConnection(strCon);
                //创建OleDbDataAdapter
                OleDbDataAdapter oleDbDa = new OleDbDataAdapter(strSql, oleDbCon);
                //实例化ds
                dsExcel = new DataSet();
                //打开连接
                oleDbCon.Open();
                //从数据库读取内容并填充到ds中
                oleDbDa.Fill(dsExcel, "Info");
                //关闭连接
                oleDbCon.Close();

                //绑定数据源
                bindingSource1.DataSource = dsExcel.Tables[0];
                //下面该句是bindingNavigator的数据绑定方法，但用该语句会提示错误为：bindingNavigator为只读。所以，只能在属性栏里的BindingSoure属性里修改
                //bindingNavigator1.DataBindings = bindingSource1;
                //显示到DataGridView
                dataGridView1.DataSource = bindingSource1;
                //标记ds有内容
                hasContent = true;
        }

========================
ds中数据插入sqlser========================       
public void ToSqlServer()
        {
            //要执行的sql语句，暂时无.这里采用Stringbuilder类，因为接下来字符串连接操作比较多
            StringBuilder strbSql = new StringBuilder();
            //SqlServer连接语句，该实例数据库为“MyDataBase”
            string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True";
            //创建连接
            SqlConnection sqlCon = new SqlConnection(strCon);
            //创建一个空的sql执行对象
            SqlCommand sqlCom = new SqlCommand();
            //把连接对象赋予sqlCom
            sqlCom.Connection = sqlCon;
            //打开连接
            sqlCon.Open();
            //用try catch 语句，捕抓错误
            try
            {
                //连续往SqlServer表里插入数据
                for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
                {
                    //要执行的insert语句：有一点要注意，在SqlServer中用  '' 标记字符串，这里记得要添加
                    strbSql.Append("insert into CutClassTable(StudentID, Name, CutClassSum, Cause) values('");
                    for (int j = 0; j < 3; j++)
                    {
                        strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[j].ToString() + "','");
                    }
                    strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[3].ToString() + "')");

                    //执行sql语句
                    string strSql = strbSql.ToString();
                    sqlCom.CommandText = strSql;
                    sqlCom.ExecuteNonQuery();
                    //strbSql里面内容要清除，否则会叠加的，提示信息重复插入等信息
                    strbSql.Remove(0, strbSql.Length);
                }
                //插入成功提示
                MessageBox.Show("导入SqlServer成功！请查看！：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (Exception ex)
            {
                //失败提示
                MessageBox.Show("导入SqlServer过程中发生错误！/n错误提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                //关闭连接
                sqlCon.Close();
            }
        }

        ///

        /// 选择Excel文件
        ///

        ///
        ///
        private void bntFindFile_Click(object sender, EventArgs e)
        {
            //文件选择对话框
            OpenFileDialog FilePath = new OpenFileDialog();
            //判断是否选择好文件
            if (FilePath.ShowDialog() == DialogResult.OK)
            {
                hasFile = true;
                strFileName = FilePath.FileName;
                tbFileName.Text = strFileName;
            }
        }

        ///

        /// 显示Excel内容到DatagridView
        ///

        ///
        ///
        private void bntShowExcel_Click(object sender, EventArgs e)
        {
            //判断是否已经选择好文件
            if (hasFile)
            {
                //显示Excel内容到DatagridView
                ShowExcelContent();
            }
        }

        private void btnToSqlServer_Click(object sender, EventArgs e)
        {
            //判断ds是否有内容
            if (hasContent)
            {  
                //导入到SqlServer
                ToSqlServer();
            }
        }
        #endregion