将EXCEL的数据读取出来写入数据库的实例方法

将EXCEL的数据读取出来写入数据库的实例方法

1. 将execl数据导到入DataSet中代码实现:

 1 public DataSet ExcelToDS(string Path)
 2     {
 3         string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
 4 "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
 5         OleDbConnection conn = new OleDbConnection(strConn);
 6         conn.Open();
 7         string strExcel = "";
 8         OleDbDataAdapter myCommand = null;
 9         DataSet ds = null;
10         strExcel = "select * from [Sheet1$]";
11         myCommand = new OleDbDataAdapter(strExcel, strConn);
12         ds = new DataSet();
13         myCommand.Fill(ds);
14         return ds;     
15     }

2. 将DataSet数据导入到数据库中代码实现:

 1 protected void btn_AddSalary_Click(object sender, EventArgs e)
 2     {
 3         bool fileOK = false;
 4         String pagenum = Request.QueryString["pagenum"];
 5         if (this.UpFile.PostedFile.ContentLength > 0)
 6         {
 7             int Pos = UpFile.PostedFile.FileName.LastIndexOf("\\") + 1;
 8             String FileName = UpFile.PostedFile.FileName.Substring(Pos);
 9             String FilePath = "./tmp/" + FileName;
10             String fileExtension = System.IO.Path.GetExtension(UpFile.FileName).ToLower();
11             String[] allowedExtensions = { ".xls" };
12             String BatchID=Request.QueryString["BatchID"];
13             lberror.Text = "";
14             foreach (String allowedExtension in allowedExtensions)
15             {
16                 if (fileExtension == allowedExtension)
17                     fileOK = true;
18             }
19           
20             if (fileOK == true)
21             {
22                 UpFile.PostedFile.SaveAs(Server.MapPath(FilePath));
23                 UpFile.PostedFile.InputStream.Close();
24                 DataSet execl=ExcelToDS(Server.MapPath(FilePath));
25                 List<String> empid = new List<String>();
26                 bool empfalse = false;
27                 OperationResult or = new OperationResult();
28                 String getempid = null;
29                 for (int s = 1; s < (execl.Tables[0].Rows.Count); s++)
30                 {
31                     //判断是否有此员工如果有则插入工资
32         
33                     getempid = (TrainingService.getEmpID(execl.Tables[0].Rows[s][0].ToString()).ToString());
34                     if (getempid != "0")
35                     {
36                         empid.Add(getempid);
37                        // lbright.Text += execl.Tables[0].Rows[s][0].ToString()+ ",";
38                     }
39                     else
40                     {
41                         lberror.Text +=execl.Tables[0].Rows[s][0].ToString()+",";
42                         empfalse = true;
43                     }
44                 }
45                 if (!empfalse)  //如果导入名单正确则执行循环插入值
46                 {
47                     foreach(String newempid in empid)
48                     {
49                         int i = 1;
50                         for (int k = 0; k < (execl.Tables[0].Columns.Count); k++)
51                         {
52                             String SalaryItem = execl.Tables[0].Columns[k].ToString();
53                             String SalaryItemType = null;
54                             String SalaryValue = null;
55                             if (k != 0 && k != 1)
56                             {
57                                 SalaryItemType = execl.Tables[0].Rows[0][k].ToString();
58                             }
59                             if (k != 0 && k != 1) //姓名不插入到数据库中
60                             {
61                                 SalaryValue = execl.Tables[0].Rows[i][k].ToString();
62                                 if (SalaryValue == null || SalaryValue == "")
63                                 {
64                                     SalaryValue = "0";
65                                 }
66 Dictionary<String, String> SalaryItem_Info = 
67 DictionaryHelper.CreateDictionary("@EmpID,@ItemName,@ItemValue,@ItemType,@BatchID,@Seq",
68 newempid, SalaryItem, SalaryValue, SalaryItemType, BatchID, k.ToString());
69                                 bool result = SalaryService.Add_Salary_Item(SalaryItem_Info);
70                                 or.IsSuccess = result;
71                             }
72                         }
73                         i++;
74                     }
75 or.ResultMsg.Add("导入工资批次成功");
76 or.AddReturnURL("普通返回", "/manage/salary/salary_batch_list.aspx?pagenum=" + pagenum);
77                 }
78                 else
79                 {
80                       or.ResultMsg.Add("导入工资批次失败 "+lberror.Text);
81                       or.AddReturnURL("返回到填回页", "/manage/salary/salary_item_import.aspx?BatchID=" + BatchID + "&pagenum="+pagenum);
82                 }
83                 Session[Constants.EXECUTE_RESULT] = or;
84                 Server.Transfer("~/result_report.aspx");
85             }
86             else
87             {
88                 WindowHelper.Alert("上传文件类型不正确!", this);
89                 return;
90             }
91         }
92         else
93         {
94             WindowHelper.Alert("请选择上传的文件!", this);
95             return;
96         }