PHPExcel日期格式

问题描述

我从MS SQL Server获得了"2012-08-09 00:00:00"(不带引号)格式的输出.

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

但是，当我将其写入excel文件时，我无法以日期格式将其写入以在excel上使用dd mmm yyyy格式.

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.

结果，我尝试将= date(2012,08,09)格式作为公式写入各个单元格.

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

但是我不想将其输出为公式，而是将数据类型完整性保持不变的值'09 Aug 2012'输出.我该怎么做呢?还是有更简单的方法?

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.

致谢.

很抱歉，不够详细.

我正在使用PHPExcel库.

I am using the PHPExcel library.

在我的sql数组中，我使用以下内容:

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;@');

在我的excel输出中，它显示了A2列，例如= DATE(2012,8,9)

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

我希望Excel不能将其显示为公式，而不是将其识别为"2012-08-09 00:00:00"是日期时间，并将其格式设置为dd mmm yyyy.

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.

推荐答案

从MS SQL获取日期作为日期/时间还是设置Excel日期是您的问题吗?

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

PHPExcel文档的整个章节都介绍了PHPExcel_Shared_Date::PHPToExcel($PHPDate)和PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)辅助方法的使用，这些方法将PHP日期转换为您设置为单元格值的Excel日期时间戳值，然后应用日期掩码之一的数字格式掩码，例如该单元格的PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2

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

代替

$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;@');

尝试设置

$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
    );

这篇关于PHPExcel日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！