phpexcel导入excel数据使用方法实例
程序员文章站
2023-11-04 22:40:40
将excel文件数据进行读取,并且返回错误的信息复制代码 代码如下:/** * 导入商品基本信息 &n...
将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;
}
下一篇: 中国软科排名2021-软科大学排名查询
推荐阅读