maatwebsite laravel excel 导出列与下拉列表

Question

我一直在使用Laravel Excel以 csv 格式导出数据，到目前为止效果很好。现在我需要以 xlsx 格式导出，以便可以在某些列中包含下拉列表。我看过这个问题，但看起来这是针对旧版本的 Laravel Excel。我还查看了文档中解释扩展包的页面，但我似乎无法弄清楚如何在导出数据时将下拉列表添加到列中。

这是我的导出类的简化版本：

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\WithHeadings;

use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class ActionItemExport implements FromCollection, WithHeadings, WithStrictNullComparison

{

    public function collection()

    {

        return $this->getActionItems();

    }

    public function headings(): array

    {

        $columns = [

            'Column 1',

            'Column 2',

            'Column 3',

            'Column 4',

            'Column 5',

            'Column 6',

            'Column 7'

        ];

        return $columns;

    }

    private function getActionItems()

    {

        $select = 'column1, column2, column3, column4, column5, column6, column7';

        $query = \DB::table('action_items')->select(\DB::raw($select));

        $query->whereNull('action_items.deleted_at');

        $ai = $query->orderBy('column1')->get();

        return $ai;

    }

}

我想做的是查询具有 column1 选项的查找表，并将这些值用于列中的下拉列表，以便当用户想要更改 Excel 工作表时，它们仅限于下拉值.

在文档中它提到使用\Maatwebsite\Excel\Sheetor \Maatwebsite\Excel\Writer，但我什至不确定在哪里使用它们，或者使用哪个。

在我的搜索过程中，我似乎无法拼凑出一个解决方案，因此我们将不胜感激。

我在用着：

maatwebsite/excel 3.1、php 7.2、laravel 5.8

Answer 1

表单事件的实现可能相当混乱，很难找到示例，所以当我看到这样的帖子时，我会尝试伸出援手。首先，我会说您真的应该查看这些附加功能的PHPSpreadsheet 文档。在这里您可以找到所需的重要信息。然后你可以翻译你在Laravel Excel中使用的内容。PHPSpreadsheet：在单元格上设置数据验证 https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-data-validation-on-a-cell这是一个基于您现有文件的示例。我还加入了一些额外的格式来自动调整列宽——我认为这是必须的。namespace App\Exports;use Maatwebsite\Excel\Concerns\FromCollection;use Maatwebsite\Excel\Concerns\WithHeadings;use Maatwebsite\Excel\Concerns\WithStrictNullComparison;use Maatwebsite\Excel\Concerns\WithEvents;use Maatwebsite\Excel\Events\AfterSheet;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;use PhpOffice\PhpSpreadsheet\Cell\DataValidation;class ActionItemExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison{&nbsp; &nbsp; protected $results;&nbsp; &nbsp; public function collection()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; // store the results for later use&nbsp; &nbsp; &nbsp; &nbsp; $this->results = $this->getActionItems();&nbsp; &nbsp; &nbsp; &nbsp; return $this->results;&nbsp; &nbsp; }&nbsp; &nbsp; // ...&nbsp; &nbsp; public function registerEvents(): array&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; return [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // handle by a closure.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AfterSheet::class => function(AfterSheet $event) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // get layout counts (add 1 to rows for heading row)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $row_count = $this->results->count() + 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $column_count = count($this->results[0]->toArray());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // set dropdown column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $drop_column = 'A';&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // set dropdown options&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $options = [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'option 1',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'option 2',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'option 3',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // set dropdown list for first data row&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setType(DataValidation::TYPE_LIST );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setErrorStyle(DataValidation::STYLE_INFORMATION );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setAllowBlank(false);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setShowInputMessage(true);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setShowErrorMessage(true);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setShowDropDown(true);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setErrorTitle('Input error');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setError('Value is not in list.');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setPromptTitle('Pick from list');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setPrompt('Please pick a value from the drop-down list.');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $validation->setFormula1(sprintf('"%s"',implode(',',$options)));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // clone validation to remaining rows&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for ($i = 3; $i <= $row_count; $i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $event->sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // set columns to autosize&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for ($i = 1; $i <= $column_count; $i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $column = Coordinate::stringFromColumnIndex($i);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $event->sheet->getColumnDimension($column)->setAutoSize(true);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },&nbsp; &nbsp; &nbsp; &nbsp; ];&nbsp; &nbsp; }}