phpexcel导入excel数据使用方法实例_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 $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 $error[6] = "Excel文件中没有数据";
}
else if(count($error)==0){
for ($i=2;$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 $offset = $i*1000+2;
$max = ($i+1)*1000+1;
$max = ($max > $dataCount) ? $highestRow : $max;
$allShopLinkedId = array();
for($j=$offset;$j if($execlAllShopLinkedId[$j]){
$allShopLinkedId[] = $execlAllShopLinkedId[$j];
}
}
// 根据SAP商品编码查询在库中的记录数.
$dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);
for($j=$offset;$j $product = array();
for($k=0;$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']) $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;
}
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
2 周前
By DDD
R.E.P.O.如果您听不到任何人，如何修复音频
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解锁Myrise中的所有内容
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
php中的卷曲：如何在REST API中使用PHP卷曲扩展
Mar 14, 2025 am 11:42 AM
PHP客户端URL（curl）扩展是开发人员的强大工具，可以与远程服务器和REST API无缝交互。通过利用Libcurl（备受尊敬的多协议文件传输库），PHP curl促进了有效的执行
解释PHP中晚期静态结合的概念。
Mar 21, 2025 pm 01:33 PM
文章讨论了PHP 5.3中引入的PHP中的晚期静态结合（LSB），从而允许静态方法的运行时分辨率调用以获得更灵活的继承。 LSB的实用应用和潜在的触摸
在PHP API中说明JSON Web令牌（JWT）及其用例。
Apr 05, 2025 am 12:04 AM
JWT是一种基于JSON的开放标准，用于在各方之间安全地传输信息，主要用于身份验证和信息交换。1.JWT由Header、Payload和Signature三部分组成。2.JWT的工作原理包括生成JWT、验证JWT和解析Payload三个步骤。3.在PHP中使用JWT进行身份验证时，可以生成和验证JWT，并在高级用法中包含用户角色和权限信息。4.常见错误包括签名验证失败、令牌过期和Payload过大，调试技巧包括使用调试工具和日志记录。5.性能优化和最佳实践包括使用合适的签名算法、合理设置有效期、
如何用PHP的cURL库发送包含JSON数据的POST请求？
Apr 01, 2025 pm 03:12 PM
使用PHP的cURL库发送JSON数据在PHP开发中，经常需要与外部API进行交互，其中一种常见的方式是使用cURL库发送POST�...
ReactPHP的非阻塞特性究竟是什么？如何处理其阻塞I/O操作？
Apr 01, 2025 pm 03:09 PM
深入解读ReactPHP的非阻塞特性ReactPHP的一段官方介绍引起了不少开发者的疑问：“ReactPHPisnon-blockingbydefault....
