一区二区久久-一区二区三区www-一区二区三区久久-一区二区三区久久精品-麻豆国产一区二区在线观看-麻豆国产视频

phpexcel導入excel數據使用方法實例

2014-10-22 19:29:10 分類：php技術 閱讀()

將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,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;
    }

php技術：phpexcel導入excel數據使用方法實例，轉載需保留來源！

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。

標簽：
主站蜘蛛池模板： 色综合天天综合高清网国产 | 天堂在线www天堂中文在线 | 9ⅰ视频在线播放 | 四虎免费最新在线永久 | 欧美亚洲另类色国产综合 | 免费黄色美女网站 | 日韩中文字幕视频 | 德国女人一级毛片免费 | 黄网站色成年片大免费软件 | 亚洲伊人国产 | 亚洲精品永久www嫩草 | 成人亚洲网站 | 青青草久久伊人 | 亚洲成人福利在线观看 | 伊人精品网 | 国产成人免费高清激情明星 | 欧美日本高清一本二本三本 | 国产第一页在线视频 | 免费观看国产精品 | 91丨九色丨对白 | 亚洲六月婷婷 | 中文字幕视频一区二区 | 亚洲综合色婷婷 | 色网在线免费观看 | 国产剧情在线视频 | 日韩激情影院 | 日韩一级片网址 | 国产91一区二区在线播放不卡 | 超91精品手机国产在线 | 欧美激情在线精品三区 | 色婷婷综合缴情综六月 | 午夜在线观看视频在线播放版 | 久久成年人视频 | 91成人网 | 国产不卡福利 | 午夜乩伦| 黄色免费在线观看视频 | 国产成人亚洲综合 | 免费黄色视屏 | 亚洲激情视频 | 国产成人综合在线视频 |