0

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?

Alex
  • 778
  • 3
  • 12
  • 27

1 Answers1

2

Try like this? 

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

Excel supports a maximum 15 significant figures

Shobi
  • 10,374
  • 6
  • 46
  • 82
  • `NumberFormat::FORMAT_NUMBER_00`'s value is `'0.00'`, so pretty much the same as what you suggested which sadly doesn't work. – Alex Oct 31 '19 at 11:53
  • Well... I think you are better off with `FORMAT_NUMBER_00 ` even though excel shows only the significant figures, you can format the entire column and change the number format to show enough decimals. Here are some info, https://stackoverflow.com/questions/44680465/how-to-change-the-default-number-format-in-excel. You can google around to find a way to show the default number of decimal places in excel. – Shobi Oct 31 '19 at 12:14