[QUESTION]: How to disable cell value calculation when importing?

[numaquevedo]

Prerequisites

Versions

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

Description

I am trying to import a basic xlsx file. I used the command to generate the import class and I am using ToCollection. I noticed that some of the values that are passed to the collection() method are already being calculated. Below are some of the steps I took to attempt to prevent this situation:

1. I created a Custom Value Binder to attempt to keep the value as string, but the value being received there is already calculated.
2. I generated the vendor config file but there is no option to prevent this.

The values are being converted like this:
01/01/2019 (Date) -> 43466
9:00:00 AM -> 0.375

I am not sure how I can fix this. I have read the entire documentation and there is nothing that could suggest a way to remediate this problem.

Please advice.

Additional Information

[patrickbrouwers]

Hey @numaquevedo the examples you are given are not being calculated. By default calculating formulas is disabled. The examples you give are how Excel internally stores dates and times. You'll have to use PhpSpreadsheets Date helper to convert them to a date time object.

[numaquevedo]

@patrickbrouwers Thank you very much. I found this link after posting the question and before reading your comment: #1832

[fajusto]

I'd like to know how could I disable for a single cell and keep the others calculating.

<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use App\Models\User;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;

class FirstSheetImport implements WithHeadingRow, WithCalculatedFormulas, ToModel
{
    public function model(array $row)
    {
        if(!is_null($row[‘nome’])){
            return new User([
                ‘name’ => $row[‘nome’],
                ‘cpf’ => $row[‘cpf’],
                ‘rg’ => $row[‘rg’],
                ‘born_date’ => Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date:: excelToDateTimeObject($row[‘nascimento’])),
                ‘work_place’ => $row[‘local_de_trabalho’],
                ‘hire_start’ => Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date:: excelToDateTimeObject($row[‘inicio’])),
                ‘hire_status’ => $row[‘situacao’],
                ‘phone’ => $row[‘telefone’],
                ‘address’ => $row[‘endereco’],
                ‘bloc’ => $row[‘bairro’],
                ‘city’ => $row[‘cidade’],
                ‘postal_code’ => $row[‘cep’],
                ‘cnpj’ => $row[‘cnpj’],
                ‘status_cnpj’ => $row[‘status_cnpj’],
                ‘rescission’ => $row[‘rescisao’],
                ‘no_creci’ => $row[‘no_creci’],
                ‘creci_exp’ => $row[‘venc_creci’],
                ‘hire_end’ => Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date:: excelToDateTimeObject($row[‘vigencia_contrato_aditivo’]))
                ]);
        }
    }
}

Like this "vigencia_contrato_aditivo" is a formula, and this way is turning into a date. That's ok until here. But I also want to store the formula too. How do I make it?

[patrickbrouwers]

You might have to use onEachRow to have control over that and call the getValue() yourself on the PhpSpreadsheet cell instance.

[barbala4o]

Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date:: excelToDateTimeObject($val)) This helped me a lot. Thank you!!! You don't need onEachRow . You can pass any value and it will transform it if date in excel.