Search code examples
phpphpexcelstring-formatting

PHPExcel can't show currency symbol

I was asked to edit a file which is using the deprecated PHPExcel library.. I'm not allowed to update to PHPSpreadSheet so I need to sort this out using PHPExcel. I'm trying to show the currency symbol with the following code:

$activeSheet->getStyle($col.$row)
            ->getNumberFormat()
            ->setFormatCode('_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)');

I also tried with the format code € ####0,00 and € ##,##0.00 with no luck..

The weird behaviour happens when I open the file, I click on the cell and hit return key without making any change, then the currency symbol is displayed correctly. How can I get this to work without any manual intervention?

Thanks

Solution

  • I solved the issue. The problem was given by the decimals and thousands separator, which were mismatching from the used format code.

    $price = number_format( $raw_price, 2, ".", "");
$activeSheet->setCellValue("G$row", $price)
$activeSheet->getStyle("G$row")->getNumberFormat()->setFormatCode( '"€" ###0,00_-' );

    I just had to edit as follows:

    $price = number_format( $raw_price, 2, ",", "");