phpexcel导入excel数据使用方法实例

/**
    *导入商品基本信息
    */
   publicfunctionimportProductBasicInfo($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选项限制的值";
       }elseif($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){
                   caseself::PRODUCT_SAP_CODE:$colums[$i]="sap_code";break;
                   caseself::PRODUCT_NAME:$colums[$i]="pname";break;
                   caseself::PRODUCT_GROUP:$colums[$i]="product_group";break;
                   caseself::PRODUCT_BRAND:$colums[$i]="product_brand";break;
                   caseself::PRODUCT_PROXY_FLAG:$colums[$i]="product_proxy_flag";break;
                   caseself::PRODUCT_BINNING:$colums[$i]="product_binning";break;
                   caseself::PRODUCT_SELL_PICK:$colums[$i]="product_sell_pick";break;
                   caseself::PRODUCT_ATTRIBUTE:$colums[$i]="product_attribute";break;
                   caseself::PRODUCT_SUPPLIER_CODE:$colums[$i]="vendor_code";break;
                   caseself::PRODUCT_SUPPLY_ADDRESS:$colums[$i]="zzwerk_code";break;
                   caseself::PRODUCT_BATCH:$colums[$i]="zzlgort_code";break;
                   default:$error[3][]=$cValue;break;
               }
           }
           //检测Excel中的基本信息是否存在
           $dataCount=$highestRow-1;
           if(count($colums)==0){
               $error[5]="没有表头";
           }
           elseif(!in_array("sap_code",$colums)){
               $error[2]="表头中商品SAP编码不存在";
           }
           elseif($dataCount<=0){
               $error[6]="Excel文件中没有数据";
           }
           elseif(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];
           }elseif(isset($error[2])){
               $resultInfo["type"]=2;
               $resultInfo["msg"]=$error[2];
           }elseif(isset($error[3])){
               $resultInfo["type"]=3;
               $resultInfo["msg"]="表头【".implode(",",$error[3])."】不存在";
           }elseif(isset($error[4])){
               $resultInfo["type"]=4;
               $resultInfo["msg"]=$error[4];
           }elseif(isset($error[6])){
               $resultInfo["type"]=6;
               $resultInfo["msg"]=$error[6];
           }elseif(isset($error[7])){
               $excelName=null;
               $objPHPWriteExcel=newPHPExcel();
               $objPHPWriteExcel->getProperties()->setCreator("yuer")
               ->setLastModifiedBy("yuer")->setTitle("")->setSubject("")
               ->setDescription("")->setKeywords("")->setCategory("");
               $prefix=substr($fileName,0,strrpos($fileName,"."));
               $suffix=substr($fileName,strrpos($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,1,"原excel行号");
               $activeSheet->setCellValueByColumnAndRow(2,1,"第几行编码存在重复");
               $activeSheet->setCellValueByColumnAndRow(3,1,self::PRODUCT_NAME);
               $activeSheet->setCellValueByColumnAndRow(4,1,self::PRODUCT_GROUP);
               $activeSheet->setCellValueByColumnAndRow(5,1,self::PRODUCT_BRAND);
               $activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
               $activeSheet->setCellValueByColumnAndRow(7,1,self::PRODUCT_BINNING);
               $activeSheet->setCellValueByColumnAndRow(8,1,self::PRODUCT_SELL_PICK);
               $activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE);
               $activeSheet->setCellValueByColumnAndRow(10,1,self::PRODUCT_SUPPLIER_CODE);
               $activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
               $activeSheet->setCellValueByColumnAndRow(12,1,self::PRODUCT_BATCH);
               $activeSheet->setCellValueByColumnAndRow(13,1,"其他原因");
               $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,$writeExcelIndex,$pId);
                   }
                   $activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo["excelRow"]);
                   if(isset($pInfo["duplicate"])){
                       $activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
                   }
                   if(isset($pInfo["emptyName"])){
                       $activeSheet->setCellValueByColumnAndRow(3,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductGroup"])){
                       $activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductBrand"])){
                       $activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductProxyFlag"])){
                       $activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductBinning"])){
                       $activeSheet->setCellValueByColumnAndRow(7,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductSellPick"])){
                       $activeSheet->setCellValueByColumnAndRow(8,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyProductAttribute"])){
                       $activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyVendorCode"])){
                       $activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyZzwerkCode"])){
                       $activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,"-为空");
                   }
                   if(isset($pInfo["emptyZzlgortCode"])){
                       $activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,"-为空");
                   }

                   if(isset($pInfo["other"])){
                       $activeSheet->setCellValueByColumnAndRow(13,$writeExcelIndex,$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($allProductas$pId=>$pInfo){
               $updateProductSql="insertintoyr_productset";
               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;
   }