How to Export XLSX with Cell Formatting as Text

[aslanonline]

Hello,

i'm using Laravel 8 with the lastest version of Yajra Datatables and Buttons.
Currently I have a problem with Excel Export. All of my Numeric Columns are formatted as Numbers in Excel, so mobilenumbers starting with 4917417 replaced with 4,91787E+11.

How can I format the whole export sheet as Text, without manually set in Office Excel.

Thank you.

[yajra]

Just to confirm, you are using excel and not csv button? I mostly encounter this behavior when you export using CSV and then opening it on MS Excel.

[3s777]

I have same problem too.
I am export in xlsx but long numbers replaced with 4,91787E+11.

[yajra]

You can use export class https://yajrabox.com/docs/laravel-datatables/master/buttons-laravel-excel and adjust as needed.

[3s777]

Thank you
I am create AccountsExport
make
class AccountsExport extends DataTablesCollectionExport implements WithMapping
and add to my AccountDataTable
protected $exportClass = AccountsExport::class;
and add in AccountsExport
class AccountsExport extends DataTablesCollectionExport implements WithMapping
{
public function headings(): array
{
return [
'Name',
'Email',
];
}

public function map($row): array
{
    return [
        $row['name'],
        $row['email'],
    ];
}

}
but nothing is changing. Excel export work like before and don't use my AccountsExport file

[yajra]

Make sure you uses the latest version. Not sure on exact version when I added this.

[3s777]

I'm sure i use latest version. Maybe I should add $exportClass somewhere else in my Datatable file?

[3s777]

Maybe i should add $exportClass somewhere here Button::make('excel')->text('Excel') ?

[aslanonline]

put this in config folder and name it excel.php

`<?php

use Maatwebsite\Excel\Excel;

return [
'exports' => [

    /*
    |--------------------------------------------------------------------------
    | Chunk size
    |--------------------------------------------------------------------------
    |
    | When using FromQuery, the query is automatically chunked.
    | Here you can specify how big the chunk should be.
    |
    */
    'chunk_size'             => 1000,

    /*
    |--------------------------------------------------------------------------
    | Pre-calculate formulas during export
    |--------------------------------------------------------------------------
    */
    'pre_calculate_formulas' => false,

    /*
    |--------------------------------------------------------------------------
    | Enable strict null comparison
    |--------------------------------------------------------------------------
    |
    | When enabling strict null comparison empty cells ('') will
    | be added to the sheet.
    */
    'strict_null_comparison' => false,

    /*
    |--------------------------------------------------------------------------
    | CSV Settings
    |--------------------------------------------------------------------------
    |
    | Configure e.g. delimiter, enclosure and line ending for CSV exports.
    |
    */
    'csv'                    => [
        'delimiter'              => ',',
        'enclosure'              => '"',
        'line_ending'            => PHP_EOL,
        'use_bom'                => false,
        'include_separator_line' => false,
        'excel_compatibility'    => false,
    ],

    /*
    |--------------------------------------------------------------------------
    | Worksheet properties
    |--------------------------------------------------------------------------
    |
    | Configure e.g. default title, creator, subject,...
    |
    */
    'properties'             => [
        'creator'        => '',
        'lastModifiedBy' => '',
        'title'          => '',
        'description'    => '',
        'subject'        => '',
        'keywords'       => '',
        'category'       => '',
        'manager'        => '',
        'company'        => '',
    ],
],

'imports'            => [

    /*
    |--------------------------------------------------------------------------
    | Read Only
    |--------------------------------------------------------------------------
    |
    | When dealing with imports, you might only be interested in the
    | data that the sheet exists. By default we ignore all styles,
    | however if you want to do some logic based on style data
    | you can enable it by setting read_only to false.
    |
    */
    'read_only' => true,

    /*
    |--------------------------------------------------------------------------
    | Ignore Empty
    |--------------------------------------------------------------------------
    |
    | When dealing with imports, you might be interested in ignoring
    | rows that have null values or empty strings. By default rows
    | containing empty strings or empty values are not ignored but can be
    | ignored by enabling the setting ignore_empty to true.
    |
    */
    'ignore_empty' => false,

    /*
    |--------------------------------------------------------------------------
    | Heading Row Formatter
    |--------------------------------------------------------------------------
    |
    | Configure the heading row formatter.
    | Available options: none|slug|custom
    |
    */
    'heading_row' => [
        'formatter' => 'slug',
    ],

    /*
    |--------------------------------------------------------------------------
    | CSV Settings
    |--------------------------------------------------------------------------
    |
    | Configure e.g. delimiter, enclosure and line ending for CSV imports.
    |
    */
    'csv'         => [
        'delimiter'        => ',',
        'enclosure'        => '"',
        'escape_character' => '\\',
        'contiguous'       => false,
        'input_encoding'   => 'UTF-8',
    ],

    /*
    |--------------------------------------------------------------------------
    | Worksheet properties
    |--------------------------------------------------------------------------
    |
    | Configure e.g. default title, creator, subject,...
    |
    */
    'properties'  => [
        'creator'        => '',
        'lastModifiedBy' => '',
        'title'          => '',
        'description'    => '',
        'subject'        => '',
        'keywords'       => '',
        'category'       => '',
        'manager'        => '',
        'company'        => '',
    ],

],

/*
|--------------------------------------------------------------------------
| Extension detector
|--------------------------------------------------------------------------
|
| Configure here which writer/reader type should be used when the package
| needs to guess the correct type based on the extension alone.
|
*/
'extension_detector' => [
    'xlsx'     => Excel::XLSX,
    'xlsm'     => Excel::XLSX,
    'xltx'     => Excel::XLSX,
    'xltm'     => Excel::XLSX,
    'xls'      => Excel::XLS,
    'xlt'      => Excel::XLS,
    'ods'      => Excel::ODS,
    'ots'      => Excel::ODS,
    'slk'      => Excel::SLK,
    'xml'      => Excel::XML,
    'gnumeric' => Excel::GNUMERIC,
    'htm'      => Excel::HTML,
    'html'     => Excel::HTML,
    'csv'      => Excel::CSV,
    'tsv'      => Excel::TSV,

    /*
    |--------------------------------------------------------------------------
    | PDF Extension
    |--------------------------------------------------------------------------
    |
    | Configure here which Pdf driver should be used by default.
    | Available options: Excel::MPDF | Excel::TCPDF | Excel::DOMPDF
    |
    */
    'pdf'      => Excel::DOMPDF,
],

/*
|--------------------------------------------------------------------------
| Value Binder
|--------------------------------------------------------------------------
|
| PhpSpreadsheet offers a way to hook into the process of a value being
| written to a cell. In there some assumptions are made on how the
| value should be formatted. If you want to change those defaults,
| you can implement your own default value binder.
|
| Possible value binders:
|
| [x] Maatwebsite\Excel\DefaultValueBinder::class
| [x] PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class
| [x] PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::class
|
*/
'value_binder' => [
    'default' => PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class
],

'cache' => [
    /*
    |--------------------------------------------------------------------------
    | Default cell caching driver
    |--------------------------------------------------------------------------
    |
    | By default PhpSpreadsheet keeps all cell values in memory, however when
    | dealing with large files, this might result into memory issues. If you
    | want to mitigate that, you can configure a cell caching driver here.
    | When using the illuminate driver, it will store each value in a the
    | cache store. This can slow down the process, because it needs to
    | store each value. You can use the "batch" store if you want to
    | only persist to the store when the memory limit is reached.
    |
    | Drivers: memory|illuminate|batch
    |
    */
    'driver'     => 'memory',

    /*
    |--------------------------------------------------------------------------
    | Batch memory caching
    |--------------------------------------------------------------------------
    |
    | When dealing with the "batch" caching driver, it will only
    | persist to the store when the memory limit is reached.
    | Here you can tweak the memory limit to your liking.
    |
    */
    'batch'     => [
        'memory_limit' => 60000,
    ],

    /*
    |--------------------------------------------------------------------------
    | Illuminate cache
    |--------------------------------------------------------------------------
    |
    | When using the "illuminate" caching driver, it will automatically use
    | your default cache store. However if you prefer to have the cell
    | cache on a separate store, you can configure the store name here.
    | You can use any store defined in your cache config. When leaving
    | at "null" it will use the default store.
    |
    */
    'illuminate' => [
        'store' => null,
    ],
],

/*
|--------------------------------------------------------------------------
| Transaction Handler
|--------------------------------------------------------------------------
|
| By default the import is wrapped in a transaction. This is useful
| for when an import may fail and you want to retry it. With the
| transactions, the previous import gets rolled-back.
|
| You can disable the transaction handler by setting this to null.
| Or you can choose a custom made transaction handler here.
|
| Supported handlers: null|db
|
*/
'transactions' => [
    'handler' => 'db',
],

'temporary_files' => [

    /*
    |--------------------------------------------------------------------------
    | Local Temporary Path
    |--------------------------------------------------------------------------
    |
    | When exporting and importing files, we use a temporary file, before
    | storing reading or downloading. Here you can customize that path.
    |
    */
    'local_path'          => storage_path('framework/laravel-excel'),

    /*
    |--------------------------------------------------------------------------
    | Remote Temporary Disk
    |--------------------------------------------------------------------------
    |
    | When dealing with a multi server setup with queues in which you
    | cannot rely on having a shared local temporary path, you might
    | want to store the temporary file on a shared disk. During the
    | queue executing, we'll retrieve the temporary file from that
    | location instead. When left to null, it will always use
    | the local path. This setting only has effect when using
    | in conjunction with queued imports and exports.
    |
    */
    'remote_disk'         => null,
    'remote_prefix'       => null,

    /*
    |--------------------------------------------------------------------------
    | Force Resync
    |--------------------------------------------------------------------------
    |
    | When dealing with a multi server setup as above, it's possible
    | for the clean up that occurs after entire queue has been run to only
    | cleanup the server that the last AfterImportJob runs on. The rest of the server
    | would still have the local temporary file stored on it. In this case your
    | local storage limits can be exceeded and future imports won't be processed.
    | To mitigate this you can set this config value to be true, so that after every
    | queued chunk is processed the local temporary file is deleted on the server that
    | processed it.
    |
    */
    'force_resync_remote' => null,
],

];
`

[3s777]

i create this file and add code, but my numbers still 5,54871E+15 (((

[3s777]

maybe i need add this config file in provider? all maybe other file?
i need install maatwebsite/excel separately?

[Revoluz]

this works on my project

[benyaminl]

@3s777

I think this is best using the export concern format column from Laravel Excel

@see https://docs.laravel-excel.com/3.0/exports/column-formatting.html
@see https://docs.laravel-excel.com/2.1/reference-guide/formatting.html

So In Short you should try use this

<?php

namespace App\DataTables;

use Yajra\DataTables\Services\DataTablesExportHandler;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class AccountsDataTablesExportHandler extends DataTablesExportHandler
    implements WithColumnFormatting, ShouldAutoSize
{


    /**
     * @return array
     */
    public function columnFormats(): array
    {
        return [
            // The Assumption is this column is the number column
            'C' => NumberFormat::FORMAT_TEXT,
        ];
    }
}

EDIT : 2nd, if there are intelligent value binder, then

YOU NEED TO OVERWRITE

it!

@see https://docs.laravel-excel.com/3.1/exports/column-formatting.html#value-binders

<?php

namespace App\DataTables;

use Yajra\DataTables\Services\DataTablesExportHandler;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
// For Custom Value Binder
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

class AccountsDataTablesExportHandler extends DataTablesExportHandler
    implements WithColumnFormatting, ShouldAutoSize
{
    /**
     * THIS IS THE REAL WAY
     * To format the value from number to string, FORECFULLY
     * @param Cell $cell
     * @param mixed $value
     * @return bool
     * @throws Exception
     */
    public function bindValue(Cell $cell, $value) {
        // The column is expected to be Column F for...
        // The Phone Number
        if ($cell->getColumn() == "F") {
                $cell->setValueExplicit($value, DataType::TYPE_STRING2);

                return true;
        }

        // Duct tape, sadly, @see https://github.com/SpartnerNL/Laravel-Excel/blob/3.1/src/DefaultValueBinder.php
        $defaultValueBinder = new DefaultValueBinder();

        // else return default behavior
        return $defaultValueBinder->bindValue($cell, $value);
    }

}

The put on your DataTables Class

namespace App\DataTables;

use Yajra\DataTables\Services\DataTable;

class AccountsDataTable extends DataTable
{
    /**
     * Export class handler.
     *
     * @var class-string
     */
    protected string $exportClass = AccountsDataTablesExportHandler::class;
}

This inspired by https://laracasts.com/discuss/channels/laravel/formatted-exported-excel-file-on-laravel-excel-based-on-yajra-datatable

And the answer before. I think this already documented thoo in the docs

[yajra]

When dealing with large datasets, you may also consider using https://yajrabox.com/docs/laravel-datatables/10.0/exports-installation, but is built with OpenSpout, Queue, and Livewire.