Laravel-excel: [BUG] WithColumnFormatting not working well

[RayhanYulanda]

Prerequisites

• [x] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [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.
• [x] 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: https://github.com/PHPOffice/PhpSpreadsheet/issues/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.