PHP如何使用xlswriter進行大數據的導入匯出？（詳解）-php教程-PHP中文網

#三、使用範例

首頁

後端開發

php教程

PHP如何使用xlswriter進行大數據的導入匯出？（詳解）

青灯夜游

Jul 07, 2022 am 11:11 AM

php

PHP如何使用xlswriter進行大數據的導入匯出？以下這篇文章跟大家介紹PHP大數據xlswriter導入導出(最優資料化)的方法，希望對大家有幫助！

PHP如何使用xlswriter進行大數據的導入匯出？（詳解）

本文介紹基於PHP擴充xlswriter的Vtiful\Kernel\Excel類別可以支援無限層級的複雜表頭導出！後續也可能會持續更新最佳化

一、準備xlswriter擴充功能

##1、windows系統：

到PECL網站下載符合自己本地PHP環境的ddl檔案下載位址：https://pecl.php.net/package/xlswriter，並複製到PHP的擴展目錄ext資料夾下，修改php.ini文件，

加上這行

extension=xlswriter

登入後複製

2、Linux系統：

使用指令安裝

pecl install xlswriter

登入後複製

php設定檔新增

extension = xlswriter.so

登入後複製

重啟:php nginx 查看PHP安裝xlswriter拓展

二、封裝匯出類別檔案（重點來了）

<?php

namespace App\Services;

use Vtiful\Kernel\Excel;

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

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

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

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

    /**
     * 设置文件名
     * @param string $fileName
     * @param string $sheetName
     * @author LWW
     */
    public function setFileName(string $fileName = &#39;&#39;, string $sheetName = &#39;Sheet1&#39;)
    {
        $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(&#39;表头数据不能为空&#39;);
        }

        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[&#39;HTTP_USER_AGENT&#39;];
        if (preg_match(&#39;/MSIE/i&#39;, $userBrowser)) {
            $fileName = urlencode($fileName);
        } else {
            $fileName = iconv(&#39;UTF-8&#39;, &#39;GBK//IGNORE&#39;, $fileName);
        }

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

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

        flush();

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

        // 删除本地文件
        @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[&#39;title&#39;])) {
                throw new \Exception(&#39;表头数据格式有误&#39;);
            }

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

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

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

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

                $this->setHeaderNeedManage($head[&#39;children&#39;], $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, &#39;height&#39;));
        $parentManage = array_column($colManage, &#39;parent&#39;);

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

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

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

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

        // 是否开启过滤选项
        if ($filter) {
            // 获取最后的单元格标识
            $filterEndColumn = self::getColumn(end($colManage)[&#39;cursorEnd&#39;]) . $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 = [
            [
                &#39;title&#39; => &#39;一级表头1&#39;,
                &#39;children&#39; => [
                    [
                        &#39;title&#39; => &#39;二级表头1&#39;,
                    ],
                    [
                        &#39;title&#39; => &#39;二级表头2&#39;,
                    ],
                    [
                        &#39;title&#39; => &#39;二级表头3&#39;,
                    ],
                ]
            ],
            [
                &#39;title&#39; => &#39;一级表头2&#39;
            ],
            [
                &#39;title&#39; => &#39;一级表头3&#39;,
                &#39;children&#39; => [
                    [
                        &#39;title&#39; => &#39;二级表头1&#39;,
                        &#39;children&#39; => [
                            [
                                &#39;title&#39; => &#39;三级表头1&#39;,
                            ],
                            [
                                &#39;title&#39; => &#39;三级表头2&#39;,
                            ],
                        ]
                    ],
                    [
                        &#39;title&#39; => &#39;二级表头2&#39;,
                    ],
                    [
                        &#39;title&#39; => &#39;二级表头3&#39;,
                        &#39;children&#39; => [
                            [
                                &#39;title&#39; => &#39;三级表头1&#39;,
                                &#39;children&#39; => [
                                    [
                                        &#39;title&#39; => &#39;四级表头1&#39;,
                                        &#39;children&#39; => [
                                            [
                                                &#39;title&#39; => &#39;五级表头1&#39;
                                            ],
                                            [
                                                &#39;title&#39; => &#39;五级表头2&#39;
                                            ]
                                        ]
                                    ],
                                    [
                                        &#39;title&#39; => &#39;四级表头2&#39;
                                    ]
                                ]
                            ],
                            [
                                &#39;title&#39; => &#39;三级表头2&#39;,
                            ],
                        ]
                    ]
                ]
            ],
            [
                &#39;title&#39; => &#39;一级表头4&#39;,
            ],
            [
                &#39;title&#39; => &#39;一级表头5&#39;,
            ],
        ];
        $data= [];
        // header头规则 title表示列标题，children表示子列，没有子列children可不写或为空
        for ($i = 0; $i < 100; $i++) {
            $data[] = [
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
                &#39;这是第&#39;. $i .&#39;行测试&#39;,
            ];
        }
        $fileName = &#39;很厉害的文件导出类&#39;;
        $xlsWriterServer = new MultiFloorXlsWriterService();
        $xlsWriterServer->setFileName($fileName, &#39;这是Sheet1别名&#39;);
        $xlsWriterServer->setHeader($header, true);
        $xlsWriterServer->setData($data);

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

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

登入後複製

匯出效果