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

将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;
    }