PHPexecl导出一个复杂的表头-PHP源码

1. php代码

getSheetCount(); //得到Execl中包含的Sheet工作簿的数量
                  
                 for($i=0;$i<$sheetCount;$i++)
                 {
                    $ActiveSheet = $PHPExeclObj->getSheet($i);
                    $highestRow = $ActiveSheet->getHighestRow(); // 取得总列数
                    $allColumn  = $ActiveSheet->getHighestColumn();
     
                    //通过嵌套循环来读取sheet工作簿里面的内容
                    for($Col=&＃39;A&＃39;;$Col<$allColumn;$Col++)
                    {
                        for($Row=1;$Row<$highestRow;$Row++)
                        {
                              $Data[$Col][$Row] = $ActiveSheet->getCell($Col.$Row)->getValue();
                        }
                    }
 
                 }
             }
              
             return $Data;
 
    }
     
     
    /*
     * 将数据写入到数据表中
     * $Data Array 表示要插入进Execl数据
     * $RuleData Array 表示数据格式的规则数组
     * $i int 表示从第几行起的插入数据
     * **/
     
    public static function SummerInsertDateToExecl($sheet,$Head,$Data,$n=3,$RuleData=array())
    {
        $SimpleHead = self::getHead($Head);
         
        $row = $n;
        foreach($Data as $key=>$valueArr)
        {       
            $m = 0;
            foreach($valueArr as $k=>$v)
            {
                $StartCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;
                $sheet->getCell($StartCol)->setValue($v);
 
                $sheet->getStyle($StartCol)->getAlignment()->applyFromArray(
                        array(
                                &＃39;horizontal&＃39;=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                &＃39;vertical&＃39;  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                &＃39;rotation&＃39;  => 0,
                                &＃39;wrap&＃39;      => TRUE,
                        )
                );
                 
                             
                if(isset($SimpleHead[$k][&＃39;col&＃39;]))
                {
                    $m = $m + $SimpleHead[$k][&＃39;col&＃39;]-1;
                    $endCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;
                    $sheet->mergeCells($StartCol.":".$endCol);
                }
                 
                $m++;
                 
                $type = false;
                 
                if(isset($SimpleHead[$k][&＃39;type&＃39;]))
                {
                     $type = $SimpleHead[$k][&＃39;type&＃39;];
                     $AllowArray = $SimpleHead[$k][&＃39;allowarray&＃39;];
                }
                 
                //设置单元格的数据验证
                if($type)
                {
                    switch ($type)
                    {
                        case &＃39;list&＃39;:
                            self::setSelectionRange($sheet, $StartCol,$AllowArray);
                        break;
                        case &＃39;range&＃39;:
                            self::setValueRange($sheet, $StartCol,$AllowArray);
                        break;
                    }
                }
 
            }
            $row ++ ;
        }
          
    }
         
    /*
     * 生成Execl单元格备注
     * $sheet 当前的工作簿对象
     * $Cell 需要设置属性的单元格
     * $content 备注内容
     * */
    private static function setComment($sheet,$Cell,$content)
    {
        $sheet->getComment($Cell)->setAuthor(&＃39;4399om&＃39;);
        $objCommentRichText = $sheet->getComment($Cell)->getText()->createTextRun(&＃39;4399om:&＃39;);
        $objCommentRichText->getFont()->setBold(true);
        $sheet->getComment($Cell)->getText()->createTextRun("\r\n");
        $sheet->getComment($Cell)->getText()->createTextRun($content);
        $sheet->getComment($Cell)->setWidth(&＃39;100pt&＃39;);
        $sheet->getComment($Cell)->setHeight(&＃39;100pt&＃39;);
        $sheet->getComment($Cell)->setMarginLeft(&＃39;150pt&＃39;);
        $sheet->getComment($Cell)->getFillColor()->setRGB(&＃39;EEEEEE&＃39;);
    }
     
    /*
     * 现在单元格的有效数据范围，暂时仅限于数字
     * $sheet 当前的工作簿对象
     * $Cell 需要设置属性的单元格
     * $ValueRange array 允许输入数组的访问
     */
    private static function setValueRange($sheet,$Cell,$ValueRange)
    {
        //设置单元格的的数据类型是数字，并且保留有效位数
        $sheet->getStyle($Cell)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
         
        $ValueRange = explode(",",$ValueRange);
         
        //开始数值有效访问设定
        $objValidation = $sheet->getCell($Cell)->getDataValidation();
        $objValidation->setType( PHPExcel_Cell_DataValidation:: TYPE_WHOLE );
        $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation:: STYLE_STOP );
        $objValidation->setAllowBlank(true);
        $objValidation->setShowInputMessage( true);            //设置显示提示信息
        $objValidation->setShowErrorMessage( true);            //设置显示错误信息
        $objValidation->setErrorTitle(&＃39;输入错误&＃39;);              //错误标题
        $objValidation->setError(&＃39;请输入数据范围在从&＃39;.$ValueRange[0].&＃39;到&＃39;.$ValueRange[1].&＃39;之间的所有值&＃39;);       //错误内容
        $objValidation->setPromptTitle(&＃39;允许输入&＃39;);         //设置提示标题
        $objValidation->setPrompt(&＃39;请输入数据范围在从&＃39;.$ValueRange[0].&＃39;到&＃39;.$ValueRange[1].&＃39;之间的所有值&＃39;); //提示内容
        $objValidation->setFormula1($ValueRange[&＃39;0&＃39;]);     //设置最大值
        $objValidation->setFormula2($ValueRange[&＃39;1&＃39;]);     //设置最小值
         
    }
     
 
    private static function OutinputHeader($objWriter)
    {
        $fileName = str_replace(&＃39;.php&＃39;, &＃39;.xlsx&＃39;, pathinfo(__FILE__, PATHINFO_BASENAME));
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header(&＃39;Content-Disposition:inline;filename="&＃39;.$fileName.&＃39;"&＃39;);
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save(&＃39;php://output&＃39;);
        exit;
    }
     
    //数据控制，设置单元格数据在一个可选方位类
    private static function setSelectionRange($sheet,$Cell,$rangeStr,$Title="数据类型")
    {
     
        $objValidation = $sheet->getCell($Cell)->getDataValidation();
        $objValidation -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP)
            -> setAllowBlank(true)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle(&＃39;输入的值有误&＃39;)
            -> setError(&＃39;您输入的值不在下拉框列表内.&＃39;)
            -> setPromptTitle(&＃39;"&＃39;.$Title.&＃39;"&＃39;)
            -> setFormula1(&＃39;"&＃39;.$rangeStr.&＃39;"&＃39;);   
    }
     
     
     
    /*
     * 构建表头
     * */
    public static function RecursionCreateExecl($head,$data)
    {
 
        $PHPExecl = new PHPExcel();
             
        $objWriter = self::createWriter($PHPExecl, &＃39;Excel2007&＃39;);
         
        $PHPExecl->getProperties()->setCreator("4399om")
        ->setLastModifiedBy("Summer")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
         
        $PHPExecl->setActiveSheetIndex(0);
         
        $sheet = $PHPExecl->getActiveSheet();
         
        self::HandleHeadToNode($sheet, $head,1,0,0);
         
        self::SummerInsertDateToExecl($sheet,$head,$data,4);
 
        self::OutinputHeader($objWriter);
 }
  
 private static function HandleHeadToNode($sheet,$Head,$beginRow,$col,$StartCol)
 {
    foreach($Head as $key=>$cells)
    {
            $row = $beginRow; //表示行
         
            $beginCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;
             
            $sheet->getCell($beginCol)->setValue($cells[&＃39;value&＃39;]);
             
            //设置表格样式
            $sheet->getStyle($beginCol)->getAlignment()->applyFromArray(
                    array(
                            &＃39;horizontal&＃39;=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            &＃39;vertical&＃39;  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            &＃39;rotation&＃39;  => 0,
                            &＃39;wrap&＃39;      => TRUE,
                    )
            );
             
            $sheet->getStyle($beginCol)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);
             
            //设置单元格的宽度
            if(isset($cells[&＃39;width&＃39;]))
            {
                $Cell = $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col));
                $Cell->setWidth($cells[&＃39;width&＃39;]);
            }
             
            //哥元素打上标记
            if(isset($cells[&＃39;Content&＃39;]))
            {
                self::setComment($sheet, $beginCol, $cells[&＃39;Content&＃39;]);
            }
              
            $merge = false; //合并单元格
            if(isset($cells[&＃39;col&＃39;]))
            {
                $col += $cells[&＃39;col&＃39;]-1;
                $merge = true;
            }
              
            if(isset($cells[&＃39;row&＃39;]))
            {
                $row += $cells[&＃39;row&＃39;]-1;
                $merge = true;
            }
             
            if($merge)
            {
                $endCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;
                $sheet->mergeCells($beginCol.":".$endCol);
            }
             
            $row ++;    
            $col ++;
         
            //表示有存在孩子节点
            if(isset($cells[&＃39;children&＃39;]) && is_array($cells[&＃39;children&＃39;])){ 
                $cols = $StartCol;
                $n = count($cells[&＃39;children&＃39;]);
                if(!self::IsExistChildren($cells[&＃39;children&＃39;]))
                {       
                    $cols     = $col-$n;
                    $StartCol = $col;
                }
 
                self::HandleHeadToNode($sheet,$cells[&＃39;children&＃39;],$row,$cols,$StartCol);
            }else{
                $StartCol = $col;
            }
         
 
     }
         
 }
 
  //判断自己的孩子节点中是否存在孙子节点
  private static function IsExistChildren($Data)
  {
      foreach($Data as $key=>$value)
      {
           if(isset($value[&＃39;children&＃39;]) && is_array($value[&＃39;children&＃39;]))
           {
               return true;
           }
      }
      return false;
  }
   
   
  //获取底层数据
  private static function getHead($Head,&$Node=array())
  {
    foreach($Head as $key=>$value)
    {
        if(isset($value[&＃39;children&＃39;]) && is_array($value[&＃39;children&＃39;]))
        {
            self::getHead($value[&＃39;children&＃39;],$Node);
        }
        else
        {   
            $Node[] = $value;
        }
    }
         
    return $Node;
  }
   
}
 
 
 
 
$Head = array(
            array(&＃39;value&＃39;=>&＃39;姓名&＃39;,&＃39;col&＃39;=>2,&＃39;row&＃39;=>2,&＃39;width&＃39;=>20,&＃39;type&＃39;=>&＃39;list&＃39;,&＃39;allowarray&＃39;=>&＃39;邓蔚之,李永航&＃39;),
            array(&＃39;value&＃39;=>&＃39;第一天&＃39;,&＃39;col&＃39;=>4,&＃39;row&＃39;=>1,&＃39;width&＃39;=>20,&＃39;Content&＃39;=>&＃39;2014-12-29号&＃39;,
                    &＃39;children&＃39;=>
                     array(
                        array(&＃39;value&＃39;=>&＃39;上午&＃39;,&＃39;col&＃39;=>1,&＃39;width&＃39;=>20,&＃39;type&＃39;=>&＃39;range&＃39;,&＃39;allowarray&＃39;=>&＃39;10,100&＃39;),
                        array(&＃39;value&＃39;=>&＃39;中午&＃39;,&＃39;col&＃39;=>1,&＃39;width&＃39;=>20,&＃39;type&＃39;=>&＃39;range&＃39;,&＃39;allowarray&＃39;=>&＃39;10,100&＃39;),
                        array(&＃39;value&＃39;=>&＃39;下午&＃39;,&＃39;width&＃39;=>20),
                        array(&＃39;value&＃39;=>&＃39;晚上&＃39;,&＃39;width&＃39;=>20),
                    ),
                     
            ),
            array(&＃39;value&＃39;=>&＃39;第二天&＃39;,&＃39;col&＃39;=>2,&＃39;row&＃39;=>1,&＃39;width&＃39;=>20,
                    &＃39;children&＃39;=>
                     array(
                        array(&＃39;value&＃39;=>&＃39;上午&＃39;,&＃39;width&＃39;=>20),
                        array(&＃39;value&＃39;=>&＃39;下午&＃39;,&＃39;width&＃39;=>20),
                     ),
            ),
);
 
 
$data = array(
            array(&＃39;邓蔚之&＃39;,&＃39;12&＃39;,&＃39;吃饭1&＃39;,&＃39;睡觉1&＃39;,&＃39;起床刷牙2&＃39;,&＃39;吃饭睡觉2&＃39;),
            array(&＃39;邓蔚之&＃39;,&＃39;25&＃39;,&＃39;吃饭1&＃39;,&＃39;睡觉1&＃39;,&＃39;起床刷牙2&＃39;,&＃39;吃饭睡觉2&＃39;),
            array(&＃39;邓蔚之&＃39;,&＃39;50&＃39;,&＃39;吃饭1&＃39;,&＃39;睡觉1&＃39;,&＃39;起床刷牙2&＃39;,&＃39;吃饭睡觉2&＃39;),
            array(&＃39;邓蔚之&＃39;,&＃39;99&＃39;,&＃39;吃饭1&＃39;,&＃39;睡觉1&＃39;,&＃39;起床刷牙2&＃39;,&＃39;吃饭睡觉2&＃39;),
            array(&＃39;邓蔚之&＃39;,&＃39;10&＃39;,&＃39;吃饭1&＃39;,&＃39;睡觉1&＃39;,&＃39;起床刷牙2&＃39;,&＃39;吃饭睡觉2&＃39;),
        );
 
$Node = PHPExeclCore::RecursionCreateExecl($Head,$data);