PHPExcel Date Format

Question

I am getting an output from MS SQL server in the '2012-08-09 00:00:00' (without quotes) format.

However, when I write it to excel file I'm unable to write it in date format to have dd mmm yyyy formatting on excel.

As a result i tried to write in format =date(2012,08,09) as a formula to the respective cells.

But I don't want to output it as a formula but rather the value '09 Aug 2012' with the data type integrity intact. How do I do this? Or is there a simpler method?

I read through the documentation but it was not clear to me, thought I would ask for clarification.

Regards.

Sorry for not being detailed enough.

I am using the PHPExcel library.

From my sql array, i use the following:

$t_year    = substr($xls_column_datas["colname"],0,4);
$t_month   = substr($xls_column_datas["colname"],5,2);
$t_day     = substr($xls_column_datas["colname"],8,2);
$t_format  = $t_year . "," . $t_month . "," . $t_day ;
$t_format  = '=date('.$t_format.')';

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($data_column_num, $data_row_num, $t_format );
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($data_column_num, $data_row_num)->getNumberFormat()->setFormatCode('[$-C09]d mmm yyyy;@');

in my excel output, it shows column A2 for e.g. =DATE(2012,8,9)

rather than showing up as a formula I want excel to recognize '2012-08-09 00:00:00' is a date time and format it to dd mmm yyyy.

Is this getting clear? Sorry.

See this link may be help you http://stackoverflow.com/questions/279917/format-text-in-excel-file-via-php — Hkachhia, Sep 11 '12 at 04:59
Мне помог [вервый ответ][1] с соседней темы [1]: http://stackoverflow.com/questions/11119631/excel-date-conversion-using-php-excel — borodatych, Aug 28 '14 at 07:27

Mark Baker · Accepted Answer · 2016-09-28T06:58:36.307

13

Is your problem in getting the date from MS SQL as a date/time, or setting the Excel date?

There is a whole section of the PHPExcel documentation that explains the use of the PHPExcel_Shared_Date::PHPToExcel($PHPDate) and PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) helper methods for converting PHP dates to an Excel datetime stamp value that you set as the cell value, and then you apply a number format mask of one of the date masks such as PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 to that cell

Instead of

$t_year     = substr($xls_column_datas["colname"],0,4);    
$t_month    = substr($xls_column_datas["colname"],5,2);    
$t_day      = substr($xls_column_datas["colname"],8,2);
$t_format   = '=date('.$t_format.')';
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($data_column_num, $data_row_num, $t_format );
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($data_column_num, $data_row_num)->getNumberFormat()->setFormatCode('[$-C09]d mmm yyyy;@');

try setting

$t_year   = substr($xls_column_datas["colname"],0,4);
$t_month  = substr($xls_column_datas["colname"],4,2);  // Fixed problems with offsets
$t_day    = substr($xls_column_datas["colname"],6,2);
$t_date   = PHPExcel_Shared_Date::FormattedPHPToExcel($t_year, $t_month, $t_day);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(
    $data_column_num, $data_row_num, $t_date 
);
$objPHPExcel->getActiveSheet()
    ->getStyleByColumnAndRow($data_column_num, $data_row_num)
    ->getNumberFormat()->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14
    );

edited Sep 28 '16 at 06:58

answered Sep 11 '12 at 06:39

Mark Baker

209,507
32
346
385

Hmm but this sets the cell format to NUMBER and it seems like 43073.4250578704 in the exported file. But there is also the DATE format for cells BUT no getDateFormat() function in PHPExcel. I dont understand it. – Čamo Jan 30 '18 at 10:17
May be cause I see it in Open Office? – Čamo Jan 30 '18 at 10:48
Nothing to do with opening in OpenOffiice.... `PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14` is the format being set in this code example, and it is not a number format, but a date format – Mark Baker Jan 30 '18 at 11:10
I have almost the same code ```$list->setCellValueByColumnAndRow( ++$column, $row, \PHPExcel_Shared_Date::PHPToExcel( $survey->task->closed )' )->getStyle()->getNumberFormat()->setFormatCode( \PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY );``` but looks as I write before? – Čamo Jan 30 '18 at 11:15
Ask question - a proper question, showing appropriate code, and not simply comments against an answer to somebody else's question - and I'll look at it..... but perhaps don't try fluent setting getStyle() from setCellValue().... use two separate statements – Mark Baker Jan 30 '18 at 12:02
Ok can you look at my question please https://stackoverflow.com/questions/48518427/phpexcel-setcellvaluebycolumnandrow-to-datetime-cell-format – Čamo Jan 30 '18 at 13:18
It seems you are right. The fluent call getStyle() does not work. Must call ->getStyleByColumnAndRow() on excel sheet object. – Čamo Jan 30 '18 at 13:41

score 8 · Answer 2 · edited Dec 19 '19 at 13:48

8

$date = PHPExcel_Style_NumberFormat::toFormattedString($data, "M/D/YYYY");

edited Dec 19 '19 at 13:48

Federico klez Culloca

26,308
17
56
95

answered Jul 30 '13 at 15:34

Thakhani Tharage

1,288
16
19

score 1 · Answer 3 · answered Sep 11 '12 at 05:08

1

Though it is unclear what are asking, If you are looking for a date conversion

 // convert old date string to YYYYmmdd format
    $date = date('d M Y', strtotime($old_date));

this will output date in 09 Aug 2012 format

answered Sep 11 '12 at 05:08

Sibu

4,609
2
26
38

score 1 · Answer 4 · answered Sep 11 '12 at 05:16

1

Why not let the server to the formatting for you? Use this query to format the date

SELECT convert(varchar(15), getdate(), 106)

This will result 11 Sep 2012

SQL SERVER: Date Format

answered Sep 11 '12 at 05:16

John Woo

258,903
69
498
492

PHPExcel Date Format

4 Answers4

Linked