减少SpreadSheet代码操作，配置形式生成Excel

由于SpreadSheet的操作过于冗长，中间添加一列的后面全部都要修改，稍有不慎就会出错，因此对其进行二次开发。
该方法对于特殊的处理，可以使用$spread对象去操作数据集。

使用如下：

	// $data 二维数组，Key值对应body的key。
	$data = [];
	$spreadsheet = new Spreadsheet();
	$head = ['名字','年龄','性别','班级'];
	$body = ['name','age','sex','class'];
	Tool::addSheet($spreadsheet, 1, 'Excel名称', $data, $head, $body);
	$spreadsheet->setActiveSheetIndex(0);
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $fileName2 = '/tmp/' . $fileName . '.xlsx';
    $writer->save($fileName2);

Tool工具类代码如下

class Tool{
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
    public static $styleArray = [
            'font' => [
                'bold' => true
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
    public static $styleArrayBody = [
        'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '666666'],
        ],
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];

    /**
     * 减少代码量,sheet操作缩减
     * @param Spreadsheet $spreadsheet sheet对象
     * @param int $sheetNum 第N个sheet (1开始)
     * @param string $title sheet的名字
     * @param array $data 数据源,必须未二维数组
     * @param array $head 每一列对应的中文名称
     * @param array $body 一维数组,数据对应的key值
     * @param int $headLine head所在的行数
     * @param mixed[array|null] $combine 所需合并的单元格,并赋值 ["A1:A3"=>'名称',....]
     * @param mixed[array|null] $bgColor 颜色 ["A1:A3"=>"E2EFDA",....]
     * @throws Exception
     */
    public static function addSheet(Spreadsheet $spreadsheet, int $sheetNum, string $title, array $data, array $head, array $body, int $headLine = 1, $combine = '', $bgColor = '')
    {
        if($sheetNum == 1) {
            $sheetActive = $spreadsheet->getActiveSheet()->setTitle($title);
        } else {
            $sheetActive = $spreadsheet->createSheet($sheetNum)->setTitle($title);
        }
        // 修改颜色
        if(!empty($bgColor)) {
            foreach ($bgColor as $k => $v) {
                $sheetActive->getStyle($k)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB($v);
            }
        }
        $i = 0;
        foreach ($head as $v)
        {
            $char = static::int2Excel($i++);
            $sheetActive->setCellValue($char.$headLine,$v);
        }
        //设置单元格样式
        $sheetActive->freezePane('A'.($headLine + 1));
        if(isset($char)) {
            $sheetActive->getStyle('A1:'.$char.$headLine)->applyFromArray(self::$styleArray)->getFont()->setSize(11);//->getColor()->setARGB('FF4747FF');
            for($i = 'A'; $i < $char; $i++) {
                if($i == 'A' || $i == 'B' ||  $i == 'C')
                    $sheetActive->getColumnDimension($i)->setWidth(30);
                $sheetActive->getColumnDimension($i)->setWidth(12);
            }
        }
        $j  = 1 + $headLine;

        // 数据源解析
        foreach($data as $v1) {
            $i = 1;
            foreach ($body as $v2) {
                if(substr_count($v2,'--')) {
                    $sheetActive->setCellValueByColumnAndRow($i++,$j,ltrim($v2,'--'));
                } else {
                    $sheetActive->setCellValueByColumnAndRow($i++,$j,$v1[$v2]);
                }
            }
            $j++;
        }
        // 合并单元格,并赋值
        if(!empty($combine)){
            foreach ($combine as $k => $v) {
                if(!strstr($k,':'))
                {
                    echo '合并单元格出错,请检查'.$k;
                    die;
                }
                $sheetActive->mergeCells($k);
                $first = explode(':',$k);
                if(is_array($first)) {
                    $first = $first[0];

                } else {
                    $first = [$first] ;
                }
                $sheetActive->setCellValue($first , $v);
            }
        }
        $total_rows = $j - 1;
        //添加所有边框居中
        $sheetActive->getStyle('A1:'.$char.$total_rows)->applyFromArray(self::$styleArrayBody);
    }

    static function excel2Int(string $str)
    {
        $num = 0;
        $strArr = str_split($str, 1);
        $len = count($strArr);
        foreach ($strArr as $k => $v) {
            $num += ((ord($v) - ord('A') + 1) * pow(26, $len - $k - 1));
        }
        return $num - 1;
    }

    /**
     * @param int $num 0:A  1:B ... 26:AA
     * @return string
     */
    static function int2Excel(int $num)
    {
        $az = 26;
        $m = (int)($num % $az);
        $q = (int)($num / $az);
        $letter = chr(ord('A') + $m);
        if ($q > 0) {
            return static::int2Excel($q - 1) . $letter;
        }
        return $letter;
    }
}