Always display specified number of decimal places in Excel

Question

I need to display two decimal places in Excel (xlsx). I'm using PHPExcel. In PHP, I can use something like the following to display only the specified number of decimal paces.

echo sprintf("%0.2f", $row['price']);

which always displays two decimal places even though the value contains no decimal point i.e if the value of $row['price'] is 1500, it will echo 1500.00. I need to write the same value to excel. I tried

$sheet->setCellValue("A1", sprintf("%0.2f", $row['price']));

but it displays 1500 instead of displaying 1500.00 (because Excel automatically assumes it to be a numeric value and the part after the decimal point i.e .00 in this case is truncated).

I also tried the following

$sheet->getStyle("A1")->getNumberFormat()->setFormatCode('0.00');

but it's not sufficient to achieve what is specified.

How can I (always) display the specified number of decimal places in Excel using PHPExcel?

score 34 · Accepted Answer · answered May 31 '12 at 12:08

34

$sheet->getStyle("A1")->getNumberFormat()->setFormatCode('0.00');

should work, as long as the value in cell A1 is a number and not a formatted string... don't try to force Excel formatting by using sprintf, simply use the format codes.

answered May 31 '12 at 12:08

Mark Baker

209,507
32
346
385

is it ok to use setFormatCode('###,###,###,##0.00'); to add commas to the mix or there a better way? – chris Oct 13 '13 at 06:20
1

It's perfectly valid to us setFormatCode('#,##0.00'); to indicate using a thousands separator - or pretty much any format code that MS Excel itself uses – Mark Baker Oct 13 '13 at 06:55

score 5 · Answer 2 · edited Nov 19 '13 at 14:08

5

Formatted as number with comma. Assuming that value is not exceeded by 9 digits.

$sheet->getStyle("A1")->getNumberFormat()->setFormatCode('###,###,###.##');

edited Nov 19 '13 at 14:08

Tiny

27,221
105
339
599

answered Jul 10 '13 at 08:59

robert.dejesica

86
1
3

score 4 · Answer 3 · edited Oct 04 '16 at 16:06

4

You can also do this:

$sheet->setCellValue("A1",sprintf("%0.2f",$row['price']),true)->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

Adding 'true' at setCellValue, you refer directly to the cell instead of the worksheet and you can call the getStyle method without the cell value.

edited Oct 04 '16 at 16:06

Petter Friberg

21,252
9
60
109

answered Oct 04 '16 at 16:04

Ichigo79

41
2

score 3 · Answer 4 · answered Aug 12 '13 at 17:26

If you want some special characters just use html_entity_decode:

$currencyFormat = html_entity_decode("€ 0,0.00",ENT_QUOTES,'UTF-8');
$objPHPExcel->getActiveSheet()
            ->getStyle("A1")
            ->getNumberFormat()->setFormatCode($currencyFormat);

score 3 · Answer 5 · edited Sep 08 '16 at 10:37

3

$objPHPExcel->getActiveSheet()->getStyle('ce')->getNumberFormat()->setFormatCode('0.00');

edited Sep 08 '16 at 10:37

Christian

27,509
17
111
155

answered Jul 25 '15 at 19:00

Lavanya

31
1

simhumileco · Answer 6 · 2020-07-06T13:11:51.897

2

Try that:

$sheet->getStyle("A1")->getNumberFormat()->setFormatCode('### ### ### ##0.00');

it works.

edited Jul 06 '20 at 13:11

answered Jun 12 '17 at 13:38

simhumileco

31,877
16
137
115

Always display specified number of decimal places in Excel

6 Answers6