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;$igetcellbycolumnandrow($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 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 $datacount) ? $highestrow : $max; $allshoplinkedid = array(); for($j=$offset;$jgetshoplinkedbyids($allshoplinkedid); for($j=$offset;$jgetvalue()); 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 $phasid’])){ $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’])’; * $logdata[‘content’] = $content.$logids; */ } return $resultinfo; }

http://www.bkjia.com/phpjc/621672.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/621672.htmltecharticle将excel文件数据进行读取,并且返回错误的信息 代码如下: /** * 导入商品基本信息 */ public function importproductbasicinfo($data){ include_o…

Posted in 未分类