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?

Answer 1

$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.