Tiny Tiny - 2 months ago 19
PHP Question

Always display specified number of decimal places in Excel

I need to display two decimal places in Excel (xlsx). I'm using

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

Comments