Import laravel excel depending on the sheet name

Question

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.

try to inverse your foreach loop : 1st the products then the rows — skytorner
– skytorner, Commented May 19, 2022 at 14:11
@skytorner I keep importing the same repeated data but now separately. First one then the other one with the other name repeated but with the same content. I have put an image in the question. — elquenosabe
– elquenosabe, Commented May 19, 2022 at 14:32
yeah I know but as you want it, you have to loop over the products 1st then the rows. Right now for each line of your excel you gonna iterate for each product 2 times and you only want 1 time per product — skytorner
– skytorner, Commented May 19, 2022 at 14:37
@skytorner But the products are the name of the sheet, I don't know how to do it for that task. There is no row in excel named product. — elquenosabe
– elquenosabe, Commented May 19, 2022 at 14:45

skytorner · Accepted Answer · 2022-05-20 13:28:03Z

0

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 😉

edited May 20, 2022 at 13:28

answered May 20, 2022 at 7:58

skytorner

4153 gold badges8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

elquenosabe Over a year ago

If I do it this way, when it is imported to the database it comes out as in the third image. i.sstatic.net/HW1rG.png

skytorner Over a year ago

can you dump the $rows and the $products ? it's getting close but i need to see the datas format... maybe try to loop over $rows[0] to try...

elquenosabe Over a year ago

I believe that it has more to do, that each sheet makes the loop of the name of the sheets and put it in the column of the database, but reading the documentation I can not access each sheet and add that sheet name.

skytorner Over a year ago

the problem is that $rows contains size/price of both type of products. So you have to a find a way to get the $rows associated with the products. That's why im asking to see the dump of each var

elquenosabe Over a year ago

Illuminate\Support\Collection^ {#1344 #items: array:2 [ "size" => "small" "price" => "$2" ] } this is $rows[0]

|

Collectives™ on Stack Overflow

Import laravel excel depending on the sheet name

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related