[BUG] WithColumnFormatting not working well

[RayhanYulanda]

Prerequisites

• Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• 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.
• Filled in the entire issue template

Versions

• PHP version: 7.4
• Laravel version: 6.2
• Package version: 3.1.23

Description

I tried using WithColumnFormatting but it doesn't work at all.

public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'G' => NumberFormat::FORMAT_DATE_DDMMYYYY,
        ];
    }

Steps to Reproduce

This is the result of G column
2020-12-01
2021-01-01
2021-03-01
2021-01-01
2021-01-01

This is the result of A column
1,96101E+17
1,96101E+17
1,96101E+17
1,96101E+17
1,96101E+17

Expected behavior:
I'm expecting it works well for format column :')

[patrickbrouwers]

Have a look here: PHPOffice/PhpSpreadsheet#168

[RayhanYulanda]

For FORMAT_TEXT i can solve it with cells bind but how about FORMAT_DATE_DDMMYYYY? I tried using cells bind but still can't work @patrickbrouwers

[patrickbrouwers]

Try declaring a cell range, like G1:G100 and see if it applies then. If not, it might be a bug in PhpSpreadsheet

[c00p3r]

@patrickbrouwers having same issue here...
could u plz elaborate how to set the cell range?
searched the docs backwards and forwards couple times... no luck :(

[patrickbrouwers]

public function columnFormats(): array
    {
        return [
            'G1:G1000' => NumberFormat::FORMAT_DATE_DDMMYYYY,
        ];
    }

``

[c00p3r]

oooh
I thought u were talking about special method ))
Thanks! I'll give it a try

[c00p3r]

@patrickbrouwers this method is not being called during import
I debug it
same as 'prepareForValidation' and 'withValidator'
so it's not helping unfortunately

@RayhanYulanda I managed to fix it this way
for my xlsx file I've set all cell to TEXT format (not in code, but in Excel)
then in Laravel when u parse and validate - it works just fine
if u need Carbon object u parse the text value Carbon::parse('2/4/2020')

[patrickbrouwers]

Columnformatting is only implemented for export yes, I'm okay with a PR that would add it for imports too.

[c00p3r]

@RayhanYulanda and whoever sees this
there's also a config value u can change

    'value_binder' => [
        'default' => PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class,
    ],

this way (I guess) all values from your file will be parsed as strings

[RayhanYulanda]

@RayhanYulanda and whoever sees this
there's also a config value u can change

    'value_binder' => [
        'default' => PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class,
    ],

this way (I guess) all values from your file will be parsed as strings

I dont want all of cells to be string because there is should be date value

[patrickbrouwers]

You can implement your own value binder, there your have all control.

[yogithesymbian]

solved by @c00p3r

    // customize
    public function map($events): array
    {
        return [
                $events->location,
                Date::dateTimeToExcel(Carbon::parse($events->date))
        ];
    }

because my api return as string , then in excel alr custom format but the value on cell is still string, so we need pas in mapping about to re map

[mohammedobaid1234]

This solution I think the best

public function columnFormats(): array
    {
        return [
            'H' => NumberFormat::FORMAT_TEXT,
        ];
    }

public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $event->sheet->getStyle('H')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_STRING);
            },
        ];
    }