Laravel excel get total number of rows before import

Question

Straight forward question. How does one get the total number of rows in a spreadsheet with laravel-excel?

I now have a working counter of how many rows have been processed (in the CompanyImport file), but I need the total number of rows before I start adding the rows to the database.

The sheet I'm importing is almost 1M rows, so I am trying to create a progress bar.

My import:

public function model(array $row)
{
    # Counter
    ++$this->currentRow;

    # Dont create or validate on empty rows
    # Bad workaround
    # TODO: better solution
    if (!array_filter($row)) {
        return null;
    }

    # Create company
    $company = new Company;
    $company->crn = $row['crn'];
    $company->name = $row['name'];
    $company->email = $row['email'];
    $company->phone = $row['phone'];
    $company->website = (!empty($row['website'])) ? Helper::addScheme($row['website']) : '';
    $company->save();

    # Everything empty.. delete address
    if (!empty($row['country']) || !empty($row['state']) || !empty($row['postal']) || !empty($row['address']) || !empty($row['zip'])) {

        # Create address
        $address = new CompanyAddress;
        $address->company_id = $company->id;
        $address->country = $row['country'];
        $address->state = $row['state'];
        $address->postal = $row['postal'];
        $address->address = $row['address'];
        $address->zip = $row['zip'];
        $address->save();

        # Attach
        $company->addresses()->save($address);

    }

    # Update session counter
    Session::put('importCurrentRow', $this->currentRow);

    return $company;

}

My controller:

public function postImport(Import $request)
{
    # Import
    $import = new CompaniesImport;

    # Todo
    # Total number of rows in the sheet to session
    Session::put('importTotalRows');

    #
    Excel::import($import, $request->file('file')->getPathname());

    return response()->json([
        'success' => true
    ]);
}

Would this work for you? https://docs.laravel-excel.com/3.1/imports/progress-bar.html — ascsoftw, Sep 15 '19 at 08:58
@ascsoftw No, sorry. That is for when you're using the console command — Kaizokupuffball, Sep 15 '19 at 13:39

score 8 · Answer 1 · answered Mar 28 '21 at 07:06

8

In Laravel Excel 3.1 you can get the total rows by implementing WithEvents and listening to beforeImport event.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeImport;

class UserImport extends ToModel, WithEvents {
    [...]

    public function registerEvents(): array
    {
        return [
            BeforeImport::class => function (BeforeImport $event) {
                $totalRows = $event->getReader()->getTotalRows();

                if (!empty($totalRows)) {
                    echo $totalRows['Worksheet'];
                }
            }
        ];
    }

    [...]
}

answered Mar 28 '21 at 07:06

glaucomorais

317
3
9

This should be the accepted answer :) – atymic Oct 04 '21 at 06:39
This is a better solution – Creatix Apr 09 '22 at 14:55
How do you access the `$totalRows` from a controller calling the Import? I mean `Excel::import(new UsersImport($this->record), $data['excel_file'],'excel')`? – Pathros Sep 02 '22 at 15:16
Or how to tell when an Import has finished from the controller? – Pathros Sep 02 '22 at 15:17
@Pathros You can use a setter and getter for the `$totalRows` in the `UserImport` class and access that in your controller. – Anisur Rahaman Sakib Jan 18 '23 at 15:57

ascsoftw · Accepted Answer · 2019-09-15T14:03:17.903

5

You can use below code to calculate number of rows

Excel::import($import, 'users.xlsx');

dd('Row count: ' . $import->getRowCount());

You can check the Docs

Update

The above method was for calculating the rows which have been imported so far. In order to get number of rows which are in the sheet, you need to use getHighestRow

    Excel::load($file, function($reader) {
        $lastrow = $reader->getActiveSheet()->getHighestRow();
        dd($lastrow);
    });

This has been referenced here by author of the Plugin.

edited Sep 15 '19 at 14:03

answered Sep 15 '19 at 09:01

ascsoftw

3,466
2
15
23

But isn't that for how you get the current row that you're processing? Or am i wrong? – Kaizokupuffball Sep 15 '19 at 13:37
Excel::load() is removed and replaced by Excel::import($yourImport) See https://docs.laravel-excel.com/3.1/getting-started/upgrade.html#upgrading-to-3-from-2-1 – user3563059 Apr 22 '20 at 02:23

score 4 · Answer 3 · answered Apr 24 '20 at 21:39

1.- Make file for import

php artisan make:import ImportableImport

2.- Your File Import

<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\Importable;

class ImportablesImport implements ToCollection
{

    use Importable;

    /**
    * @param Collection $collection
    */
    public function collection(Collection $collection)
    {
        //
    }
}

3.- Your controller

$array = (new ImportablesImport)->toArray($file);
dd(count($array[0]));

This doc: https://docs.laravel-excel.com/3.1/imports/importables.html

One Milliion records to array ? are you sure it works ? – Kia Boluki Nov 26 '20 at 19:37 — Kia Boluki, Nov 26 '20 at 19:37

score 0 · Answer 4 · answered Oct 26 '20 at 15:35

You can use below code to get number of rows before import

$fileExtension     = pathinfo($file, PATHINFO_EXTENSION);
$temporaryFileFactory=new \Maatwebsite\Excel\Files\TemporaryFileFactory(
    config('excel.temporary_files.local_path', 
            config('excel.exports.temp_path', 
            storage_path('framework/laravel-excel'))
    ),
    config('excel.temporary_files.remote_disk')
);


$temporaryFile = $temporaryFileFactory->make($fileExtension);
$currentFile = $temporaryFile->copyFrom($file,null);            
$reader = \Maatwebsite\Excel\Factories\ReaderFactory::make(null,$currentFile);
$info = $reader->listWorksheetInfo($currentFile->getLocalPath());
$totalRows = 0;
foreach ($info as $sheet) {
    $totalRows+= $sheet['totalRows'];
}
$currentFile->delete();

The code taken from Laravel Excel libary

score 0 · Answer 5 · answered Aug 22 '22 at 07:14

Check Below Example:

$sheet->getActiveSheet()->getStyle('A2:A' . $sheet->getHighestRow())->getFont()->setBold(true);

by using getHighestRow() method you can fetch the total number of rows. In the above code sample I've applied font as BOLD to the second cell of first column till the maximum row count of that same first column.

Detailed Code Snippet of another example:

$excel->sheet('Employee Details', function ($sheet) use ($AllData) {
                $sheet->fromArray($AllData);
                $sheet->setAutoSize(true);
                $sheet->getStyle('A2:A' . $sheet->getHighestRow())->applyFromArray(array('alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
                
            });

Laravel excel get total number of rows before import

5 Answers5

Check Below Example:

Linked