Laravel-excel: [PROPOSAL] A simple way to disable intelligent formatting

[carnevalle]

Prerequisites

Versions

• PHP version: 7.3.4
• Laravel version: 5.8.26
• Package version: 3.1

Description

I just had a problem when using this framework to import a CSV file. Inside the file I had a barcode, which was turned into scientific notation. See https://github.com/Maatwebsite/laravel-excel-docs/pull/80

It would be great if there was a simple method of disabling the intelligent formatting - at least when importing a csv file. Something like "just give me the values inside the file'.

Example

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithoutIntelligentFormatting;

class FileImport implements ToModel, WithoutIntelligentFormatting
{

}

Additional Information

Any additional information, configuration or data that might be needed to understand the proposal.

[patrickbrouwers]

Perhaps you can use the config setting to change it to your own custom value binder for all your imports and exports? https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/config/excel.php#L132

[patrickbrouwers]

Or perhaps PhpOffice\PhpSpreadsheet\Cell\StringValueBinder could be of use to use

[carnevalle]

As mentioned in the PR to the documentation I figured out how to override this functionality, but this intelligent behavior caused an error that went into production as it was only a small percentage of the columns that had barcodes long enough to trigger scientific notation.

It was furthermore time consuming to debug and figure out how to fix it. I hope the addition to the documentation can help others with the same problem.

But in reality I would rather have this 'intelligent' behavior be opt-in than opt-out - so that the default behavior is that values going in are the same values coming out - especially for text imports like CSV. Then I can decide in my import class how I want to format them.

I think adding this as a feature could also make the documentation clearer.

[patrickbrouwers]

Making it opt-in would be a breaking change, so that's not something we'll be able to do soon.

[carnevalle]

I (now) understand this is achievable today using a custom value binder, but could it be an idea to add a trait something like below to make it more explicit how to disable this hidden magic?

<?php

namespace Maatwebsite\Excel\Concerns;

use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;

trait DisableIntelligentFormatting
{
    public function bindValue(Cell $cell, $value)
    {
        // sanitize UTF-8 strings
        if (is_string($value)) {
            $value = StringHelper::sanitizeUTF8($value);
        }

        // Set value explicit
        $cell->setValueExplicit($value, DataType::TYPE_STRING);

        // Done!
        return true;
    }
}

Then it would be used like this

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\DisableIntelligentFormatting;

class FileImport implements ToModel, WithCustomValueBinder
{
    use DisableIntelligentFormatting;
}

[patrickbrouwers]

You can already do that:

class FileImport extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements WithCustomValueBinder

[carnevalle]

Aarh I see. Lol. Talk about reinventing the wheel :-)

Should I add something to the documentation for this use case?

I could just add a section to the pages about custom formatting values?

[patrickbrouwers]

Yes note to the docs would be good I think. Yes that sounds like the right place. Thanks for contributing! :)

[carnevalle]

Thank you for building awesome software, that has enabled me to easily build a cool queued and chunked import feature into Laravel Nova :-)

[patrickbrouwers]

@carnevalle glad you like it! :) Can we be expecting a nice postcard from your home town? ;) https://docs.laravel-excel.com/3.1/getting-started/license.html#postcardware