laravel maatwebsite excel export currency row

Question

Im using the laravel maatwebsite package to export an excel sheet. One column contains euro currency, so im trying to make a currency column for the data.

my code for the columns:

    $sheet->setColumnFormat(array(
        'G' =>  \PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00,
        'H' =>  "[$EUR ]#,##0.00_-",
        'I' =>  \PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE,
    ));

with no luck, when for example 5 (without any decimals) is inserted in one of these columns the data looks like 5 instead of € 5.00

How is the formating set in the correct way?

Davron Achilov · Answer 1 · 2022-06-01T06:07:21.367

4

You can add data-format attribute to<td>
Example:

<td data-format="$#,##0_-">Price</td>
<td data-format="0.00">Price</td>
<td data-format="0%">Percentage</td>
<td data-format="dd/mm/yy">Date</td>
<td data-format="h:mm:ss AM/PM">Date/Time</td>

edited Jun 01 '22 at 06:07

answered Aug 01 '19 at 14:50

Davron Achilov

536
4
14

Excellent answer. Where did you find this in documentation? – Hmerman6006 May 25 '22 at 20:34
@Davron Achilov, is this working on laravel export to excel? – Rosidin Bima Sep 26 '22 at 07:52
@RosidinBima, yes, it's working for export – Davron Achilov Oct 17 '22 at 11:22

score 0 · Answer 2 · answered Jul 20 '16 at 03:17

0

Hey im not sure about proper method but this is simple trick :P

foreach($jobs  as $index =>$row){
$data=array($index+1,$row->invoice_no,$row->customer_name,'€'.$row->amount);
$sheet->fromArray(array($data),null,'A6',false,false);
  }

$sheet->appendRow(8, array(
           'TOTAL','€'.$sum 
));

answered Jul 20 '16 at 03:17

Hamelraj

4,676
4
19
42

If you use this trick, the value will be converted to a string. You can't make calculations in excel with strings. – D. J. May 13 '19 at 07:43
Yes you can't but you can do calculation in the function level not in a sheet . – Hamelraj May 13 '19 at 08:23
I've added an answer where the value will be formatted to a accounting value. – D. J. May 13 '19 at 09:19

score 0 · Answer 3 · answered May 13 '19 at 09:15

With the above answer your value will be converted to a string value in Excel. I've used the code snipped below to set the format on a accounting value.

$sheet->setColumnFormat(array(
    'C2' => '[$€-2] * #,##0.00;-[$€-2] * #,##0.00_-;_-[$€-2] * "-"??_-;_-@_-',
));

laravel maatwebsite excel export currency row

3 Answers3

Linked