Cell Currency Formatting

Topics: Developer Forum
Jan 14, 2011 at 10:31 AM

I am trying to format the cells with large numbers, having a "." every 3 digits and showing no cents / pennies whatever - ie so no decimal

eg

2.451.380 

 

How do i do that in PHPExcel ?

 

thanks

Coordinator
Jan 14, 2011 at 12:51 PM

Exactly as you'd do it in MS Excel: you set a number format mask of

"#,##0"
Jan 14, 2011 at 2:12 PM

er....the number format is "#.##0 _€"

so how can set the cell with it ?

i've found this in the documentaion

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

so i assume using it, but i can find no examples of user defined formatting

Coordinator
Jan 14, 2011 at 2:17 PM
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode("#.##0 _�");
Jan 24, 2011 at 11:32 AM
Edited Jan 24, 2011 at 1:40 PM

 

edit.. another weird thing is i am setting the format so $objPHPExcel->getActiveSheet()->getStyle('B3:Y46')->getNumberFormat()->setFormatCode("#.##0");

but in excel its #,##0
andy ideas ?
Coordinator
Jan 24, 2011 at 8:04 PM
soupdragon wrote:

 

edit.. another weird thing is i am setting the format so $objPHPExcel->getActiveSheet()->getStyle('B3:Y46')->getNumberFormat()->setFormatCode("#.##0");

but in excel its #,##0
andy ideas ?

 Excel stores format codes internally using , (comma) as a thousand separator and . (full stop) as a decimal separator. This may be different when viewed in Excel, which uses localization settings when masks and formatted numbers are displayed. At present, we don't support localization when setting number format masks, so you need to set them as the internal format.

Jan 25, 2011 at 7:45 AM

any ideas what i need then to use as the internal format so that it works ?

Coordinator
Jan 25, 2011 at 8:03 AM
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode("#,##0 _€");
Sep 9, 2013 at 5:38 PM
Edited Sep 9, 2013 at 11:55 PM
Using this format ($#,##0.00) does the following


data in cell 253444
shows as $253,444.00