C# Excel导入SQL数据库

//导入的Excel表列数与数据库表的列数要相同

private void button1_Click(object sender, EventArgs e)

        {

            setFile();

        }

 

        private void setFile()

        {

            string connString = "server=localhost;uid=sa;pwd=123;database=master";

            OpenFileDialog fd = new OpenFileDialog();//提示用户打开要导入到数据库的Excel文件

            if (fd.ShowDialog() == DialogResult.OK)

            {

                TransfersqlData(fd.FileName, "sheet1", connString);

            }

        }

 

        public void TransfersqlData(string exceFile, string sheetName, string connectionString)

        {

            DataSet ds = new DataSet();

            try

            {

                //获取Excel某表的全部信息

                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + exceFile + ";" + "Extended Properties=Excel 8.0;";

                OleDbConnection conn = new OleDbConnection(strCon);

                conn.Open();

 

                string strCmd = string.Format("Select * from [{0}$]", sheetName);

                OleDbDataAdapter oda = new OleDbDataAdapter(strCmd, conn);

                oda.Fill(ds, sheetName);

                if (ds.Tables.Count == 0)

                {

                    return;

                }

                //将获取的Excel信息写入数据库

                using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))

                {

                    this.progressBar1.Value = 0;

                    sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);//设置进度

                    sbc.DestinationTableName = "InfoForm";//设置数据库表名

                    sbc.WriteToServer(ds.Tables[0]);

                    MessageBox.Show("恭喜导入成功！");

                }

            }

            catch

            {

                if (MessageBox.Show("所导入的文件与数据库不匹配！", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error) == DialogResult.OK)

                {

                    setFile();

                }

            }

        }