Laravel-excel, change column format (string/integer) in Export to CSV

Question

Hope one of you can help me with a tiny problem :)

I'm trying to make this:

Ancient Petrified Leaf,"18703","Majordomo Executus","Bossen","Priest"

Into this:

"Ancient Petrified Leaf",18703,"Majordomo Executus","Bossen","Priest"

Let's pretend these are the column/table names:

A, B, C, D, E

Tables:

$table->string('A');
$table->integer('B');
$table->string('C');
$table->string('D');
$table->string('E');

This is my export class:

<?php

namespace App\Exports;

use App\SoftReserve;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class PugsExport implements FromQuery, WithColumnFormatting, WithMapping
{

    use Exportable;

    public function __construct($id)
    {
        $this->id = $id;
    }

    public function query()
    {
        return SoftReserve::query()->where('pug_id', $this->id);
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_NUMBER,
            'C' => NumberFormat::FORMAT_TEXT,
            'D' => NumberFormat::FORMAT_TEXT,
            'E' => NumberFormat::FORMAT_NUMBER,
        ];
    }

    /**
    * @var $softreserve
    */
    public function map($softreserve): array
    {
        return [
            $softreserve->item_name,
            $softreserve->item_id,
            $softreserve->item_boss,
            $softreserve->character_name,
            $softreserve->character->spec->class,
        ];
    }

}

Any suggestions why I do not recieve the correct column formats when I export to CSV? Seems colA is not a string (no quotes)?? Seems colB is a string and not a number/integer (without quotes).

What am I doing wrong here?

Without knowing much about formatting excel, couldn't it be that item_id should be casted to int in the map? like (int) $item_id — mrhn, Aug 23 '20 at 19:42

score 1 · Answer 1 · answered Sep 09 '22 at 06:44

You can try using custom value binding.

For example:

...
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
    
class PugsExport extends DefaultValueBinder implements WithCustomValueBinder, FromQuery, WithColumnFormatting, WithMapping
{
  ...
  public function bindValue(Cell $cell, $value)
  {
        $numericalColumns = ['B']; // columns with numerical values

        if (!in_array($cell->getColumn(), $numericalColumns) || $value == '' || $value == null) {
            $cell->setValueExplicit($value, DataType::TYPE_STRING);

            return true;
        }

        if (in_array($cell->getColumn(), $numericalColumns)) {
            $cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
  }
  ...
}

Laravel-excel, change column format (string/integer) in Export to CSV

1 Answers1