phpexcel 用法总结

//数据准备
public function fd_outarray(){            
    $xlsName  = "社团分拣表";
//定义表头
    $xlsCell  = array(
            array('leader_name','团长'),
            array('address','团长地址'),
            array('cat_name','商品分类'),
            array('good_name','商品名称'),
            array('batch_no','编码'),
            array('num','数量'),
            array('unit','单位'),
            array('weight','重量'),
            array('leader_qianzi', '团长签字'),
            array('driver_qianzi', '司机签字'),
        );

//TP框架下条件查询数据，具体根据实际情况去查    
    $model = new Order();
    $begin_time = input('get.begin_time','');
    $end_time = input('get.end_time','');
  
    if (!empty($begin_time) && !empty($end_time) ) {
          if ($begin_time){
                $model->where('o.create_time >= ' . strtotime($begin_time));
          }
          if ($end_time){
                   
                $model->where('o.create_time <= ' . strtotime($end_time));
          }
          $model->where('og.delivery_type != 2');
          $model->where('o.pay_state = 1');
          $model->where('og.check_state = 0');

          $leader_id_list= $model
                ->alias('o')
                ->join('Ordergoods og','og.order_id = o.id','LEFT')
                ->join('Goods g',' g.id = og.goods_id','LEFT')
                ->join('leader l','o.leader_id=l.id','LEFT') 
                ->join('User u','og.user_id=u.id','LEFT') 
                       
                ->field('o.leader_id as leader_id_order')
                ->group('o.leader_id')
                ->order('o.leader_id DESC')
                ->select();
//循环团长id 查询每个的信息：以多个sheet区的形式展示数据。
      foreach ($leader_id_list as $key => $value) {    
         if ($begin_time){
             $model->where('o.create_time >= ' . strtotime($begin_time));
         } 
         if ($end_time){               
             $model->where('o.create_time <= ' . strtotime($end_time));
         }
         $model->where('og.delivery_type != 2');
         $model->where('o.pay_state = 1');
         $model->where('og.check_state = 0');
         $model->where('o.leader_id = '.$value['leader_id_order']);
         $lists[]= $model
                   ->alias('o')
                   ->join('Ordergoods og','og.order_id = o.id','LEFT')
                   ->join('Goods g',' g.id = og.goods_id','LEFT')
                   ->join('Category cat','g.cat_id=cat.id','LEFT')
                   ->join('leader l','o.leader_id=l.id','LEFT') 
                   ->join('User u','og.user_id=u.id','LEFT')    
                   ->field('l.name as leader_name,l.tel,l.address,cat.name as cat_name,g.name as good_name, g.batch_no, sum(og.num) as num ,g.unit, g.weight')
                   ->order('o.leader_id DESC')
                   ->group('g.batch_no')
                   ->order('o.leader_id DESC')
                   ->select();
      }

      ob_clean();

      $rs =$this->fd_export($xlsName,$xlsCell,$lists,$begin_time,$end_time);
      exit;
  }else{
       echo '请选择时间段';
  }
}

/**
往表格中装数据 
$begin_time,$end_time两参数是与表格无关，是一些其他的参数
*/
public function fd_export($expTitle,$expCellName,$expTableData,$begin_time,$end_time){
       
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
    $fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
          
    vendor("PHPExcel.PHPExcel");        
    $objPHPExcel = new \PHPExcel();
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //所有单元格数据左右居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);  //所有单元格上下居中
    $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
     
    foreach ($expTableData as $key => $value) {
              $dataNum = count($expTableData[$key])+2;
    //创建sheet工作区
              $objPHPExcel->createSheet();
              $objPHPExcel->setactivesheetindex($key);
                
     //行内合并
              $objPHPExcel->getActiveSheet($key)->mergeCells('A1:'.$cellName[$cellNum-1].'1');
      //设置合并过的单元格中的值
              $objPHPExcel->setActiveSheetIndex($key)->setCellValue('A1', $begin_time.'---'.$end_time.'社团分拣表');

      //上下合并            

              $objPHPExcel->getActiveSheet(0)->mergeCells('A3:A'.$dataNum);//合并单元格
              $objPHPExcel->getActiveSheet(0)->mergeCells('B3:B'.$dataNum);//合并单元格
           

          //配置边框样式
              $styleArray = array(
                  'borders' => array(
                      'allborders' => array(
                          //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                          'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                      ),
                  ),
              );
              //执行边框样式
              $objPHPExcel->getActiveSheet($key)->getStyle('A1:J' .$dataNum)->applyFromArray($styleArray);
      //设置自动换行
          $objPHPExcel->getActiveSheet($key)->getStyle('A3:J'.$dataNum)->getAlignment()->setWrapText(true); 
//装数据
          for($i=0;$i<$cellNum;$i++){            
              $objPHPExcel->setActiveSheetIndex($key)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
          }
          for($i=0;$i<$dataNum;$i++){
              for($j=0;$j<$cellNum;$j++){
                  $objPHPExcel->getActiveSheet($key)->setCellValue($cellName[$j].($i+3), $expTableData[$key][$i][$expCellName[$j][0]]);
              }
          }
        }
          header('pragma:public');
          header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
          header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
          $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
      //下载表格    
          $objWriter->save('php://output');
          exit;
      }
/*
如果是保存表格文件，而不下载，也可以使用   $objWriter->save(文件目录);

*/

遇到的问题：使用phpoffice/phpexcel导出数据不全或者文件有错误

原因：因为mysql使用了utf8mb4的数据存储格式，所以导致有表情的字段会出现这种问题。

解决方案：替换字段中的表情符号

// 在网上找到了两种方案
//第一种：
public static function filterEmoji($text, $replaceTo = '')
    {
        $clean_text = "";
        // Match Emoticons
        $regexEmoticons = '/[\x{1F600}-\x{1F64F}]/u';
        $clean_text = preg_replace($regexEmoticons, $replaceTo, $text);
        // Match Miscellaneous Symbols and Pictographs
        $regexSymbols = '/[\x{1F300}-\x{1F5FF}]/u';
        $clean_text = preg_replace($regexSymbols, $replaceTo, $clean_text);
        // Match Transport And Map Symbols
        $regexTransport = '/[\x{1F680}-\x{1F6FF}]/u';
        $clean_text = preg_replace($regexTransport, $replaceTo, $clean_text);
        // Match Miscellaneous Symbols
        $regexMisc = '/[\x{2600}-\x{26FF}]/u';
        $clean_text = preg_replace($regexMisc, $replaceTo, $clean_text);
        // Match Dingbats
        $regexDingbats = '/[\x{2700}-\x{27BF}]/u';
        $clean_text = preg_replace($regexDingbats, $replaceTo, $clean_text);
        return $clean_text;
    }
//第二种
public static function wxNickNameFormat($nickName){
        $value = json_encode($nickName);
        $value = preg_replace("/\\\u[ed][0-9a-f]{3}\\\u[ed][0-9a-f]{3}/","*",$value);
        return json_decode($value);
    }