首页 > 代码库 > 利用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); }
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。