Community Learn Tools Library Leisure
search
English
Home > php教程 > PHP源码 > body text

PHPexecl导出一个复杂的表头

PHP中文网
Release： 2016-05-26 08:19:47
Original
1842 people have browsed it

  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);
Copy after login
Related labels：
PHPexecl导出一个复杂的表头
source：php.cn
Previous article：PHP常用的三种设计模式汇总_php技巧 Next article：PHP生成指定随机字符串的方法
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
Efficient way to fill missing values ​​in unordered map from SQL table (SQL/C++) Question Currently there is a system that reads unique identifiers (ids) and their attache...
From 2024-04-04 11:47:44
0
1
345
How can I make text turn into a new element (like a divider) when clicked? I'm trying to find a way to make certain text on the page turn into separators/new element...
From 2024-04-04 11:21:03
0
1
3580
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template