dcat使用xlswriter分块导出数据，自定义列宽行高，首行合并表名

DogLoML 的个人博客 / 199 / 0 / 创建于 1年前 / 更新于 1年前

之前用了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;
    }
}