PHP如何使用xlswriter进行大数据的导入导出？（详解）

extension=xlswriter

pecl install xlswriter

extension = xlswriter.so

<?php

namespace App\Services;

use Vtiful\Kernel\Excel;

class MultiFloorXlsWriterService
{
    // 默认宽度
    private $defaultWidth = 16;
    // 默认导出格式
    private $exportType = '.xlsx';
    // 表头最大层级
    private $maxHeight = 1;
    // 文件名
    private $fileName = null;

    private $xlsObj;
    private $fileObject;
    private $format;

    /**
     * MultiFloorXlsWriterService constructor.
     * @throws \App\Exceptions\ApiException
     */
    public function __construct()
    {
        // 文件默认输出地址
        $path = base_path().'/public/uploads/excel';
        $config = [
            'path' => $path
        ];

        $this->xlsObj = (new \Vtiful\Kernel\Excel($config));
    }

    /**
     * 设置文件名
     * @param string $fileName
     * @param string $sheetName
     * @author LWW
     */
    public function setFileName(string $fileName = '', string $sheetName = 'Sheet1')
    {
        $fileName = empty($fileName) ? (string)time() : $fileName;
        $fileName .= $this->exportType;

        $this->fileName = $fileName;

        $this->fileObject = $this->xlsObj->fileName($fileName, $sheetName);
        $this->format = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
    }

    /**
     * 设置表头
     * @param array $header
     * @param bool $filter
     * @throws \Exception
     * @author LWW
     */
    public function setHeader(array $header, bool $filter = false)
    {
        if (empty($header)) {
            throw new \Exception('表头数据不能为空');
        }

        if (is_null($this->fileName)) {
            self::setFileName(time());
        }

        // 获取单元格合并需要的信息
        $colManage = self::setHeaderNeedManage($header);

        // 完善单元格合并信息
        $colManage = self::completeColMerge($colManage);

        // 合并单元格
        self::queryMergeColumn($colManage, $filter);

    }

    /**
     * 填充文件数据
     * @param array $data
     * @author LWW
     */
    public function setData(array $data)
    {
        foreach ($data as $row => $datum) {
            foreach ($datum as $column => $value) {
                $this->fileObject->insertText($row + $this->maxHeight, $column, $value);
            }
        }
    }

    /**
     * 添加Sheet
     * @param string $sheetName
     * @author LWW
     */
    public function addSheet(string $sheetName)
    {
        $this->fileObject->addSheet($sheetName);
    }

    /**
     * 保存文件至服务器
     * @return mixed
     * @author LWW
     */
    public function output()
    {
        return $this->fileObject->output();
    }

    /**
     * 输出到浏览器
     * @param string $filePath
     * @throws \Exception
     * @author LWW
     */
    public function excelDownload(string $filePath)
    {
        $fileName = $this->fileName;
        $userBrowser = $_SERVER['HTTP_USER_AGENT'];
        if (preg_match('/MSIE/i', $userBrowser)) {
            $fileName = urlencode($fileName);
        } else {
            $fileName = iconv('UTF-8', 'GBK//IGNORE', $fileName);
        }

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Content-Length: ' . filesize($filePath));
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Cache-Control: max-age=0');
        header('Pragma: public');

        if (ob_get_contents()) {
            ob_clean();
        }

        flush();

        if (copy($filePath, 'php://output') === false) {
            throw new \Exception($filePath . '地址出问题了');
        }

        // 删除本地文件
        @unlink($filePath);

        exit();
    }

    /**
     * 组装单元格合并需要的信息
     * @param array $header
     * @param int $col
     * @param int $cursor
     * @param array $colManage
     * @param null $parent
     * @param array $parentList
     * @return array
     * @throws \Exception
     * @author LWW
     */
    private function setHeaderNeedManage(array $header,int $col = 1,int &$cursor = 0,array &$colManage = [], $parent = null,array $parentList = [])
    {
        foreach ($header as $head) {
            if (empty($head['title'])) {
                throw new \Exception('表头数据格式有误');
            }

            if (is_null($parent)) {
                // 循环初始化
                $parentList = [];
                $col = 1;
            } else {
                // 递归进入，高度和父级集合通过相同父级条件从已有数组中获取，避免递归增加与实际数据不符
                foreach ($colManage as $value) {
                    if ($value['parent'] == $parent) {
                        $parentList = $value['parentList'];
                        $col = $value['height'];
                        break;
                    }
                }
            }

            // 单元格标识
            $column = $this->getColumn($cursor) . $col;

            // 组装单元格需要的各种信息
            $colManage[$column] = [
                'title'      => $head['title'],      // 标题
                'cursor'     => $cursor,             // 游标
                'cursorEnd'  => $cursor,             // 结束游标
                'height'     => $col,                // 高度
                'width'      => $this->defaultWidth, // 宽度
                'mergeStart' => $column,             // 合并开始标识
                'hMergeEnd'  => $column,             // 横向合并结束标识
                'zMergeEnd'  => $column,             // 纵向合并结束标识
                'parent'     => $parent,             // 父级标识
                'parentList' => $parentList,         // 父级集合
            ];

            if (isset($head['children']) && !empty($head['children']) && is_array($head['children'])) {
                // 有下级，高度加一
                $col += 1;
                // 当前标识加入父级集合
                $parentList[] = $column;

                $this->setHeaderNeedManage($head['children'], $col, $cursor, $colManage, $column, $parentList);
            } else {
                // 没有下级，游标加一
                $cursor += 1;
            }
        }

        return $colManage;
    }

    /**
     * 完善单元格合并信息
     * @param array $colManage
     * @return mixed
     * @author LWW
     */
    private function completeColMerge(array $colManage)
    {
        $this->maxHeight = max(array_column($colManage, 'height'));
        $parentManage = array_column($colManage, 'parent');

        foreach ($colManage as $index => $value) {
            // 设置横向合并结束范围：存在父级集合，把所有父级的横向合并结束范围设置为当前单元格
            if (!is_null($value['parent']) && !empty($value['parentList'])) {
                foreach ($value['parentList'] as $parent) {
                    $colManage[$parent]['hMergeEnd'] = self::getColumn($value['cursor']) . $colManage[$parent]['height'];
                    $colManage[$parent]['cursorEnd'] = $value['cursor'];
                }
            }

            // 设置纵向合并结束范围：当前高度小于最大高度 且 不存在以当前单元格标识作为父级的项
            $checkChildren = array_search($index, $parentManage);
            if ($value['height'] < $this->maxHeight && !$checkChildren) {
                $colManage[$index]['zMergeEnd'] = self::getColumn($value['cursor']) . $this->maxHeight;
            }
        }

        return $colManage;
    }

    /**
     * 合并单元格
     * @param array $colManage
     * @param bool $filter
     * @author LWW
     */
    private function queryMergeColumn(array $colManage,bool $filter)
    {
        foreach ($colManage as $value) {
            $this->fileObject->mergeCells("{$value['mergeStart']}:{$value['zMergeEnd']}", $value['title']);
            $this->fileObject->mergeCells("{$value['mergeStart']}:{$value['hMergeEnd']}", $value['title']);

            // 设置单元格需要的宽度
            if ($value['cursor'] != $value['cursorEnd']) {
                $value['width'] = ($value['cursorEnd'] - $value['cursor'] + 1) * $this->defaultWidth;
            }

            // 设置列单元格样式
            $toColumnStart = self::getColumn($value['cursor']);
            $toColumnEnd = self::getColumn($value['cursorEnd']);
            $this->fileObject->setColumn("{$toColumnStart}:{$toColumnEnd}", $value['width']);
        }

        // 是否开启过滤选项
        if ($filter) {
            // 获取最后的单元格标识
            $filterEndColumn = self::getColumn(end($colManage)['cursorEnd']) . $this->maxHeight;
            $this->fileObject->autoFilter("A1:{$filterEndColumn}");
        }
    }

    /**
     * 获取单元格列标识
     * @param int $num
     * @return string
     * @author LWW
     */
    private function getColumn(int $num)
    {
        return Excel::stringFromColumnIndex($num);
    }
}

    /**
     * 导出测试
     * @author LWW
     */
    public function export()
    {
        $header = [
            [
                'title' => '一级表头1',
                'children' => [
                    [
                        'title' => '二级表头1',
                    ],
                    [
                        'title' => '二级表头2',
                    ],
                    [
                        'title' => '二级表头3',
                    ],
                ]
            ],
            [
                'title' => '一级表头2'
            ],
            [
                'title' => '一级表头3',
                'children' => [
                    [
                        'title' => '二级表头1',
                        'children' => [
                            [
                                'title' => '三级表头1',
                            ],
                            [
                                'title' => '三级表头2',
                            ],
                        ]
                    ],
                    [
                        'title' => '二级表头2',
                    ],
                    [
                        'title' => '二级表头3',
                        'children' => [
                            [
                                'title' => '三级表头1',
                                'children' => [
                                    [
                                        'title' => '四级表头1',
                                        'children' => [
                                            [
                                                'title' => '五级表头1'
                                            ],
                                            [
                                                'title' => '五级表头2'
                                            ]
                                        ]
                                    ],
                                    [
                                        'title' => '四级表头2'
                                    ]
                                ]
                            ],
                            [
                                'title' => '三级表头2',
                            ],
                        ]
                    ]
                ]
            ],
            [
                'title' => '一级表头4',
            ],
            [
                'title' => '一级表头5',
            ],
        ];
        $data= [];
        // header头规则 title表示列标题，children表示子列，没有子列children可不写或为空
        for ($i = 0; $i < 100; $i++) {
            $data[] = [
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
            ];
        }
        $fileName = '很厉害的文件导出类';
        $xlsWriterServer = new MultiFloorXlsWriterService();
        $xlsWriterServer->setFileName($fileName, '这是Sheet1别名');
        $xlsWriterServer->setHeader($header, true);
        $xlsWriterServer->setData($data);

        $xlsWriterServer->addSheet('这是Sheet2别名');
        $xlsWriterServer->setHeader($header);   //这里可以使用新的header
        $xlsWriterServer->setData($data);       // 这里也可以根据新的header定义数据格式

        $filePath = $xlsWriterServer->output();     // 保存到服务器
        $xlsWriterServer->excelDownload($filePath); // 输出到浏览器
    }