public HttpResponseBase ImportExcel() { string newName = string.Empty; string json = string.Empty; InspectionReportQuery query = new InspectionReportQuery(); CertificateCategoryQuery ccquery = new CertificateCategoryQuery(); List<CertificateCategoryQuery> CClist = new List<CertificateCategoryQuery>(); List<CertificateCategoryQuery> CClistTwo = new List<CertificateCategoryQuery>(); _inspectionReport = new BLL.gigade.Mgr.InspectionReportMgr(mySqlConnectionString); try { if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0) { HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"]; newName = Server.MapPath(excelPath) + excelFile.FileName; excelFile.SaveAs(newName); DataTable dt = new DataTable(); NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName); dt = helper.SheetData(); uint brandID = 0; int truenum = 0; json = "{success:true,msg:0,total:0}"; if (dt.Rows.Count > 0) { #region 循環數據判斷是否符合要求 foreach (DataRow dr in dt.Rows) { if (string.IsNullOrEmpty(dr[0].ToString())) { continue; } uint num = 0; if (uint.TryParse(dr[0].ToString(), out num)) { query.product_id = num; if (_inspectionReport.GetProductById(query))//判斷商品id是否存在 { brandID= _inspectionReport.GetBrandId(query.product_id); ccquery.certificate_categorycode = dr[1].ToString();//大類code是否存在 CClist = _inspectionReport.CheckBigCode(ccquery); if (CClist != null && CClist.Count > 0) { query.certificate_type1 = CClist[0].rowID.ToString(); ccquery.frowID = CClist[0].rowID; ccquery.certificate_category_childcode = dr[2].ToString(); CClistTwo = _inspectionReport.GetLsit(ccquery); if (CClistTwo != null && CClistTwo.Count > 0)//小類code是否存在 { query.certificate_type2 = CClistTwo[0].rowID.ToString(); DateTime dtime = DateTime.MinValue; if (DateTime.TryParse(dr[3].ToString(), out dtime))//判斷有校時間格式是否正確 { query.certificate_expdate = dtime; query.certificate_desc = dr[4].ToString(); query.certificate_filename=brandID.ToString()+"-"+query.product_id.ToString()+"-"+ccquery.certificate_categorycode+"-"+ccquery.certificate_category_childcode+".jpg"; if (!string.IsNullOrEmpty(dr[5].ToString())&&query.certificate_filename==dr[5].ToString())//判斷檔案是否為空,對應的檔案名稱是否正確 { query.certificate_filename = dr[5].ToString(); query.k_user = (Session["caller"] as Caller).user_id; query.m_user = (Session["caller"] as Caller).user_id; query.k_date = DateTime.Now; query.m_date = DateTime.Now; if (!_inspectionReport.CheckInspectionReport(query))//判斷數據是否已存在 { if (_inspectionReport.InsertInspectionReport(query))//新增數據 { truenum++;//一共新增成功多少條數據 } } // //else //{ // if (_inspectionReport.UpdateInspectionReport(query))//新增數據 // { // truenum++;//一共新增成功多少條數據 // } // else // { // json = "{success:false}"; // } //} // } } } } } } } #endregion json = "{success:true,msg:1,total:" + truenum + "}"; } else { json = "{success:true,msg:3:total:0}"; } } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }