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.
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
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With