PHP_XLSXWriter|PHP_XLSXWriter 数据库输出辅助类 PHP_XLSXWriter数据库输出辅助类

为了减轻后期开发的麻烦，写了这个类，用以简化输出配置，自动合并表头样式
PHP_XLSXWriter : https://github.com/mk-j/PHP_XLSXWriter
先看例子（test.php）：

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);

文章图片
导出效果
【PHP_XLSXWriter|PHP_XLSXWriter 数据库输出辅助类】辅助类 Helper.php：

$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 = https://www.it610.com/article/[]; $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; } }