搜索

首页  >  文章  >  php教程  >  PHPexecl导出一个复杂的表头

PHPexecl导出一个复杂的表头

PHP中文网
PHP中文网原创
2016-05-26 08:19:471845浏览

  1. php代码

<?php
 
require_once dirname(__FILE__) . &#39;/../Classes/PHPExcel/IOFactory.php&#39;;
 
class PHPExeclCore extends PHPExcel_IOFactory{
     
     
    public static function SummerCreateExecl($Head,$data)
    {       
        self::SummerCreateExeclHead($Head,$data,"Excel2007");
    }
     
    public static function SummerReadExecl($dir)
    {
             if(!file_exists($dir))
             {
                echo "Execl Not Exist";
             }
             else
             {
                 $PHPExeclObj =  self::load($dir);
                  
                 $sheetCount = $PHPExeclObj->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);
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：PHP常用的三种设计模式汇总_php技巧下一篇：PHP生成指定随机字符串的方法

相关文章

查看更多