phpexcel匯入excel資料的範例程式碼

/** * 匯入商品基本資料 * edit: bbs.it-home.org */ 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,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'])<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; } 複製代碼

