php多维数组合并单元格导出

在上一篇文章中我们简单的说了数据的导入及导出，
在这片文章中，我们说一下对多维数组的，
合并单元格后导出的，一对多的数据格式。

我们对上一篇的数据格式进行修改成我们需要的格式，
单元格的合并，主要在于对行号的计算，
这里我们定义多个变量来记录行号。。。

foreach ($data as $k => $v) { // 循环外部数据处理(行)
    $lines ++; // 记录外部行号
    $descCount = isset($descField) ? count($v['desc']) : 0;
    // $newExcel->getActiveSheet()->mergeCells('A19:A22'); // 单元格合并
    foreach ($key_field as $m => $n) { // (列)
        $letter = num_letter($m+1);

        $objSheet = $objSheet->setCellValue($letter . $lines, $v[$n]);
        if ($descCount > 0) { // 处理内部desc数据
            $descCount > 1 && $newExcel->getActiveSheet()->mergeCells("$letter$lines:$letter".($lines+$descCount-1)); // 单元格合并
            $descLine = $lines; // 行号(记录多行desc数据行号)
            foreach ($v['desc'] as $x => $y) { // 循环desc数据(行)
                foreach ($descKeyField as $q => $w) { // (列)
                    $letter = num_letter(count($key_field)+$q+1);
                    $objSheet = $objSheet->setCellValue($letter . $descLine, $y[$w]);
                }
                $descLine ++; // 行号(记录多行desc数据行号)
            }
        }
    }
    $descCount > 0 && $lines = $lines + $descCount - 1; // 重新处理数据行号
}

完成示例代码：

<?php
/**
 * @Author: [FENG] <1161634940@qq.com>
 * @Date:   2020-04-16 20:49:34
 * @Last Modified by:   [FENG] <1161634940@qq.com>
 * @Last Modified time: 2020-04-19 13:15:51
 */

include_once './vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory; //use \PHPExcel_Style_NumberFormat;    //设置列的格式==>>设置文本格式

class Ceshi
{
    public static function export()
    {
        $data = [
            [ 'id'=>1, 'name'=>'张三', 'sex'=>'男', 'age'=>20, 'desc'=>[
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
            ] ],
            [ 'id'=>2, 'name'=>'李四', 'sex'=>'女', 'age'=>18, 'desc'=>[
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
            ] ],
            [ 'id'=>3, 'name'=>'王五', 'sex'=>'女', 'age'=>25, 'desc'=>[
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
            ] ],
            [ 'id'=>4, 'name'=>'赵六', 'sex'=>'男', 'age'=>22, 'desc'=>[
                ['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],
            ] ],
        ];

        // $fieldArr = ['ID'=>'id', '姓名'=>'name', '性别'=>'sex', '年龄'=>'age', '电话'=>'tel', '测试'=>'ceshi']; // 声明对应字段关系
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('测试表');  //设置当前sheet的标题
        $lines = 1;

        $fieldArr = [ 'id' => 'ID', 'name' => '姓名', 'sex' => '性别', 'age' => '年龄', 'desc' => [
            'chine' => '语文', 'math' => '数学', 'english' => '英语'
        ] ]; // 声明对应字段关系

        $fieldArr && $fieldArr = array_change_key_case($fieldArr,CASE_LOWER);

        if (isset($fieldArr['desc'])) {
            $descField = array_change_key_case($fieldArr['desc'],CASE_LOWER);;
            $descKeyField = array_keys($descField);
            unset($fieldArr['desc']);
            $titleFieldArr = array_merge($fieldArr, $descField);
            $key_field = array_keys($fieldArr);
        } else {
            $titleFieldArr = $fieldArr;
            $key_field = $titleFieldArr ? array_keys($titleFieldArr) : array_keys($data[0]);
        }

        $value_field = $titleFieldArr ? array_values($titleFieldArr) : array_keys($data[0]);

        foreach ($value_field as $k => $v) {
            $letter = num_letter($k+1);
            $newExcel->getActiveSheet()->getColumnDimension($letter)->setAutoSize(true); // 简单设置列宽
            $objSheet = $objSheet->setCellValue($letter . $lines, $v); // 设置标题
        }

        //第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。
        //->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
        foreach ($data as $k => $v) { // 循环外部数据处理(行)
            $lines ++; // 记录外部行号
            $descCount = isset($descField) ? count($v['desc']) : 0;
            // $newExcel->getActiveSheet()->mergeCells('A19:A22'); // 单元格合并
            foreach ($key_field as $m => $n) { // (列)
                $letter = num_letter($m+1);

                $objSheet = $objSheet->setCellValue($letter . $lines, $v[$n]);
                if ($descCount > 0) { // 处理内部desc数据
                    $descCount > 1 && $newExcel->getActiveSheet()->mergeCells("$letter$lines:$letter".($lines+$descCount-1)); // 单元格合并
                    $descLine = $lines; // 行号(记录多行desc数据行号)
                    foreach ($v['desc'] as $x => $y) { // 循环desc数据(行)
                        foreach ($descKeyField as $q => $w) { // (列)
                            $letter = num_letter(count($key_field)+$q+1);
                            $objSheet = $objSheet->setCellValue($letter . $descLine, $y[$w]);
                        }
                        $descLine ++; // 行号(记录多行desc数据行号)
                    }
                }
            }
            $descCount > 0 && $lines = $lines + $descCount - 1; // 重新处理数据行号
        }

        // downloadExcel($newExcel, '测试表', 'Xlsx', './public'); // 保存到本地
        downloadExcel($newExcel, '测试表', 'Xlsx'); //生成文件直接下载
    }

}

// 生成随机数
function suiji()
{
    return rand(50,100);
}

// 公共文件，十进制转二十六进制(基数为A-Z)
function num_letter($num) {
    $num = intval($num);
    if ($num <= 0)
        return false;
    $letterArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
    $letter = '';
    do {
        $key = ($num - 1) % 26;
        $letter = $letterArr[$key] . $letter;
        $num = floor(($num - $key) / 26);
    } while ($num > 0);
    return $letter;
}

// 公共文件，用来传入xls并下载
function downloadExcel($newExcel, $filename, $format, $savePath = false)
{
    if(!$savePath){   //网页下载
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }
        header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);
        $objWriter->save('php://output');exit;
    } else {
        ob_clean();
        ob_start();
        $objWriter = IOFactory::createWriter($newExcel, $format);
        $savePath = trim(trim($savePath, '.'), '/') . '/' . $filename . date('Y-m-d') . '.' . strtolower($format);
        $objWriter->save($savePath);
        /* 释放内存 */
        $newExcel->disconnectWorksheets();
        unset($newExcel);
        ob_end_flush();
    }
}

Ceshi::export(); // 数据文件的导出到ececl