将Excel转化为DataSet，并保存到数据库

来源：互联网发布：电子表格查找相同数据编辑：程序博客网时间：2024/05/16 07:20

将Excel转化为DataSet

1.先将Excel文件上传，取到上传的Excel文件的绝对路径；

2.将Excel中的数据读取到DataSet中；

        /// <summary>        /// 将Excel转化为DataSet        /// </summary>        /// <param name="path">Excel路径</param>        /// <returns>数据集</returns>        public static DataSet ExcelToDataSet(string path)        {            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";            DataSet myDataSet = new DataSet();            using (OleDbConnection myOleDbConnection = new OleDbConnection(strConn))            {                myOleDbConnection.Open();                string strExcel = " select * from [sheet1$]";                using (OleDbCommand myOleDbCommand = new OleDbCommand(strExcel, myOleDbConnection))                {                    myOleDbCommand.ExecuteNonQuery();                    using (OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand))                    {                        myOleDbDataAdapter.Fill(myDataSet);                    }                }            }            return myDataSet;        }

3.结合SqlCommandBuilder将DataSet中的数据存储到数据库；

        /// <summary>        /// 将DataSet中的数据一次性存储到数据库        /// </summary>        /// <param name="sql">数据</param>        /// <param name="strTblName">要操作的表</param>        /// <returns></returns>public static int CommondDataTable(DataTable dt, string strTblName)        {             using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                SqlTransaction trans = Connection.BeginTransaction();                try                {                    SqlDataAdapter adapter = new SqlDataAdapter();                    SqlCommand cmd = new SqlCommand("select * from " + strTblName, Connection);                    cmd.Transaction = trans;                    adapter.SelectCommand = cmd;                    SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);                    int reuslt = adapter.Update(dt);                    trans.Commit();                    return reuslt;                }                catch (Exception ex)                {                    trans.Rollback();                    return 0;                }            }        }