最近公司業(yè)務(wù)有一些需要導(dǎo)出多級表頭的業(yè)務(wù)需求，百度之后腹侣，沒有很好的應(yīng)用demo，然后接觸了XLSXWriter齿穗，就想著一勞永逸傲隶，寫個工具類，示例只有三級窃页，但是不止于三級

應(yīng)用舉例

比如說我們需要這樣一個表頭

image

1.XLSXWriter應(yīng)用分析

根據(jù)XLSXWriter工具類的示例

我們需要插入三行數(shù)據(jù)作為表頭

image

然后再合并掉對應(yīng)的單元格

image

2.工具類編寫

調(diào)試的注釋啥的跺株，都在里面复濒，有興趣的自己可以看一下。需要放在和xlsxwriter.class.php同一目錄下使用

<?php
require_once 'web/libraries/XLSXwriter.php';
class PHPExeclCore
{
    private $headerData = array();
    private $headerWidthSize = 0;
    private $headerHeightSize = 0;


    private $colDefalutFormat = 'string';// 列默認(rèn)數(shù)據(jù)格式
    private $colDefalutWidth = '10';// 列默認(rèn)寬度

    private $header = array();
    private $headerWidth = array();

    // 數(shù)據(jù)字段
    private $dataFields = array();

    // 標(biāo)題行默認(rèn)樣式
    private $headerDefalutStyle = array(
        'halign' => 'center',//水平居中
        'valign' => 'center',//豎直居中
        'font-style' => 'bold',
        'font-size' => 12,
        'border' => 'top,bottom,left,right'
        //'fill'=>'#fff'// 背景色
        // 邊框有點問題不能用
        // 高度也不行 用font-size撐開);
    );
    // 標(biāo)題行處理數(shù)組
    /**
     * $headerRow = array(
     * array('content' =>['Merge Cells Example','','','',''],'style'=>[['halign'=>'center'],array(),array(),array(),array()]),
     * array('content' =>['姓名', '吃飯', '', '考勤', ''],'style'=>[['halign'=>'center','valign'=>'center'],['halign'=>'center'],[],['halign'=>'center'],[]]),
     * array('content' =>['', '上午', '下午', '上午', '下午'],'style'=>[[],['halign'=>'center'],[],[],[]]),
     * );
     */
    private $headerRow = array(0 => array('content' => array(), 'style' => array()));
    private $headerMargin = array();

    // 數(shù)據(jù)行
    private $dataRow = array();
    // 現(xiàn)在夠用了乒省，先不做了
    private $dataStyle = array();


    public function __construct()
    {
    }

    public function __get($name)
    {
        if (isset($this->$name)) {
            return $this->$name;
        } else {
            return null;
        }
        // TODO: Implement __get() method.
    }

    /**
     * 設(shè)置表頭
     * @param array $head
     */
    public function setHeader($header = array())
    {
        $this->headerData = $header;
        // 設(shè)置表頭尺寸
        $this->setHeaderSize($this->headerData);
        // 生成rows 和 margin 設(shè)置樣式
        $this->dealHeader($this->headerData);
    }


    public function setData($data = array())
    {
        foreach ($data as $d) {
            $row = array();
            foreach ($this->dataFields as $field) {
                $value = isset($d[$field]) ? $d[$field] : '';
                $row[] = $value;
            }
            $this->dataRow[] = $row;
        }
    }


    /**
     * 導(dǎo)出
     * @param string $filename
     * @param string $sheet
     */
    public function writeToStdOut($filename = 'test', $sheet = 'Sheet1')
    {
        //$header = ['字段','標(biāo)題','寬度','類型']
        $filename .= '.xlsx';
        $writer = new \XLSXWriter();
        $writer->writeSheetHeader($sheet, $this->header, $col_options = array('suppress_row' => true, 'widths' => $this->headerWidth));
        foreach ($this->headerRow as $row)
            $writer->writeSheetRow($sheet, $row['content'], $row['style']);
        foreach ($this->headerMargin as $margin) {
            $writer->markMergedCell($sheet, $margin['startRow'], $margin['startCol'], $margin['endRow'], $margin['endCol']);
        }
        foreach ($this->dataRow as $data)
            $writer->writeSheetRow($sheet, $data);
        header('Content-disposition: attachment; filename="' . \XLSXWriter::sanitize_filename($filename));
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        $writer->writeToStdOut();
    }


    /**
     *  生成rows 和 margin 設(shè)置樣式
     * @param $headerData
     */
    private function dealHeader($headerData)
    {

        $this->recursionSetHeaderRowsAndMargin($headerData, 0, 0, $this->headerDefalutStyle);
    }

    /**
     * 遞歸設(shè)置表頭行數(shù)據(jù)巧颈，和合并表格數(shù)據(jù)源
     * @param $headerData
     * @param $row
     * @param $startCol
     * // 換個思路，填坑賦值
     */
    private function recursionSetHeaderRowsAndMargin($headerData, $row, $col, $defStyle = array())
    {
        $heightSize = $this->headerHeightSize;

        $startRow = $row;
        $startCol = $col;
        $content = array();//標(biāo)題
        $style = array();//樣式

        foreach ($headerData as $single) {
            $marginCol = isset($single['marginCol']) ? $single['marginCol'] : 1;
            // 合并
            // 是否有子節(jié)點
            $hasChildren = (isset($single['children']) && !empty($single['children'])) ? true : false;
            // 有子節(jié)點合并一行袖扛，沒有子節(jié)點直接把剩下行都算上
            if ($hasChildren) {
                $marginRow = 1;
            } else {
                // 一共3行 當(dāng)前第1行需要合并1砸泛，2兩行
                $marginRow = $heightSize - $startRow;
            }


            $style = isset($single['style']) ? array_merge($defStyle, $single['style']) : $defStyle;
            // 如果不是合并一行一列就加入合并單元格的數(shù)組,同時每個合并的單元格都加樣式(主要是方便邊框)
            if ($marginCol != 1 || $marginRow != 1) {
                //0+2-1=1 合并了0，1兩個單元格 end是1
                $endCol = $startCol + $marginCol - 1;
                // 當(dāng)前第1行需要合并2兩行,目標(biāo)單元格行號1+2-1
                $endRow = $startRow + $marginRow - 1;
                $this->headerMargin[] = array(
                    'startRow' => $startRow,
                    'startCol' => $startCol,
                    'endRow' => $endRow,
                    'endCol' => $endCol);

                if (isset($style['border'])) {
                    $s = array('border' => $style['border']);
                    for ($r = 0; $r < $marginRow; $r++) {
                        $row = $startRow + $r;
                        for ($c = 0; $c < $marginCol; $c++) {
                            $col = $startCol + $c;
                            $this->setHeaderRowCell($row, $col, 'style', $s);
                        }
                    }
                }

            }
            $this->setHeaderRowCell($startRow, $startCol, 'style', $style);


            $content = isset($single['title']) ? $single['title'] : '';
            $this->setHeaderRowCell($startRow, $startCol, 'content', $content);


            //處理子節(jié)點
            if ($hasChildren) {
                $this->recursionSetHeaderRowsAndMargin($single['children'], $startRow + 1, $startCol, $style);
            }
            $startCol += $marginCol;

        }


    }

    //
    //
    /**
     * 設(shè)置表頭單元格尺寸
     * 同事設(shè)置列的數(shù)據(jù)類型和寬度
     * @param $header
     */
    private function setHeaderSize(&$headerData)
    {

        list($w, $h) = $this->recursionCalSize($headerData);
        $this->headerWidthSize = $w;
        $this->headerHeightSize = $h;
        $this->setHeaderRow($w, $h);


    }

    /**
     * 初始化表頭行蛆封，占坑
     * @param $w
     * @param $h
     */
    private function setHeaderRow($w, $h)
    {
        $data = array();
        $content = array();
        $style = array();
        while (true) {
            $w--;
            $content[] = '';
            $style[] = array();
            if ($w <= 0) break;
        }

        while (true) {
            $h--;
            $data[] = array('content' => $content, 'style' => $style);

            if ($h <= 0)
                break;
        }

        $this->headerRow = $data;
        unset($content);
        unset($style);
        unset($data);

    }

    /**
     * 修改表頭行的數(shù)據(jù)
     * @param $col
     * @param $row
     * @param $filed
     * @param $value
     */
    private function setHeaderRowCell($row, $col, $key, $value)
    {

        if (isset($this->headerRow[$row][$key][$col])) {
            $this->headerRow[$row][$key][$col] = $value;
        } else {
            return false;
        }
    }

    /**
     *
     * @param $headerData
     * @return array
     */
    private function recursionCalSize(&$headerData)
    {
        $w = 0; // 根節(jié)點++
        $h = 0; // 是子類高度的最大值
        $childHeightArr = array();
        foreach ($headerData as &$single) {
            $singleH = 1;
            // 沒有子節(jié)點設(shè)置為空數(shù)組

            if ((isset($single['children']) && !empty($single['children']))) {
                list($cw, $ch) = $this->recursionCalSize($single['children'], $h);
                $w += $cw;
                $singleH += $ch;
                $single['marginCol'] = $cw;

            } else {

                //設(shè)置表頭需要要參數(shù)
                //字段類型
                $type = isset($single['format']) ? $single['format'] : $this->colDefalutFormat;
                // 字段寬度
                $width = isset($single['width']) ? $single['width'] : $this->colDefalutWidth;
                $this->header[] = $type;
                $this->headerWidth[] = $width;
                // data的鍵
                $field = isset($single['field']) ? $single['field'] : '';
                $this->dataFields[] = $field;

                $w++;
                $singleH = 1;
            }
            $childHeightArr[] = $singleH;
        }
        $h = max($childHeightArr);
        return array($w, $h);
    }
}

3.使用示例

<?php
/**
 * 表頭配置為一個數(shù)組
 * 根節(jié)點參數(shù)
 * title  必填唇礁，表頭的內(nèi)容
 * field  根節(jié)點列的內(nèi)容要顯示的data字段，默認(rèn)為空[則這一列不顯示數(shù)據(jù)]
 * width  根節(jié)點的列寬,默認(rèn)為10
 * format 根節(jié)點的列在excel中的顯示類型惨篱，默認(rèn)為string盏筐，其他的我沒有測試，因為我實際的應(yīng)用過程中砸讳，String就夠用了琢融，如果要使用其他類型，請參照xlsxwriter示例自行修改測試
 *      format和width的默認(rèn)值可以再tool里面修改
 *      colDefalutFormat = 'string';// 列默認(rèn)數(shù)據(jù)格式
 *      colDefalutWidth = '10';// 列默認(rèn)寬度
 *
 * 一般節(jié)點的參數(shù)
 * title  必填绣夺，表頭的內(nèi)容
 * style=> 應(yīng)用的樣式吏奸，會應(yīng)用到根節(jié)點
 *      默樣式可以修改headerDefalutStyle的配置
 * children 必填,子節(jié)點
 */
$headerConfig =
                array(
                    array('title' => '存量項目', 'field' => 'project_name','format'=>'string'),//根節(jié)點
                    array('title' => '業(yè)態(tài)', 'field' => 'layout','format'=>'string'),//根節(jié)點
                    array('title' => '標(biāo)簽', 'field' => 'project_label','format'=>'string'),//根節(jié)點
                    array('title' => '委托管理公司', 'field' => 'business_area_company','format'=>'string','width'=>'20'),//根節(jié)點
//                    ['title' => '姓名', 'field' => 'name','width'=>'40'],//根節(jié)點
                    // 一般節(jié)點
                    array('title' => '建筑面積',
                        'children' => array(
                            array('title' => '6月30日', 'field' => 'build_area_sum_1'),
                            array('title' => '9月30日', 'field' => 'build_area_sum_2'),
                        )
                    ),
                    array(
                        'title' => '實際收入',
                        'children' => array(
                            array('title' => '6月30日', 'field' => 'rent_income_sum_1'),
                            array('title' => '9月30日', 'field' => 'rent_income_sum_2'),
                        )
                    ),
                    array('title' => '單價',
                        'children' => array(
                            array('title' => '6月30日', 'field' => 'avg_price_1'),
                            array('title' => '9月30日', 'field' => 'avg_price_2'),
                            array('title' => '差值', 'field' => 'avg_price_diff')
                        )
                    ),
                    array('title' => '空置率（%）',
                        'children' => array(
                            array('title' => '6月30日', 'field' => 'rent_rate_1'),
                            array('title' => '9月30日', 'field' => 'rent_rate_2'),
                            array('title' => '差值', 'field' => 'rent_rate_diff')
                        )
                    )
                );
$data = [
            ['project_name' => '張三', 'project_name' => '8:23', 'build_area_sum_1' => '9:23', 'avg_price_1' => '9:23'],
            ['project_name' => '李四', 'project_name' => '8:24', 'build_area_sum_1' => '9:24', 'avg_price_1' => '9:24'],
        ];

include_once("../extend/PHP_XLSXWriter-master/XLSXWriterMultiHeaderTool.php");
$tool = new \XLSXWriterMultiHeaderTool();
$tool->setHeader($headerConfig);
$tool->setData($data);
$filename = date('_YmdHis');
$tool->writeToStdOut($filename);

4.效果

因為data里面沒有對應(yīng)的字段，或者節(jié)點設(shè)置的時候沒有指定的field陶耍，所以有幾列是空的奋蔚，表頭的顏色是因為寫了style的fill屬性

image