PHPSpreadsheet autopopulating 0's in empty cells and the formulas are saved as string values

Question

I have to paste the value of variable $val in cell 'B3' in Sheet 0. After this, I have to export sheet1 as pdf.

But I can see that when I am converting sheet1 as pdf, the formulas are not printed 'as values' but they are printed as a string.

Moreover, 0's are getting populated in empty cells. Attaching screenshot of the same.

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("ExcelAutomating.xlsx");
$val= 506;
$sheet =  $spreadsheet->getSheet("0");  
$sheet->setCellValue('B3',$val);
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(true); 
$writer->save("ExcelAutomating.xlsx");
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
$writer->setSheetIndex(1);
$writer->setPreCalculateFormulas(true); 
$writer->save("{$val}.pdf");
?>

score 0 · Accepted Answer · answered Jun 15 '21 at 17:13

The auto-population of 0 in empty cells was solved by simply opening excel-> Click on file-> Options -> Advanced -> de-select the checkbox containing "Show a zero in cells that have zero value. And for formula, you need to make sure that all cells involved in, should be of same format. Click on cell then right click, then select format and cross check the if format are same.

PHPSpreadsheet autopopulating 0's in empty cells and the formulas are saved as string values

1 Answers1