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

辅助类 Helper.php：

<?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;
    }
}