Search code examples
phpexcellaravelconsolelaravel-excel

Import laravel excel depending on the sheet name

I have an Excel file with several sheets, each sheet has a specific name and that name is in the "name" column of a product table. I want to make that when I import the Excel file, the name of the sheet in the DB is also imported.

I am using Laravel Excel and PHPOficce packages. And the import is in the laravel console commands.

class ProductImport implements ToCollection, WithHeadingRow, WithProgressBar 
{

    use Importable;

    public function collection(Collection $rows) 
    {

        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load(storage_path('app/fruit/listFruit.xlsx'));
        $products = $spreadsheet->getSheetNames();
        //name of sheets
        //$products=['banana', 'strawberry'];
        
        foreach ($rows as $row) {

            foreach ($products as $product) {

                ProductImport::updateOrCreate(
                    [
                        'price' => $row['price'],
                        'size' => $row['size'],
                        'product' => $product,//nameSheet
                    ],
                );
            }
        }
    }
}

<?php

namespace App\Console\Commands;

use App\Imports\ProductImport;
use PhpOffice;
use Illuminate\Console\Command;

class UploadProduct extends Command
{

    protected $signature = 'fruit:productList';

    protected $description = 'Command to inject fruit data';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {

        $this->output->title('Starting import');
        (new ProductImport)->withOutput($this->output)->import(storage_path('app/fruit/listFruit.xlsx'));
        $this->output->success('Import successful');
        
    }


}

What should come out

What comes out

if I reverse the foreach

Excel

I want that the variable $product every time I go through a sheet, the name of the sheet is imported in the database and that I don't get only the name of the first sheet.

Solution

  • I think it's gonna be more illustrative that way

    As I said, you just have to switch your foreach loops : 1st the products then the rows (those are your variables name, maybe that's what you didnt understand in my explanation)

    what you have

    foreach ($rows as $row) {
    
    foreach ($products as $product)
        .....
     }
}

    What it should be

    foreach ($products as $product) {

    foreach ($rows as $row) {
          ....
    }
}

    EDIT :

    here the beginning of the code you need (i've created the xls file with same data as you have)

    $reader           = IOFactory::createReader($inputFileType);
$spreadsheet      = $reader->load($inputFileName);
$loadedSheetNames = $spreadsheet->getSheetNames();

print '<pre>';
foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    $sheet     = $spreadsheet->getSheet($sheetIndex);
    $sheetData = $sheet->toArray(null, true, true, true);
    var_dump($loadedSheetName, $sheetData);
}

die;

    Now, we're going through each sheet and we get the content of it

    $sheetData is an array with the data by sheet (product) so you'll still have to add something to go throught it . I let you do this part

    I know you're starting but I still have to say that the solution was in the link I gave you 😉