phpexcel導入excel資料的範例程式碼-php教程-PHP中文網

/**
* 匯入商品基本資訊
* edit: bbs.it-home.org
*/
public function importProductBasicInfo$data){
include PHPExcel.php';
include_once 'PHPExcel/IOFactory.php';
include_once 'PHPExcel/Reader/Excel5.php';
// 定義一個錯誤集合.
$error = php';
// 定義一個錯誤集合.
$error = php';
// 定義一個錯誤集合.
$error = php'; $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 = $objRe.> $filePath);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet-getHighheum highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$colums = array();
$data = array();
$Id(AllId = array(); $excel( ;
$execlAllShopLinkedId = array();
for($i=0;$i $cValue = trim($objWorksheet->getCellByumnAndRow($i,11) ->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_brandAND : $colums[$i] = 'product_brandAND : $colums[$i] = 'product_brandAND : $colums[$i] = 'product_brandAND : $colums[$i] = 'product_brand '; break;
case self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag"; break;
case self::PRODUCT_BINNING : $colums[$i] = "product_bin"; > case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick"; break;
case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break; case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code"; break;
case self::DUCT_BATCH PRO. ] = "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 $error[6] = "Excel檔案中沒有資料";
}
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]['dtrueuplicValue' = 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] ;
}
}
// 根據SAP商品編碼查詢在庫中的記錄數.
$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;
}
}
//取得檔案中的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][$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")
-ifiedLastMod ("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 ->setCellValueByygAnd 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) ;
$activeactiveSheet- :PRODUCT_BRAND);
$activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
$activeSheet->setCellValueByColumnAndRow (7,1,1,221,3); (8,1,self::PRODUCT_SELL_PICK);
$activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE) ;
$activeSheet->setCellValueBySUumnAndRow(10,1. 🎜> $activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
$activeSheet->setCellValueByColumnAndRow (12,1,self::PRODUCT_BATCH); '其他原因');
$activeSheet->getColumnDimensionByColumn(0)->setWidth(15) ;
$activeSheet->getColumnDimensionByColumn(1)->setWidth(20);setWidth(20);setWidth(20);
$activeSheet->getColumnDimensionByColumn (3)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(4)->setWidth(20);getColumnDimensionByColumn(5)->setWidth(20) ;
$activeSheet->getColumnDimensionByColumn(6)->setWidth(20);
$activeSheet->getColumnDisionBy.
$activeSheet->getColumnDimensionByColumn (8)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(9)->setWidth(20);
$activeSheet-Bgetumn( (20) ;
$activeSheet->getColumnDimensionByColumn(11)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(12)->setWidth(20);setWidth(20);
$writeExcelIndex = 2;
foreach ($error[7] as $pId=>$pInfo){
if(isset($pInfo['hasId'] ) ){
$activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.'-此供應商編碼已存在');
} else {
$activeactiveSheet->setCellValueByValueBellMo(celdemoneamadmdaldimaad,adimaadsadsad,adakfIncxIncmOdemak,ad , $pId);
}
$activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo['excelRow']);
if(isset($pInfo['duplicate) '])){
$activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
}
if(isset($pInfo['emptyName'])){
$active(isset($pInfo['emptyName'])){
$active-t setCellValueByColumnAndRow (3,$writeExcelIndex,'-為空');
}
if(isset($pInfo['emptyProductGroup'])){
$activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,'-為空');
}
if(isset( $ pInfo['emptyProductBrand'])){
$activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,'-為空');
}
if(isset($pInfo['emptyProduct');
}
if(isset($pInfo['emptyProduct]Proxy'plag') {
$activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,'-為空');
}
if(isset($pInfo['emptyProductBinning'])){
$SmInfo['emptyProductBinning'])){
$Sheet-> setCellValueByColumnAndRow(7,$writeExcelIndex,'-為空');
}
if(isset($pInfo['emptyProductSellPick'])){
$activeSheet->setCellcelValueByn,Andcowrite( -為空');
}
if(isset($pInfo['emptyProductAttribute'])){
$activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,'-為空'); }
if(isset($pInfo['emptyVendorCode'])){
$activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,'-為空');
}
}
if(ississet ($pInfo['emptyZzwerkCode'])){
$activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,'-為空');
}
if(isset ($pInfopInfo['emptyZnepInfo )){
$activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,'-為空');
}
if(isset($pInfo['other']) ){
$activeSheet->setCellValueByColumnAndRow(13,$writeExcelIndex,$pInfp['other']);
}
$writeExcelIndex ;
} $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 = '插入yr_product 集合';
if(isset($pInfo['pname']) && trim($pInfo['pname') '])){
$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','''',$pInfo[ 'sap_code']).'',';
}
}
if(isset($pInfo['product_group'])) {
$updateProductSql = $updateProductSql.'product_groupl.'product='' .$pInfo['product_group'].'',';
}
if(isset($pInfo['product_brand'])){
$updateProductSql = $updateProductSql.'product_brand=''. $pInfo['product_brand'].'',';
}
if(isset($pInfo['product_proxy_flag'])){
$updateProductSql = $updateProductSql.'' pInfo['product_proxy_flag'].'',';
}
if(isset($pInfo['product_binning'])){
$updateProductSql = $updateProductSInfol.' > $updateProductSql = $updateProductSInfol.'product_bin.' ['product_binning'].'',';
}
if(isset($pInfo['product_sell_pick'])){
$updateProductSql = $updateProductSql.'product''sell_pick[ 'product_sell_pick'].'',';
}
if(isset($pInfo['product_attribute'])){
$ updateProductSql = $updateProductSInfol.'product''f產品屬性'].'',';
}
if(isset($pInfo['vendor_code'])){
$updateProductSql = $updateProductSql.'vendor_code=''.$pInfo[' vendor_code '].'',';
}
if(isset($pInfo['zzwerk_code'])){ $updateProductSql = $updateProductSql.'zzwerk_code=''.$pInfo ' ].'','; }
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;
}

複製程式碼