示例只有三级,但是不止于三级
应用举例
比如说我们需要这样一个表头
1.XLSXWriter应用分析
根据XLSXWriter工具类的示例
我们需要插入三行数据作为表头
然后再合并掉对应的单元格
2.工具类编写
调试的注释啥的,都在里面,有兴趣的自己可以看一下。需要放在和xlsxwriter.class.php同一目录下使用
-
<?php
-
/**
-
* Created By
-
* 模块说明:
-
* User: helloc
-
* Datetime: 2020/1/17 11:07
-
*/
-
require_once __DIR__.DIRECTORY_SEPARATOR.'xlsxwriter.class.php';
-
class XLSXWriterMultiHeaderTool
-
{
-
private $headerData =[];
-
private $headerWidthSize = 0;
-
private $headerHeightSize = 0;
-
private $colDefalutFormat = 'string';// 列默认数据格式
-
private $colDefalutWidth = '10';// 列默认宽度
-
// 设置表头需要要参数 从根节点中取
-
//$writer->writeSheetHeader($sheet1, $header, $col_options = ['suppress_row'=>true,'widths'=>$headerWidth] )
-
private $header = [];
-
private $headerWidth =[];
-
// 数据字段
-
private $dataFields = [];
-
// 标题行默认样式
-
private $headerDefalutStyle = [
-
'halign'=>'center',//水平居中
-
'valign'=>'center',//竖直居中
-
'font-style'=>'bold',
-
'font-size'=>12,
-
'border'=>'top,bottom,left,right'
-
//'fill'=>'#fff'// 背景色
-
// 边框有点问题不能用
-
// 高度也不行 用font-size撑开
-
];
-
// 标题行处理数组
-
/**
-
$headerRow = array(
-
array('content' =>['Merge Cells Example','','','',''],'style'=>[['halign'=>'center'],[],[],[],[]]),
-
array('content' =>['姓名', '吃饭', '', '考勤', ''],'style'=>[['halign'=>'center','valign'=>'center'],['halign'=>'center'],[],['halign'=>'center'],[]]),
-
array('content' =>['', '上午', '下午', '上午', '下午'],'style'=>[[],['halign'=>'center'],[],[],[]]),
-
);
-
*/
-
private $headerRow =[
-
0=>['content'=>[],'style'=>[]],
-
];
-
private $headerMargin =[];
-
// 数据行
-
private $dataRow = [
-
];
-
// 现在够用了,先不做了
-
private $dataStyle = [];
-
public function __construct()
-
{
-
}
-
public function __get($name)
-
{
-
if (isset($this->$name)) {
-
return $this->$name;
-
} else {
-
return null;
-
}
-
// TODO: Implement __get() method.
-
}
-
/**
-
* 设置表头
-
* @param array $head
-
*/
-
public function setHeader($header = [])
-
{
-
$this->headerData = $header;
-
// 设置表头尺寸
-
$this->setHeaderSize($this->headerData);
-
// 生成rows 和 margin 设置样式
-
$this->dealHeader($this->headerData);
-
}
-
public function setData($data = []){
-
foreach ($data as $d){
-
$row = [];
-
foreach ($this->dataFields as $field){
-
$value = isset($d[$field])?$d[$field]:'';
-
$row[] = $value;
-
}
-
$this->dataRow[] = $row;
-
}
-
// dump($this->dataFields);
-
// dump($this->dataRow);
-
dump($data);exit;
-
// exit;
-
}
-
/**
-
* 导出
-
* @param string $filename
-
* @param string $sheet
-
*/
-
public function writeToStdOut($filename='test',$sheet='Sheet1'){
-
//$header = ['字段','标题','宽度','类型']
-
$filename .= '.xls';
-
$writer = new \XLSXWriter();
-
$writer->writeSheetHeader($sheet, $this->header, $col_options = ['suppress_row'=>true,'widths'=>$this->headerWidth] );
-
foreach($this->headerRow as $row)
-
// $row['style']['height']=30;
-
$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 设置样式
-
* @param $headerData
-
*/
-
private function dealHeader($headerData){
-
// $widthSize = $this->headerWidthSize;
-
// $heightSize = $this->headerHeightSize;
-
// echo "宽度:{$widthSize},高度:{$heightSize}";
-
// $headerData = [
-
// [
-
// 'title' => 'test1',
-
// 'marginCol' => '1',
-
// 'style'=>[
-
// 'halign'=>'left','valign'=>'center'
-
// ]
-
// ],
-
// [
-
// 'title' => 'test2',
-
// 'marginCol' => '3',
-
// 'children'=>[
-
// [
-
// 'title'=>'上午',
-
// 'field'=>'workam'
-
// ],
-
// [
-
// 'title'=>'上午',
-
// 'field'=>'workpm'
-
// ],
-
// ]
-
// ],
-
// [
-
// 'title' => 'test3',
-
// 'marginCol' => '1',
-
// ],
-
//
-
// ];
-
$this->recursionSetHeaderRowsAndMargin($headerData,0,0,$this->headerDefalutStyle);
-
// dump($this->headerMargin);
-
// dump($this->headerRow);exit;
-
// exit;
-
}
-
/**
-
* 递归设置表头行数据,和合并表格数据源
-
* @param $headerData
-
* @param $row
-
* @param $startCol
-
* // 换个思路,填坑赋值
-
*/
-
private function recursionSetHeaderRowsAndMargin($headerData,$row,$col,$defStyle=[]) {
-
// $widthSize = $this->headerWidthSize;
-
$heightSize = $this->headerHeightSize;
-
$startRow = $row;
-
$startCol = $col;
-
// echo "开始行{$startRow},开始列{$startCol}<br />";
-
// $writer->markMergedCell($sheet1, $start_row=0, $start_col=0, $end_row=0, $end_col=4);
-
// $writer->markMergedCell($sheet1, $start_row=1, $start_col=0, $end_row=2, $end_col=0);// 姓名
-
// $writer->markMergedCell($sheet1, $start_row=1, $start_col=1, $end_row=1, $end_col=2);// 吃饭
-
// $writer->markMergedCell($sheet1, $start_row=1, $start_col=3, $end_row=1, $end_col=4);// 考勤
-
$content = [];//标题
-
$style = [];//样式
-
foreach ($headerData as $single){
-
// dump($single);exit;
-
$marginCol = isset($single['marginCol'])?$single['marginCol']:1;
-
// 合并
-
// 是否有子节点
-
$hasChildren = (isset($single['children']) && !empty($single['children'])) ? true:false;
-
// 有子节点合并一行,没有子节点直接把剩下行都算上
-
if($hasChildren){
-
$marginRow = 1;
-
}else{
-
// 一共3行 当前第1行需要合并1,2两行
-
$marginRow =$heightSize-$startRow;
-
}
-
// if($startRow ==1 && $startCol ==0){
-
// }
-
$style = isset($single['style'])?array_merge($defStyle,$single['style']):$defStyle;
-
// 如果不是合并一行一列就加入合并单元格的数组,同时每个合并的单元格都加样式(主要是方便边框)
-
if($marginCol !=1 || $marginRow != 1){
-
//0+2-1=1 合并了0,1两个单元格 end是1
-
$endCol = $startCol+$marginCol-1;
-
// 当前第1行需要合并2两行,目标单元格行号1+2-1
-
$endRow = $startRow+$marginRow-1;
-
// if($endRow == -1){
-
// dump($marginRow);
-
// dump($startRow);exit;
-
// }
-
$this->headerMargin[] = [
-
'startRow'=>$startRow,
-
'startCol'=>$startCol,
-
'endRow'=>$endRow,
-
'endCol'=>$endCol
-
];
-
if(isset($style['border'])){
-
$s = ['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);
-
//处理子节点
-
if($hasChildren){
-
$this->recursionSetHeaderRowsAndMargin($single['children'],$startRow+1,$startCol,$style);
-
}
-
$startCol +=$marginCol;
-
// $marginRow = isset($single['marginCol'])?$single['marginCol']:1;
-
// $startRow = $row;
-
// $startCol = $col;
-
// dump($single);exit;
-
}
-
// dump($content);
-
// dump($style);exit;
-
// $this->headerRow[$row]['content'] =$content;
-
// $this->headerRow[$row]['style'] = $style;
-
}
-
//
-
//
-
/**
-
* 设置表头单元格尺寸
-
* 同事设置列的数据类型和宽度
-
* @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 = [];
-
$content = [];
-
$style = [];
-
while (true){
-
$w--;
-
$content[] = '';
-
$style[] =[];
-
if($w<=0) break;
-
}
-
while (true){
-
$h--;
-
$data[] = ['content'=>$content,'style'=>$style];
-
if($h<=0)
-
break;
-
}
-
$this->headerRow = $data;
-
unset($content);
-
unset($style);
-
unset($data);
-
}
-
/**
-
* 修改表头行的数据
-
* @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; // 根节点++
-
$h = 0; // 是子类高度的最大值
-
$childHeightArr = [];
-
foreach ($headerData as &$single) {
-
$singleH = 1;
-
// 没有子节点设置为空数组
-
// $children = (isset($single['children']) && !empty($single['children'])) ? $single['children'] : [];
-
// if ($children) {
-
if((isset($single['children']) && !empty($single['children']))){
-
list($cw, $ch) = $this->recursionCalSize($single['children'], $h);
-
// dump($h);
-
// dump($ch);
-
$w += $cw;
-
$singleH += $ch;
-
$single['marginCol'] = $cw;
-
// echo $single['title'].":".$h;
-
} else {
-
//设置表头需要要参数
-
//字段类型
-
$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;
-
// $childWithArr[] = $singleW;
-
// if ($single['title'] == '总表头') {
-
// dump($childHeightArr);
-
// }
-
}
-
// $w = array_sum($childWithArr);
-
$h = max($childHeightArr);
-
return [$w, $h];
-
}
-
}
3.使用示例
-
<?php
-
/**
-
* 表头配置为一个数组
-
* 根节点参数
-
* title 必填,表头的内容
-
* field 根节点列的内容要显示的data字段,默认为空[则这一列不显示数据]
-
* width 根节点的列宽,默认为10
-
* format 根节点的列在excel中的显示类型,默认为string,其他的我没有测试,因为我实际的应用过程中,String就够用了,如果要使用其他类型,请参照xlsxwriter示例自行修改测试
-
* format和width的默认值可以再tool里面修改
-
* colDefalutFormat = 'string';// 列默认数据格式
-
* colDefalutWidth = '10';// 列默认宽度
-
*
-
* 一般节点的参数
-
* title 必填,表头的内容
-
* style=> 应用的样式,会应用到根节点
-
* 默样式可以修改headerDefalutStyle的配置
-
* children 必填,子节点
-
*/
-
$headerConfig = [
-
[
-
'title' => '总表头',
-
'style'=> ['fill'=>'#33ccff'],
-
'children' => [
-
['title' => '姓名', 'field' => 'name','format'=>'string','width'=>'40'],//根节点
-
// ['title' => '姓名', 'field' => 'name','width'=>'40'],//根节点
-
// 一般节点
-
[
-
'title' => '吃饭',
-
'children' => [
-
['title' => '上午', 'field' => 'eatam'],
-
['title' => '上午', 'field' => 'eatpm', 'width' => '40'],
-
]
-
],
-
[
-
'title' => '工作',
-
'children' => [
-
['title' => '上午', 'field' => 'workam'],
-
['title' => '下午', 'field' => 'workpm'],
-
]
-
],
-
[
-
'title' => '喝水',
-
'children' => [
-
['title' => '上午','format'=>'string','width'=>'40'],
-
['title' => '下午'],
-
]
-
],
-
]
-
]
-
];
-
$data = [
-
['name' => '张三', 'eatam' => '8:23', 'workam' => '9:23', 'workpm' => '9:23'],
-
['name' => '李四', 'eatam' => '8:24', 'workam' => '9:24', 'workpm' => '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里面没有对应的字段,或者节点设置的时候没有指定的field,所以有几列是空的,表头的颜色是因为写了style的fill属性