PHPExcel Mysql date format

Question

I'm retrieving data from the mysql database and using it to generate an excel file. I keep on getting a corrupted xlsx file which is being affected by the date syntax. How do you pick a date (type) value from the database and feed it in a cell using PHPExcel? e.g

$objPHPExcel -> getActiveSheet() -> SetCellValue('A' . $i, $result["name"]); 
$objPHPExcel -> getActiveSheet() -> SetCellValue('B' . $i, $result["dateofcontract"]);

Answer 1

According to documentation you can do it like this:

/* PHPExcel_Cell_AdvanceValueBinder required for this sample */
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

// MySQL-like timestamp '2008-12-31' or date string
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
$objPHPExcel->getActiveSheet()
            ->setCellValue('B' . $i, $result['dateofcontract']));
$objPHPExcel->getActiveSheet()
            ->getStyle('B' . $i)
            ->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

Answer 2

There are built-in functions within PHPExcel for converting unix timestamps or PHP DateTime objects to Excel datetimestamps, and that will also translate some string date formats to Excel datetimestamps as well:

PHPExcel_Shared_Date::PHPToExcel()

Then you set the resultant Excel datetimestamp value as the cell value, and set the cell numberformat style to a mask representing a date/time format. This is exactly what the Advanced Value Binder that peterm has mentioned does, although the Advanced Value Binder also manipulates certain other values (such as percentages) that you may not wish changing.

Answer 3

Dependent on how the date is stored in the DB retrieve it as a timestamp and then use php's date() function.

EG.

"SELECT UNIX_TIMESTAMP(dateofcontract) AS dateofcontract"

$formattedDate = date("m.d.y",$dateofcontract) // outputs 03.10.01

Obviously you need to assign the db query result to $dateofcontract, to see all the possible formats for the date see here: date()