CODEWITHSUNDEEP
phpphpspreadsheetphpoffice
JamesG
JamesG

Reputation: 1698

PHPSpreadsheet and writing large numbers of decimal places

I'm having difficulty with PHPSpreadsheet when creating an XLSX file and attempting to write large numbers of decimal places to numerical values.

PHPSpreadsheet is rounding my 14 decimal place numbers, but I need them stored exactly as presented.

I'm using setFormatCode('0.00000000000000') as described in the documentation, but it's not working as I would expect.

Here's my test code:

<?php

require __DIR__ . '/vendor/autoload.php'; // Installed via composer

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$array = [
         0.03790728347833,
      1345.28748532874927,
    121345.18248762894914, // all 14 DP
];

$format = '0.00000000000000'; // 14 DP

// write the data
$spreadsheet->getActiveSheet()
    ->fromArray($array, null, 'A1');

// Format the cells
$spreadsheet->getActiveSheet()->getStyle('A1:C1')->getNumberFormat()->setFormatCode($format);

// Column sizing
foreach(range('A','C') as $columnID)
{
    $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save("test.xlsx");

// A1 = 0.03790728347833 - same
// A2 = 1345.28748532870000 - rounded
// A3 = 121345.18248763000000 - rounded

Could anyone provide a way to store this many decimal places without rounding?

Upvotes: 1

Views: 3294

Answers (1)

JamesG
JamesG

Reputation: 1698

This appears to be a limitation of Excel rather than PHPSpreadsheet.

Excel is limited to 15 significant figures according to Wikipedia.

Upvotes: 2

Related Questions