Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Always display specified number of decimal places in Excel

Tags:

php

phpexcel

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?

like image 874
Tiny Avatar asked May 31 '12 11:05

Tiny


People also ask

How do you set a certain number of decimal places in Excel?

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.

How do I make Excel default to 2 decimal places?

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.

How do I get Excel to stop changing decimal places?

Options -> Advanced -> UN-CHECK "Automatically insert a decimal point" Options -> Advanced -> UN-CHECK "Extend data range formats and formulas"


1 Answers

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

like image 53
Mark Baker Avatar answered Oct 18 '22 19:10

Mark Baker