stemcell/app/code/Stemcell/Customer/Model/Lab/Export.php

<?php

namespace Stemcell\Customer\Model\Lab;

use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Ui\Component\MassAction\Filter;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Stemcell\Customer\Model\ResourceModel\Lab\CollectionFactory as LabCollectionFactory;
use Magento\Framework\Filesystem;
use Stemcell\Customer\Model\ResourceModel\Salesperson\CollectionFactory as SalespersonCollectionFactory;
use Magento\Customer\Model\ResourceModel\Customer\CollectionFactory as CustomerCollectionFactory;
use Stemcell\Customer\Model\Salesperson;
use Magento\Customer\Model\ResourceModel\Group\CollectionFactory as GroupCollectionFactory;

class Export
{
    protected Filesystem\Directory\WriteInterface $directory;

    protected array $salesperson = [];

    protected array $dealers = [];

    const VENDOR_CUSTOMER_GROUP_CODE = [
        'STEMCELL CN 经销商组',
        '库存查询组',
    ];

    public function __construct(
        protected Filter $filter,
        protected LabCollectionFactory $labCollectionFactory,
        protected Filesystem $filesystem,
        protected Filesystem\DirectoryList $directoryList,
        protected SalespersonCollectionFactory $salespersonCollectionFactory,
        protected CustomerCollectionFactory $customerCollectionFactory,
        protected GroupCollectionFactory $groupCollectionFactory,
    ) {
        $this->directory = $filesystem->getDirectoryWrite(DirectoryList::VAR_DIR);
    }

    protected function getSalesperson()
    {
        if (!empty($this->salesperson)) {
            return $this->salesperson;
        }

        $collection = $this->salespersonCollectionFactory->create()->addFieldToSelect('*');
        foreach ($collection as $salesperson) {
            /* @var $salesperson Salesperson */
            $this->salesperson[$salesperson->getId()] = $salesperson->getName();
        }
        return $this->salesperson;
    }

    protected function getDealers()
    {
        if (!empty($this->dealers)) {
            return $this->dealers;
        }
        $groups = $this->groupCollectionFactory->create()
            ->addFieldToFilter('customer_group_code', ['in' => self::VENDOR_CUSTOMER_GROUP_CODE]);

        $collection = $this->customerCollectionFactory->create()->addAttributeToSelect('firstname')
                ->addAttributeToFilter('group_id', ['in' => $groups->getColumnValues('customer_group_id')]);
        foreach ($collection as $customer) {
            $this->dealers[$customer->getEntityId()] = $customer->getFirstname();
        }

        return $this->dealers;
    }

    public function getExcelFile()
    {
        $this->directory->create('export');
        $file = 'export/Labs-'.date("YmdH:i:s") . '.xlsx';
        $filePath = $this->directoryList->getPath('var').'/'.$file;

        $this->getSalesperson();
        $this->getDealers();

        $spreadSheet = new Spreadsheet();
        $writer = IOFactory::createWriter($spreadSheet, 'Xlsx');
        $worksheet = $spreadSheet->getActiveSheet();
        $styles = [
            'alignment' => [
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $columnIndex = 'A';
        $headers = $this->getHeaders();
        foreach ($headers as $row) {
            $cell = $worksheet->getCell($columnIndex.'1');
            $cell->getAppliedStyle()->applyFromArray($styles);
            $cell->setValue($row);
            $columnIndex++;
        }

        $rowIndex = 2;
        $result = $this->getFilterResult();
        foreach ($result as $row) {
            /* @var $row \Stemcell\Customer\Model\Lab */
            $columnIndex = 'A';
            $worksheet->getColumnDimension($columnIndex)->setWidth(15);
            $worksheet->getRowDimension($rowIndex)->setRowHeight(20);
            foreach (array_keys($headers) as $field) {
                $value = '';
                if (in_array($field, ['customer_id', 'salesperson_id'])) {
                    if ($row->getData($field)) {
                        if ($field == 'customer_id') {
                            $value = isset($this->dealers[$row->getData($field)])? $this->dealers[$row->getData($field)] : '';
                        } else {
                            $value = isset($this->salesperson[$row->getData($field)])? $this->salesperson[$row->getData($field)] : '';
                        }
                    }
                } elseif ($field == 'is_active') {
                    $value = $row->getData($field) == 1? '是':'否';
                } else {
                    $value = $row->getData($field);
                }
                $cell = $worksheet->getCell($columnIndex.$rowIndex);

                $cell->setValue($value);
                $columnIndex++;
            }
            $rowIndex++;
        }

        $writer->save($filePath);
        return [
            'type' => 'filename',
            'value' => $file,
            'rm' => true  // can delete file after use
        ];
    }

    /**
     * @return \Stemcell\Customer\Model\Lab[]
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    protected function getFilterResult()
    {
        /* @var $component \Magento\Ui\Component\Listing */
        $component = $this->filter->getComponent();

        // md5() here is not for cryptographic use.
        // phpcs:ignore Magento2.Security.InsecureFunction
        $name = md5(microtime());
        $file = 'export/'. $component->getName() . '-'.date("YmdH:i:s") . '.xlsx';

        $this->filter->prepareComponent($component);
        $this->filter->applySelectionOnTargetProvider();

        /* @var $dataProvider \Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider */
        $dataProvider = $component->getContext()->getDataProvider();

//        $this->directory->create('export');
//        $filePath = $this->directoryList->getPath('var').'/'.$file;
        $dataProvider->setLimit(0, 100000);

        $items = $dataProvider->getSearchResult()->getItems();

        return $items;
    }

    protected function getHeaders()
    {
        return [
            'lab_code' => 'Lab ID',
            'booking_account' => 'Booking Account',
            'pi_sap_id' => 'PI SAP ID',
            'customer_id' => 'Dealer Name',
            'salesperson_id' => 'Sales Person',
            'is_active' => 'Is Active',
        ];
    }

}