• [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

Description

I created new import with WithChunkReading and batch size. The issue i am facing is importer converts date columns to timestamp( i believe its timestamp) 43257.0. after investing the issue i found very old thread https://github.com/Maatwebsite/Laravel-Excel/issues/404 and one of the solution that fixed it for me was setting true value to false in class ReadChunk available at vendor/maatwebsite/excel/src/Jobs/ReadChunk.php. line is $this->reader->setReadDataOnly(true); This solution works for now but when we will do composer update it will be gone as its not configurable in library.

Steps to Reproduce

1. create excel
2. add column with any date format.
3. import excel using chunk method importer via library.

Expected behavior:

I would expect the library to upload the date as expected.

Actual behavior:

library converts date to timestamp( assuming its timestamp)

Additional Information

Here is my import class,

`<?php namespace App\Imports;

use App\Sample; use Maatwebsite\Excel\Concerns\ToModel; use Maatwebsite\Excel\Concerns\WithBatchInserts; use Maatwebsite\Excel\Concerns\WithChunkReading; use Maatwebsite\Excel\Concerns\WithHeadingRow; use Maatwebsite\Excel\Imports\HeadingRowFormatter;

HeadingRowFormatter::default('none');

class Sample implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading {

public function model(array $row)
{


    return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => $row['date'],
    ]);
}

public function batchSize(): int
{
    return 1000;
}

public function chunkSize(): int
{
    return 1000;
}

}`

I've been playing around with this for a few days but simply cannot make it work. I have some long numbers(16 digits) stored in my database which I am then using the toArray() function to create my xlsx excel spreadsheet. The code i'm using to format the column: $sheet->setColumnFormat(array( 'A' => '@' ));

However the numbers are being shown in excel as numbers and they are being shortened with scientific notation.

Is there anything I can do about this outside of appending a space either side?

I'm aware of excel's 15 digit limit however when formatting as text I was lead to believe this would not be a problem.

Excel::filter('chunk')->load($path)->chunk(250, function($results) use ($data) { print_r($results); });

this function gets every time first 250 rows of excel , not get the other rows , my excel file contains 12000 Rows but it returns first 250 rows.

Please help me , may be i am wrong or may be this function returns only first 250 rows , is there any other way to get all rows in chunk wise ?

Note : i am using multiple sheets .

Prerequisites

Versions

• PHP version: 7.2
• Laravel version: 5.7
• Package version: 3.1

Description

I have some trouble getting the after import event to work. The event never fires. :( The after sheet works fine, what am I doing wrong?

i have tried both the register events and the auto register events.

My code

namespace App\Imports;

use App\Order;
use App\Order\Address;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Events\AfterImport;
use Maatwebsite\Excel\Events\BeforeImport;

class OrderAddressImport implements
    WithHeadingRow,
    ToModel,
    WithBatchInserts,
    WithChunkReading,
    WithCustomCsvSettings,
    ShouldQueue,
    WithEvents
{
    use Importable, RegistersEventListeners;

    protected $order;

    public function __construct(Order $order)
    {
        $this->order = $order;
    }

    /**
     * @param array $row
     * @return Address
     */
    public function model(array $row)
    {
        $data = ['order_id' => $this->order->id];
        $data = array_merge($data, $row);

        return new Address($data);
    }


    public function batchSize() : int
    {
        return 1000;
    }

    public function chunkSize() : int
    {
        return 5000;
    }

    /**
     * @return array
     */
    public function getCsvSettings(): array
    {
        return [
            'delimiter' => ';',
            'enclosure' => '',
            'input_encoding' => 'UTF-8'
        ];
    }

    public static function afterImport(AfterImport $event)
    {
       dd($event);
    }
}

    new UsersImport implements ToModel, WithHeadingRow , WithValidation
    {
            use Importable;

            /**
             * @param array $row
             *
             * @return Model|null
             */
            public function model(array $row)
            {
                return new User([
                    'name'        => $row['name'],
                    'email'        => $row['email'],
                    'password' => 'secret',
                ]);
            }

            /**
             * @return array
             */
            public function rules(): array
            {
                return [
                    'email' => Rule::in(['[email protected]']),

                     // Above is alias for as it always validates in batches
                     '*.email' => Rule::in(['[email protected]']),
                ];
            }
        }

Will redirect back (like Laravel's validation) with an error bag with message: 'There was an error on row 2. The selected email is invalid.'

Alternatively you can catch the validation exception and handle it:

try {
    $import->import('import-users.xlsx');
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
     $failures = $e->failures();
     // each failure is an instance of Failure
     $failure->row(); // row that went wrong
     $failure->attribute(); // either heading key or column index
     $failure->errors(); // Actual error messages   
}

I am importing a file.xlsx in this there is field DOB = 16-OCT-1992 , after the getting values in file DOB is converted into 34215 ,i think it is not calculating formulas in DOB field , Please help me out ? Urgent

Excel::filter('chunk')->load('file.xlsx')->chunk(250, function($results) { foreach($results as $row) { // do stuff } });

Prerequisites

• [x] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
• [x] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [x] Checked that your question isn't already asked before.
• [x] Filled in the entire issue template

Versions

• PHP version: 7.2
• Laravel version: 7.22.4
• Package version: 3.1.20

Description

Temporary files are creating on one server on queued export

Additional Information

Hello, I am running my application on laravel vapor (serverless). On queued exporting , the temporary files are not get stored on disk s3. I am stuck on that!

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

Versions

• PHP version: 7.0
• Laravel version: 5.5
• Package version: 3.0

Description

I'm using this code :

<html>
    <h1>{{ $report->title }}</h1>
    <br/>
    <h2>{{ $report->description }}</h2>
    <br/>
    <p>{{ __('excel.dateLabel') }} {{ $date }}</p>
    <br/>
    <table>
        <thead>
            <tr>
                <th>{{ __('excel.firstColumn') }}</th>
                <th>{{ __('excel.secondColumn') }}</th>
                <th>{{ __('excel.thirdColumn') }}</th>
                <th>{{ __('excel.fourthColumn') }}</th>
            </tr>
        </thead>
    </table>
</html>

But when I would use class or style tag only background-color work. Color, font-weight.... not working.

Steps to Reproduce

<html>
    <h1>{{ $report->title }}</h1>
    <br/>
    <h2>{{ $report->description }}</h2>
    <br/>
    <p>{{ __('excel.dateLabel') }} {{ $date }}</p>
    <br/>
    <table>
        <thead>
            <tr>
                <th style="font-weight : bold">{{ __('excel.firstColumn') }}</th>
                <th>{{ __('excel.secondColumn') }}</th>
                <th>{{ __('excel.thirdColumn') }}</th>
                <th>{{ __('excel.fourthColumn') }}</th>
            </tr>
        </thead>
    </table>
</html>

I've tried not to bother you with this but I am getting no where so I'm afraid I have to ask for help. I'm having two issues with Maatwebsite/Laravel-Excel / PHPExcel that I can not find an answer to and I'm hoping that somebody out there can help.

1. I'm importing a csv and one of the columns has numbers in it with leading zeros that keep getting trimmed off. How can I prevent that? Preferable I would like all values to be treated as strings and not have the values manipulated at all.

Excel::load($filePath)->toArray();

1. I'm also exporting a csv that must have an empty last column in each row. The problem is that, that column is dropped on export. If I add a space as the value then it remains but unfortunately I need the value to be empty.

I use this code

$fileName = '/var/www/file.xls';
Excel::load($fileName, function($reader) {

        })->get();

And I get this error

PHPExcel_Exception in RowIterator.php line 125 Row 2 is out of range (2 - 1)

 in RowIterator.php line 125
at PHPExcel_Worksheet_RowIterator->seek('2') in RowIterator.php line 99
at PHPExcel_Worksheet_RowIterator->resetStart('2') in RowIterator.php line 81
at PHPExcel_Worksheet_RowIterator->__construct(object(PHPExcel_Worksheet), '2', null) in Worksheet.php line 2568
at PHPExcel_Worksheet->getRowIterator('2') in ExcelParser.php line 329
at ExcelParser->parseRows() in ExcelParser.php line 182
at ExcelParser->parseWorksheet() in ExcelParser.php line 132
at ExcelParser->parseFile(array()) in LaravelExcelReader.php line 1024
at LaravelExcelReader->_parseFile(array()) in LaravelExcelReader.php line 473

Problem seems to be that the Excel file has more than one Pages.

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.13
• Laravel version: 5.6
• Package version: ^3.0

Description

I am getting Allowed memory size of 134217728 bytes exhausted when i try to export with FromQuery option

Steps to Reproduce

Expected behavior:

I want to fix my problem :)

Actual behavior:

Additional Information

namespace App\Exports;

use App\OldTransaction;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Http\Request;
use jDate;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class OldDepositExport implements FromQuery, ShouldQueue, WithMapping, WithHeadings, ShouldAutoSize
{
	use Exportable;
	
	/**
	 * DepositExport constructor.
	 * @param Request $request
	 */
	public function __construct(Request $request)
	{
	}

	public function headings(): array
	{
		return [
			'ID',
		];
	}


	public function map($transaction): array
	{
		return [
			$transaction->id,
		];
	}

	public function query()
	{
		return User::query()
			->where('status', '=', 1)
			->select(['id']);
	}
}

See this image https://i.imgur.com/yMgUqXP.jpg

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• [X] Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1

What version of Laravel are you using?

8.75

What version of PHP are you using?

8.1.6

Describe your issue

if their error comes it rollback the entries from the first table. but it does not roll back the entries from the second table.

Can anyone guide how it is possible?

what my case is.

if their error comes it rollback the entries from the agent table. but it does not roll back the entries from the payment table.

How can the issue be reproduced?

To create this error I hardcoded the value for the third row and set the status value to null. but status only takes an integer value why it creates the error.

it gives an error Incorrect integer value: '' for column 'status'

but it rollbacks entries from the agent table but not from the payment table

class noteImport implements OnEachRow {

    public function onRow(Row $row){
        $row = $row->toArray();
        
            $Option = [
            "firstpay" => 1,
            "amount" => 1,
            "payment_type" => 1,
            "info" => "1",
            "enter_date" => date('y/m/d'),
            "recieve_date" => date('y/m/d'),
            "status" => $row['srno'] != 3 ? 1 : "",     
            ];

            $agent = Agent::create([
            "agent_code" => "1",
            "name" => "1",
            "address" => "1",
            "phone" => 454,
            "password" => "asd",
            "upload" => "1",
            "status" => 1,
            ]);

        $agent->payment()->create($Option);
    }
}

What should be the expected behaviour?

The expected behavior is it should delete an entry from the second (payment) table

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• [X] Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1.44

What version of Laravel are you using?

9.44

What version of PHP are you using?

8.1.2

Describe your issue

Verified that this bug was introduced between 3.1.40 (last working) and 3.1.44.

When utilizing non standard database connection (provided by env) the application fails to interact with the database in the validator.

When laravel excel calls (indirectly) into the Database Manager, the app config is not be respected.

I noticed that from this point the database manager is pointed at a connection that is not configured (disregarding the app config):

For posterity, here is the state of the connection when laravel excel resolves initially:

How can the issue be reproduced?

From a new Laravel App: configure a database connection (e.g. testing). Create an import that uses rules() and ensure at least one of these rules requires the database (exists:table_name,column).

What should be the expected behaviour?

No error should occur. Laravel excel should respect the application configuration.

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• [X] Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1.44

What version of Laravel are you using?

9.43.0

What version of PHP are you using?

8.0.26

Describe your issue

If an worksheet (xlsx, xls, ods) is imported that contains a dates the value of a cell is numeric, so it ignores the configured format(from the style). Note: imports.read_only should be switched to false to allow the correct reading of worksheets.

For ReaderFactory the PhpOffice\PhpSpreadsheet\Reader is configured with setReadDataOnly using the value from the config file. For ReadChunk the value is always set to true. So (global) styles will not be applied to date cells.

I assume that also the handling of empty cells will differ (see below).

How can the issue be reproduced?

Create a import class that implements the WithChunkReading and WithFormatData. Load an xlsx, xls, ods file that uses a date (format should be available specified in the sheet styles).

What should be the expected behaviour?

Dates should always handled the same way for all readers. Or; both readers should use the configuration values.

For ReadChunk the code should be changed like this:

$this->reader->setReadDataOnly(config('excel.imports.read_only', true));
$this->reader->setReadEmptyCells(!config('excel.imports.ignore_empty', false));

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• [X] Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1.40

What version of Laravel are you using?

9.41.0

What version of PHP are you using?

8.1.12

Describe your issue

When implementing the WithMultipleSheets interface, the sheets() method is called twice. It does not result in duplication of the sheets, but the code inside is executed twice. In my case, I had calls to database made in that function, wth resulted in dupicate queries (But data was correct).

How can the issue be reproduced?

<?php

namespace App\Exports\WorkedHours;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class WorkedHoursExport implements WithMultipleSheets
{
    use Exportable;

    public function sheets(): array
    {
      \Log::debug('Calling sheets method')
    }
}

What should be the expected behaviour?

The method is only called once.

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• [X] Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1

What version of Laravel are you using?

9.41

What version of PHP are you using?

8.1.0

Describe your issue

Hello,

I am trying to read the data of a XLSX file and import them into DB. Until now, I produced excel files using my LibreOffice and the SkipsEmptyRows interface in order to read file line by line and ignore empty rows. However, when I created the same Excel file with the same data in Google Docs, for some reason empty rows are not ignored. After some research, I found that google docs create rows with an empty string. Please find below a file example with this problem.

So, my question is how can I skip rows / cells which include empty strings ?

I tried the following but with no success

• Update the variable ignore_empty => true, in config/excel.php

• registerEvents

public function registerEvents(): array { return [ BeforeImport::class => function (BeforeImport $event) { return $event->getReader()->getPhpSpreadsheetReader()->setReadEmptyCells(false); } ];

I noticed that the calculation of total rows is coming from the function listWorksheetInfo which is into vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Xlsx.php

The weird thing here is that while it's reading the file and storing the total numbers of rows, it jumps suddenly from "real" total number of rows to a "magic" number of rows.

Does anyone have explanation about this? Or how can I manage it in order to ignore these empty lines?

Extra Note : I am using the WithChunkReadinginterface in order to keep the memory usage under control & OnEachRowinterface in order to process each row's data

SampleExample (1).xlsx

How can the issue be reproduced?

To set a counter about the total number of rows instead of reading $xml->getAttribute('r')

What should be the expected behaviour?

In this case the total of number of rows should be 15 and not 1048576

Please take note of our contributing guidelines: https://docs.laravel-excel.com/3.1/getting-started/contributing.html Filling out the template is required. Any pull request that does not include enough information to be reviewed in a timely manner may be closed at the maintainers' discretion.

1️⃣ Why should it be added? What are the benefits of this change? In the docs this functionality is listed but is not included in the model import. This adds the IsEmptyWhen functionality for model imports

2️⃣ Does it contain multiple, unrelated changes? Please separate the PRs out. Just one single feature

3️⃣ Does it include tests, if possible?

4️⃣ Any drawbacks? Possible breaking changes?

5️⃣ Mark the following tasks as done:

• [x] Checked the codebase to ensure that your feature doesn't already exist.
• [x] Take note of the contributing guidelines.
• [x] Checked the pull requests to ensure that another person hasn't already submitted a fix.
• [] Added tests to ensure against regression.
• [x] Updated the changelog

6️⃣ Thanks for contributing! 🙌