Laravel-excel: How to import multiple sheets with Laravel-Excel

[thomasbuckle-uk-zz]

Hi I'm currently trying to import an excel file into a project.

Having some trouble getting Laravel-Excel to import multiple sheets, I just dont understand the docs.

Here's the page I'm talking about- https://laravel-excel.maatwebsite.nl/3.1/imports/multiple-sheets.html

It says to import by sheet index then you use

0 => new FirstSheetImport(),

But how do I import a second, third or fourth sheet? Do I do something like this-

1 => new FirstSheetImport(),

Thanks for your help!

[patrickbrouwers]

Yes, just make an array with sheet index as key.

0 => new FirstSheetImport(),
1 => new SecondSheetImport(),
//etc

[patrickbrouwers]

Updated the docs with more explanation: https://laravel-excel.maatwebsite.nl/3.1/imports/multiple-sheets.html

[ikmespinoza]

Yes, just make an array with sheet index as key.

0 => new FirstSheetImport(),
1 => new SecondSheetImport(),
//etc

How about having indefinite numbers of sheets? Because I was planning like to have more of a flexible type of importing, e.g. for today, I can upload 4 sheets of data, then the next day I have 2 sheets, and so on. Is there a way to do this? By not always going to the ImportCode just to specify the number of sheets to upload? @patrickbrouwers

[GlennM]

Do you need separate imports for each sheet?

Did you already have a look at this? https://docs.laravel-excel.com/3.1/imports/multiple-sheets.html#conditional-sheet-loading
Perhaps you can dynamically determine the arguments to pass to the onlySheets method?

[ikmespinoza]

Do you need separate imports for each sheet?

Did you already have a look at this? https://docs.laravel-excel.com/3.1/imports/multiple-sheets.html#conditional-sheet-loading
Perhaps you can dynamically determine the arguments to pass to the onlySheets method?

Just a single import for all the sheets I have for a single file.

Yeah, I already read that part of the document. I don't need onlySheets method, coz I need to import all the sheets I have.

The problem is, I needed to process all the sheets in a specific file, regardless of how many sheets does it have.

[GlennM]

If it's the same import, you can just do the basic way of importing which should pass all the sheets in the file.

From the docs:
When a file has multiple sheets, each sheet will go through the import object.

[ikmespinoza]

If it's the same import, you can just do the basic way of importing which should pass all the sheets in the file.

From the docs:
When a file has multiple sheets, each sheet will go through the import object.

Hmm here's my code for reference btw. Did I miss something? Because this only process the first sheet.

class CategoriesProductsImport implements WithMultipleSheets
{   
  public function sheets(): array
  {
    return [
      new CategoriesProductsSheetImport()
    ];
  }
}

class CategoriesProductsSheetImport implements ToCollection
{
  /**
  * @param array $row
  *
  * @return \Illuminate\Database\Eloquent\Model|null
  */
  public function collection(Collection $rows)
  {
    $firstRowDone = false;
    $category = null;

    foreach ($rows as $row)
    {
      if(!$firstRowDone) {
        $category = Category::create([
          'name' => $row[0]
        ]);

        $firstRowDone = true;
      } else {
        $product = Product::create([
          'name' => $row[0],
          'category_id' => $category->id
        ]);
      }
    }
  }
}

[GlennM]

You don't need WithMultipleSheets at all if you're using the same Import class for all of your sheets. The basic way of importing will go through all the sheets and import the data accordingly.

(If you want to handle each sheet separately, you'll need to implement the WithMultipleSheets concern.)

[ikmespinoza]

You don't need WithMultipleSheets at all if you're using the same Import class for all of your sheets. The basic way of importing will go through all the sheets and import the data accordingly.

(If you want to handle each sheet separately, you'll need to implement the WithMultipleSheets concern.)

Oh my bad. Sorry for wasting your time man. And thank you for answering my queries, it's working now.

[GlennM]

Glad to hear it's working. Thank you for using Laravel Excel!

[antonioreyna]

how can i return a collection for multiple sheets?

on each sheet i need to return a collection with specific format reading each cell of the row, i used ot do it in the 2.0 version but now i dont understand how to do it

[GlennM]

how can i return a collection for multiple sheets?

on each sheet i need to return a collection with specific format reading each cell of the row, i used ot do it in the 2.0 version but now i dont understand how to do it

https://docs.laravel-excel.com/3.1/imports/collection.html
https://docs.laravel-excel.com/3.1/imports/multiple-sheets.html <= Use this to be able to specify a separate import class per sheet.

If you need more help, please open a new issue.

[ipontt]

I'm wondering if I can process two/three sheets at the same time and not separately (the WithMultipleSheet approach don't seem to agree with me when it comes to imports)

My file has the following format:

Sheet 1: Has the bulk of the data to import. It's in human readable format and has data validation via lists saved in Sheets 2 and 3.

For example

# Sheet1 (Import)
#|Name        |City     |
-+------------+---------+
1|Jim Jackson |Orehon   |
2|Paul Piers  |Ohio     |
3|Ryan Roberts|Manhattan|

# Sheet2 (Cities)
City id|Name     |
-------+---------+
45     |Orehon   |
32     |Ohio     |
19     |Manhattan|
````



md5-c3d04a4345f495ed7478cf98bb1dadc8

What should be inserted

|Person id|City id|

-+---------+-------+
1|55 |45 |
2|43 |32 |
3|22 |19 |
```
I want to import using the associated ids instead of having to look them up. through string comparison every time. Am I missing something? Is this the wrong approach?