dcat使用xlswriter分塊匯出資料，自定義列寬行高，首行合併表名

之前用了laravel-excel封裝dcat的表格資料匯出，那玩意太耗記憶體了，要改的話也不好改，乾脆換xlswriter這個擴充套件來搞。
首先按文件把擴充套件安裝上
xlswriter-docs.viest.me/
然後安裝ide程式碼提示工具
composer require viest/php-ext-xlswriter-ide-helper:dev-master

然後隨便找個目錄新建匯出類檔案，我的是這樣子的，複製過去改一改就ok

<?php
namespace App\Exports\OutPerson;

use App\Models\OutPerson;
use Carbon\Carbon;
use Dcat\Admin\Grid\Exporter;
use Dcat\Admin\Grid\Exporters\AbstractExporter;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Vtiful\Kernel\Excel;
use Vtiful\Kernel\Format;

class OutPersonExport extends AbstractExporter {
    //column只是方便檢視對應關係，列號是自動計算的，列號範圍從a到zz
    public $header = [
        0  => ['column' => 'a', 'width' => 8, 'name' => '序號'],
        1  => ['column' => 'b', 'width' => 10, 'name' => '住址-鄉鎮街道'],
        2  => ['column' => 'c', 'width' => 10, 'name' => '姓名'],
        3  => ['column' => 'd', 'width' => 10, 'name' => '職業'],
        4  => ['column' => 'e', 'width' => 5, 'name' => '是否在外定居'],
        5  => ['column' => 'f', 'width' => 15, 'name' => '備註'],
        6  => ['column' => 'g', 'width' => 10, 'name' => '外出時間'],
        7  => ['column' => 'h', 'width' => 10, 'name' => '返回時間'],
        8  => ['column' => 'i', 'width' => 10, 'name' => '省'],
        9  => ['column' => 'j', 'width' => 10, 'name' => '市'],
        10 => ['column' => 'k', 'width' => 10, 'name' => '區縣'],
        11 => ['column' => 'l', 'width' => 10, 'name' => '街道鄉鎮'],
        12 => ['column' => 'm', 'width' => 10, 'name' => '小區樓棟'],
        13 => ['column' => 'n', 'width' => 10, 'name' => '住址-社群、村'],
        14 => ['column' => 'o', 'width' => 10, 'name' => '住址-小區樓棟'],
        15 => ['column' => 'p', 'width' => 25, 'name' => '身份證號碼'],
        16 => ['column' => 'q', 'width' => 20, 'name' => '聯絡方式'],
        17 => ['column' => 'r', 'width' => 5, 'name' => '性別'],
    ];
    public $fileName = '外出人員匯出表';
    public $tableTitle = '外出人員匯出表';
    /**
     * @var Collection
     */
    public $data;

    public function getTmpDir(): string {
        $tmp = ini_get('upload_tmp_dir');

        if ($tmp !== false && file_exists($tmp)) {
            return realpath($tmp);
        }

        return realpath(sys_get_temp_dir());
    }

    public function setFilename($filename) {
        $this->fileName = $filename . Date('YmdHis') . '.xlsx';
        return $this;
    }

    public function getFilename() {
        return $this->fileName;
    }

    public function getHeader() {
        return $this->header;
    }

    public function getTableTitle() {
        return $this->tableTitle;
    }

    public function getData() {
        return $this->data;
    }

    public $index;

    public function setData($data) {
        if (!$data instanceof \Illuminate\Support\Collection) {
            $data = collect($data);
        }
        $this->data = $data;
        $this->index = 1;

        return $this;
    }

    // 格式化日期的，不需要可以刪除
    public static function formatDate($date, $format = "m-d") {
        if ($date) {
            return (new Carbon($date))->format($format);
        }
        return null;
    }


    public function map($row) {
        if (!$row instanceof Model) {
            return $row;
        }
        // dd($row);
        /** @var OutPerson $row */
        // 如果有關聯關係，需要在grid或者model定義with預載入，不然每一個查一下，嚴重影響速度
        $person = $row->person;
        return [
            /*'a' =>*/ // 行號主要方便檢視對應關係，可以刪除
            $this->index++,       //序號
            /*'b' =>*/
            $person->street->name ?? null,       //所屬鄉鎮街道
            /*'c' =>*/
            $person->name,       //姓名
            /*'d' =>*/
            $person->job,       //職業
            /*'e' =>*/
            YesOrNo[$row->live_at_out]??null,       //是否在外定居
            /*'f' =>*/
            $row->note,       //備註
            /*'g' =>*/
            static::formatDate($row->leave_at),       //外出時間
            /*'h' =>*/
            static::formatDate($row->will_return_at),       //返回時間
            /*'i' =>*/
            $row->province->name ?? null,       //省
            /*'j' =>*/
            $row->city->name ?? null,       //市
            /*'k' =>*/
            $row->region->name ?? null,       //區縣
            /*'l' =>*/
            $row->street->name ?? null,       //街道鄉鎮
            /*'m' =>*/
            $row->floor,       //小區樓棟
            /*'n' =>*/
            $person->community->name ?? null,       //現住地社群、村
            /*'o' =>*/
            $person->floor,       //現住地小區樓棟
            /*'p' =>*/
            $person->id_card . ' ',       //身份證號碼
            /*'q' =>*/
            $person->phone . ($person->phone2 ? "\n" . $person->phone2 : ""),       //聯絡方式+備用號碼
            /*'r' =>*/
            Genders[$person->gender] ?? null,       //性別
        ];

        // return $this;
    }

    public const FontFamily = '微軟雅黑';
    public const RowHeight = 40; // 行高
    public $filePath; // 表格儲存的地址
    public $excel;
    public $headerLen; // 表頭長度

    public function __construct() {
        parent::__construct();
        $config = ['path' => $this->getTmpDir() . '/']; // 檔案儲存的路徑
        // dd($config);
        $this->excel = (new Excel($config))/*->constMemory($fileName,'Sheet1')*/ ->fileName($this->setFilename($this->fileName)->fileName, 'Sheet1');
    }

    public function store($isAll = false) {
        $fileHandle = $this->excel->getHandle();
        $format1 = new Format($fileHandle);
        $format2 = new Format($fileHandle);
        // $format3 = new Format($fileHandle);
        /** @var Collection $data */
        $data = $this->getData();
        $header = $this->getHeader();
        $this->headerLen = count($header);
        $columnWidths = array_column($header, 'width');
        $columnNames = array_column($header, 'name');
        // header
        $data->prepend($columnNames);
        // title
        $title = array_fill(1, $this->headerLen - 1, '');
        $title[0] = $this->getTableTitle();
        $data->prepend($title);

        // title style
        $titleStyle = $format1->fontSize(16)
                              ->bold()
                              ->font(self::FontFamily)
                              ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                              ->wrap()
                              ->toResource();

        // $headerStyle = $format3/*->fontSize(10)*/
        //                        // ->font(self::FontFamily)
        //                        // ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
        //     // ->border(Format::BORDER_THIN)
        //                        ->bold()
        //                        // ->wrap()
        //                        ->toResource();

        // global style
        $globalStyle = $format2->fontSize(10)
                               ->font(self::FontFamily)
                               ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                               ->border(Format::BORDER_THIN)
                               ->wrap()
                               ->toResource();

        // 獲取最後一列的列名
        $end = $this->getColumn($this->headerLen - 1);

        // dd($end);
        // 應用樣式
        $this->excel = $this->excel/*->defaultFormat($globalStyle)*/// 預設樣式
        ->MergeCells("A1:{$end}1", $this->getFilename())         // 合併title單元格
        ->setRow("A1", 50, $titleStyle)                       // title樣式
        ->setRow("A2", self::RowHeight)                       // header樣式
        ->freezePanes(2, 0);        // 凍結前兩行，列不凍結
        // 設定列寬 以及預設樣式
        foreach ($columnWidths as $k => $columnWidth) {
            $column = $this->getColumn($k);
            // dd($column);
            $this->excel->setColumn($column . ':' . $column, $columnWidth, $globalStyle);
        }
        // 資料填充，匯出
        if ($isAll) {
            $this->insertData($data);
            $this->filePath = $this->chunk(function(int $times, $perPage) {
                // dump($this->buildData($times, $perPage));
                return $this->buildData($times, $perPage);
            })->output();
        } else {
            $this->filePath = $this->insertData($data)->output();
        }

        return $this;

        // 以下是php原生寫法，後面改成了laravel的response寫法
        // Set Header
        // header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // header('Content-Disposition: attachment;filename="' . $fileName . '"');
        // header('Content-Length: ' . filesize($this->filePath));
        // header('Content-Transfer-Encoding: binary');
        // header('Cache-Control: must-revalidate');
        // header('Cache-Control: max-age=0');
        // header('Pragma: public');
        // ob_clean();
        // flush();
        // if (copy($this->filePath, 'php://output') === false) {
        //         throw new \Exception('failed to write output');
        // }
        // // Delete temporary file
        // @unlink($this->filePath);
        // exit();
    }

    public $shouldDelete = false;
    public $startDataRow = 2; // 第三行開始資料行(0是第一行）
    public $currentLine = 0;  // 當前資料插入行

    public function insertData($data) {
        // dd($this->currentLine, $data);
        foreach ($data as $row => $rowData) {
            $rowData = $this->map($rowData);
            // 對資料行處理
            if ($this->currentLine >= $this->startDataRow) {
                $this->excel->setRow($this->currentLine + 1, self::RowHeight);          // 設定行高，這裡的行又是從1開始的，所以+1
            }
            foreach ($rowData as $column => $columnData) {
                $this->excel->insertText($this->currentLine, $column, $columnData);
            }

            $this->currentLine++;
        }
        return $this;
    }

    public function output() {
        return $this->excel->output();
    }

    // 根據列的序號，得到對應字母列號，範圍a~zz
    public function getColumn(int $columnIndex) {
        $columnIndex++;
        $first = 64 + (int)($columnIndex / 26);
        $second = 64 + $columnIndex % 26; // 26個字母
        if($second===64){ // 如果餘0，說明是26的倍數，末位是Z，首位暫不進位，27才進位
            $first--;
            $second='Z';
        }else{
            $second = chr($second);
        }
        if ($first > 90/*64 + 26*/) {
            throw new \Exception('超出最大列數');
        } else if ($first === 64) {
            $first = '';
        } else {
            $first = chr($first);
        }

        // dd(ord('Z'));
        // dd($first, $second);
        return $first . $second;
    }

    public function shouldDelete($v = true) {
        $this->shouldDelete = $v;
        return $this;
    }

    public function download($filePath = null) {
        if ($filePath) {
            $this->filePath = $filePath;
        }
        if ($key = request('key')) {
            $this->filePath = base64_decode($key);
        }
        response()->download($this->filePath)->deleteFileAfterSend($this->shouldDelete)->send();
        exit();
    }

    public function export() {

        $isAll = $this->scope === Exporter::SCOPE_ALL;
        if ($isAll) {
            set_time_limit(0);
            $this->setData([])->store(true);
        } else {
            $this->setData($this->buildData())->store();
        }

        // 這個是備選方案
        // if ($isAll) {
        //     $filePath = base64_encode($this->filePath);
        //     dump('匯出完成');
        //     echo "<a href='/admin/out_persons/export/download?key=$filePath'>點選下載</a>";
        //     exit();
        // }

        $this->shouldDelete()->download();
    }

    public function chunk($callback = null) {
        $times = 1;
        $chunkSize = 5000;   // 分塊處理 5000查一次 越小記憶體佔用越少
        $max = 100000;       // 限制最大匯出10萬資料 調整到合適即可
        $completed = 0;
        $debug = false; // 設為true可以看匯出佔用和時間情況
        $start = microtime(true);
        if ($debug) {
            // dd($start);
            dump('開始：' . memory_get_peak_usage() / 1000 / 1024);
        }
        do {
            /** @var Collection $result */
            $result = $callback($times, $chunkSize);
            // dd($result->toArray());
            $count = count($result);
            $completed += $count;
            // dd($times,$result,$count);
            $this->insertData($result);
            unset($result);
            if ($debug) {
                dump($completed . '：' . (number_format(microtime(true) - $start, 2)) . "-" . memory_get_peak_usage() / 1024000);
            } /*else {
                dump('已匯出：' . $completed . '條，耗時' . (number_format(microtime(true) - $start, 2)) . '秒');
            }*/
            $times++;
        } while ($count === $chunkSize && $completed < $max);
        if ($debug) {
            dump('資料插入完成，開始匯出到檔案...');
        }
        return $this;
    }
}

匯出效果：

以後有空再封裝一下，使用佇列，或者是弄成進度條的形式，代替dcat自帶的匯出按鈕。

本作品採用《CC 協議》，轉載必須註明作者和本文連結