PHP 基于XLSXWriter的多级表头Excel导出

最新推荐文章于 2024-03-07 17:08:51 发布

feitianli37

最新推荐文章于 2024-03-07 17:08:51 发布

阅读量761

点赞数

原文链接：https://blog.csdn.net/hellocgege/article/details/104040021

版权

示例只有三级，但是不止于三级

应用举例

比如说我们需要这样一个表头

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属性

feitianli37

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PHP 基于XLSXWriter的多级表头Excel导出

示例只有三级，但是不止于三级应用举例比如说我们需要这样一个表头1.XLSXWriter应用分析根据XLSXWriter工具类的示例我们需要插入三行数据作为表头然后再合并掉对应的单元格2.工具类编写调试的注释啥的，都在里面，有兴趣的自己可以看一下。需要放在和xlsxwriter.class.php同一目录下使用 <?php /** * Created By * 模块说明: * User: helloc .
复制链接

扫一扫