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
    ]);
}

Gemot

Would this work for you? docs.laravel-excel.com/3.1/imports/progress-bar.html – Interview

@Interview No, sorry. That is for when you're using the console command – Gemot

Answer 1

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.

Interview

But isn't that for how you get the current row that you're processing? Or am i wrong? – Gemot

Excel::load() is removed and replaced by Excel::import($yourImport) See docs.laravel-excel.com/3.1/getting-started/… – Shores

Answer 2

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'];
                }
            }
        ];
    }

    [...]
}

The key Worksheet can be different depending on your Excel file sheet name(s). A user might name it Sheet1 while another user may name it Worksheet.
So if you're sure your users will upload a file that has only one sheet you could avoid problems by getting the total row count from the array's first element like this:

$totalRowCount = reset($totalRows);

Channel

This should be the accepted answer :) – Physic

This is a better solution – Quarterphase

How do you access the $totalRows from a controller calling the Import? I mean Excel::import(new UsersImport($this->record), $data['excel_file'],'excel')? – Prayer

Or how to tell when an Import has finished from the controller? – Prayer

@Prayer You can use a setter and getter for the $totalRows in the UserImport class and access that in your controller. – Beezer

Hello, sorry with this, but I'm working with import sheets in laravel, and if I copy, paste your code, send me this: Undefined array key "Worksheet". I replace the name with my sheet names, but don't work, can you help me please? – Exercitation

Answer 3

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

Maiamaiah

One Milliion records to array ? are you sure it works ? – Anisomerous

Answer 4

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

Irrelevancy

Answer 5

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)));
                
            });

Conoscenti