PHPexcel 如何合根据join模型关联进行导出合并单元格数据（Laravel yii 模型关联一对多）

chu_yubo

已于 2023-08-03 08:36:40 修改

阅读量527

点赞数

分类专栏： php web后端开发文章标签： php excel

于 2020-03-17 13:22:20 首次发布

本文链接：https://blog.csdn.net/chu_yubo/article/details/104919824

版权

web后端开发同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

php

11 篇文章 0 订阅

订阅专栏

问题场景：

当我们在用php导出excel的时候，可能会有这种需求，在excel表格中进行合并单元格，譬如下面这种情况

此时如果我们可以利用PHPexcel中的函数 mergeCells进行合并单元格，不过我们还得分析我们导出的数据从哪些单元格合并到哪些单元格，不具有通用性，因此我们的诉求是有没有一个方法自动分析，给我们进行单元格的合并。

设计思路：

我们一般提供数组来进行导出，数组中某些键值存在子数组，子数组进行合并单元格，我们就可以循环遍历出子数组的个数来进行预判别哪些单元格需要合并，哪些单元格不需要合并。这种含有子数组的例子可以是在我们用框架 Yii或者Laravel 进行模型关联时hasMany，或者是sql表中记录json对象等。以下面这个例子来说：

             [
                  ['code' => '028','created_by' => '超级管理员','product_name' => '沐浴露','net_weight' => '500','created_at' => '2019-10-15','packing' => []],
                  ['code' => '029','created_by' => '超级管理员','product_name' => '洗发水','net_weight' => '100','created_at' => '2019-10-15', 'packing' => [['code' => '0010','packing' => '洗发水通用瓶子'],['code' => '0011','packing' => '洗发水样品通用瓶子']]]
              ]

此时我们想导出上面图片这种类型的excel，如果是你的话你会怎么做呢？

代码实现


<?php


namespace App\Utils;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class MergeExcelUtils
{
    /**
     * @param int $int
     * @return string
     */
    public function intToChr(int $int = 0): string
    {
        $start = 65;
        $max = 26;
        $prefix = '';
        if ($int >= $max) {
            $prefix = $this->intToChr(($int / $max) - 1);
        }
        return $prefix . chr($start + ($int % $max));
    }

    /**
     * getXlsxTemplateForJoinWithArray()
     * 三维数组通用导出模板，适用于JOIN WITH 数组的导出，其中，with对应的字段要用@隔开
     * @param $head
     * 表头信息 eg ：['code' => '产品编码','created_by' => '创建人','product_name' => '产品名称','net_weight' => '净含量','created_at' => '创建时间','packing@code' => '包装代码', 'packing@packing' => '包装名称']
     * @param $source
     * 表单信息 eg：
     *  [
     *      ['code' => '028','created_by' => '超级管理员','product_name' => '沐浴露','net_weight' => '500','created_at' => '2019-10-15','packing' => []],
     *      ['code' => '029','created_by' => '超级管理员','product_name' => '洗发水','net_weight' => '100','created_at' => '2019-10-15', 'packing' => [['code' => '0010','packing' => '洗发水通用瓶子'],['code' => '0011','packing' => '洗发水样品通用瓶子']]]
     *  ]
     * ;
     * @return Spreadsheet
     * @throws Exception
     * @author ChuYubo
     */
    public function getXlsxTemplateForJoinWithArray($head, $source): Spreadsheet
    {
        //统计总共多少行
        $count = 1;
        //统计每一个数据对应多上行
        $sourceCountArray = [];
        if ($source && is_array($source)) {
            foreach ($source as $sourceKey => $sourceItem) {
                $maxCount = 1;
                if ($sourceItem && is_array($sourceItem)) {
                    $sourceItem = array_values($sourceItem);
                    foreach ($sourceItem as $itemKey => $itemValue) {
                        if (is_array($itemValue) && count($itemValue) > $maxCount) {
                            $maxCount = count($itemValue);
                        }
                    }
                }
                $sourceCountArray[$sourceKey] = $maxCount;
                $count += $maxCount;
            }
        }

        $headKey = array_values(array_keys($head));
        $head = array_values($head);
        $headKeyArray = array_flip($headKey);
        $children = [];
        if ($source) {
            //重新编排source内容，要求 键值和之前head对应的键值（已转化成0,1,2,3,4...）一一对应，其中with对应的的位置下均为该数组，
            //并且规划数组对应位置children 如： [5 => ['code' => 5,'packing' => 6],6 => ['code' => 5,'packing' => 6]];
            $source = array_map(static function ($array) use ($headKeyArray, &$children) {
                $array_return = [];
                foreach ($array as $key => $item) {
                    if (array_key_exists($key, $headKeyArray)) {
                        $array_return[$headKeyArray[$key]] = $item;
                    } else {
                        foreach ($headKeyArray as $headKey => $headValue) {
                            if (strpos($headKey, $key . '@') !== false) {
                                $array_return[$headValue] = $item;
                                $children[$headValue] = [substr($headKey, strlen($key . '@')) => $headValue];
                            }
                        }
                    }
                }
                return $array_return;
            }, $source);
        }

        //生成文档
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //置字体大小为10
        $spreadsheet->getDefaultStyle()->getFont()->setName('Calibri');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
        //样式=边框线+对准
        $sheet->getStyle('A1:' . $this->intToChr(count($head) - 1) . $count)->applyFromArray(
            [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['argb' => '00000000'],
                    ]
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical' => Alignment::VERTICAL_CENTER,
                ]
            ]
        );
        //1.输出头
        for ($sym = 0, $symMax = count($head) - 1; $sym <= $symMax; $sym++) {
            $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($this->intToChr($sym) . '1', $head[$sym], DataType::TYPE_STRING);
        }

        //输出内容
        for ($row = 2, $source_id = 0; $row <= $count; $source_id++) {
            //该合并多少行
            $num = $sourceCountArray[$source_id];
            for ($columnNumber = 0, $columnNumberMax = (count($head) - 1); $columnNumber <= $columnNumberMax; $columnNumber++) {
                $childFatherKey = -1;
                $childKey = '';

                if (array_key_exists($columnNumber, $children)) {
                    if (in_array($columnNumber, $children[$columnNumber], false)) {
                        $childFatherKey = $columnNumber;
                        $childKey = array_search($columnNumber, $children[$columnNumber], false);
                    }
                }

                if ($childFatherKey === -1) {
                    $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($this->intToChr($columnNumber) . $row, $source[$source_id][$columnNumber], DataType::TYPE_STRING);
                    $spreadsheet->setActiveSheetIndex(0)->mergeCells($this->intToChr($columnNumber) . $row . ':' . $this->intToChr($columnNumber) . ($row + $num - 1));
                    $spreadsheet->setActiveSheetIndex(0)->getStyle($this->intToChr($columnNumber) . $row)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
                } else {
                    for ($number = 0; $number < $num; $number++) {
                        $spreadsheet->setActiveSheetIndex(0)->setCellValueExplicit($this->intToChr($columnNumber) . ($row + $number), $source[$source_id][$childFatherKey][$number][$childKey] ?? '', DataType::TYPE_STRING);
                    }
                }
            }

            $row += $num;
        }
        return $spreadsheet; #此时导出
    }

此时我们通过调用方法，就可以将想要的excel进行导出


        header("access-control-expose-headers: Authorization, Content-Disposition");
        header('Content-Disposition:attachment;filename="产品列表.xlsx"');
        header('Cache-Control:max-age=0');
        $excel = new MergeExcelUtils();
        $spreadsheet = $excel->getXlsxTemplateForJoinWithArray(
            ['code' => '产品编码','created_by' => '创建人','product_name' => '产品名称','net_weight' => '净含量','created_at' => '创建时间','packing@code' => '包装代码', 'packing@packing' => '包装名称'],
              [
                  ['code' => '028','created_by' => '超级管理员','product_name' => '沐浴露','net_weight' => '500','created_at' => '2019-10-15','packing' => []],
                  ['code' => '029','created_by' => '超级管理员','product_name' => '洗发水','net_weight' => '100','created_at' => '2019-10-15', 'packing' => [['code' => '0010','packing' => '洗发水通用瓶子'],['code' => '0011','packing' => '洗发水样品通用瓶子']]]
              ]);
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

需要注意

在head头信息中的键值，在列表中一定要存在。在laravel中，如果直接导出$writer->save(‘php://output’) 可能会出现跨域或者其他问题，所以在laravel中最好是生成BinaryFileResponse类进行返回，并且中文名称的话可能会导致导出excel文件命名出现乱码问题，因此导出我们可以这么操作


        $excel = new MergeExcelUtils();
        $spreadsheet = $excel->getXlsxTemplateForJoinWithArray(
            ['code' => '产品编码','created_by' => '创建人','product_name' => '产品名称','net_weight' => '净含量','created_at' => '创建时间','packing@code' => '包装代码', 'packing@packing' => '包装名称'],
              [
                  ['code' => '028','created_by' => '超级管理员','product_name' => '沐浴露','net_weight' => '500','created_at' => '2019-10-15','packing' => []],
                  ['code' => '029','created_by' => '超级管理员','product_name' => '洗发水','net_weight' => '100','created_at' => '2019-10-15', 'packing' => [['code' => '0010','packing' => '洗发水通用瓶子'],['code' => '0011','packing' => '洗发水样品通用瓶子']]]
              ]);
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

         /** @var  Maatwebsite\Excel\Files\TemporaryFileFactory $tmpFile */
        $tmpFile = app(TemporaryFileFactory::class);
        $tmpFilePath = $tmpFile->makeLocal()->getLocalPath();
        $writer->save($tmpFilePath);
        $filename= '产品列表.xlsx';
        return BinaryFileResponse::create($tmpFilePath)
            ->setContentDisposition('attachment', $filename, str_replace('%', '', Str::ascii($filename)))
            ->deleteFileAfterSend(true);