PHPExcel 表头合并及前置内容

本文链接：https://www.cnblogs.com/tujia/p/14265790.html

PHPExcel 助手请看：https://www.cnblogs.com/tujia/p/11358096.html

现在有原助手类的基础上，再扩展表头合并及表头前置内容的功能

一、表头合并

1）增加流程分支：

if (isset($titles['keys']) && isset($titles['labels'])) {
    $titles['rowIndex'] = $rowIndex;
    list($fieldsMap, $rowIndex) = self::genMergeHead($sheet, $titles);
}

 2）实现方法：

/**
 * 处理表头合并 Tiac @2021.01.11
 * @see https://www.cnblogs.com/tujia/p/14265790.html
 * @param  [type]  $sheet        [description]
 * @param  [type]  $titles       [description]
 * @param  integer $callbackType 递归类型，0默认，1字段处理递归，2表头处理递归
 * @return [type]                [description]
 */
public static function genMergeHead($sheet, $titles, $callbackType=0)
{
    if (in_array($callbackType, [0, 1])) {
        // 字段列表
        $fieldsMap = [];
        // 数组深度
        $depth = 1;
        $maxDepth = 0;
        $updatedDepth = false;
        foreach ($titles['keys'] as $key => $value) {
            if (is_array($value)) {
                $updatedDepth == false && $depth += 1 && $updatedDepth = true;
                list($f, $d) = self::genMergeHead($sheet, ['keys'=>$value], 1);
                $maxDepth = max($maxDepth, $d);
                $fieldsMap = array_merge($fieldsMap, $f);
            } else {
                $fieldsMap[] = $value;
            }
        }

        if ($callbackType == 1) {
            return [$fieldsMap, $depth];
        }
        
        $depth = $maxDepth + 1;
    }

    $chr = ['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'];

    $maxDepth = isset($titles['maxDepth'])? $titles['maxDepth']:$depth;
    $depth = isset($titles['maxDepth'])? $titles['depth']:1;
    $updatedDepth = false;
    $rowIndex = isset($titles['rowIndex'])? $titles['rowIndex']:1;
    $colIndex = isset($titles['colIndex'])? $titles['colIndex']:0;
    foreach ($titles['labels'] as $key => $value) {
        $p1 = $chr[$colIndex].$rowIndex;
        if (is_array($value)) {
            $updatedDepth == false && $depth += 1 && $updatedDepth = true;
            $rowIndex = $rowIndex+$maxDepth-$depth-($maxDepth==$depth? 0:1);
            $p2 = $chr[$colIndex+self::array_count($value)-1].$rowIndex;
            // echo $key, ' ', $p1, ':', $p2, '<hr>';
            $p1 != $p2 && $sheet->mergeCells($p1.':'.$p2);
            $sheet->setCellValue($p1, $key);
            list($rowIndex, $colIndex) = self::genMergeHead($sheet, [
                'labels'=>$value,
                'depth'=>$depth,
                'maxDepth'=>$maxDepth,
                'rowIndex'=>$rowIndex+1,
                'colIndex'=>$colIndex
            ], 2);
            $rowIndex -= 1;
            $colIndex -= 1;
        } else {
            $d = isset($titles['depth'])? $titles['depth']:$depth;
            $p2 = $chr[$colIndex].($rowIndex+$maxDepth-$d);
            // echo $value, ' ', $p1, ':', $p2, '<hr>';
            $p1 != $p2 && $sheet->mergeCells($p1.':'.$p2);
            $sheet->setCellValue($p1, $value);
        }

        $colIndex++;
    }

    if ($callbackType == 2) {
        return [$rowIndex, $colIndex];
    }

    return [$fieldsMap, $rowIndex+$maxDepth-1];
}

// 计算数组的元素个数
private static function array_count($arr, $includeSelf=false)
{
    if ($includeSelf == true) {
        return count($value, COUNT_RECURSIVE);
    }

    $count = 0;
    foreach ($arr as $key => $value) {
        if (!is_array($value)) {
            $count += 1;
        } else {
            $count += self::array_count($value);
        }
    }

    return $count;
}

3）使用示例：

$titles = [
    'labels' => [
        '日期',
        '总入账金额',
        '回收' => ['数量', '最高报价预付款金额', '客单价', '回收充值金额'],
        '竞拍' => ['数量', '商品金额', '客单价', '中检', '名匠卡'],
        '表库' => [
            '数量',
            '商品金额',
            '各端金额' => ['H5', '小程序', '大APP', 'APP'],
            '客单价',
            '中检',
            '名匠卡',
            '数量',
            '清洗养护',
            '维修抵扣',
            '商家中介'
        ],
    ],
    'keys' => [
        'f1',
        'f2',
        '回收' => ['f3', 'f4', 'f5', 'f6'],
        '竞拍' => ['f7', 'f8', 'f9', 'f10', 'f11'],
        '表库' => [
            'f12',
            'f13',
            '各端金额' => ['f14', 'f15', 'f16', 'f17'],
            'f18',
            'f19',
            'f20',
            'f21',
            'f22',
            'f23',
            'f24',
        ],
    ]
];

$dataArray = [
    ['f1'=>'abc', 'f2'=>'abc', 'f3'=>'abc', 'f4'=>'abc', 'f5'=>'abc', 'f6'=>'abc', 'f7'=>'abc', 'f8'=>'abc', 'f9'=>'abc', 'f10'=>'abc', 'f11'=>'abc', 'f12'=>'abc', 'f13'=>'abc', 'f14'=>'abc', 'f15'=>'abc', 'f16'=>'abc', 'f17'=>'abc', 'f18'=>'abc', 'f19'=>'abc', 'f20'=>'abc', 'f21'=>'abc', 'f22'=>'abc', 'f23'=>'abc', 'f24'=>'abc']
];

ExcelHelper::setDefaultStyle([
    'alignment' => [
        'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
]);
ExcelHelper::export($titles, $dataArray, 'test.xls');

二、前置内容（注：需配合表头合并的代码一起使用）

1）功能实现：

// 前置内容
if (isset($titles['before']) && !empty($titles['before'])) {
    foreach ($titles['before'] as $row) {
        foreach ($row as $colIndex => $value) {
            $sheet->setCellValue($chr[$colIndex] . $rowIndex, $value);
        }
        $rowIndex += 1;
    }
}

2）使用示例：

$titles = [
    'before' => [
        ['订单金额', '26,362', '客户支付款', '16,651', '退款金额', '20', '佣金', '200', '应付商家款', '19,622', '可提现金额', '5,462'],
        ['', '', '', '', '', '', '', '', '', '', '', ''],
    ],
    'labels' => ['店铺名称','店铺编号','订单编号','订单金额','业务类型','客户支付款','退款金额','佣金','提现手续费','应付商家款','订单状态','可提现金额','支付时间','流水号'],
    'keys' => ['f1','f2','f3','f4','f5','f6','f7','f8','f9','f10','f,11','f12','f13','f14'],
];

$dataArray = [
    ['f1'=>'abc', 'f2'=>'abc', 'f3'=>'abc', 'f4'=>'abc', 'f5'=>'abc', 'f6'=>'abc', 'f7'=>'abc', 'f8'=>'abc', 'f9'=>'abc', 'f10'=>'abc', 'f11'=>'abc', 'f12'=>'abc', 'f13'=>'abc', 'f14'=>'abc', 'f15'=>'abc', 'f16'=>'abc', 'f17'=>'abc', 'f18'=>'abc', 'f19'=>'abc', 'f20'=>'abc', 'f21'=>'abc', 'f22'=>'abc', 'f23'=>'abc', 'f24'=>'abc']
];

ExcelHelper::setDefaultStyle([
    'alignment' => [
        'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
]);
ExcelHelper::export($titles, $dataArray, 'test.xls');

本文链接：https://www.cnblogs.com/tujia/p/14265790.html

---

完。