把excel文件数据导入到sql数据库表中

最新推荐文章于 2024-07-12 14:33:35 发布

lidan00123

最新推荐文章于 2024-07-12 14:33:35 发布

阅读量404

点赞数

文章标签： excel sql 数据库 dataset string sqlserver

本文链接：https://blog.csdn.net/lidan00123/article/details/6455232

版权

/// <summary>
        /// 导入一个excel文件
        /// </summary>
        /// <param name="FileName">要导入的文件名</param>
        /// <returns>返回一个集合</returns>
        private DataSet GetDataSet(string FileName)
        {
            //连接字符串
            string oledbConStr = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source ='" + FileName + "';Extended Properties=Excel 8.0";
            //sql语句
            string oleSql = "select * from [Sheet1$]";
            //创建con对象链接数据库
            OleDbConnection oledbCon = new OleDbConnection(oledbConStr);
            //创建dataAdapter填充数据集
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oleSql, oledbCon);
            //创建DataSet对象
            DataSet ExcelDs = new DataSet();
            //打开链接
            oledbCon.Open();
            //填充数据集
            oledbAdapter.Fill(ExcelDs);
            //关闭链接
            oledbCon.Close();
            return ExcelDs;
        }
        /// <summary>
        /// 将excel中的考勤信息导入数据库
        /// </summary>
        private void AddSourceFromExcel()
        {
            DataSet excelDs = new DataSet();
            //要执行的sql语句，暂时无.这里采用Stringbuilder类，因为接下来字符串连接操作比较多
            StringBuilder sqlBuilder = new StringBuilder();
            //创建连接
            string ConStr = "Data Source=.;Initial Catalog=Employee;Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(ConStr);
            //选择一个打开的文件
            OpenFileDialog openFile = new OpenFileDialog();
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                excelDs = GetDataSet(openFile.FileName);
            }
            //创建命令
            SqlCommand com = con.CreateCommand();
            //打开连接
            con.Open();
            //捕抓错误
            try
            {

                //循环把数据插入到sqlServer中
                for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++)
                {
                    sqlBuilder.Append("insert into Checking (Month ,Day ,EmpId) values ( '");
                    for (int j = 0; j < 2; j++)
                    {
                        sqlBuilder.Append(excelDs.Tables[0].Rows[i].ItemArray[j].ToString() + "','");

}
sqlBuilder.Append(excelDs.Tables[0].Rows[i].ItemArray[2].ToString() + " ')");

                    //执行sql语句
                    string sql = sqlBuilder.ToString();
                    com.CommandText = sql;
                    com.ExecuteNonQuery();

                    sqlBuilder.Remove(0, sqlBuilder.Length);
                }
                MessageBox.Show("考勤导入成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入考勤过程中发生错误！/n错误提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Close();
            }
        }