Sample code for phpexcel to import excel data

/**
 * Import basic product information
 * edit: bbs.it-home.org
*/
 public function importProductBasicInfo($data){
 include_once 'PHPExcel.php';
 include_once 'PHPExcel/IOFactory.php';
 include_once 'PHPExcel/Reader/Excel5.php';
 //Define an error collection.
 $error = array();
 $resultInfo = null;
 $needNext = true;
 //Upload files to the specified location on the server
 $ fileName = $_FILES["productinfo"]['name'];
 $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], 'product');
 //If the file is uploaded successfully, execute the import excel operation
 if($filePath == 1) {
 $error[1] = "The uploaded file exceeds the value limited by the upload_max_filesize option in php.ini";
 }else if($filePath == 4){
 $error[ 4] = "No file was uploaded";
 }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 $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;
 }
 }
 //Detect whether basic information in Excel exists
 $ dataCount = $highestRow - 1;
 if(count($colums) == 0) {
 $error[5] = "No header";
 }
 else if(!in_array('sap_code',$colums)) {
 $error[2] = "The product SAP code in the header does not exist";
 }
 else if($dataCount  $error[6] = "There is no data in the Excel file";
 }
 else if(count($error)==0){
 for ($i=2;$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 $offset = $i*1000+2;
 $max = ($i+1)*1000+1;
 $max = ($max > $dataCount) ? $highestRow : $max;
 $allShopLinkedId = array();
 for($j=$offset; $j if($execlAllShopLinkedId[$j]){
 $allShopLinkedId[] = $execlAllShopLinkedId[$j];
 }
 }
 // Query in the library based on SAP product code The number of records.
 $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);
 for($j=$offset;$j $ product = array();
 for($k=0;$k $tempV = trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue()) ;
 if($tempV && $tempV != '') {
 $product[$colums[$k]] = $tempV;
 }
 }
 //Get the SAP encoding in the file
 $id = $product[ 'sap_code'];
 if(!$id){
 continue;
 }
 //Check whether the product SAP code already exists
 if(!in_array($id,$dbShopProducts)){
 $allProduct[$id] = $product;
 }else{
 $error[7][$id]['hasId'] = true;
 }
 //Whether the product name is empty
 if(!isset($product['pname'])) {
 $error[7][$id]['emptyName'] = true;
 }
 //Whether the product category (product group) is empty
 if(!isset($product['product_group'])){
 $error[7][$id]['emptyProductGroup'] = true;
 }
 //Whether the product level (brand) is empty
 if(!isset($product['product_brand'])){
 $error [7][$id]['emptyProductBrand'] = true;
 }
 //Whether the proxy sales flag is empty
 if(!isset($product['product_proxy_flag'])){
 $error[7][$ id]['emptyProductProxyFlag'] = true;
 }
 //Whether the packing list is empty
 if(!isset($product['product_binning'])){
 $error[7][$id]['emptyProductBinning '] = true;
 }
 //Whether the sell first, buy later indicator is empty
 if(!isset($product['product_sell_pick'])){
 $error[7][$id]['emptyProductSellPick'] = true;
 }
 //Whether the product attribute is empty
 if(!isset($product['product_attribute'])){
 $error[7][$id]['emptyProductAttribute'] = true;
 }
 / /Whether the supplier code is empty
 if(!isset($product['vendor_code'])){
 $error[7][$id]['emptyVendorCode'] = true;
 }
 //Whether the supply location is Empty
 if(!isset($product['zzwerk_code'])){
 $error[7][$id]['emptyZzwerkCode'] = true;
 }
 //Whether the library area is empty
 if(!isset ($product['zzlgort_code'])){
 $error[7][$id]['emptyZzlgortCode'] = true;
 }
 if(isset($error[7][$id])){
 $ error[7][$id]['excelRow'] = $j;
 }
 }
 }
 }
 }
 $resultInfo['fileName'] = $fileName;
 //Return error message
 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'] = 'Header ['.implode(',',$error[3]).'] does not exist';
 }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,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 ($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'])                        $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'].''';
 }
 //Final SQL statement
 $result = $this->excuteMultiInsertSql($updateProductSql);
 }
 $resultInfo['type'] = 8;
 $resultInfo['msg'] = "Importing basic product information successfully";
  /*
 * // Log operation.
 * $content = 'Batch new product import operation successful: imported supply product codes are ->';
 * $logData['content'] = $content. $logIds;
 */
 }
 return $resultInfo;
 }