首页 > 代码库 > 利用OLEDB+SqlClient实现EXCEL批量导入数据

利用OLEDB+SqlClient实现EXCEL批量导入数据

以下是几个自己写的类

        /// <summary>        /// 取得Excel对象        /// </summary>        /// <param name="strConn">OLEDB连接字符串</param>        /// <param name="sql">SQL语句</param>        /// <returns></returns>        public static DataTable GetExecuteDataTable(string strConn, string sql)        {            DataTable dt = new DataTable();            using (OleDbConnection conn = new OleDbConnection(strConn))            {                using (OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn))                {                    oda.Fill(dt);                    return dt;                }            }        }        /// <summary>        /// 导入数据        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="mydelegate">执行插入的方法</param>        /// <returns>返回受影响的行数</returns>        public static int Introduction(string sql, Func<SqlConnection,int> mydelegate)        {            int rownumber = 0;//受影响的行数            using (SqlConnection conn = new SqlConnection(connStr))            {                conn.Open();                rownumber = mydelegate(conn);            }            return rownumber;        }        /// <summary>        /// 生成插入语句        /// </summary>        /// <param name="table"></param>        /// <param name="column"></param>        /// <returns></returns>        public static string CreateInsertSQL(string table, params string[] column)        {            StringBuilder sb = new StringBuilder();//拼接字段            StringBuilder sb1 = new StringBuilder();//拼接参数字段            foreach (string item in column)            {                sb.Append(string.Format("{0},", item));                sb1.Append(string.Format("@{0},", item));            }            return string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, sb.ToString().TrimEnd(,), sb1.ToString().TrimEnd(,));        }
       string excelConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelpath);            string excelSql = string.Format("SELECT [网点编号],[网点名称],[派工单位],[所属区域],[变更信息] FROM [table$]");            string sql = Caihuashun_SQLHelper.CreateInsertSQL("[Caihuashun_Units_T]", "wdbh", "wdmc", "pgdw", "ssqy", "biangeng");            DataTable exceldt = Caihuashun_SQLHelper.GetExecuteDataTable(excelConn, excelSql);            Func<SqlConnection, int> myDelegate = (conn) =>            {                int i = 0;                foreach (Caihuashun_Units_T item in DtToList(exceldt))                {                    using (SqlCommand cmd = new SqlCommand(sql, conn))                    {                        SqlParameter[] sp = {                                                new SqlParameter("@wdbh",item.wdbh),                                                new SqlParameter("@wdmc",item.wdmc),                                                new SqlParameter("@pgdw",item.pgdw),                                                new SqlParameter("@ssqy",item.ssqy),                                                new SqlParameter("@biangeng",item.biangeng)                                            };                        cmd.Parameters.AddRange(sp);                        i += cmd.ExecuteNonQuery();                    }                }                return i;            };            return Caihuashun_SQLHelper.Introduction(sql, myDelegate);
try            {                OpenFileDialog open = new OpenFileDialog();                open.Filter = "Execl files (*.xls)|*.xls";//打开文件的类型                open.Title = "选择要导入的文件";                open.FilterIndex = 0;//文件类型默认第一个                open.RestoreDirectory = true;//上一次选择的目录                if (open.ShowDialog() == DialogResult.OK)                {                    function.MboxSuccess(string.Format("成功导入{0}条数据!", bll.Introduction(open.FileName)));                }            }            catch (Exception ex)            {                function.MboxError(ex.Message);            }