/// <summary> /// Excel导入Access /// </summary> public void Excel2Access(string[] filenames) { try { for (int j = 0; j < filenames.Length; j++) { OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder(); connectStringBuilder.DataSource = filenames[j]; connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0"; connectStringBuilder.Add("Extended Properties", "Excel 8.0"); using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString)) { DataSet ds = new DataSet(); string sql = string.Format("select * from [{0}$]", sheetName); OleDbCommand cmdLiming = new OleDbCommand(sql, cn); cn.Open(); using (OleDbDataReader dr = cmdLiming.ExecuteReader()) { ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName }); DataTable dt = ds.Tables[sheetName]; if (dt.Rows.Count > 0) { for (int i = 4; i < dt.Rows.Count; i++) { //写入数据库数据 if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString()))) { string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')"; AccessHelper.SQLExecute(MySql); } } MessageBox.Show("数据导入成功!"); string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList "; DriverListBLL driverListBll = new DriverListBLL(); DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql); dgvBlackList.DataSource = dtDriverList; dgvBlackList.Rows[0].Selected = false; } else { MessageBox.Show("请检查你的Excel中是否存在数据"); } } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }