Laravel-excel: [QUESTION] How to ignore conversion from string to integer during export to Excel?

[tarzanpasha]

[QUESTION].

Prerequisites

Versions

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

Description

Excuse me, a have a problem. There is a string field in mysql database
$table->string('number');
when i convert model to excel some values are converted from string to som other format like
2,0617E+17
it is 206170000011900058 in table an i se it as 2,0617E+17 when open excel application.

and in excel

it must be a string.
How can I export it ?

class ProtocolsExport implements FromCollection, WithHeadings, ShouldAutoSize, WithColumnFormatting
{

public function collection()
{
return Protocol::all();
}

public function columnFormats(): array
{
return [
'B' => NumberFormat::FORMAT_GENERAL
];
}

Additional Information

I don't undestant how to solve problem. Please, help me!
there are some other problems like this in other columns

[GlennM]

Please could you try with Formatting Columns and using \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT or \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_GENERAL as format?

[tarzanpasha]

FORMAT_GENERAL

ufrortunately that didn't help me.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProtocolsTable extends Migration
{
    public function up()
    {
        Schema::create('protocols', function (Blueprint $table) {
            $table->increments('id');
            $table->string('number');
            $table->integer('lot');
            $table->string('winner_inn',12);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('protocols');
    }
}

And

    public function columnFormats(): array
    {
        return [
            'B' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT,
            'D' => NumberFormat::FORMAT_TEXT
        ];
    }

And i have a link with all files (dump, model, mysql dump, excel)

https://drive.google.com/drive/folders/1okL2jMWgCgQ_8Wwbex6vtIemRTVJnVaF?usp=sharing

[patrickbrouwers]

You probably want to implement a custom value binder then. See more here: https://laravel-excel.maatwebsite.nl/3.1/exports/custom-formatting-values.html

[bolechen]

+1 same issue for me

[GlennM]

+1 same issue for me

Have you tried the solution approach from @patrickbrouwers ?

[bolechen]

I use the Laravel-Nova-Excel package, I don't know how to custom it, May I ask you to introduce it in more detail?

Very thx for help. @patrickbrouwers

[bolechen]

I found a temporary solution to the problem. @tarzanpasha
Add a space at the end of a integer that needs to be formatted into string.
Sample code is as follows.

public function map($money): array
{
    "number" => $numberValue,
    "string" => $numberValue . ' ',
];

[patrickbrouwers]

class DownloadWithDrawToExcel extends DownloadExcel implements WithCustomValueBinder, FromQuery, WithColumnFormatting, WithMapping
{
    public function bindValue(Cell $cell, $value)
    {
        $cell->setValueExplicit($value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

        return true;
    }

    // ... other methods
}

[bolechen]

class DownloadWithDrawToExcel extends DownloadExcel implements WithCustomValueBinder, FromQuery, WithColumnFormatting, WithMapping
{
public function bindValue(Cell $cell, $value)
{
$cell->setValueExplicit($value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

    return true;
}

// ... other methods

}

Useful for me, thx for help.

[PheRum]

+1 same issue for me