example use OpenSpout write xlsx php laravel "openspout/openspout": "v4.13.1"

export-xlsx-command.php

<?php

namespace App\Console\Commands;

use App\Repositories\PriceImportExportRepository;
use Illuminate\Console\Command;
use App\Models\PriceImportExport;
use Illuminate\Support\Facades\DB;

use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Style\Style;


class ExportNag extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:exportnag {id}';

    public $folderExport = 'export_nag/';
    Public $nameFileExport = 'HB_Avail_Guide_';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Export Nag';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->nameFileExport = $this->nameFileExport.date('m-Y').'.xlsx';
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $id = $this->argument('id');
        $repo = new PriceImportExportRepository();
        $exportDetail = $repo->getExportById($id);
        if (empty($exportDetail)) {
            return 1;
        }

        $fileExport = $this->pushToFile($this->nameFileExport);

        //updateExport
        $update = [
            'status' => PriceImportExport::STATUS_COMPLETE,
            'fileName' => pathinfo($fileExport, PATHINFO_FILENAME) . '.xlsx',
            'filePath' => $fileExport,
            'completed_at' => date('Y-m-d H:i:s')
        ];
        $repo->updateExport($id,$update);
    }

    public function pushToFile($fileName) {

        $path = 'app/'.FOLDER_EXPORT.'/'.$this->folderExport.$fileName;
        $filePath = storage_path($path);

        // Tạo thư mục nếu chưa có
        if (!file_exists(dirname($filePath))) {
            mkdir(dirname($filePath), 0755, true);
        }

        // Tạo writer
        $writer = new Writer();
        $writer->openToFile($filePath);

        $grayStyle = (new Style())
            ->setBackgroundColor('c0c0c0') // xám nhạt
            ->setFontBold()
            ->setFontColor('000000');       // đen

        $yellowStyle = (new Style())
            ->setBackgroundColor('ffff00') // vàng
            ->setFontBold()
            ->setFontColor('000000');       // đen

        $orangeStyle = (new Style())
            ->setBackgroundColor('ffc000') // cam
            ->setFontBold()
            ->setFontColor('000000');       // đen

        $blackStyle = (new Style())
            ->setBackgroundColor('000000') // den
            ->setFontBold()
            ->setFontColor('ffffff');       // trang

        $listHeader = [
            ['Mfg Code','mfg_code',''],
            ['Class Code','class_code',''],
            ['Part Number','sku',''],
            ['Jobber Price','jobber_price',$yellowStyle],
            ['PartType','partType',''],
            ['PCQ','pcq',''],
            ['From Year','year_from',''],
            ['To Year','year_to',''],
            ['Make','make',''],
            ['Model','model',''],
            ['Vehicle Type','vehicle_type',''],
            ['Cost','cost',$orangeStyle],
            ['Profit','profit',$orangeStyle],
            ['Selling Price','selling_price',$orangeStyle],
            ['Adjust Price','',$blackStyle],
            ['Adj From Date','',$blackStyle],
            ['Adj To Date','',$blackStyle],
        ];
        
        //add style for title
        $cells = [];
        foreach ($listHeader as $value) {
            $style = $grayStyle;
            if(!empty($value[2])){
                $style = $value[2];
            }
            $cells[] = $this->safeCell($value[0], $style);
        }

        $writer->addRow(new Row($cells));

        DB::table('item_nag')->select([
            'mfg_code',
            'class_code',
            'sku',
            'jobber_price',
            'partType',
            'pcq',
            'year_from',
            'year_to',
            'make',
            'model',
            'vehicle_type',
            'cost',
            'profit',
            'selling_price'
        ])->orderBy('id')->chunk(1000, function ($rows) use ($writer, $listHeader) {
            foreach ($rows as $rowData) {
                $cells = [];
                foreach ($listHeader as $head) {
                    $val = "";
                    if (!empty($head[1])) {
                        $key = $head[1];
                        $val = $rowData->$key ?? '';
                    }
                    $cells[] = Cell::fromValue($val);
                }
                $writer->addRow(new Row($cells));
            }
        });

        $writer->close();
        return 'storage/'.$path;
    }

    private function safeCell($value, $style = null) {
        $cell = Cell::fromValue(mb_convert_encoding($value ?? '', 'UTF-8'));
        if ($style) {
            $cell->setStyle($style);
        }
        return $cell;
    }
}

Composer.json add

"openspout/openspout": "v4.13.1",

<?php

namespace App\Console\Commands\ExportASIN;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Style\Style;


class ExportAsin extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'export:asin';
    public function __construct()
    {
        parent::__construct();
    }
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Export YMM with Amazon Asin';
    public $folderExport = 'export_ymm_amazon/';
    Public $nameFileExport = 'R1_YMM_ASIN_';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $fileName = $this->nameFileExport.date('Y_m_d_His').'.xlsx';
        $filePath = storage_path('export')."/".$this->folderExport.$fileName;

        // Tạo thư mục nếu chưa có
        if (!file_exists(dirname($filePath))) {
            mkdir(dirname($filePath), 0755, true);
        }

        $writer = new Writer();
        $writer->openToFile($filePath);

        $blackStyle = (new Style())
            ->setBackgroundColor('000000') // den
            ->setFontBold()
            ->setFontColor('ffffff');       // trang

        $listHeader = [
            ['Year','year',$blackStyle],
            ['Make','make',$blackStyle],
            ['Model','model',$blackStyle],
            ['ASIN','asin',$blackStyle],
        ];
        
        //add style for title
        $cells = [];
        foreach ($listHeader as $value) {
            if(!empty($value[2])){
                $style = $value[2];
            }
            $cells[] = $this->safeCell($value[0], $style);
        }

        $writer->addRow(new Row($cells));

        DB::table('item_ymmo as iym')
            ->join('items as it', 'it.id', '=', 'iym.item_id')
             ->join('items_site as isite', function ($join) {
                $join->on('isite.item_id', '=', 'it.id')
                    ->where('isite.site_code', '=', 'R1')
                    ->where('isite.status', '!=', '0');
            })
            ->join('makes as mk', 'mk.id', '=', 'iym.make_id')
            ->join('models as md', 'md.id', '=', 'iym.model_id')
            // ->where('iym.status', 1)
            ->where('iym.status', '!=', '0')
            ->whereNotNull('it.amazon')
            ->where('it.amazon', '!=', '')
            ->select(
                'iym.year',
                'mk.name as make',
                'md.name as model',
                DB::raw('GROUP_CONCAT(DISTINCT it.amazon SEPARATOR "\n") as asin')
            )
            ->groupBy('iym.year', 'mk.name', 'md.name')
            ->orderBy('iym.year')
            ->chunk(10000, function ($rows) use ($writer, $listHeader) {
                foreach ($rows as $rowData) {
                    $cells = [];
                    foreach ($listHeader as $head) {
                        $val = "";
                        if (!empty($head[1])) {
                            $key = $head[1];
                            $val = $rowData->$key ?? '';
                        }
                        $cells[] = Cell::fromValue($val);
                    }
                    $writer->addRow(new Row($cells));
                }
            });

        $writer->close();
        echo $filePath. PHP_EOL;
        return;

    }

    private function safeCell($value, $style = null) {
        $cell = Cell::fromValue(mb_convert_encoding($value ?? '', 'UTF-8'));
        if ($style) {
            $cell->setStyle($style);
        }
        return $cell;
    }
}