استفاده از فرمول در خروجی اکسل

در این بخش از دوره ی آموزش کاربردی Laravel-Excel قصد داریم  با یک مثال ساده نحوه استفاده از فرمول در خروجی excel را آموزش بدهیم.

برای ساخت یک خروجی حرفه‌ای‌تر با این آموزش همراه باشید.

دیتابیس زیر را در نظر بگیرید:

هر دانش آموز می‌تواند چندین درس داشته باشد و هر درس می‌تواند توسط چندین دانش آموز انتخاب شده باشد پس رابطه چند به چند با هم دارند در نتیجه کلاس model دانش آموز به صورت زیر می‌شود.

class Student extends Model
{
    protected $table = 'students';

    public $timestamps = false;

    public function courses()
    {
        return $this->belongsToMany(

               Course::class, 'students_has_courses'

        )->withPivot('score');
    }
}

در این مثال قصد داریم تا نمرات یک دانش آموزش را در یک فایل excel به همراه معدل وی که با استفاده از فرمول به دست می‌آید ثبت کنیم. پس یک کلاس ِExport با استفاده از دستور زیر می‌سازیم:

$ php artisan make:export ScoreExport

در construct این کلاس اطلاعات یک دانش آموز دریافت می‌شود. پس یک route برای آن به صورت زیر مینویسم:

Route::get('scores/{student}', function (Student $student) {
    return new ScoresExport($student);
});

با استفاده از route model binding شی مدل  student را ساخته و به کلاس ScoreExport ارسال می‌کنیم.

کد نهایی کلاس ScoreExport به شکل زیر است که در ادامه به توضیح قسمت‌های مهم  می‌پردازیم:

<?php

namespace App\Exports;

use App\Student;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Contracts\Support\Responsable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class ScoresExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithEvents, Responsable
{
    use Exportable;

    /** file name */
    private $fileName;

    /** @var Collection $courses*/
    protected Collection $courses;



    /** ScoreExport construct */
    public function __construct(Student $student)
    {
        $this->fileName = $student->name . '.xlsx';
        $this->courses = $student->courses;
    }

    /** @return \Illuminate\Support\Collection */
    public function collection()
    {
        return $this->courses;
    }

    /** @return array */
    public function headings(): array
    {
        return [

           'Name',

           'Score',

           'Ratio'

        ];
    }

    /**
     * @param mixed $row
     * @return array
     */
    public function map($course): array
    {
        return [

           $course->name,

           $course->pivot->score,

           $course->ratio

        ];
    }

    public function registerEvents(): array
    {
        $styleArray = ['font' => ['bold' => true]];
        return [
            AfterSheet::class =>

                function (AfterSheet $event) use ($styleArray) {
                    $event->sheet->getStyle('A1:C1')

                          ->applyFromArray($styleArray);
                    // write 'Average' word
                    $event->sheet->setCellValue('A7', 'Average');
                    // calculate average
                    $event->sheet->setCellValue(
                        'B7',
                        '=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)'
                    );
            }
        ];
    }
}

●      نام فایل excel که در نهایت دانلود می‌شود برابر با نام دانش آموز است. پس مشخص کردن نام فایل داخل کلاس export انجام می‌شود. برای این کار از ترکیب trait با نام Exportable و اینترفیس Responsable استفاده کردیم. در این صورت باید property با نام fileName مقدار دهی شود که در constructor این اتفاق افتاده است.

●      در constructor درس های دانش آموز استخراج شده و در یک property به نام courses ذخیره می‌شود. از این property در متد collection برای خروجی excel استفاده شده است. و در نهایت در متد map فقط مقادیری که نیاز داریم مشخص می‌شوند.

●      در متد registerEvents و در قسمت AfterSheet به کمک متد setCellValue می‌توانیم مقدار یک سلول را مشخص کنیم. در این مثال فرض شده که هر دانش آموز دقیقا ۵ درس دارد. پس با این فرض اسم های دروس در سلول های A2 تا A6 ثبت می‌شوند. در سلول A7 کلمه Average را می‌نویسیم و در سلول B7 با استفاده از فرمول های موجود در excel معدل دانش آموز ( میانگین وزن دار ) را محاسبه می‌کنیم. 

نکته:‌ در صورتی که تعداد دروس ثابت نباشد می‌توان با توجه به تعداد دروس سلول مربوط به کلمه Average و معدل دانش آموز را داینامیک کرد. برای مثال بجای 'A7' بنویسیم :

'A' . ( $courseCount + 2 )

در نهایت excel تولید شده به شکل زیر خواهد بود: