Laravel-excel: [BUG] Start row (502) is beyond highest row (1)

Created on 20 Nov 2018 · 7Comments · Source: Maatwebsite/Laravel-Excel

Prerequisites

[X] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
[X] Checked that your issue isn't already filed.
[X] Checked if no PR was submitted that fixes this problem.

Versions

PHP version: 7.1
Laravel version: 5.7
Package version: 3.1.3

Description

Importing a file using batches which has 5 entries beyond row 1 is throwing an error.

Without "WithBatchInserts, WithCustomChunkSize, WithChunkReading" it will go past to the last row of the file.

With "WithBatchInserts, WithCustomChunkSize, WithChunkReading" it will throw the beyond highest row "1" error.

Sample import file: sample.xlsx.

    /**
     * {@inheritdoc}
     */
    public function batchSize(): int
    {
        return 500;
    }

    /**
     * {@inheritdoc}
     */
    public function chunkSize(): int
    {
        return 500;
    }

Steps to Reproduce

Expected behavior:

The batch reading should continue till the end of the file.

Actual behavior:

On initial load, the progressbar reports highest row number is 88985. When it gets to processing the 6th row, it reports an incorrect error of "Start row (502) is beyond highest row (1)". We've not hit the next batch at all, and the row we were last on was actually 5.

The file only contains 5 actual rows of data (yet the number of rows available in Excel extend well above this - the source of the file cannot be changed).

In trying to resolve the issue, I added a beforeSheet event and dumped the getHighestRow() response which correctly outputted string(1) "5".

    public static function beforeSheet(BeforeSheet $event)
    {
        /**
         * @var $worksheet Worksheet
         */
        $worksheet = $event->getSheet();
        $highestRow = $worksheet->getHighestRow();
        var_dump($highestRow);
    }

This wrote the following to the console:

    0/88985 [>---------------------------]   0%string(1) "5"
     4/88985 [>---------------------------]   0%int(1)

Stacktrace:

#0 C:\Users\greg\PhpstormProjects\script\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\RowIterator.php(49): PhpOffice\PhpSpreadsheet\Worksheet\RowIterator->resetStart(502)
#1 C:\Users\greg\PhpstormProjects\script\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\Worksheet.php(2650): PhpOffice\PhpSpreadsheet\Worksheet\RowIterator->__construct(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), 502, NULL)
#2 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Imports\ModelImporter.php(40): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->getRowIterator(502, NULL)
#3 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Sheet.php(217): Maatwebsite\Excel\Imports\ModelImporter->import(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), Object(App\Imports\SourceOne\CompanyFile\IndexSheetImport), 502)
#4 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Jobs\ReadChunk.php(99): Maatwebsite\Excel\Sheet->import(Object(App\Imports\SourceOne\CompanyFile\IndexSheetImport), 502)
#5 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Database\Concerns\ManagesTransactions.php(29): Maatwebsite\Excel\Jobs\ReadChunk->Maatwebsite\Excel\Jobs\{closure}(Object(Illuminate\Database\MySqlConnection))
#6 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Database\DatabaseManager.php(327): Illuminate\Database\Connection->transaction(Object(Closure))
#7 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php(223): Illuminate\Database\DatabaseManager->__call('transaction', Array)
#8 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Jobs\ReadChunk.php(103): Illuminate\Support\Facades\Facade::__callStatic('transaction', Array)
#9 [internal function]: Maatwebsite\Excel\Jobs\ReadChunk->handle()
#10 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(29): call_user_func_array(Array, Array)
#11 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#12 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(31): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure))
#13 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\Container.php(572): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL)
#14 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Bus\Dispatcher.php(94): Illuminate\Container\Container->call(Array)
#15 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php(116): Illuminate\Bus\Dispatcher->Illuminate\Bus\{closure}(Object(Maatwebsite\Excel\Jobs\ReadChunk))
#16 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Pipeline\Pipeline.php(104): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Maatwebsite\Excel\Jobs\ReadChunk))
#17 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Bus\Dispatcher.php(98): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#18 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\ChunkReader.php(58): Illuminate\Bus\Dispatcher->dispatchNow(Object(Maatwebsite\Excel\Jobs\ReadChunk))
#19 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Support\Collection.php(418): Maatwebsite\Excel\ChunkReader->Maatwebsite\Excel\{closure}(Object(Maatwebsite\Excel\Jobs\ReadChunk), 1)
#20 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\ChunkReader.php(59): Illuminate\Support\Collection->each(Object(Closure))
#21 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Reader.php(85): Maatwebsite\Excel\ChunkReader->read(Object(App\Imports\SourceOne\CompanyImport), Object(PhpOffice\PhpSpreadsheet\Reader\Xlsx), 'C:\\Users\\greg\\A...')
#22 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Excel.php(120): Maatwebsite\Excel\Reader->read(Object(App\Imports\SourceOne\CompanyImport), 'MatchedMatrixAl...', 'Xlsx', NULL)
#23 C:\Users\greg\PhpstormProjects\script\vendor\maatwebsite\excel\src\Concerns\Importable.php(37): Maatwebsite\Excel\Excel->import(Object(App\Imports\SourceOne\CompanyImport), 'MatchedMatrixAl...', NULL, 'Xlsx')
#24 C:\Users\greg\PhpstormProjects\script\app\Services\SourceOneService.php(35): App\Imports\SourceOne\CompanyImport->import('MatchedMatrixAl...')
#25 C:\Users\greg\PhpstormProjects\script\app\Console\Commands\Matrix.php(52): App\Services\SourceOneService->import(Array)
#26 [internal function]: App\Console\Commands\Matrix->handle()
#27 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(29): call_user_func_array(Array, Array)
#28 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#29 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php(31): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure))
#30 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Container\Container.php(572): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL)
#31 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Console\Command.php(183): Illuminate\Container\Container->call(Array)
#32 C:\Users\greg\PhpstormProjects\script\vendor\symfony\console\Command\Command.php(255): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#33 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Console\Command.php(170): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#34 C:\Users\greg\PhpstormProjects\script\vendor\symfony\console\Application.php(886): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#35 C:\Users\greg\PhpstormProjects\script\vendor\symfony\console\Application.php(262): Symfony\Component\Console\Application->doRunCommand(Object(App\Console\Commands\Matrix), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#36 C:\Users\greg\PhpstormProjects\script\vendor\symfony\console\Application.php(145): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#37 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Console\Application.php(89): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#38 C:\Users\greg\PhpstormProjects\script\vendor\laravel\framework\src\Illuminate\Foundation\Console\Kernel.php(122): Illuminate\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#39 C:\Users\greg\PhpstormProjects\script\artisan(37): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

Additional Information

Any additional information, configuration or data that might be necessary to reproduce the issue.

bug

Source

greggunner

All 7 comments

@patrickbrouwers This issue is related to the closed 1889. I tested against the other files and found the is definitely some issue with how the batches are being processed and reading the file as the error output does not correspond to the issue being experienced.

greggunner on 21 Nov 2018

I was able to reproduce it with ods file.

create file with 100 filled rows. Save it.
erase data from 95 rows, keep only first five filled - save file again.
What happens when you try to import data form this file (100 items per chunk, for example):
\Maatwebsite\Excel\ChunkReader::read is fired, requesting \Maatwebsite\Excel\ChunkReader::getTotalRows and from inside -
phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Ods.php will do the following, while parsing file:

do {
    $xml->read();
    if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
        $rowspan = $xml->getAttribute('table:number-rows-repeated');
        $rowspan = empty($rowspan) ? 1 : $rowspan;
        $tmpInfo['totalRows'] += $rowspan;

It gets $rowspan of 1 for each non-empty string,
It gets rowspan = 95 for all cleared rows
It gets rowspan = 1 for null value, returning 101 rows in total.

Therefore, we'll have 2 ReadChunk jobs with startRow 1 and 101.

Next, \Maatwebsite\Excel\Jobs\ReadChunk::handle uses $this->reader->load($this->file) to load file chunk, and ODS reader parses each cell, calculating correct amount of maxRows in \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::createNewCell
(changing setReadDataOnly and setReadEmptyCells parameters doesn't help to fix beyond highest row issue )

The problem is, that actual data from chunk loading is read after creation of all job chunks based on "false" generic info provided by reader.

This commit (https://github.com/chorry/Laravel-Excel/commit/9cfb2777d3b9a0bc5ee70bb1b36a06430f7dfc51) should fix this, but i'm not sure if it breaks original design (if its okay - i'll make a PR).

Tests are passing https://travis-ci.org/chorry/Laravel-Excel/builds/460202954