导入Excel数据至Access
private void GetExcelSheetsByUpload(string strExcelPath) { OleDbConnection oledbConn = null; DataTable dt = null; try { string strExtend = Path.GetExtension(txtFileName.Text.Trim()); string connString = null; // 连接字符串 if (strExtend == ".xls") //97-2003 excel版本 connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelPath + ";Extended Properties=Excel 8.0;"; else if (strExtend == ".xlsx") //2007及以后excel版本 connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; else { AlertError("选择的文件有误!"); return; } oledbConn = new OleDbConnection(connString); oledbConn.Open(); //取得到包含数据架构的数据表 dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { AlertError("选择的文件有误!"); return; } string[] excelSheets = new string[dt.Rows.Count]; int i = 0; // 添加工作表名称到字符串数组 foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } //循环检索数据表并导入数据 for (int j = 0; j < excelSheets.Length; j++) { OleDbDataAdapter oleDbAdp = new OleDbDataAdapter("SELECT [药品名称],[规格],[生产厂家],[单位],[批准文号],[药品价格],[生产批号],[有效日期],[备注] FROM [" + excelSheets[j] + "]", oledbConn); DataSet ds = new DataSet(); oleDbAdp.Fill(ds); foreach (DataRow dr in ds.Tables[0].Rows) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [tbl_product](product_name,product_code,product_pack,factory_code,create_factory,unit,grv_code,create_code,unit_price,effect_date,remark,last_byStaff,last_byDate,create_date,create_by,status)"); strSql.Append(" values('").Append(dr["药品名称"].ToString()).Append("',"); strSql.Append("'").Append(CommonHelper.GetChineseSpell(dr["药品名称"].ToString())).Append("',"); strSql.Append("'").Append(dr["规格"].ToString()).Append("',"); strSql.Append("'").Append(CommonHelper.GetChineseSpell(dr["生产厂家"].ToString())).Append("',"); strSql.Append("'").Append(dr["生产厂家"].ToString()).Append("',"); strSql.Append("'").Append(dr["单位"].ToString()).Append("',"); strSql.Append("'").Append(dr["批准文号"].ToString()).Append("',"); strSql.Append("'").Append(dr["生产批号"].ToString()).Append("',"); strSql.Append("'").Append(dr["药品价格"].ToString()).Append("',"); strSql.Append("'").Append(dr["有效日期"].ToString()).Append("',"); strSql.Append("'").Append(dr["备注"].ToString()).Append("',"); strSql.Append("'").Append(base.UserName).Append("',"); strSql.Append("'").Append(DateTime.Now.ToString()).Append("',"); strSql.Append("'").Append(DateTime.Now.ToString()).Append("',"); strSql.Append("'").Append(base.UserName).Append("',"); strSql.Append("'").Append("A").Append("')"); OleDbHelper.GetOleDbExecuteNonQuery(strSql.ToString()); } break;//目前只支持导入一个工作表 } AlertInfo("数据导入成功!"); } catch (Exception ex) { AlertError("导入数据异常:" + ex.ToString()); } finally { if (oledbConn != null) { oledbConn.Close(); oledbConn.Dispose(); } if (dt != null) dt.Dispose(); } }