PHPExcel：如何设置单元格的日期格式 [英] PHPExcel: how to set date format for a cell

问题描述

我需要将日期保存到Excel文件，它必须以格式dd / mm / yyyy（或用户的本地日期格式）输出，并被视为一个日期，以便他们的列

以下是代码：

 < ？php 
 include_once（../ PHPExcel / Classes / PHPExcel.php）; 
 date_default_timezone_set（'Europe / London'）; 
 $ objPHPExcel = new PHPExcel（）; 
 $ cacheMethod = PHPExcel_CachedObjectStorageFactory :: cache_in_memory_gzip; 
 PHPExcel_Settings :: setCacheStorageMethod（$ cacheMethod）; 
 
 $ objPHPExcel = new PHPExcel（）; 
 $ sheet = $ objPHPExcel-> getActiveSheet（）; 
 PHPExcel_Shared_Font :: setAutoSizeMethod（PHPExcel_Shared_Font :: AUTOSIZE_METHOD_EXACT）; 
 
 //我没有找到dd / mm / yyyy格式，所以我用了yyyy-mm-dd 
 $ sheet-> setCellValueByColumnAndRow（0，1，2014-10- 16\" ）; 
 $片状> getStyleByColumnAndRow（0，1）
  - > getNumberFormat（） - > setFormatCode（
 PHPExcel_Style_NumberFormat :: FORMAT_DATE_YYYYMMDD2 
）的
 
 $ objWriter = PHPExcel_IOFactory :: createWriter（$ objPHPExcel，'Excel2007'）; 
 $ objWriter-> save（test.xlsx）; 
  

它创建一个文件，而不是我的本地日期格式，我看到：2014-10-16 ，单元格格式为所有格式 - >yyyy-mm-dd。我希望它被解析并以我的本地日期格式输出。

我查看了 PHPExcel / Classes / PHPExcel / Style / NumberFormat.php ，并找到许多日期格式：

  const FORMAT_DATE_YYYYMMDD2 ='yyyy-mm-dd ; 
 const FORMAT_DATE_YYYYMMDD ='yy-mm-dd'; 
 const FORMAT_DATE_DDMMYYYY ='dd / mm / yy'; 
 const FORMAT_DATE_DMYSLASH ='d / m / y'; 
 const FORMAT_DATE_DMYMINUS ='d-m-y'; 
 ... 
  

但我不确定要使用什么。

解决方案

  $ sheet-> setCellValueByColumnAndRow（0 ，1，2014-10-16）; 
  

在单元格中设置字符串值，而不是日期。只是因为你把这个解释为一个日期，并不意味着计算机程序会自动将它解释为日期。

查看日期PHPExcel中的示例文档和示例和您将会看到您需要将单元格值设置为MS Excel序列化时间戳（自1900年1月1日以来的天数的浮点值）。您可以使用PHPExcel函数（如 PHPExcel_Shared_Date :: PHPToExcel（））将人类日期/ PHP DateTime对象/ Unix时间戳转换为MS Excel序列化时间戳。 b

  $ sheet-> setCellValueByColumnAndRow（0，1，PHPExcel_Shared_Date :: PHPToExcel（'2014-10-16'））; 
  

将值存储为时间戳后，可以应用所需的任何日期格式掩码到该单元格以获得您所需的格式

I need to save a date to Excel file, it must be output in format "dd/mm/yyyy" (or the local date format of the user), and to be treated as a date so a column of them could be sorted correctly.

Here is the code:

<?php
include_once("../PHPExcel/Classes/PHPExcel.php");
date_default_timezone_set('Europe/London');
$objPHPExcel = new PHPExcel();
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);

//I didn't find dd/mm/yyyy format, so I used yyyy-mm-dd
$sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16");
$sheet->getStyleByColumnAndRow(0, 1)
    ->getNumberFormat()->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("test.xlsx");

It creates a file, but instead of my local date format I see: "2014-10-16" and the format of the cell is "All formats" -> "yyyy-mm-dd". I wanted it to be parsed and output in my local date format.

I looked into the source code of PHPExcel/Classes/PHPExcel/Style/NumberFormat.php, and found many date formats:

const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
const FORMAT_DATE_DMYSLASH = 'd/m/y';
const FORMAT_DATE_DMYMINUS = 'd-m-y';
...

But I am not sure what to use. How can I achieve the desired goal?

解决方案

$sheet->setCellValueByColumnAndRow(0, 1, "2014-10-16");

Sets a string value in the cell, not a date. Just because you interpret that as a date, doesn't mean that computer programs automagically interpret it as a date.

Look at the date Examples in the PHPExcel Documentation and Examples, and you'll see that you need to set the cell value to a MS Excel serialized timestamp (a float value of the number of days since 1st January 1900). You can use the PHPExcel functions like PHPExcel_Shared_Date::PHPToExcel() to convert human dates/PHP DateTime objects/Unix timestamps to MS Excel Serialized timestamps.

$sheet->setCellValueByColumnAndRow(0, 1, PHPExcel_Shared_Date::PHPToExcel( '2014-10-16' ));

Once you've stored the value as a timestamp, you can then apply whatever date format mask you want to that cell to get your desired formatting

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