Currency Euro sign

Topics: Developer Forum, User Forum
Sep 14, 2011 at 12:54 PM

Hello all,

I've been searching the discussions for quite some time now and cannot find the solution to my problem.

I'm trying to add the euro sign € in front of my financial numbers.

I've read that this has something to do with the charset so I've forced my php page to UTF-8 with this code:
mb_internal_encoding("utf-8");

I tried different ways of adding the euro sign to my NumberFormat:
- $objPHPExcel->getActiveSheet()->getStyle('G14')->getNumberFormat()->setFormatCode("€ #,##0.00");
- $objPHPExcel->getActiveSheet()->getStyle('G14')->getNumberFormat()->setFormatCode("_€ #,##0.00");

When generating the sheet is says:
Notice: iconv_strlen(): Detected an illegal character in input string in /classes/PHPExcel/Shared/String.php on line 544


Can any of you explain to me how to set the euro sign in a proper way? And if this is the way to set UTF-8 in my PHP page?

Thanks in advanced!!

Sep 14, 2011 at 1:25 PM

Also tried to set the string to UTF-8:

$currencyFormat = iconv("ISO-8859-1", "UTF-8", '€ #,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('G14')->getNumberFormat()->setFormatCode($currencyFormat);

This opens the sheet without a warning but does not display the euro sign €

Sep 15, 2011 at 7:44 AM

I solved the puzzle.

Here's what I did:
$objPHPExcel->getActiveSheet()->SetCellValue('G14', '=CHAR(128) & "125"');

Using the code snippit above gives you the value €125 in cell G14

Sep 15, 2011 at 10:32 AM

Unfortunately, that is only half the problem - although that works, you are converting the values to strings, so sum (G14:G15) returns 0 (for example).

There must be a solution.....

Sep 15, 2011 at 11:24 AM

I have found the answer (no idea why it is required) - use:

$currencyFormat =  iconv('Windows-1252','utf-8','#,#0.##\€;[Red]-#,#0.##\€');

I can;t understand why that works because

var_dump(iconv_get_encoding('all'));

confirms that the input_encoding on my linux server is ISO-8859-1

Sep 15, 2011 at 11:36 AM

I'm doing all my calculations in PHP so I don't use excel to calc numbers, that's why it works for me

Oct 26, 2011 at 2:30 PM

There is another way too - creating a template spreadsheet in Excel (I used Excel 2010) with just one cell (I used A1) containing a number and formatted to display the Euro symbol. You can then duplicate the style in PHPExcel to any cells in the spreadsheet you're working on where the symbol is needed.
Here's some code to illustrate (done with CodeIgniter, in case any of the syntax seems weird):

//set template and new spreadsheet locations
$templatelocation = '/testdata/excel_templates/euro_cell_template.xls';
$filename = 'test_spreadsheet';                                        
$excelfilelocation = '/testdata/my_spreadsheets/' . $filename;

//load the CodeIgniter Excel library and create the spreadsheet
$this->load->library('excel');
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$this->excel->workbook->createSheet();

//set some test currency data
$this->excel->workbook->getActiveSheet()->setCellValue('C3', 300);
$this->excel->workbook->getActiveSheet()->setCellValue('E5', 400);
               
//get style from template to add the Euro symbol to test data in new spreadsheet
$this->excel->workbook->getActiveSheet()->duplicateStyle($objReader->load($template_location)->getActiveSheet()->getStyle('A1'), 'C3:E5');

//add some general data to the spreadsheet
$this->excel->workbook->getProperties()->setCreator("Online Tool");
$this->excel->workbook->getProperties()->setTitle("Testing Euro currency formatting in PHPExcel");
$this->excel->workbook->getProperties()->setSubject("Euro formatted cells");

//generate it
$objWriter = PHPExcel_IOFactory::createWriter($this->excel->workbook, 'Excel2007');
$objWriter->save($excelfilelocation . '.xlsx');

It all boils down to the line where duplicateStyle is used. Hope this helps!

Apr 20, 2015 at 8:16 AM
Edited Apr 20, 2015 at 8:17 AM
enclose the currency symbol in double quote (")
like this:
a. if u enclosed the format pattern in double quote, escape it by adding backslash before the double quote that enclosed ur format symbol
$objPHPExcel->getActiveSheet()->getStyle('G14')->getNumberFormat()->setFormatCode(" \"€\" #,##0.00"); 
b. if u enclosed the format pattern in single quote
$objPHPExcel->getActiveSheet()->getStyle('G14')->getNumberFormat()->setFormatCode(' "€" #,##0.00'); 
whatever option above u chose, the result will be like this: € 3,999.22
Feb 26 at 7:56 AM
I use:
$currencyFormat = iconv("Windows-1252", "UTF-8", '€ #,##0.00');
$objPHPExcel->getActiveSheet()->getStyle($colonna.'6')->getNumberFormat()->setFormatCode($currencyFormat);