CODEWITHSUNDEEP
phpexcelsymfonyphpspreadsheet
Alex
Alex

Reputation: 854

PHPSpreadsheet not showing decimal numbers, despite using formatting

I'm working on an Excel export where each row needs to have a percentage value, rounded to one decimal place. After exporting the spreadsheet, users need to be able to use those values for calculations inside the spreadsheet, hence why I can't simply format them as strings (or percentages, Excel doesn't like calculations using percentages for some reason). I've tried a few different things, but none of them work;

$sheet->getCell($coord)->setValueExplicit(
    $amount['percentage'],
    DataType::TYPE_NUMERIC
);

in which $coord is the required cell (for example 'A1') and $amount['percentage'] is a float like 45.2.

Another thing I've tried (and got straight from the PHPSpreadsheet documentation);

$sheet->setCellValue($coord, $amount['percentage']);
$sheet->getStyle($coord)
    ->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_NUMBER_00);

In both cases the number is added as a whole number without decimal places (so 45 instead of 45.2), the decimal number only shows when clicking the "Increase Decimal" button in excel.

If I change DataType::TYPE_NUMERIC to DataType::TYPE_STRING the number is displayed the way I want, but since it's printed as a string it can't be used for further calculations.

Is it at all possible to always display the decimal number after exporting?

Upvotes: 1

Views: 4955

Answers (1)

Shobi
Shobi

Reputation: 11461

Try like this? 

$sheet->getStyle($coord)
    ->getNumberFormat()
    ->setFormatCode('0.000000');

Excel supports a maximum 15 significant figures

Upvotes: 3

Related Questions