CODEWITHSUNDEEP
phpexcel
user765368
user765368

Reputation: 20346

Make Excel recognize Date format sent from PHP

simple question here, I'm using PHPExcel to export a datagrid to an Excel file. In my datagrid, I have a few columns that contain dates and are formatted as 'Y-m-d'. When I extract the grid data, Excel does not recognize the data into those columns as Dates (Excel says that they have a General format and that they have no specific number format).

Is there anyway that I can format the dates so that Excel can recognize them as dates before the data is sent to Excel?

Thanks in advance

Upvotes: 1

Views: 1056

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

As described in the PHPExcel documentation and in the Examples such as 02types.php

You need to convert those "formatted" dates to an MS Excel serialized date/timestamp

$dateString = '2015-12-21';
$excelTimestamp = PHPExcel_Shared_Date::PHPToExcel($dateString);

and then set that $excelTimestamp as the cell value

$objPHPExcel->getActiveSheet()->setCellValue('C9', $excelTimestamp);

You should then set the format mask for the cell

$objPHPExcel->getActiveSheet()
    ->getStyle('C9')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

Upvotes: 1

Yogendra
Yogendra

Reputation: 2234

PHPExcel have in-built package to set number data as an excel date. If you i got you right then you are just passing number value as an date and getting type error.

Try like: $date = PHPExcel_Style_NumberFormat::toFormattedString($data, "d-M-Y");

Or refer doc link for more details: http://www.cmsws.com/examples/applications/phpexcel/Documentation/API/PHPExcel_Style/PHPExcel_Style_NumberFormat.html#constFORMAT_DATE_DDMMYYYY

Upvotes: 1

Related Questions