您当前的位置: 首页 > 网站编程 > PHP教程 > phpexcel导入excel数据使用方法实例

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

作者:不详 来源:网络 发布时间: 2014-08-14 09:11 点击:
phpexcel导入excel数据使用方法,大家参考使用吧 将Excel文件数据进行读取,并且返回错误的信息 代码如下: /** * 导入商品基本信息 */ public function importProductBasicInfo($data){ include_once 'PHPExcel.php'; include_once 'PHPExcel/IOFactory.php'; include_o

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

  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;

  }
分享到:
本文"phpexcel导入excel数据使用方法实例"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4