asp.net Excel数据导入到数据库中
protected void Btn_Import_Click(object sender, EventArgs e) { bool Result_Import = false; bool Result = false; Guid RanageID = Guid.NewGuid(); string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower(); if (FileUpload1.HasFile == false) { Function.AlertMsg("请您先选择后缀名为.xls或.xlsx的Excel文件", "SetForm.aspx?eid=" + EmployeeID); } else if (IsXls != ".xls" && IsXls != ".xlsx") { Function.AlertMsg("请选择后缀名为.xls或.xlsx的Excel文件", "SetForm.aspx?eid=" + EmployeeID); } else { string NewFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random().Next(99, 9999) + IsXls;//新文件名 string NewPath = Server.MapPath("../attachment/Excel/") + NewFileName;//服务器保存路径 FileUpload1.SaveAs(NewPath); DataSet Ds = ExcelDs(NewPath, NewFileName); DataRow[] Dr = Ds.Tables[0].Select(); int RowsNum = Ds.Tables[0].Rows.Count; if (RowsNum.Equals(0)) { Function.AlertMsg("该考核表为空表,请重新上传!", "SetForm.aspx?eid=" + EmployeeID); } else { string RanageIDs = ""; #region for (int i = 0; i < Dr.Length; i++)//遍历Excel中的考核范围并添加到数据库 { string Ranage = Dr[i]["范围"].ToString(); string Weight = Dr[i]["权重"].ToString(); string Target = Dr[i]["考核指标"].ToString(); string Standard = Dr[i]["考核标准"].ToString(); string Remark = Dr[i]["描述"].ToString(); if (Ranage != "" && Weight != "") { RanageID = Guid.NewGuid(); opa_appraiseranage.ID = RanageID; opa_appraiseranage.Ranage = Ranage; opa_appraiseranage.Weight = int.Parse(Weight); opa_appraiseranage.IsImport = true; Result = OPA_AppraiseRanage.InsertRanageByEID(opa_appraiseranage); if (Result) { RanageIDs = RanageIDs + "'" + RanageID + "',"; Result_Import = true; } else { Result_Import = false; break; } } if (Target != "") { opa_appraisetarget.RanageID = RanageID; opa_appraisetarget.Target = Target; opa_appraisetarget.Standard = Standard; opa_appraisetarget.Remark = Remark; opa_appraisetarget.IsImport = true; bool resultTarget = OPA_AppraiseTarget.InsertTargetByEID(opa_appraisetarget); if (resultTarget) { Result_Import = true; } else { Result_Import = false; break; } } }//考核表录入循环完毕 #endregion #region if (Result_Import)//表格导入成功,则删除该员工原有的考核表信息 { bool R = OPA_AppraiseRanage.DeleteBeforeRanageTargetAndUpdateIsImportByEID(opa_appraiseranage); if (R) { Function.AlertMsg("考核表信息导入成功!", "PreviewAppraiseForm.aspx?eid=" + EmployeeID); File.Delete(NewPath);//删除上传的考核表Excel文件 } else { RanageIDs = RanageIDs.Substring(0, RanageIDs.Length - 1); opa_appraiseranage.IDs = RanageIDs; bool Result_D = OPA_AppraiseRanage.DeleteImportRanageTargetByRIDs(opa_appraiseranage);//根据考核范围ID和状态IsImport=1删除刚才导入的考核范围指标信息 if (Result_D) { Function.AlertMsg("考核表信息录入失败,数据已成功回滚,请检查数据后尝试重新录入!", "SetForm.aspx?eid=" + EmployeeID); } else { Function.AlertMsg("考核表信息录入失败,数据回滚失败,请联系管理员!", "SetForm.aspx?eid=" + EmployeeID); } } } #endregion } } }
public DataSet ExcelDs(string FilenamePath, string Table) { string strConn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ='" + FilenamePath + "';Extended Properties='Excel 8.0'"; OleDbConnection Odbconn = new OleDbConnection(strConn); OleDbDataAdapter Odda = new OleDbDataAdapter("select * from [Sheet1$]", Odbconn); DataSet Ds = new DataSet(); Odda.Fill(Ds, Table); return Ds; }