How Can I set dynamic number format with separator in laravel excel maatwebsite?

Question

I have an amount column and I want to set integer format when the number does not have a decimal and double format when the number has a decimal. in both ways, I want to add separators to numbers. currently, I use bindValue but excel cells don't know the amount column as number format and I should select them and convert them to numbers.

public function bindValue(Cell $cell, $value)
{
    if (is_int($value)) {
        $cell->setValueExplicit($value, '#,##0');

        return true;
    }else if (is_double($value)) {
        $cell->setValueExplicit($value, '#,##0.00');

        return true;
    }else{
        $cell->setValueExplicit($value,  DataType::TYPE_STRING);

        return true;

    }
}

how can I fix it?

Inkle

Answer 1

You can format an entire column by using

• WithColumnFormatting

    namespace App\Exports;
    
    use PhpOffice\PhpSpreadsheet\Shared\Date;
    use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
    use Maatwebsite\Excel\Concerns\WithColumnFormatting;
    use Maatwebsite\Excel\Concerns\WithMapping;
    
    class InvoicesExport implements WithColumnFormatting, WithMapping
    {
        public function map($invoice): array
        {
            return [];
        }
        
        public function columnFormats(): array
        {
            return [
                'B' => NumberFormat::FORMAT_NUMBER_00,
                'C' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
            ];
        }
    }

• If you want a more custom solution you should use the AfterSheet event. For more details follow the documentation on this here.

Krutz