• 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 }
    

    


    


    

    
    
          
    • 

          
  • 

            相关阅读:
    
            
              【剑指Offer面试编程题】题目1370：数组中出现次数超过一半的数字--九度OJ
                
    【剑指Offer面试编程题】题目1369：字符串的排列--九度OJ
                
    【剑指Offer面试编程题】题目1503：二叉搜索树与双向链表--九度OJ
                
    【剑指Offer面试编程题】题目1524：复杂链表的复制--九度OJ
                
    【剑指Offer面试编程题】题目1368：二叉树中和为某一值的路径--九度OJ
                
    【剑指Offer面试编程题】题目1367：二叉搜索树的后序遍历序列--九度OJ
                
    【剑指Offer面试编程题】题目1523：从上往下打印二叉树--九度OJ
                
    【剑指Offer面试编程题】题目1366：栈的压入、弹出序列--九度OJ
                
    【剑指Offer面试编程题】题目1522：包含min函数的栈--九度OJ
                
    【剑指Offer面试编程题】题目1391：顺时针打印矩阵--九度OJ
                
                    
          
    • 
          
  • 
            原文地址：https://www.cnblogs.com/allenzhang/p/5253880.html
          
    • 

        

      



      
      

        

        

      

    

  


  

  
Copyright © 2020-2023 
  润新知