PHP_XLSXWriter 样式,PHP_XLSXWriter 数据库输出辅助类

为了减轻后期开发的麻烦，写了这个类，用以简化输出配置，自动合并表头样式

vendor('XLSXWriter.Helper');

$ds=M('kaoqin_tongji')->where("BMID=111 and YueFen='2021-01'")->select();

$writer = new \XLSXWriterHelper();

$writer->writeToStdOutX('统计导出', [['-', '统计导出', [

['XH', '序号', 8],

['BM', '部门', 16],

['XM', '姓名', 12],

['YCQ', '应出勤', 8],

['SCQ', '实际出勤', 10],

['SDK', '实际打卡', 10],

['CC', '出差天数', 10],

['JX', '计薪天数', 10],

['ZMJB', '周末加班', 10],

['JRJB', '节日加班', 10],

['-', '请休假', [

['NJ', '年假', 6],

['HJ', '婚假', 6],

['PCJ', '陪产假', 8],

['SAJ', '丧假', 6],

['CJ', '产假', 6],

['GSJ', '工伤假', 8],

['SJ', '事假', 6],

['BJ', '病假', 6],

]],

['-', '夜值', [

['YZ1', '夜值A', 8],

['YZ2', '夜值B', 8],

]],

]]], $ds);

导出效果

辅助类 Helper.php：

require_once 'XLSXWriter.class.php';

class XLSXWriterHelper

{

private function getHdsWidths($hds)

{

$re = [];

foreach ($hds as $hid => $h) {

foreach ($h as $lid => $l) {

if (array_key_exists(1, $l) && (int) $l[1] > 0) {

$re[$lid] = (int) $l[1];

}

}

}

return $re;

}

private function getHdsRowFs($hds)

{

$re = [];

foreach ($hds as $hid => $h) {

foreach ($h as $lid => $l) {

if (array_key_exists(2, $l)) {

$re[$lid] = $l[2];

}

}

}

return $re;

}

private function getHdsRowDs($hds)

{

$re = [];

$lm = 0;

foreach ($hds as $hid => $h) {

$ls = max(array_keys($h));

if ($ls > $lm) {

$lm = $ls;

}

}

foreach ($hds as $hid => $h) {

$re[$hid] = array_pad([], $lm, '');

foreach ($h as $lid => $l) {

$re[$hid][$lid] = $l[0];

}

}

return $re;

}

private function getHdsMergeInfo($hds)

{

$re = [];

foreach ($hds as $hid => $h) {

foreach ($h as $lid => $l) {

if (array_key_exists('merge', $l)) {

$re[] = $l['merge'];

} else {

if (!empty($l[0]) && $hid < (count($hds) - 1) && $hds[$hid + 1][$lid][0] == '') {

$re[] = [$hid, $lid, $hid + 1, $lid];

}

}

}

}

return $re;

}

private function config2HDS($config, &$hds, $h = 0, $l = 0)

{

$hs = [];

$i = -1;

foreach ($config as $v) {

++$i;

if (!array_key_exists($h, $hds)) {

$hds[$h] = [];

}

if ($v[0] == '-') {

$w = self::config2HDS($v[2], $hds, $h + 1, $l + $i);

$hds[$h][$l + $i] = [$v[1], 'merge' => [$h, $l + $i, $h, $l + $i + $w]];

for ($k = 1; $k <= $w; ++$k) {

$hds[$h][$l + $i + $k] = [''];

}

$i += $w;

} else {

$hds[$h][$l + $i] = [$v[1], $v[2], $v[0]];

}

}

return $i;

}

public static function writeToStdOutX($filename = '导出', $config = [], $ds = [])

{

ob_end_clean();

ob_start();

header('Content-Disposition:attachment;filename='.$filename.'.xlsx');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Transfer-Encoding: binary');

header('Cache-Control: must-revalidate');

header('Pragma: no-cache');

header('Expires: 0');

$writer = new \XLSXWriter();

$writer->setTempDir('./Uploadfile/temp');

$style = ['border' => 'left,right,top,bottom', 'border-style' => 'thin', 'valign' => 'center', 'wrap_text' => 'true', 'height' => 20];

$hds = [];

self::config2HDS($config, $hds);

$hd = self::getHdsRowDs($hds);

$hw = self::getHdsWidths($hds);

$hf = self::getHdsRowFs($hds);

$hm = self::getHdsMergeInfo($hds);

$hs = [

'suppress_row' => true,

'widths' => $hw,

];

$writer->writeSheetHeader('Sheet1', array_pad([], count($hw), 'string'), $col_options = $hs);

foreach ($hd as $dhi => $dh) {

$writer->writeSheetRow('Sheet1', $dh, array_merge($style, $dhi == 0 ? ['halign' => 'center', 'font-style' => 'bold'] : ['halign' => 'center']));

}

$data = [];

$i = 0;

foreach ($ds as $d) {

$line = [++$i];

foreach ($hf as $hk => $v) {

$line[$hk] = $d[$v];

}

$data[] = $line;

}

foreach ($data as $d) {

$writer->writeSheetRow('Sheet1', $d, $style);

}

foreach ($hm as $mg) {

$writer->markMergedCell('Sheet1', $start_row = $mg[0], $start_col = $mg[1], $end_row = $mg[2], $end_col = $mg[3]);

}

$writer->writeToStdOut();

die;

}

}