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
完。
