phpexcel导入excel数据使用方法实例
php  /  管理员 发布于 6年前   111
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;
}
123 在
Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..路人 在
php中使用hyperf框架调用讯飞星火大模型实现国内版chatgpt功能示例中评论 教程很详细,如果加个前端chatgpt对话页面就完美了..Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号