How to set money format in PhpSpreadSheet?

Question

I'm using PHPSpreadSheet and I want to set money format for a cell. In PHPExcel you can do this by using the following lines...

$this->phpExcelObject->getActiveSheet()
     ->getStyle('D4')
     ->getNumberFormat()
     ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

I tried to this in PHPSpreadSheet, this is what i did...

$this->spreadsheet->getActiveSheet()
     ->getStyle('D4')
     ->getNumberFormat()
     ->setFormatCode('$ #,##0.00');

But it doesn't work. Any ideas? or am I doing it wrong? Thanks in advance.

score 26 · Answer 1 · answered Oct 30 '18 at 20:07

26

I was able to do the following with PHPSpreadsheet:

$spreadsheet->getActiveSheet()
    ->getStyle($thisCol)
    ->getNumberFormat()
    ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

The format code for FORMAT_CURRENCY_USD_SIMPLE == '"$"#,##0.00_-'

answered Oct 30 '18 at 20:07

Shawn Northrop

5,826
6
42
80

What will be the format code if i wanted XXXXXXXX to be transformed to X,XX,XX,XXX.00. instead of XX,XXX,XXX.00 ? – Abhinav Keshri Jun 18 '21 at 03:14

Black · Answer 2 · 2019-12-10T11:23:43.107

You can use this, if you are working with applyFromArray:

$sheet = $spreadsheet->getActiveSheet();

$sheet->getStyle($thisCol)->applyFromArray(

    'numberFormat' => [
        'formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR
    ]
);

Otherwise use it like this:

$sheet = $spreadsheet->getActiveSheet();
$sheet->getStyle($thisCol)
      ->getNumberFormat()
      ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR);

Use FORMAT_CURRENCY_USD for dollar

Note: You can also add _SIMPLE e.g. FORMAT_CURRENCY_EUR_SIMPLE if you like to show two decimal point values e.g. 12,62 € instead of 12 €

Checkout the class \PhpOffice\PhpSpreadsheet\Style\NumberFormat to find out all possible values.

The class is stored in vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Style\NumberFormat.php

score -1 · Answer 3 · answered Sep 11 '18 at 18:37

Try this:

<?php

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Cell\DataType;

$xls   = new Spreadsheet();
$sheet = $xls->getActiveSheet();

$sheet->getStyle('D4')->getNumberFormat()->setFormatCode('###,###,###.##');
$sheet->setCellValueExplicit('D4', $your_value, DataType::TYPE_NUMERIC);

score -7 · Accepted Answer · answered Sep 11 '18 at 21:38

I couldn't do it with PHPSpreadSheet. In case someone runs with the same problem, this is what I did (not a proper solution but it's something).

I created a function to return the value with the money symbol.

public function formatValueMoney($number){
  $val = number_format($number,2,".",",");
  $setVal = '$'.$val;

  return $setVal;
}

$int = formatValueMoney(34567898765);
echo $int;

and this : $34,567,898,765.00 is the result. i just added this in the cell.

How to set money format in PhpSpreadSheet?

4 Answers4