php PHPExcel 日期格式

PHPExcel Date Format

提问by Saidur Rahman

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

我正在从 MS SQL 服务器以“2012-08-09 00:00:00”（不带引号）格式获取输出。

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.

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

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

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

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?

但我不想将它作为公式输出，而是将值 '09 Aug 2012' 输出，数据类型完整性完好无损。我该怎么做呢？或者有更简单的方法吗？

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.

我正在使用 PHPExcel 库。

From my sql array, i use the following:

从我的 sql 数组中，我使用以下内容：

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

在我的 excel 输出中，它显示列 A2 例如 =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.

而不是显示为公式，我希望 excel 识别 '2012-08-09 00:00:00' 是日期时间并将其格式化为 dd mmm yyyy。

Is this getting clear? Sorry.

这清楚了吗？对不起。

回答by Mark Baker

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

您在从 MS SQL 获取日期作为日期/时间或设置 Excel 日期时有问题吗？

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_YYYYMMDD2to that cell

PHPExcel 文档的一整节解释了如何使用 thePHPExcel_Shared_Date::PHPToExcel($PHPDate)和PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)helper 方法将 PHP 日期转换为您设置为单元格值的 Excel 日期时间戳值，然后应用其中一个日期掩码的数字格式掩码比如PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2那个单元格

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

回答by Thakhani Tharage

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

回答by Sibu

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

这将以 2012 年 8 月 9 日的格式输出日期

回答by John Woo

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

这将导致 11 Sep 2012

SQL SERVER: Date Format

SQL SERVER：日期格式