HomeLanguagesPHP | Spreadsheet | Setting a date and/or time value in a...

PHP | Spreadsheet | Setting a date and/or time value in a cell

In PHP Spreadsheet, the date or time values are stored in an excel sheet in the form of timestamp which is a floating point value. So in order to store date/time in a human-readable format, need to calculate the correct excel timestamp and finally set a number format mask.

Example:




<?php
  
// PHP program to set a date time value in excel sheet
require_once('vendor/autoload.php');
  
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
   
// Creates New Spreadsheet
$spreadsheet = new Spreadsheet(); 
  
// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet(); 
   
// Set the number format mask so that the excel timestamp 
// will be displayed as a human-readable date/time
$spreadsheet->getActiveSheet()->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode(
    \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
    );
   
// Get current date and timestamp
// Convert to an Excel date/time
$dateTime = time(); 
$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(
                  $dateTime ); 
   
// Set cell A1 with the Formatted date/time value
$sheet->setCellValue('A1',$excelDateValue);
   
// Write an .xlsx file 
$writer = new Xlsx($spreadsheet);
  
// Save .xlsx file to the current directory
$writer->save('gfgdate.xlsx');
?>


Output:
datetime

Reference: https://phpspreadsheet.readthedocs.io/en/develop/topics/accessing-cells/

RELATED ARTICLES

Most Popular

Dominic
32688 POSTS0 COMMENTS
Milvus
131 POSTS0 COMMENTS
Nango Kala
7057 POSTS0 COMMENTS
Nicole Veronica
12198 POSTS0 COMMENTS
Nokonwaba Nkukhwana
12293 POSTS0 COMMENTS
Shaida Kate Naidoo
7182 POSTS0 COMMENTS
Ted Musemwa
7447 POSTS0 COMMENTS
Thapelo Manthata
7160 POSTS0 COMMENTS
Umr Jansen
7150 POSTS0 COMMENTS