Excel 导入数据库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public partial class UploadFile : System.Web.UI.Page 2 { 3 public List<UserEntity> User_List = new List<UserEntity>(); 4 protected void Page_Load(object sender, EventArgs e) 5 { 6 if (!IsPostBack) 7 { 8 Label1.Visible = false; 9 HiddenField1.Value = ""; 10 CommonFunction.DeleteFilesForOverServeDays(); 11 } 12 } 13 /// <summary> 14 /// 返回结果字符串 15 /// </summary> 16 /// <returns></returns> 17 protected string GetResultStr() 18 { 19 int successNum = 0; 20 string successStr = ""; 21 int failNum = 0; 22 string failStr = ""; 23 int abnormalNum = 0; 24 string abnormalStr = ""; 25 int RepeaterNum = 0; 26 string RepeaterStr = ""; 27 string totalStr = ""; 28 int infoPartialNum = 0; 29 string infoPartial = ""; 30 HiddenField1.Value = ""; 31 if (UpLoadFileToServer() != "") 32 { 33 Config config = new Config(); 34 DataSet ds = new DataSet(); 35 UserBAO bao = new UserBAO(); 36 ds = GetExcelData(ConnectionString(Server.MapPath(config.GetUserCopyImportPath()) + HiddenField1.Value)); 37 if (ds != null || ds.Tables[0].Rows.Count > 0) 38 { 39 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) 40 { 41 42 #region 输出批量导入数据的详细信息 43 if (ds.Tables[0].Rows[i][0].ToString() != "" || ds.Tables[0].Rows[i][4].ToString() != "" || ds.Tables[0].Rows[i][1].ToString() == "") 44 { 45 //还差一个密码验证的长度大于8位,包含数字字母特殊字符 46 if (bao.GetUser(ds.Tables[0].Rows[i][0].ToString()).BaseInfo == null) 47 { 48 string account = ds.Tables[0].Rows[i][0].ToString(); 49 string email = ds.Tables[0].Rows[i][2].ToString(); 50 EntityUserExtInfo model = new EntityUserExtInfo(); 51 model.UserName = ds.Tables[0].Rows[i][0].ToString(); 52 model.UserDispName = ds.Tables[0].Rows[i][1].ToString(); 53 model.CompanyName = ds.Tables[0].Rows[i][3].ToString(); 54 model.DeptName = ds.Tables[0].Rows[i][4].ToString(); 55 model.Tel = ds.Tables[0].Rows[i][5].ToString(); 56 model.Mobile = ds.Tables[0].Rows[i][6].ToString(); 57 model.Description = ds.Tables[0].Rows[i][7].ToString(); 58 if (bao.AddUser(account, CommonFunction.GetPassword(), email, model) > 0) 59 { 60 successNum++; 61 successStr += account + "|"; 62 UserEntity userInfo = new UserEntity(); 63 userInfo.UserAccount = account; 64 userInfo.UserPassword = CommonFunction.GetPassword(); 65 User_List.Add(userInfo); 66 } 67 else 68 { 69 failNum++; 70 failStr += account + "|"; 71 } 72 } 73 else 74 { 75 RepeaterNum++; 76 RepeaterStr += ds.Tables[0].Rows[i][0].ToString() + "|"; 77 } 78 } 79 else 80 { 81 infoPartialNum++; 82 infoPartial += ds.Tables[0].Rows[i][0].ToString() + "|"; 83 } 84 #endregion 85 86 } 87 totalStr += "1.成功导入" + successNum + "条数据;【详细:账户:(" + successStr + ")】;"; 88 totalStr += "2.特殊原因失败" + failNum + "条数据【详细:账户:(" + failStr + ")】;"; 89 totalStr += "3.已经存在未插入" + RepeaterNum + "条数【详细:账户:(" + RepeaterStr + ")】;"; 90 totalStr += "4.信息不完整:" + infoPartialNum + "条数据【详细:账户:(" + infoPartial + ")】;"; 91 } 92 } 93 return totalStr; 94 } 95 /// <summary> 96 /// 上传文件到服务器 97 /// </summary> 98 /// <returns></returns> 99 protected string UpLoadFileToServer() 100 { 101 Config config = new Config(); 102 bool isAllow = false; 103 string filePath = Server.MapPath(config.GetUserCopyImportPath()); 104 string fileType = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); 105 if (FileUpload1.HasFile) 106 { 107 string[] allowFile = { ".xls", ".xlsx" }; 108 for (int i = 0; i < allowFile.Length; i++) 109 { 110 if (fileType == allowFile[i].ToString()) 111 { 112 isAllow = true; 113 } 114 } 115 } 116 if (isAllow == true) 117 { 118 string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + fileType; 119 FileUpload1.SaveAs(filePath + fileName); 120 HiddenField1.Value = fileName; 121 } 122 return HiddenField1.Value; 123 } 124 /// <summary> 125 /// Excel连接字符串 126 /// </summary> 127 /// <param name="path"></param> 128 /// <returns></returns> 129 private string ConnectionString(string path) 130 { 131 string strCon = ""; 132 if (path.ToLower().IndexOf(".xlsx") > 0) 133 { 134 strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties='Excel 12.0;HDR=YES'"; 135 } 136 else if (path.ToLower().IndexOf(".xls") > 0 && path.EndsWith("xls")) 137 { 138 strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=YES;'"; 139 } 140 return strCon; 141 } 142 /// <summary> 143 /// 获取Excel中数据 144 /// </summary> 145 /// <param name="strCon"></param> 146 /// <returns></returns> 147 private DataSet GetExcelData(string strCon) 148 { 149 Config config = new Config(); 150 string catalogName = config.GetExcelCatalogName(); 151 using (OleDbConnection olecon = new OleDbConnection(strCon)) 152 { 153 string strSql = "select * from [" + catalogName + "$]"; 154 olecon.Open(); 155 OleDbDataAdapter myda = new OleDbDataAdapter(strSql, strCon); 156 DataSet ds = new DataSet(); 157 myda.Fill(ds); 158 return ds; 159 }; 160 } 161 /// <summary> 162 /// 批量导入 163 /// </summary> 164 /// <param name="sender"></param> 165 /// <param name="e"></param> 166 protected void btn_ImportBatch_Click(object sender, EventArgs e) 167 { 168 Config config = new Config(); 169 Label1.Visible = true; 170 HiddenField2.Value = GetResultStr(); 171 if (HiddenField2.Value.Length > 4090) 172 { 173 HiddenField2.Value = HiddenField2.Value.Substring(0, 4090) + "..."; 174 } 175 Page.ClientScript.RegisterStartupScript(Page.GetType(), Guid.NewGuid().ToString(), "<script>GetResultVal();</script>"); 176 } 177 }