从Excel文件倒入数据到SQL数据库

  1  /// <summary>
  2     /// 从Excel文件中导入数据
  3     /// </summary>
  4     /// <param name="sender"></param>
  5     /// <param name="e"></param>
  6     protected void BtnExeclInput_ServerClick(object sender, EventArgs e)
  7     {
  8         try
  9         {
 10             DynamicAccountInfo daInfo = new DynamicAccountInfo();
 11             string filename = FileUpload1.FileName;//获取文件名
 12 
 13             bool IsSucc = false;//向数据库插入数据是否成功
 14             bool IsTrue = true;//初始化输入数据是正确的
 15             bool Istrue = true;
 16 
 17             if (filename == "")
 18             {
 19                 SetInfo("请选择文件进行导入！");
 20             }
 21             else
 22             {
 23 
 24                 FileUpload1.SaveAs(Server.MapPath("../Excel文件/") + filename);
 25                 string _strfilename = Server.MapPath("../Excel文件/") + filename;
 26 
 27                 DataTable dtExcel = daDB.ExeclToDataTable(_strfilename, "Sheet1");
 28 
 29                 //对Excel文件的设备信息进行验证
 30                 for (int i = 0; i < dtExcel.Rows.Count; i++)
 31                 {
 32                     string DeviceID = dtExcel.Rows[i][0].ToString();
 33                     string UserMonth = dtExcel.Rows[i][1].ToString();
 34                     string state = dtExcel.Rows[i][4].ToString();
 35                      string deptname = dtExcel.Rows[i][5].ToString();
 36                     string FaceNo = dtExcel.Rows[i][7].ToString();
 37                     if (DeviceID=="")
 38                     {
 39                         IsTrue = false;
 40                         SetInfo("第"+(i+1)+"行的设备编号为空了！");  
 41                     }
 42                     else if (Convert.ToDecimal(dtExcel.Rows[i][3].ToString())<0)
 43                     {
 44                          IsTrue = false;
 45                          SetInfo("第" + (i + 1) + "行的资产净值不能小于0 ！");  
 46                     }
 47                     else if (deptname=="")
 48                     {
 49                          IsTrue = false;
 50                          SetInfo("第" + (i + 1) + "行的使用部门不能为空 ！");  
 51                     }
 52                     else if (FaceNo=="")
 53                     {
 54                         daInfo.FaceID = 0; 
 55                     }
 56                     else if (state != "待用" && state != "在用" && state != "在修" && state != "报废")//判断设备状态：0待用；1在用；2在修；3报废；
 57                     {
 58                         IsTrue = false;
 59                         SetInfo("您输入的第" + (i + 1) + "行的使用状态不正确！");
 60                     }
 61                     else
 62                     {
 63                         IsTrue = VerifyInfo(DeviceID, UserMonth, deptname, FaceNo);
 64                         Istrue = VerifyTime(state, dtExcel.Rows[i][9].ToString(), dtExcel.Rows[i][10].ToString());
 65                     }  
 66 
 67                 }//end of for
 68 
 69                 if (IsTrue && Istrue)//所有数据都正确时,开始添加数据
 70                 {
 71                     for (int i = 0; i < dtExcel.Rows.Count; i++)
 72                     {
 73                         daInfo.DeviceID = dtExcel.Rows[i][0].ToString();
 74                         daInfo.UseMonth = int.Parse(dtExcel.Rows[i][1].ToString());
 75                         daInfo.AccuAmort = Convert.ToDecimal(dtExcel.Rows[i][2].ToString());
 76                         daInfo.NetWorth = Convert.ToDecimal(dtExcel.Rows[i][3].ToString());
 77 
 78                         switch (dtExcel.Rows[i][4].ToString())
 79                         {
 80                             case "待用": daInfo.State = 0; break;
 81                             case "在用": daInfo.State = 1; break;
 82                             case "在修": daInfo.State = 2; break;
 83                             case "报废": daInfo.State = 3; break;
 84                         }
 85                         string deptname = dtExcel.Rows[i][5].ToString();
 86 
 87                         daInfo.DeptID = daDB.GetDeptIDFromDB(deptname);
 88 
 89                         daInfo.RepairPlace = dtExcel.Rows[i][6].ToString();
 90                         string faceNo = dtExcel.Rows[i][7].ToString();
 91                         if (faceNo == "")
 92                         {
 93                             daInfo.FaceID = 0;
 94                         }
 95                         else
 96                         {
 97                             daInfo.FaceID = daDB.GetFaceIDFromDB(faceNo);
 98                         }
 99                         
100 
101                         daInfo.DeviceSource = dtExcel.Rows[i][8].ToString();
102                         daInfo.SDate = Convert.ToDateTime(dtExcel.Rows[i][9].ToString());
103                         if (dtExcel.Rows[i][10].ToString() == "")
104                         {
105                             daInfo.EDate = Convert.ToDateTime("1900-01-01");
106                         }
107                         else
108                         {
109                              daInfo.EDate = Convert.ToDateTime(dtExcel.Rows[i][10].ToString());
110                         }
111                         daInfo.Remark1 = dtExcel.Rows[i][11].ToString();
112                         daInfo.Guid = Guid.NewGuid().ToString();
113 
114                         IsSucc = daDB.AddDynamicAccountInfo(daInfo);
115                     }
116                     File.Delete(_strfilename);//删除服务器的临时文件
117                     if (IsSucc)
118                     {
119                         string tempScript = "<script language='javascript' type='text/javascript'> alert('添加数据成功 ！');window.close();</script> ";
120                         ClientScript.RegisterStartupScript(this.GetType(), "", tempScript);
121                     }
122                     else
123                     {
124                         SetInfo("添加数据失败！");
125                     }
126 
127                 }
128                 else
129                 {
130                     File.Delete(_strfilename);//删除服务器的临时文件
131                    
132                 }
133             }
134         }
135         catch (Exception ex)
136         {
137             SetInfo(ex.Message);
138         }
139 
140     }
141 
142 

首先在项目文件夹中创建一个Excel文件
daDB.ExeclToDataTable(_strfilename, "Sheet1");

 1  public DataTable ExeclToDataTable(string strfilename, string strSheetName)
 2     {
 3         string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strfilename + ";" + "Extended Properties=Excel 5.0;";
 4 
 5         string strExcel = string.Format("select * from [{0}$]", strSheetName);
 6         DataSet ds = new DataSet();
 7 
 8         using (OleDbConnection conn = new OleDbConnection(strconn))
 9         {
10             conn.Open();
11             OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strconn);
12             adapter.Fill(ds, strSheetName);
13             conn.Close();
14         }
15 
16         return ds.Tables[strSheetName];
17     }

daDB.AddDynamicAccountInfo(daInfo);
是一个插入语句。