maatwebsite laravel excel导出列与下拉列表

des4xlb0  于 2023-08-08  发布在  其他
关注(0)|答案(2)|浏览(141)

我一直在使用Laravel Excel以csv格式导出数据，到目前为止效果很好。现在我需要导出为xlsx，以便在某些列中包含下拉列表。我看过this question，但它看起来像是老版本的Laravel Excel。我还看了文档中解释extending the package的页面，但我似乎不知道如何在导出数据时向列添加下拉列表。
这是我的导出类的简化版本：

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;
    }
}

字符串
我想做的是查询一个包含column 1选项的查找表，并将这些值用于列中的下拉列表，以便当用户想要更改Excel工作表时，它们仅限于下拉值。
在文档中提到使用\Maatwebsite\Excel\Sheet或\Maatwebsite\Excel\Writer，但我甚至不确定在哪里使用它们，或者使用哪一个。
在我的搜索过程中，我似乎无法拼凑出一个解决方案，因此任何帮助都将不胜感激。
我正在使用：
maatwebsite/excel 3.1，php 7.2，laravel 5.8

laravel

2条答案

按热度按时间
wrrgggsh

wrrgggsh1#

工作表事件的实现可能会相当混乱，很难找到例子，所以当我看到这样的帖子时，我试着伸出援手。首先，我认为您应该真正了解PHPSpreadsheet documentation的这些附加特性。在这里你可以找到你需要的重要信息。然后您可以翻译您找到的内容以在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
{
    protected $results;

    public function collection()
    {
        // store the results for later use
        $this->results = $this->getActionItems();

        return $this->results;
    }

    // ...

    public function registerEvents(): array
    {
        return [
            // handle by a closure.
            AfterSheet::class => function(AfterSheet $event) {

                // get layout counts (add 1 to rows for heading row)
                $row_count = $this->results->count() + 1;
                $column_count = count($this->results[0]->toArray());

                // set dropdown column
                $drop_column = 'A';

                // set dropdown options
                $options = [
                    'option 1',
                    'option 2',
                    'option 3',
                ];

                // set dropdown list for first data row
                $validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();
                $validation->setType(DataValidation::TYPE_LIST );
                $validation->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation->setAllowBlank(false);
                $validation->setShowInputMessage(true);
                $validation->setShowErrorMessage(true);
                $validation->setShowDropDown(true);
                $validation->setErrorTitle('Input error');
                $validation->setError('Value is not in list.');
                $validation->setPromptTitle('Pick from list');
                $validation->setPrompt('Please pick a value from the drop-down list.');
                $validation->setFormula1(sprintf('"%s"',implode(',',$options)));

                // clone validation to remaining rows
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);
                }

                // set columns to autosize
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

字符串

赞(0）分享  回复(0）举报  2023-08-08
vhmi4jdf

vhmi4jdf2#

下面是从datalist中导出多个列的下拉列表的例子，从表和标题中提取，具有粗体和背景色等样式。

<?php

namespace App\Exports;

use App\Category;
use App\Product;
use App\SKU;
use App\Tra_Tax;
use Illuminate\Support\Facades\Auth;
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;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ProductExport implements FromCollection, WithHeadings, WithEvents, WithStrictNullComparison, WithStyles
{
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $results;
    public function collection()
    {
        $user = Auth::user();
        if($user->type=="staff"){
            $this->id = $user->created_by;
        }else{
            $this->id = $user->id;
        }
        $this->results = $this->getProducts();
        return $this->results;
    }
    public function getProducts(){
        $product = Product::select('product_name','description','category','price','sale_price','quantity','sku',
        'sku_quantity','bar_code','tax','sold_by')
        ->where('created_by',$this->id)->where('status',1)->take(1)->get();
        $product[0]->category = Category::where('id',$product[0]->category)->pluck('name')->first();
        return $product;
    }
    public function headings(): array
    {
        return [
          'Product Name','Description','Category','Price','Sale Price','Quantity','SKU','SKU Quantity',
          'Bar Code','Tax','Sold By'
        ];
    }
    public function styles(Worksheet $sheet)
    {
        return [
             1    => ['font' => ['bold' => true]],
        ];
    }
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
               
                //sheet object & no. rows
                $row_count = 50;
                $column_count = count($this->results[0]->toArray());
                $sheet = $event->sheet;

                //header color
                $sheet->getDelegate()->getStyle('A1:L1')
                ->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('ADD8E6');

                //category dropdown
                $category = Category::where('created_by',$this->id)->pluck('name')->toArray();
                $validation2 = $sheet->getCell('C2')->getDataValidation();
                $validation2->setType(DataValidation::TYPE_LIST );
                $validation2->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation2->setAllowBlank(false);
                $validation2->setShowInputMessage(true);
                $validation2->setShowErrorMessage(true);
                $validation2->setShowDropDown(true);
                $validation2->setErrorTitle('Input error');
                $validation2->setError('Value is not in list.');
                $validation2->setPromptTitle('Pick from category list');
                $validation2->setPrompt('Please pick a value from the drop-down list.');
                $validation2->setFormula1('"' . implode(',',$category). '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("C{$i}")->setDataValidation(clone $validation2);
                }

                //sku dropdown
                $sku = SKU::where('created_by',$this->id)->pluck('unit')->toArray();
                $validation = $sheet->getCell('G2')->getDataValidation();
                $validation->setType(DataValidation::TYPE_LIST);
                $validation->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation->setAllowBlank(false);
                $validation->setShowInputMessage(true);
                $validation->setShowErrorMessage(true);
                $validation->setShowDropDown(true);
                $validation->setErrorTitle('Input error');
                $validation->setError('Value is not in list.');
                $validation->setPromptTitle('Pick from sku list');
                $validation->setPrompt('Please pick a value from the drop-down list.');
                $validation->setFormula1('"' . implode(',',$sku). '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("G{$i}")->setDataValidation(clone $validation);
                }

                //tax dropdown
                $tax = 'A,B,C,D,E';
                $validation1 = $sheet->getCell('J2')->getDataValidation();
                $validation1->setType(DataValidation::TYPE_LIST );
                $validation1->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation1->setAllowBlank(false);
                $validation1->setShowInputMessage(true);
                $validation1->setShowErrorMessage(true);
                $validation1->setShowDropDown(true);
                $validation1->setErrorTitle('Input error');
                $validation1->setError('Value is not in list.');
                $validation1->setPromptTitle('Pick from tax list');
                $validation1->setPrompt('Please pick a value from the drop-down list.');
                $validation1->setFormula1('"' . $tax. '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("J{$i}")->setDataValidation(clone $validation1);
                }

                //sold by dropdown
                $sold_by ="each,weight";
                $validation2 = $sheet->getCell('K2')->getDataValidation();
                $validation2->setType(DataValidation::TYPE_LIST );
                $validation2->setErrorStyle(DataValidation::STYLE_INFORMATION );
                $validation2->setAllowBlank(false);
                $validation2->setShowInputMessage(true);
                $validation2->setShowErrorMessage(true);
                $validation2->setShowDropDown(true);
                $validation2->setErrorTitle('Input error');
                $validation2->setError('Value is not in list.');
                $validation2->setPromptTitle('Pick from sold by list');
                $validation2->setPrompt('Please pick a value from the drop-down list.');
                $validation2->setFormula1('"' . $sold_by. '"');
                for ($i = 3; $i <= $row_count; $i++) {
                    $event->sheet->getCell("K{$i}")->setDataValidation(clone $validation2);
                }

                //set size of column as per the length
                for ($i = 1; $i <= $column_count; $i++) {
                    $column = Coordinate::stringFromColumnIndex($i);
                    $event->sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

字符串

赞(0）分享  回复(0）举报  2023-08-08
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com