John Davidson

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

0 comments
Message:


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");
?>


My Answer:
PHPSpreadsheet does not have a built-in feature for autopopulating cells with a specific value. However, you can achieve this by using PHP code to loop through the cells and set the desired value. Here is an example of how you can autopopulate cells with the value "0":

php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set the value "0" in all cells from A1 to Z10
for ($row = 1; $row <= 10; $row++) {
for ($col = 'A'; $col <= 'Z'; $col++) {
$cell = $col . $row;
$sheet->setCellValue($cell, 0);
}
}

$writer = new Xlsx($spreadsheet);
$writer->save('autopopulate.xlsx');


This code will create a new Excel file with all cells from A1 to Z10 populated with the value "0". You can adjust the range of cells and the value as needed for your specific requirements.

Rate this post

3 of 5 based on 6648 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us