Format Number with "," as decimal separator

Topics: Developer Forum
Sep 6, 2008 at 2:05 PM
Hi :

In this thread i read about formating  numbers.

This is code that thread give:

I try this:


and :




The numbers are formated lik this:
2203,20 and negative -50,40 but are formated as text, and align left, and not as number.

Where is brong?

Can you give my some help

Tanks in advance


Sep 6, 2008 at 2:11 PM
What component are you using? Excel5 writer or Excel2007 writer? Which version of PHPExcel? Also, can you please post a more complete script where we can see how you are setting the cell values?
Sep 6, 2008 at 5:09 PM



= 4;



( !$rs->EOF )




$objPHPExcel->getActiveSheet()->setCellValue('A' . $nRow, FormatNumProduto( $rs->fields['idproduto'] ) );



$objPHPExcel->getActiveSheet()->setCellValue('B' . $nRow, utf8_encode($rs->fields['modelo'] ));




$objPHPExcel->getActiveSheet()->getStyle('C' . $nRow)->getNumberFormat()->setFormatCode("##0;(-##0)");



$objPHPExcel->getActiveSheet()->getStyle('E' . $nRow)->getNumberFormat()->setFormatCode('#########0,00;(-#########0,00)');


$objPHPExcel->getActiveSheet()->getStyle('F' . $nRow)->getNumberFormat()->setFormatCode('#.##0,00;[Red](#.##0,00)');



$objPHPExcel->getActiveSheet()->setCellValue('C' . $nRow, $rs->fields['qtd']);




if ( $rs->fields['idmoeda'] == 2 ) {



$nStockTotalUS += $rs->fields['total'];



$objPHPExcel->getActiveSheet()->setCellValue('E' . $nRow, number_format( $rs->fields['total'], 2, ',', '') );



$objPHPExcel->getActiveSheet()->setCellValue('F' . $nRow, 0);




else {



$nStockTotalBR += $rs->fields['total'];



$objPHPExcel->getActiveSheet()->setCellValue('E' . $nRow, 0);



I try this :


$objPHPExcel->getActiveSheet()->setCellValue('F' . $nRow$rs->fields['total'] );
and this :



->getActiveSheet()->setCellValue('F' . $nRow, number_format( $rs->fields['total'], 2, ',', '') );












E column are us dolars, F column are local money.

Sep 6, 2008 at 5:11 PM
when i click in a cell bad formated and then click enter to go out,  excel put the right format in the cell.
Sep 6, 2008 at 5:30 PM
sorry, my writer is Excel2007
Sep 6, 2008 at 6:54 PM
I took a look at your script. Here is what you need to do:

Download latest source of PHPExcel.
After PHPExcel 1.6.3 was released a bug was discovered where negative number would be stored as text

When you want to set numerical cell values never do like this:
$cell->setValue('123,45'); // wrong: will become text

Instead do like this:
$cell->setValue('123.45'); // ok: will become number
$cell->setValue(123.45); // also ok: will become number

When you want to set the number format code from PHPExcel always use '.' (point) to indicate decimal separator, and always use ',' (comma) to indicate thousands separator. This is true even when you really want decimal separator to be a ',' and thousands separator to be '.' when you open the spreadsheet in MS Office Excel. But it is the application that decides how to render the decimal separator. Thus on a Spanish platform one may see a number as 1.030,45 when one opens the spreadsheet while for the same spreadsheet the number will be seen as 1,030.45 on an English platform.

Caution: Therefore you cannot just copy and paste the number format as seen in MS Office Excel and use it in your PHP script. What you see in the applications dialog for setting number format is platform dependent. You need to use the applications internal format which is as described above.

Example, working script with latest source. (PHPExcel 1.6.3 will not work for negative numbers)

$ws->setCellValue('F1', 1030.45);

// will show as 1.030,45 in MS Office Excel on Spanish platform
// will show as 1,030.45 in MS Office Excel on English platform
Sep 6, 2008 at 9:21 PM

I test download the last change set Number 17115.

I try this :

$objPHPExcel->getActiveSheet()->setCellValue('F4', $rs->fields['stock'] );

Give me this error :
Notice: Undefined index: 3628.80 in D:\webroot\homeinf\util\phpexcel\PHPExcel\Writer\Excel2007\Worksheet.php on line 989

3628.80 is the value to be set in the cell.

this is the code that give problem :

case 'n':   // Numeric
 if (PHPExcel_Shared_Date::isDateTime($pCell)) {
  $dateValue = $pCell->getValue();
  if (is_string($dateValue)) {
   // Error string
   $objWriter->writeElement('v', $pFlippedStringTable[$dateValue]);
  } elseif (!is_float($dateValue)) {
   // PHP serialized date/time or date/time object
   $objWriter->writeElement('v', PHPExcel_Shared_Date::PHPToExcel($dateValue));
  } else {
   // Excel serialized date/time
   $objWriter->writeElement('v', $dateValue);
 } else {
  $objWriter->writeElement('v', $pCell->getValue());

File : Classes\PHPExcel\Writer\Excel2007\Worksheet.php
Line Number : 989

Without format has no errors.

This work, but i have not format.

$objPHPExcel->getActiveSheet()->setCellValue('F4', $rs->fields['stock'] );

Sep 6, 2008 at 9:40 PM
Thank you. I verified the issue. This is happening when one supplies the value as a string. I guess your $rs->fields['stock'] variable is a string and not a float.

I was not aware of this problem. Will investigate further and post a bug report if necessary.

If you need a temporary quick fix, use this:

$objPHPExcel->getActiveSheet()->setCellValue('F4', (float) $rs->fields['stock'] );

Sep 6, 2008 at 10:42 PM
Please continue the discussion here:
Sep 17, 2009 at 1:54 PM


I continue the discussion here, because my concern is not about date formatting, missing (float) setup or the [Color] notation. My question is: Is it possible to format a number into English format (12,345.67) using a German Microsoft Excel version to open it? German notation is (12.345,67). Because of the special handling for . and , it's seems to be impossible for me, even using the number format setup in Excel itself. When I try this setup: #,##0.00 I got this output: 123456,67000. After submitting this setup, I reopened the number format setup and got this setup: 0,00000 (which explains the number formatting).

It's very important for me, that the produced number format will not be transformed, because we want to produce localization-dependend Excel files. I hope this won't end up in using strings instead.


Thanks in advance and also for this nice library :)

Sep 17, 2009 at 2:41 PM


>> Is it possible to format a number into English format (12,345.67) using a German
>> Microsoft Excel version to open it?

As far as I am aware it is not possible. From the Excel document itself you can control whether or not to use thousands or decimal separators, but you cannot control the actual characters to use. The actual characters to use for thousand and decimal separators are decided by the application used to open the workbook, and the user can at any time change those settings.

I think we should appreciate that the author cannot define characters to be used for thousand and decimal separators. The real question is why you are trying to control that?

The correct approach is to use


Now, when a German user open the workbook he will probably see 12.345,67 unless he has changed his locale, or settings in MS Office Excel. English users will see 12,345.67.

Note also that when the German user inspects the number format setup he will see #.##0,00 in Excel even though you used #,##0.00. That is because even the format mask itself is being localized by MS Office Excel.

Sep 17, 2009 at 3:00 PM
Edited Sep 17, 2009 at 3:01 PM
koyama wrote:

The real question is why you are trying to control that?

Thanks for your quick answer.

Long answer: We generate DOCX documents for a Federal Office. They provide those documents in different languages. The reports include Excel tables. So we generate those Excel files, which include translations for some cells and of course number formatting. The generated report can never be perfect. So the guys from the Fed. Office will add or remove some stuff, change a thing here and there. At the end, there is f.e. a French guy, editing the English stuff in the Excel table and moves the table back to the (still English) Word document. It's not acceptable, that the English Word document now contains French number formatting. That's what I'm afraid of. The document will be printed if it's reviewed successfully.

But maybe that's more an Excel than a PHPExcel problem, right? :(

Sep 17, 2009 at 3:31 PM


Ok, I see, you may have a special situation. You're right, this is really an Excel problem. But if you find some technique to solve the problem, please share it anyway.

Sep 17, 2009 at 3:38 PM
Edited Sep 17, 2009 at 3:38 PM

I will (in case), thanks so far.

Sep 18, 2009 at 12:33 PM
Edited Sep 18, 2009 at 12:34 PM

I tried a lot,

- using similar utf 8 characters such as Arabic Decimal Separator (hex d9 ab) for comma or Tibetian Mark Ngas Bzung Sgor Rtags (hex e0 bc b7) as dot (problem is still that the native decimal delimiter is required, if you want to show decimals)

- escape the separators with backslash

- adding a backspace control character after the separators

- adding xml:lang="enUS" to the <numFmt> in styles.xml

nothing works. So I will add "output as string" or "output as number" radioboxes to the export functionality with a little explanation.

Ultimate alternative is to switch the Windows localization setup or maybe use macros, but both are not options in my case.