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