将Excel文件数据进行读取,并且返回错误的信息 代码如下:/** * 导入商品基本信息 */ public function importProductBasicInfo($data){ include_once 'PHPExcel.php'; include_once 'PHPExcel/IOFactory.php'; include_once 'PHPExcel/Reader/Excel5.php'; // 定义一个错误集合. $error = array(); $resultInfo = null; $needNext = true; //上传文件到服务器指定位置 $fileName = $_FILES["productinfo"]['name']; $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"],'product'); //如果上传文件成功,就执行导入excel操作 if($filePath == 1) { $error[1] = "上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值"; }else if($filePath == 4){ $error[4] = "没有文件被上传"; }else{ $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filePath); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $colums = array(); $data = array(); $excelAllId = array(); $excelIdRow = array(); $execlAllShopLinkedId = array(); for($i=0;$i<$highestColumnIndex;$i++){ $cValue = trim($objWorksheet->getCellByColumnAndRow($i,1)->getValue()); switch ($cValue) { case self::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break; case self::PRODUCT_NAME : $colums[$i] = "pname"; break; case self::PRODUCT_GROUP : $colums[$i] = "product_group"; break; case self::PRODUCT_BRAND : $colums[$i] = 'product_brand'; break; case self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag"; break; case self::PRODUCT_BINNING : $colums[$i] = "product_binning"; break; case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick"; break; case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break; case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break; case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code"; break; case self::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break; default : $error[3][] = $cValue; break; } } //检测Excel中的基本信息是否存在 $dataCount = $highestRow - 1; if(count($colums) == 0) { $error[5] = "没有表头"; } else if(!in_array('sap_code',$colums)){ $error[2] = "表头中商品SAP编码不存在"; } else if($dataCount <= 0){ $error[6] = "Excel文件中没有数据"; } else if(count($error)==0){ for ($i=2;$i<=$highestRow;$i++){ $colkey = array_search('sap_code'); $shopLinkedIdValue = trim($objWorksheet->getCellByColumnAndRow($colkey,$i)->getValue()); if(!$shopLinkedIdValue) { continue; } if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){ $error[7][$shopLinkedIdValue]['duplicate'] = true; $error[7][$shopLinkedIdValue]['excelRow'][] = $i; $execlAllShopLinkedId[$i] = $shopLinkedIdValue; $error[7][$shopLinkedIdValue]['noId'] = true; }else { $excelIdRow[$shopLinkedIdValue] = $i; $execlAllShopLinkedId[$i] = $shopLinkedIdValue; } } $dealMultiple = ceil($dataCount / 1000); $allProduct = array(); for($i=0;$i<$dealMultiple;$i++){ $offset = $i*1000+2; $max = ($i+1)*1000+1; $max = ($max > $dataCount) ? $highestRow : $max; $allShopLinkedId = array(); for($j=$offset;$j<=$max;$j++){ if($execlAllShopLinkedId[$j]){ $allShopLinkedId[] = $execlAllShopLinkedId[$j]; } } // 根据SAP商品编码查询在库中的记录数. $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId); for($j=$offset;$j<=$max;$j++){ $product = array(); for($k=0;$k<$highestColumnIndex;$k++){ $tempV = trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue()); if($tempV && $tempV != '') { $product[$colums[$k]] = $tempV; } } //获取文件中的SAP编码 $id = $product['sap_code']; if(!$id){ continue; } //检测商品SAP编码是否已经存在 if(!in_array($id,$dbShopProducts)){ $allProduct[$id] = $product; }else{ $error[7][$id]['hasId'] = true; } //商品名是否为空 if(!isset($product['pname'])){ $error[7][$id]['emptyName'] = true; } //商品类目(商品组)是否为空 if(!isset($product['product_group'])){ $error[7][$id]['emptyProductGroup'] = true; } //产品层次(品牌)是否为空 if(!isset($product['product_brand'])){ $error[7][$id]['emptyProductBrand'] = true; } //经代销标志是否为空 if(!isset($product['product_proxy_flag'])){ $error[7][$id]['emptyProductProxyFlag'] = true; } //装箱清单是否为空 if(!isset($product['product_binning'])){ $error[7][$id]['emptyProductBinning'] = true; } //先销后采标识是否为空 if(!isset($product['product_sell_pick'])){ $error[7][$id]['emptyProductSellPick'] = true; } //商品属性是否为空 if(!isset($product['product_attribute'])){ $error[7][$id]['emptyProductAttribute'] = true; } //供应商编码是否为空 if(!isset($product['vendor_code'])){ $error[7][$id]['emptyVendorCode'] = true; } //供应地点是否为空 if(!isset($product['zzwerk_code'])){ $error[7][$id]['emptyZzwerkCode'] = true; } //库区是否为空 if(!isset($product['zzlgort_code'])){ $error[7][$id]['emptyZzlgortCode'] = true; } if(isset($error[7][$id])){ $error[7][$id]['excelRow'] = $j; } } } } } $resultInfo['fileName'] = $fileName; //返回错误信息 if(count($error)>0){ if(isset($error[1])){ $resultInfo['type'] = 1; $resultInfo['msg'] = $error[1]; }else if(isset($error[2])){ $resultInfo['type'] = 2; $resultInfo['msg'] = $error[2]; }else if(isset($error[3])){ $resultInfo['type'] = 3; $resultInfo['msg'] = '表头【'.implode(',',$error[3]).'】不存在'; }else if(isset($error[4])){ $resultInfo['type'] = 4; $resultInfo['msg'] = $error[4]; }else if(isset($error[6])){ $resultInfo['type'] = 6; $resultInfo['msg'] = $error[6]; }else if(isset($error[7])){ $excelName = null; $objPHPWriteExcel = new PHPExcel(); $objPHPWriteExcel->getProperties()->setCreator("yuer") ->setLastModifiedBy("yuer")->setTitle("")->setSubject("") ->setDescription("")->setKeywords("")->setCategory(""); $prefix = substr($fileName,strrpos($fileName,'.')); $suffix = substr($fileName,'.')); $excelName = date("Y_m_d_H_i_s").'_'.mt_rand(1,99).'_'.$prefix.'ErrorReport'.$suffix; $excelName = Base_Tool_Pinyin::getPinyin($excelName); $objPHPWriteExcel->setActiveSheetIndex(0); $activeSheet = $objPHPWriteExcel->getActiveSheet(); $activeSheet->setTitle('错误报告'); $activeSheet->setCellValueByColumnAndRow(0,1,self::PRODUCT_SAP_CODE); $activeSheet->setCellValueByColumnAndRow(1,'原excel行号'); $activeSheet->setCellValueByColumnAndRow(2,'第几行编码存在重复'); $activeSheet->setCellValueByColumnAndRow(3,self::PRODUCT_NAME); $activeSheet->setCellValueByColumnAndRow(4,self::PRODUCT_GROUP); $activeSheet->setCellValueByColumnAndRow(5,self::PRODUCT_BRAND); $activeSheet->setCellValueByColumnAndRow(6,self::PRODUCT_PROXY_FLAG); $activeSheet->setCellValueByColumnAndRow(7,self::PRODUCT_BINNING); $activeSheet->setCellValueByColumnAndRow(8,self::PRODUCT_SELL_PICK); $activeSheet->setCellValueByColumnAndRow(9,self::PRODUCT_ATTRIBUTE); $activeSheet->setCellValueByColumnAndRow(10,self::PRODUCT_SUPPLIER_CODE); $activeSheet->setCellValueByColumnAndRow(11,self::PRODUCT_SUPPLY_ADDRESS); $activeSheet->setCellValueByColumnAndRow(12,self::PRODUCT_BATCH); $activeSheet->setCellValueByColumnAndRow(13,'其他原因'); $activeSheet->getColumnDimensionByColumn(0)->setWidth(15); $activeSheet->getColumnDimensionByColumn(1)->setWidth(20); $activeSheet->getColumnDimensionByColumn(2)->setWidth(20); $activeSheet->getColumnDimensionByColumn(3)->setWidth(20); $activeSheet->getColumnDimensionByColumn(4)->setWidth(20); $activeSheet->getColumnDimensionByColumn(5)->setWidth(20); $activeSheet->getColumnDimensionByColumn(6)->setWidth(20); $activeSheet->getColumnDimensionByColumn(7)->setWidth(20); $activeSheet->getColumnDimensionByColumn(8)->setWidth(20); $activeSheet->getColumnDimensionByColumn(9)->setWidth(20); $activeSheet->getColumnDimensionByColumn(10)->setWidth(20); $activeSheet->getColumnDimensionByColumn(11)->setWidth(20); $activeSheet->getColumnDimensionByColumn(12)->setWidth(20); $activeSheet->getColumnDimensionByColumn(13)->setWidth(20); $writeExcelIndex = 2; foreach ($error[7] as $pId=>$pInfo){ if(isset($pInfo['hasId'])){ $activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.'-此供应商编码已经存在'); } else { $activeSheet->setCellValueByColumnAndRow(0,$pId); } $activeSheet->setCellValueByColumnAndRow(1,$pInfo['excelRow']); if(isset($pInfo['duplicate'])){ $activeSheet->setCellValueByColumnAndRow(2,$excelIdRow[$pId]); } if(isset($pInfo['emptyName'])){ $activeSheet->setCellValueByColumnAndRow(3,'-为空'); } if(isset($pInfo['emptyProductGroup'])){ $activeSheet->setCellValueByColumnAndRow(4,'-为空'); } if(isset($pInfo['emptyProductBrand'])){ $activeSheet->setCellValueByColumnAndRow(5,'-为空'); } if(isset($pInfo['emptyProductProxyFlag'])){ $activeSheet->setCellValueByColumnAndRow(6,'-为空'); } if(isset($pInfo['emptyProductBinning'])){ $activeSheet->setCellValueByColumnAndRow(7,'-为空'); } if(isset($pInfo['emptyProductSellPick'])){ $activeSheet->setCellValueByColumnAndRow(8,'-为空'); } if(isset($pInfo['emptyProductAttribute'])){ $activeSheet->setCellValueByColumnAndRow(9,'-为空'); } if(isset($pInfo['emptyVendorCode'])){ $activeSheet->setCellValueByColumnAndRow(10,'-为空'); } if(isset($pInfo['emptyZzwerkCode'])){ $activeSheet->setCellValueByColumnAndRow(11,'-为空'); } if(isset($pInfo['emptyZzlgortCode'])){ $activeSheet->setCellValueByColumnAndRow(12,'-为空'); } if(isset($pInfo['other'])){ $activeSheet->setCellValueByColumnAndRow(13,$pInfp['other']); } $writeExcelIndex++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPWriteExcel,'Excel5'); $excelPath = FILE_PATH.DS.'feedback'.DS.$excelName; $objWriter->save($excelPath); $resultInfo['type'] = 7; $resultInfo['msg'] = $fileName."文件中存在错误"; $resultInfo['errorReport'] = $excelName; // 日志操作,暂时空着 } }else{ //导入数据 $logIds = ''; $i = 0; foreach ($allProduct as $pId => $pInfo){ $updateProductSql = 'insert into yr_product set '; if(isset($pInfo['pname']) && trim($pInfo['pname'])){ $updateProductSql = $updateProductSql.'pname=''.str_replace(''','''',$pInfo['pname']).'','; } //如果SAP编码不足18位,则用0从左开始补全 if(isset($pInfo['sap_code'])){ if(strlen($pInfo['sap_code'])<18){ $pInfo['sap_code'] = str_pad($pInfo['sap_code'],18,"0",STR_PAD_LEFT); $updateProductSql = $updateProductSql.'sap_code=''.str_replace(''',$pInfo['sap_code']).'','; } } if(isset($pInfo['product_group'])){ $updateProductSql = $updateProductSql.'product_group=''.$pInfo['product_group'].'','; } if(isset($pInfo['product_brand'])){ $updateProductSql = $updateProductSql.'product_brand=''.$pInfo['product_brand'].'','; } if(isset($pInfo['product_proxy_flag'])){ $updateProductSql = $updateProductSql.'product_proxy_flag=''.$pInfo['product_proxy_flag'].'','; } if(isset($pInfo['product_binning'])){ $updateProductSql = $updateProductSql.'product_binning=''.$pInfo['product_binning'].'','; } if(isset($pInfo['product_sell_pick'])){ $updateProductSql = $updateProductSql.'product_sell_pick=''.$pInfo['product_sell_pick'].'','; } if(isset($pInfo['product_attribute'])){ $updateProductSql = $updateProductSql.'product_attribute=''.$pInfo['product_attribute'].'','; } if(isset($pInfo['vendor_code'])){ $updateProductSql = $updateProductSql.'vendor_code=''.$pInfo['vendor_code'].'','; } if(isset($pInfo['zzwerk_code'])){ $updateProductSql = $updateProductSql.'zzwerk_code=''.$pInfo['zzwerk_code'].'','; } if(isset($pInfo['zzlgort_code'])){ $updateProductSql = $updateProductSql.'zzlgort_code=''.$pInfo['zzlgort_code'].'''; } //最终的SQL语句 $result = $this->excuteMultiInsertSql($updateProductSql); } $resultInfo['type'] = 8; $resultInfo['msg'] = "导入商品基本信息成功"; /* * // 日志操作. * $content = '批量新建商品导入操作成功:导入的供应商品编码有->'; * $logData['content'] = $content.$logIds; */ } return $resultInfo; }
(编辑:安卓应用网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|