برای کار با فایل های اکسل در لاراول از کتابخانه laravel-excel استفاده می‌کنیم. این کتابخانه در واقع یک wrapper روی پکیج phpspreadsheet است که کار با فرمت های xls،xlsx و csv را در لاراول راحت‌تر می کند. با استفاده از این پکیج می توانید به راحتی از فایل های با این فرمت ها، اطلاعات را وارد دیتابیس کنید و همینطور از اطلاعات دیتابیس خروجی اکسل بگیرید.

برای شروع بایذ لاراول را نصب کنیم، دیتابیس را بسازیم و آن را به لاراول متصل کنیم. بعد از این باید این کتابخانه

بعد از نصب لاراول باید پکیج laravel-excel را نصب کنیم.

composer require maatwebsite/excel

اگر از لاراول 9 استفاده کنید احتمالا موقع نصب این پکیج با خطای ناسازگاری با پکیج‌های دیگر مواجه می‌شوید. در این صورت با این دستور آن را نصب کنید. علت این خطا وابستگی پکیج به یک پکیج دیگر به نام psr/simple-cache است.

composer require psr/simple-cache:^1.0 maatwebsite/excel

من اینجا قرار است دو کار را انجام بدهم یکی خروجی گرفتن از اطلاعات دیتابیس و دیگری ورود اطلاعات به دیتابیس با استفاده از فایل اکسل.

پس اول از همه بیایید یک سری اطلاعات برای نمونه وارد دیتابیس کنیم.

با اجرای دستور php artisan migrate جدول users که migration آن به صورت پیش فرض وجود دارد ایجاد می شود.

یک seeder برای جدول users می سازیم

php artisan make:seeder UserSeeder

برای تولید داده های fake هم نیاز به یک factory داریم که لاراول پیشفرض userFactory را دارد.

داخل متد definition در کلاس database/factories/UserFactory.php این کدها را می نویسیم.

//  database/factories/UserFactory.php    
public function definition()
    {
       return [
            'name' => $this->faker->name(),
            'email' => $this->faker->unique()->safeEmail(),
            'email_verified_at' => now(),
            'password' => Hash::make(time() .Str::random(5)),   
            'remember_token' => Str::random(10),
        ];
    }

این کلاس factory اطلاعات fake را برای ما می‌سازد. الان باید در userSeeder این کلاس را صدا بزنیم.

در متد run کلاس UserSeeder کد زیر را می‌نویسیم:

class UserSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        \App\Models\User::factory(20)->create();

    }
}

الان اگر seeder را اجرا کنیم 20 کاربر در دیتابیس ایجاد می شود:

php artisan db:seed --class=UserSeeder

خوب تا اینجا ما یک سری کاربر وارد دیتابیس کردیم. حالا می خواهیم که از این جدول users یک خروجی اکسل داشته باشیم.

خروجی اکسل از دیتابیس

پکیج laravel-excel این کار را خیلی ساده کرده است.

این دستور را اجرا کنید:

php artisan make:export UsersExport --model=User

با این کار یک فایل به نام UsersExport.php در پوشه ای جدید به نام exports در پوشه app درست می شود.

<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}

داخل متد collection مشخص شده که می خواهیم خروجی همه کاربران را داشته باشیم.

حالا کافی است در کنترلر به این شکل از Export استفاده کنید:

بدیهی است که باید route را برای این کنترلر ایجاد کرده باشید تا بتوانید آن را تست کنید:

Route::get('/export-users',[UserController::class,'ExportUsers']);


use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    public function ExportUsers(){

        return Excel::download(new UsersExport,'users-data.xlsx');
    }


}

اگر به آدرس مشخص شده بروید فایل برای شما دانلود می شود.

ذخیره فایل در سرور به جای دانلود شدن

اگر به جای دانلود بخواهید خروجی را در سرور ذخیره کنید باید از این دستور در کنترلر استفاده کنید.

 public function storeFile(){
        Excel::store(new UsersExport,'users.xlsx');
 }

در پارامتر اول از متد store باید یک شی از کلاس export که ساخته بودید بسازید. و در پارامتر دوم اسم فایل را بنویسید. فایل به صورت پیشفرض در مسیر storage/app ذخیره می شود.

خروجی گرفتن از ستون های دلخواه در جدول

برای اینکه فقط از ستون هایی که می خواهید خروجی اکسل بگیرید کافی است این را در متد collection وارد کوئری کنید. مثلا اگر فقط به اسم و ایمیل کاربران نیاز داشته باشیم به این شکل آن را مشخص می کنیم:

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    
    public function collection()
    {
        return User::all(['name','email']);
    }
}

ورود اطلاعات به دیتابیس از فایل اکسل

برای ورود اطلاعات باید ابتدا دستور زیر را اجرا کنیم تا یک کلاس برای import اطلاعات ساخته شود.

php artisan make:import ProductsImport --model=Product

من اینجا قصد دارم در یک جدول به نام products اطلاعات محصولات را از طریق فایل اکسل وارد کنم.

یک فایل اکسل برای نمونه آماده کردم که شامل 100 عدد محصول fake با ویژگی های مشخص است. اگر می‌خواهید همراه با من این کار را آزمایش کنید از اینجا فایل را دانلود کنید.

ساختار فایل بدین صورت است که در هر ردیف یک محصول قرار دارد و در هر ستون یک ویژگی محصول. این اطلاعات به همین شکل باید در دیتابیس قرار بگیرد.

مهم نیست اگر فایل شما دارای ستون های کمتر یا بیشتری باشد. می‌توانید تعیین کنید موقع ورود به دیتابیس کدام ستون ها قرار بگیرند و کدام قرار نگیرند.

در مسیر app/imports یک فایل به نام productsImport ساخته شده است در متد model باید این کار را انجام دهیم.

یکی از پارامترهای متد model متغیر $row است که از جنس آرایه است. این بیانگر هر ردیف از فایل اکسل است. هر ستون از فایل اکسل در این آرایه قرار دارد. برای مثال اگر بنویسیم $row[0] این یعنی ستون اول. به همین ترتیب ستون های بعدی را پیدا می کنیم.

در داخل کلاس ProductsImport ابتدا هر کدام از ستون ها را در یک متغیر ذخیره می‌کنیم که موقع دسترسی به آن‌ها راحت‌تر باشیم.

و بعد به شکل زیر کدهای ورود اطلاعات به دیتابیس را در کلاس ProductsImport را می‌نویسیم.

 public function model(array $row)
    {

        $title = $row[0];
        $price = $row[1];
        $description = $row[2];
        $width = $row[3];
        $height = $row[4];
        $weight = $row[5];
        $color = $row[6];

        return new Product([
            'title' => $title,
            'price' => $price,
            'description' => $description,
            'width' => $width,
            'height' => $height,
            'weight' => $weight,
            'color' => $color,
        ]);
    }

بعد از این باید یک روت و کنترلر برای این کار بسازیم.

Route::get('/import-products',[ProductController::class,'import']);

حالا در کنترلر با استفاده از این دستور می توانیم عمل ورود اطلاعات را انجام بدهیم.

use App\Imports\ProductsImport;
 
use Maatwebsite\Excel\Facades\Excel;

class ProductController extends Controller
{
    public function import(){

        
        Excel::import(new ProductsExport,storage_path('app') . 'products.xlsx');

    }
}

من فایل را در مسیر storage/app گذاشتم اما در یک سناریوی واقعی می توانیم این فایل را از کاربر دریافت کنیم آپلود کنیم و بعد عملیات ایمپورت را انجام بدهیم.

الان با رفتن به مسیر /import-products فایل products.xlsx باید در دیتابیس وارد شود.

در حالت عادی این کتابخانه شروع به خواندن فایل اکسل از سطر اول می کند و لی چون اینجا ردیف اول عنوان هستند باید خط اول را نادیده بگیریم. برای این در کلاس ProductsImport اول اینترفیس WithStartRow را implement می‌کنیم. بعد یک متد به نام startRow می سازیم و در آن متد عدد ردیفی که میخواهیم از آن شروع کنیم را برمی‌گردانیم.

namespace App\Imports;

use App\Models\Product;
use Maatwebsite\Excel\Concerns\ToModel;

use Maatwebsite\Excel\Concerns\WithStartRow;

class ProductsImport implements ToModel,WithStartRow
{
   
    public function model(array $row)
    {

         public function startRow(): int{

        return 2;
    }
    
}

بیایید الان این کار را تست کنیم.

به مسیر import-products/ بروید.

بعد از این کار باید اطلاعاتی که در فایل اکسل داشتید در جدول products در دیتابیس ذخیره شده باشد.

ایمپورت کردن فایل های اکسل بزرگ

اگر فایل اکسل شما تعدا زیادی رکورد داشته باشد ایمپورت کردن یکباره آن در دیتابیس مقدار زیادی از ram سرور را اشغال می‌کند. برای رفع این مشکل پکیج laravel-excel یک متد دارد به نام chunckSize. با استفاداده از این متد به این کتابخانه می گوییم که به جای این که یکباره همه اطلاعات را پردازش کند آنها را به بخش های مثلا 100 تایی یا 1000 تایی تقسیم کند و یکی یکی آن ها را بخواند این شکلی هم منابع سرور کمتر مصرف می شوند و هم اینکه کار ایمپورت سریعتر انجام خواهد شد.

پس در کلاس ProductsImport می توانیم این متد را هم اضافه کنیم. اما قبل از آن حتما باید اینترفیس WithChunkReading را implement کنید.

use App\Models\Product;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
 
class ProductsImport implements ToModel, WithChunkReading
{
    public function model(array $row)
    {
        //insert in database...
    }
    
    public function chunkSize(): int
    {
        return 50;
    }
}

کار با این کتابخانه جزییات زیاد دیگری دارد مثلا اعتبار سنجی هر رکورد در فایل اکسل یا نوشتن تست برای کلاس Import یا Export ای که نوشتید. برای همه این‌ها در مستندات کتابخانه laravel-excel مثال وجود دارد. بد نیست خودتان یک نگاهی به آنها بیاندازید.