Currency shows strange characters when viewed using MS excel

[jonjieviduya]

Summary of problem or feature request

When viewing the exported file, the Philippine Peso (₱) currency shows strange characters: â,±. I tried both ₱ and the ₱ but both of these are not working and still show the same strange characters.

Code snippet of problem

->addColumn('Order Amount', function ($payment) {
    return '₱' . number_format((float) $payment->amount, 2);
})

System details

• Operating System: win10
• PHP Version: 8.1
• Laravel Version: 8
• Laravel-Datatables Version: 9.11

[yajra]

You might need to add Order Amount to the raw columns.

Or you can use the render option via js to handle the money formatting.

[yajra]

Oh, the context was on export ~~ I will have to review this one. I think the formatting should be handled on the Excel cell format level for this to work.

[yajra]

There are new features released via https://github.com/yajra/laravel-datatables-buttons/pull/186 and https://github.com/yajra/laravel-datatables-buttons/pull/187. That might help in your case.

[jonjieviduya]

@yajra yes it is for export. Using these links, how can I format the currency? BTW it is working when you open it using google sheet. The issue only occurs when opening in ms excel.

There are new features released via yajra/laravel-datatables-buttons#186 and yajra/laravel-datatables-buttons#187. That might help in your case.

[yajra]

Try using exportFormat('₱#,##0.00'). This is how I usually check Excel.

[jonjieviduya]

@yajra I still got the same output.

[jonjieviduya]

Here's the dataTable method btw @yajra

public function dataTable($query)
{
    return datatables()
        ->eloquent($query)
        ->addColumn('Select', function ($payment) {
            return '<input type="checkbox" name="record_id[]" value="'. $payment->id .'">';
        })
        ->addColumn('Order #', function ($payment) {
            return '<a href="'. route('admin.orders.default.bulk', $payment->bulk_order_key) . '" target="_blank">' . $payment->bulk_order_key . '</a>';
        })
        ->addColumn('Account', function ($payment) {
            return $payment->bank;
        })
        ->addColumn('Bank Name', function ($payment) {
            return $payment->bank_name;
        })
        ->addColumn('Account Name', function ($payment) {
            return $payment->account_name;
        })
        ->addColumn('Status', function ($payment) {
            return $payment->status;
        })
        ->addColumn('Payment Date', function ($payment) {
            return date('M d, Y', strtotime($payment->payment_date));
        })
        ->addColumn('Order Amount', function ($payment) {
            return $payment->amount;
        })
        ->addColumn('Deposit Slip', function ($payment) {
            return '<a href="'. route('admin.deposit_slip', ['file' => $payment->id]) . '" target="_blank">View</a>';
        })
        ->addColumn('Deposit Slip Link', function ($payment) {
            return route('admin.deposit_slip', ['file' => $payment->id]);
        })
        ->rawColumns(['Select', 'Order #', 'Order Amount', 'Deposit Slip']);
}

[yajra]

I just tested this and it works fine for me:

Id	Account UUID	Account Id	Member UUID	Balance	Date Created
12	871d932d-09b8-404c-917d-cc1cfde8873d	79133479	a09eecb3-4603-411c-91d8-dfa534e9725b	₱ 0.00	2024-09-22
11	cb0db73c-acf0-4021-9edc-1d8e57f9c5d0	38123042	938d9a51-d380-42c6-9505-fa3c2f224fe1	₱ 0.00	2024-09-22

DecimalMoneyColumn::make('balance')
    ->exportRender(fn($row, $amount) => '₱ '.number_format($amount / 100, 2)),

Here is my Decimal money class, need this since I used int to store the amount:

class DecimalMoneyColumn extends Column
{
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);

        $this->addClass('text-right')
            ->render('(data/100).toFixed(2).replace(/\B(?<!\.\d*)(?=(\d{3})+(?!\d))/g, ",")');
    }
}

[jonjieviduya]

@yajra what is this code for? Sorry I'm a bit confused since I am using float instead of int

$this->addClass('text-right')
            ->render('(data/100).toFixed(2).replace(/\B(?<!\.\d*)(?=(\d{3})+(?!\d))/g, ",")');

Also, what method should I use, render() or exportRender? Can you share the documentation for this?

P.S. I am using the Laravel-Datatables version 9.11

[yajra]

Oh Laravel 9, I think exportRender is not available there. I suggest you check Laravel Excel on how to render/format a cell.

Tested on Laravel 11 and works fine. I highly suggest you upgrade if possible.

[jonjieviduya]

@yajra the laravel version is v8 and the datatables is v9.

Laravel Version: 8
Laravel-Datatables Version: 9.11

[jonjieviduya]

Are you using Laravel Excel for this project @yajra?

[jonjieviduya]

Also, it terms of upgrading the package from v9 to v10 to v11, I think the documentation is no longer updated.

https://yajrabox.com/docs/laravel-datatables/10.0/upgrade

@yajra

[yajra]

Are you using Laravel Excel for this project @yajra?

Yes but for small export only. However, for newer projects, I skipped Laravel Excel and moved to OpenSpout / fastexcel. For thousands to millions of exports, you can use https://github.com/yajra/laravel-datatables-export.

[yajra]

Also, it terms of upgrading the package from v9 to v10 to v11, I think the documentation is no longer updated.

https://yajrabox.com/docs/laravel-datatables/10.0/upgrade

@yajra

Thanks for noticing, upgrading from 10 to 11 is just a matter of changing the version in composer. No major breaking change.

[jonjieviduya]

@yajra Any breaking changes from yajra-datatables v9 to v11? I'm planning to upgrade its version from 9 to 11.

My system setup details in case you need it: PHP Version: 8.1 Laravel Version: 8 Laravel-Datatables Version: 9.11

[yajra]

None that I know of. The version change is mostly due to aligning to the Laravel Framework version.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.