PHPExcel: how to set date format for a cell
Solution 1
$sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16");
Sets a string value in the cell, not a date. Just because you interpret that as a date, doesn't mean that computer programs automagically interpret it as a date.
Look at the date Examples in the PHPExcel Documentation and Examples, and you'll see that you need to set the cell value to a MS Excel serialized timestamp (a float value of the number of days since 1st January 1900). You can use the PHPExcel functions like PHPExcel_Shared_Date::PHPToExcel()
to convert human dates/PHP DateTime objects/Unix timestamps to MS Excel Serialized timestamps.
$sheet->setCellValueByColumnAndRow(0, 1, PHPExcel_Shared_Date::PHPToExcel( '2014-10-16' ));
Once you've stored the value as a timestamp, you can then apply whatever date format mask you want to that cell to get your desired formatting
Solution 2
This code generates 4 cells in date format.
<?php
include_once("../PHPExcel/Classes/PHPExcel.php");
date_default_timezone_set('Europe/London');
$objPHPExcel = new PHPExcel();
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$format = 'dd/mm/yyyy';
for ($i = 1; $i < 5; ++$i)
{
$date = new DateTime('2016-12-0'.$i);
$sheet->setCellValueByColumnAndRow(0, $i,
PHPExcel_Shared_Date::PHPToExcel( $date ));
$sheet->getStyleByColumnAndRow(0, $i)
->getNumberFormat()->setFormatCode($format);
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("test.xlsx");
user4035
Updated on July 09, 2022Comments
-
user4035 almost 2 years
I need to save a date to Excel file, it must be output in format "dd/mm/yyyy" (or the local date format of the user), and to be treated as a date so a column of them could be sorted correctly.
Here is the code:
<?php include_once("../PHPExcel/Classes/PHPExcel.php"); date_default_timezone_set('Europe/London'); $objPHPExcel = new PHPExcel(); $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); $objPHPExcel = new PHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT); //I didn't find dd/mm/yyyy format, so I used yyyy-mm-dd $sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16"); $sheet->getStyleByColumnAndRow(0, 1) ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 ); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save("test.xlsx");
It creates a file, but instead of my local date format I see: "2014-10-16" and the format of the cell is "All formats" -> "yyyy-mm-dd". I wanted it to be parsed and output in my local date format.
I looked into the source code of
PHPExcel/Classes/PHPExcel/Style/NumberFormat.php
, and found many date formats:const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'; const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd'; const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy'; const FORMAT_DATE_DMYSLASH = 'd/m/y'; const FORMAT_DATE_DMYMINUS = 'd-m-y'; ...
But I am not sure what to use. How can I achieve the desired goal?