c#大圣之路笔记——c# 通过页面把excel中的数据导入到DB中
//前端代码
1 <table width="100%"> 2 3 <tr> 4 <td> 5 <input type="file" id="myFile" name="myFile" runat="server" class="btnUpload" size="46" /> 6 <asp:Button runat="server" ID ="btnUpload" Text="上传" OnClick="btnUpload_Click" CssClass="ButtonCommand" /> 7 </td> 8 </tr> 9 </table>
//后台代码
1 protected OleDbConnection xlconn; 2 protected OleDbDataAdapter xlda; 3 protected SqlConnection conn;
1 /// <summary> 2 /// 验证excel sheet 中列明是否正确 3 /// </summary> 4 /// <param name="dt"></param> 5 /// <returns></returns> 6 protected Boolean CheckTemplate(DataTable dt) 7 { 8 Boolean bolResult; 9 bolResult = true; 10 int i; 11 List<string> list = new List<string>(); 12 13 for (i = 0; i < dt.Columns.Count; i++) 14 { 15 list.Add(dt.Columns[i].ColumnName.ToString()); 16 } 17 18 try 19 { 20 21 if (list[0].ToString().Trim() != "GID") 22 { 23 string msg = "第 A 列应该为：GID"; 24 this.PageAlert(msg); 25 bolResult = false; 26 } 27 28 if (list[1].ToString().Trim() != "渠道省份ID") 29 { 30 string msg = "第 B 列应该为：渠道省份ID"; 31 this.PageAlert(msg); 32 bolResult = false; 33 } 34 35 if (list[2].ToString().Trim() != "渠道省份名称") 36 { 37 string msg = "第 C 列应该为：渠道省份名称"; 38 this.PageAlert(msg); 39 bolResult = false; 40 } 41 if (list[3].ToString().Trim() != "渠道城市ID") 42 { 43 string msg = "第 D 列应该为：渠道城市ID"; 44 this.PageAlert(msg); 45 bolResult = false; 46 } 47 if (list[4].ToString().Trim() != "渠道城市名称") 48 { 49 string msg = "第 E 列应该为：渠道城市名称"; 50 this.PageAlert(msg); 51 bolResult = false; 52 } 53 if (list[5].ToString().Trim() != "渠道县ID") 54 { 55 string msg = "第 F 列应该为：渠道县ID"; 56 this.PageAlert(msg); 57 bolResult = false; 58 } 59 if (list[6].ToString().Trim() != "渠道县名称") 60 { 61 string msg = "第 G 列应该为：渠道县名称"; 62 this.PageAlert(msg); 63 bolResult = false; 64 } if (list[7].ToString().Trim() != "渠道镇ID") 65 { 66 string msg = "第 H 列应该为：渠道镇ID"; 67 this.PageAlert(msg); 68 bolResult = false; 69 } 70 if (list[8].ToString().Trim() != "渠道镇名称") 71 { 72 string msg = "第 I 列应该为：渠道镇名称"; 73 this.PageAlert(msg); 74 bolResult = false; 75 } 76 if (list[9].ToString().Trim() != "渠道村ID") 77 { 78 string msg = "第 J 列应该为：渠道村ID"; 79 this.PageAlert(msg); 80 bolResult = false; 81 } 82 if (list[10].ToString().Trim() != "渠道村名称") 83 { 84 string msg = "第 K 列应该为：渠道村名称"; 85 this.PageAlert(msg); 86 bolResult = false; 87 } 88 if (list[11].ToString().Trim() != "渠道编号") 89 { 90 string msg = "第 L 列应该为：渠道编号"; 91 this.PageAlert(msg); 92 bolResult = false; 93 } 94 if (list[12].ToString().Trim() != "渠道级别") 95 { 96 string msg = "第 M 列应该为：渠道级别"; 97 this.PageAlert(msg); 98 bolResult = false; 99 } 100 if (list[13].ToString().Trim() != "渠道类型") 101 { 102 string msg = "第 N 列应该为：渠道类型"; 103 this.PageAlert(msg); 104 bolResult = false; 105 } 106 if (list[14].ToString().Trim() != "渠道名称") 107 { 108 string msg = "第 O 列应该为：渠道名称"; 109 this.PageAlert(msg); 110 bolResult = false; 111 } 112 if (list[15].ToString().Trim() != "渠道地址") 113 { 114 string msg = "第 P 列应该为：渠道地址"; 115 this.PageAlert(msg); 116 bolResult = false; 117 } 118 if (list[16].ToString().Trim() != "渠道负责人") 119 { 120 string msg = "第 Q 列应该为：渠道负责人"; 121 this.PageAlert(msg); 122 bolResult = false; 123 } 124 if (list[17].ToString().Trim() != "渠道负责人电话") 125 { 126 string msg = "第 R 列应该为：渠道负责人电话"; 127 this.PageAlert(msg); 128 bolResult = false; 129 } 130 if (list[18].ToString().Trim() != "渠道邮箱") 131 { 132 string msg = "第 S 列应该为：渠道邮箱"; 133 this.PageAlert(msg); 134 bolResult = false; 135 } 136 if (list[19].ToString().Trim() != "店面省份ID") 137 { 138 string msg = "第 T 列应该为：店面省份ID"; 139 this.PageAlert(msg); 140 bolResult = false; 141 } 142 if (list[20].ToString().Trim() != "店面省份名称") 143 { 144 string msg = "第 U 列应该为：店面省份名称"; 145 this.PageAlert(msg); 146 bolResult = false; 147 } 148 if (list[21].ToString().Trim() != "店面城市ID") 149 { 150 string msg = "第 v 列应该为：店面城市ID"; 151 this.PageAlert(msg); 152 bolResult = false; 153 } 154 if (list[22].ToString().Trim() != "店面城市名称") 155 { 156 string msg = "第 W 列应该为：店面城市名称"; 157 this.PageAlert(msg); 158 bolResult = false; 159 } 160 if (list[23].ToString().Trim() != "店面县ID") 161 { 162 string msg = "第 X 列应该为：店面县ID"; 163 this.PageAlert(msg); 164 bolResult = false; 165 } 166 if (list[24].ToString().Trim() != "店面县名称") 167 { 168 string msg = "第 Y 列应该为：店面县名称"; 169 this.PageAlert(msg); 170 bolResult = false; 171 } 172 if (list[25].ToString().Trim() != "店面镇ID") 173 { 174 string msg = "第 Z 列应该为：店面镇ID"; 175 this.PageAlert(msg); 176 bolResult = false; 177 } 178 if (list[26].ToString().Trim() != "店面镇名称") 179 { 180 string msg = "第 AA 列应该为：店面镇名称"; 181 this.PageAlert(msg); 182 bolResult = false; 183 } 184 if (list[27].ToString().Trim() != "店面村ID") 185 { 186 string msg = "第 AB 列应该为：店面村ID"; 187 this.PageAlert(msg); 188 bolResult = false; 189 } 190 if (list[28].ToString().Trim() != "店面村名称") 191 { 192 string msg = "第 AC 列应该为：店面村名称"; 193 this.PageAlert(msg); 194 bolResult = false; 195 } 196 if (list[29].ToString().Trim() != "店面编号") 197 { 198 string msg = "第 AD 列应该为：店面编号"; 199 this.PageAlert(msg); 200 bolResult = false; 201 } 202 if (list[30].ToString().Trim() != "业务类型") 203 { 204 string msg = "第 AE 列应该为：业务类型"; 205 this.PageAlert(msg); 206 bolResult = false; 207 } 208 if (list[31].ToString().Trim() != "店面品牌") 209 { 210 string msg = "第 AF 列应该为：店面品牌"; 211 this.PageAlert(msg); 212 bolResult = false; 213 } 214 if (list[32].ToString().Trim() != "店面名称") 215 { 216 string msg = "第 AG 列应该为：店面名称"; 217 this.PageAlert(msg); 218 bolResult = false; 219 } 220 if (list[33].ToString().Trim() != "店面地址") 221 { 222 string msg = "第 AH 列应该为：店面地址"; 223 this.PageAlert(msg); 224 bolResult = false; 225 } 226 if (list[34].ToString().Trim() != "店长") 227 { 228 string msg = "第 AI 列应该为：店长"; 229 this.PageAlert(msg); 230 bolResult = false; 231 } 232 if (list[35].ToString().Trim() != "店长手机") 233 { 234 string msg = "第 AJ 列应该为：店长手机"; 235 this.PageAlert(msg); 236 bolResult = false; 237 } 238 if (list[36].ToString().Trim() != "店面邮箱地址") 239 { 240 string msg = "第 AK 列应该为：店面邮箱地址"; 241 this.PageAlert(msg); 242 bolResult = false; 243 } 244 if (list[37].ToString().Trim() != "店面级别") 245 { 246 string msg = "第 AL 列应该为：店面级别"; 247 this.PageAlert(msg); 248 bolResult = false; 249 } 250 if (list[38].ToString().Trim() != "商圈名称") 251 { 252 string msg = "第 AM 列应该为：商圈名称"; 253 this.PageAlert(msg); 254 bolResult = false; 255 } 256 if (list[39].ToString().Trim() != "商圈地址") 257 { 258 string msg = "第 AN 列应该为：商圈地址"; 259 this.PageAlert(msg); 260 bolResult = false; 261 } 262 if (list[40].ToString().Trim() != "商圈类型") 263 { 264 string msg = "第 AO 列应该为：商圈类型"; 265 this.PageAlert(msg); 266 bolResult = false; 267 } 268 } 269 catch 270 { 271 string msg = "请核对模板格式是否正确!"; 272 this.PageAlert(msg); 273 } 274 return bolResult; 275 } 276 277 /// <summary> 278 /// 通过excel导入数据 279 /// </summary> 280 /// <param name="sender"></param> 281 /// <param name="e"></param> 282 protected void btnUpload_Click(object sender, EventArgs e) 283 { 284 285 286 if (this.myFile.PostedFile.FileName.Trim() == "") 287 { 288 string msg = "Please select a file!"; 289 this.PageAlert(msg); 290 return; 291 } 292 if (!Directory.Exists(Server.MapPath("upload"))) 293 { 294 Directory.CreateDirectory(Server.MapPath("upload")); 295 } 296 297 string aFile = this.myFile.PostedFile.FileName.ToString(); 298 299 string aFirstName = aFile.Substring(aFile.LastIndexOf("\\") + 1, (aFile.LastIndexOf(".") - aFile.LastIndexOf("\\") - 1)); //文件名 300 301 string ext = this.myFile.PostedFile.FileName.Substring(this.myFile.PostedFile.FileName.LastIndexOf(".")).ToLower(); 302 string fileNO = System.DateTime.Now.Year.ToString("00") + System.DateTime.Now.Month.ToString("00") + System.DateTime.Now.Day.ToString("00") + System.DateTime.Now.Hour.ToString("00") + System.DateTime.Now.Minute.ToString("00") + System.DateTime.Now.Second.ToString("00") + "_" + WWID; 303 string sFileSavePath = Server.MapPath("upload") + "\\" + fileNO + ext; 304 this.myFile.PostedFile.SaveAs(sFileSavePath); 305 DataSet dt = new DataSet(); 306 try 307 { 308 if (ext == ".xls") 309 { 310 xlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + sFileSavePath + "';Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""); 311 // "provider = microsoft.jet.oledb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", 312 } 313 else 314 { 315 xlconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sFileSavePath + "';Extended Properties=\"Excel 12.0 Xml;HDR=YES\""); 316 } 317 } 318 319 catch (Exception ex) 320 { 321 this.PageAlert(ex.Message); 322 return; 323 } 324 325 326 string sheetname = "渠道店面模板"; 327 try 328 { 329 xlda = new OleDbDataAdapter("select * from [" + sheetname + "$]", xlconn); 330 xlda.Fill(dt); 331 332 int ct = dt.Tables[0].Rows.Count; 333 334 } 335 catch 336 { 338 this.Page.ClientScript.RegisterStartupScript(this.GetType(), "Alter", "<script language=javascript>alert('请核对模板是否正确!');</script>"); 339 342 return; 343 } 344 finally 345 { 346 xlconn.Close(); 347 } 348 349 string connString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]; 350 SqlConnection conn = new SqlConnection(connString); 351 conn.Open(); 352 355 try 356 { 357 358 SqlCommand cmd = new SqlCommand(); 359 360 361 // load the data into table 362 363 if (CheckTemplate(dt.Tables[0])) 364 { 365 366 int i = 1; 369 cmd.Connection = conn; 370 371 foreach (DataRow dr in dt.Tables[0].Rows) 372 { 373 string gid = dr[0].ToString().Trim() ; 374 string chnl_state_id = dr[1].ToString().Trim(); 375 string chnl_state_nm = dr[2].ToString().Trim(); 376 string chnl_city_id = dr[3].ToString().Trim(); 377 string chnl_city_nm = dr[4].ToString().Trim(); 378 string chnl_county_id = dr[5].ToString().Trim(); 379 string chnl_county_nm = dr[6].ToString().Trim(); 380 string chnl_town_id = dr[7].ToString().Trim(); 381 string chnl_town_nm = dr[8].ToString().Trim(); 382 string chnl_village_id = dr[9].ToString().Trim(); 383 string chnl_village_nm = dr[10].ToString().Trim(); 384 string chnl_no = dr[11].ToString().Trim(); 385 string chnl_mbr_type = dr[12].ToString().Trim(); 386 string chnl_type = dr[13].ToString().Trim(); 387 string chnl_nm = dr[14].ToString().Trim(); 388 string chnl_addr = dr[15].ToString().Trim(); 389 string chnl_person = dr[16].ToString().Trim(); 390 string chnl_tel = dr[17].ToString().Trim(); 391 string chnl_mail = dr[18].ToString().Trim(); 392 string stor_state_id = dr[19].ToString().Trim(); 393 string stor_state_nm = dr[20].ToString().Trim(); 394 string stor_city_id = dr[21].ToString().Trim(); 395 string stor_city_nm = dr[22].ToString().Trim(); 396 string stor_county_id = dr[23].ToString().Trim(); 397 string stor_county_nm = dr[24].ToString().Trim(); 398 string stor_town_id = dr[25].ToString().Trim(); 399 string stor_town_nm = dr[26].ToString().Trim(); 400 string stor_village_id = dr[27].ToString().Trim(); 401 string stor_village_nm = dr[28].ToString().Trim(); 402 string stor_no = dr[29].ToString().Trim(); 403 string business_type = dr[30].ToString().Trim(); 404 string stor_brnd = dr[31].ToString().Trim(); 405 string stor_nm = dr[32].ToString().Trim(); 406 string stor_addr = dr[33].ToString().Trim(); 407 string stor_rep = dr[34].ToString().Trim(); 408 string stor_rep_tel = dr[35].ToString().Trim(); 409 string stor_mail = dr[36].ToString().Trim(); 410 string stor_cat_type = dr[37].ToString().Trim(); 411 string mall_nm = dr[38].ToString().Trim(); 412 string mall_addr = dr[39].ToString().Trim(); 413 string mall_type = dr[40].ToString().Trim(); 414 string upld_dtm = DateTime.Now.ToString("yyyy-MM-dd "); 415 416 417 // file loading successful, then input the data into final table 418 468 SqlParameter[] paraList = new SqlParameter[] 469 { 470 Parameters.GenerateSqlParameterWithNullValue("@GID", gid ), 471 Parameters.GenerateSqlParameterWithNullValue("@chnl_State_id", chnl_state_id), 472 Parameters.GenerateSqlParameterWithNullValue("@chnl_State_nm", chnl_state_nm), 473 Parameters.GenerateSqlParameterWithNullValue("@chnl_City_id", chnl_city_id), 474 Parameters.GenerateSqlParameterWithNullValue("@chnl_City_nm", chnl_city_nm), 475 Parameters.GenerateSqlParameterWithNullValue("@chnl_County_id", chnl_county_id), 476 Parameters.GenerateSqlParameterWithNullValue("@chnl_County_nm", chnl_county_nm), 477 Parameters.GenerateSqlParameterWithNullValue("@chnl_Town_id", chnl_town_id), 478 Parameters.GenerateSqlParameterWithNullValue("@chnl_Town_nm", chnl_town_nm), 479 Parameters.GenerateSqlParameterWithNullValue("@chnl_Village_id", chnl_village_id), 480 Parameters.GenerateSqlParameterWithNullValue("@chnl_Village_nm", chnl_village_nm), 481 Parameters.GenerateSqlParameterWithNullValue("@chnl_no", chnl_no), 482 Parameters.GenerateSqlParameterWithNullValue("@chnl_mbr_type", chnl_mbr_type), 483 Parameters.GenerateSqlParameterWithNullValue("@chnl_type", chnl_type), 484 Parameters.GenerateSqlParameterWithNullValue("@chnl_nm", chnl_nm), 485 Parameters.GenerateSqlParameterWithNullValue("@chnl_addr", chnl_addr), 486 Parameters.GenerateSqlParameterWithNullValue("@chnl_rep", chnl_person), 487 Parameters.GenerateSqlParameterWithNullValue("@chnl_rep_tel", chnl_tel), 488 Parameters.GenerateSqlParameterWithNullValue("@chnl_mail", chnl_mail), 489 Parameters.GenerateSqlParameterWithNullValue("@stor_State_id", stor_state_id), 490 Parameters.GenerateSqlParameterWithNullValue("@stor_State_nm", stor_state_nm), 491 Parameters.GenerateSqlParameterWithNullValue("@stor_City_id", stor_city_id), 492 Parameters.GenerateSqlParameterWithNullValue("@stor_City_nm", stor_city_nm), 493 Parameters.GenerateSqlParameterWithNullValue("@stor_County_id", stor_county_id), 494 Parameters.GenerateSqlParameterWithNullValue("@stor_County_nm", stor_county_nm), 495 Parameters.GenerateSqlParameterWithNullValue("@stor_Town_id", stor_town_id), 496 Parameters.GenerateSqlParameterWithNullValue("@stor_Town_nm", stor_town_nm), 497 Parameters.GenerateSqlParameterWithNullValue("@stor_Village_id", stor_village_id), 498 Parameters.GenerateSqlParameterWithNullValue("@stor_Village_nm", stor_village_nm), 499 Parameters.GenerateSqlParameterWithNullValue("@stor_no", stor_no), 500 Parameters.GenerateSqlParameterWithNullValue("@businessType", business_type), 501 Parameters.GenerateSqlParameterWithNullValue("@stor_brnd", stor_brnd), 502 Parameters.GenerateSqlParameterWithNullValue("@stor_nm", stor_nm), 503 Parameters.GenerateSqlParameterWithNullValue("@stor_addr", stor_addr), 504 Parameters.GenerateSqlParameterWithNullValue("@stor_rep", stor_rep), 505 Parameters.GenerateSqlParameterWithNullValue("@stor_rep_tel", stor_rep_tel), 506 Parameters.GenerateSqlParameterWithNullValue("@stor_mail", stor_mail), 507 Parameters.GenerateSqlParameterWithNullValue("@stor_cat_type", stor_cat_type), 508 Parameters.GenerateSqlParameterWithNullValue("@mall_nm", mall_nm), 509 Parameters.GenerateSqlParameterWithNullValue("@mall_addr", mall_addr), 510 Parameters.GenerateSqlParameterWithNullValue("@mall_type", mall_type), 511 Parameters.GenerateSqlParameterWithNullValue("@updateDatetime", upld_dtm) 512 }; 513 514 i= SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure,"prc_oem_chnl_stor_data_upload",paraList); 515 516 } 520 } 521 else 522 { 524 this.PageAlert("上传失败 !!"); 525 528 return; 529 } 530 531 } 532 533 catch (Exception ex) 534 { 535 this.PageAlert(ex.Message); 536 //transaction.Rollback(); 537 return; 538 } 539 finally 540 { 541 conn.Close(); 542 } 543 this.PageAlert("上传成功！"); 544 573 574 } 575 576 577 578 579 /// <summary> 580 /// 提示代码 581 /// </summary> 582 /// <param name="strMsg"></param> 583 private void PageAlert(string strMsg) 584 { 585 this.Page.ClientScript.RegisterStartupScript(this.GetType(), "Alter", "<script language=javascript>alert('" + strMsg + "');</script>"); 586 }
1 ///GenerateSqlParameterWithNullValue 2 //是通过 创建一个静态类 3 // 创建一个静态方法得到的扩展方法 4 5 /// <summary> 6 /// SQL parameter 7 /// </summary> 8 /// <remarks> @ 2012-12-12 </remarks> 9 public static class Parameters 10 { 11 /// <summary> 12 /// Generate SQL parameter with Null (DBNull.Value) value 13 /// </summary> 14 /// <param name="parameterName">parameter name</param> 15 /// <param name="parameterValue">parameter value</param> 16 /// <returns>SQL parameter with Null (DBNull.Value) value</returns> 17 public static SqlParameter GenerateSqlParameterWithNullValue(string parameterName, string parameterValue) 18 { 19 SqlParameter sqlParameter = new SqlParameter(); 20 sqlParameter.ParameterName = parameterName; 21 if (string.IsNullOrWhiteSpace(parameterValue) || parameterValue.ToUpper().Equals("ALL")) 22 { 23 sqlParameter.Value = DBNull.Value; 24 } 25 else 26 { 27 sqlParameter.Value = parameterValue; 28 } 29 return sqlParameter; 30 } 31 32 public static SqlParameter GenerateSqlParameterWithNullValue(string parameterName, int parameterValue) 33 { 34 SqlParameter sqlParameter = new SqlParameter(); 35 sqlParameter.ParameterName = parameterName; 36 if (parameterValue.Equals("")) 37 { 38 sqlParameter.Value = DBNull.Value; 39 } 40 else 41 { 42 sqlParameter.Value = parameterValue; 43 } 44 return sqlParameter; 45 } 46 }
