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?
By using a button: Select the cells that you want to format. On the Home tab, click Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.
Create a new workbook, press Ctrl+A to select all cells in the sheet, right click and choose Format Cells. In Number tab, select Number section, the default decimal places should be 2, if not, change it to 2. Select the checkbox of "Use 1000 Separator(,)". Click OK.
Options -> Advanced -> UN-CHECK "Automatically insert a decimal point" Options -> Advanced -> UN-CHECK "Extend data range formats and formulas"
$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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With