PHPExcel中的dd/mm/yyyy格式在单元格中添加单引号(') [英] dd/mm/yyyy format in PHPExcel adding single quote(') in cell

问题描述

在我的网页中，我正在使用PHPexcel 1.8生成Excel文件.现在，我面临的问题如下所述.

In my webpage, I am using PHPexcel 1.8 to generate excel files. Now I am facing an issue which describes below.

我有一些日期值，格式为dd/mm/yyyy.价值即将到来.但开头会显示single quote(').

I have some date values in the format dd/mm/yyyy. The value is coming. But it shows with a single quote(') at the begining.

我尝试了许多解决方案.但是没有人帮助我.

I tried many solutions. But none helps me out.

我的示例代码部分在下面给出

$cnt = 1;
foreach($gluuidArr as $uuid){
    $this -> excel_180->getActiveSheet() ->setCellValue('A'.$cnt,$aVal);
    $this -> excel_180->getActiveSheet() ->setCellValue('B'.$cnt,$bVal);
    $this -> excel_180 -> getActiveSheet() -> setCellValue('C'.$cnt, $cVal);
    $this -> excel_180->getActiveSheet() ->setCellValueExplicit('D'.$cnt,$dVal, PHPExcel_Cell_DataType::TYPE_STRING);
    $this -> excel_180 -> getActiveSheet() -> setCellValue('E'.$cnt, $eVal);

    $cnt += 1;
}

，其中$aVal, $bVal,..在循环内声明，并且$eval包含dd/mm/yyyy格式的日期

where $aVal, $bVal,.. are declaring inside the loop and $eval contains date in dd/mm/yyyy format

假定日期为20/05/2016，当单击excel单元格时，它将显示为'20/05/2016

suppose the date is 20/05/2016, when click on the excel cell, it will show like '20/05/2016

我尝试过

$this -> excel_180->getActiveSheet()->getStyle('E'.$cnt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

但是没有运气.

请帮助我摆脱这个问题.任何帮助都将不胜感激.

Please help me to get rid of this issue. Any help could be appreciated.

推荐答案

那是因为您没有存储日期，而是存储了一个恰好代表人类日期的字符串. MS Excel不会将日期存储为字符串，而是存储为浮点数，代表自1900年1月1日(或Mac版本为1904年1月1日)以来的天数.

That's because you're not storing a date, you're storing a string that happens to represent a date to human beings. MS Excel doesn't store dates as strings, but as floats, representing the number of days since 1st January 1900 (or 1st January 1904 on the Mac version)

如果要在PHPExcel中存储日期/时间值，则需要将该日期/时间转换为MS Excel时间戳值，然后为该单元格设置格式掩码，以指示其显示方式.

If you want to store a date/time value in PHPExcel, then you need to convert that date/time to an MS Excel timestsamp value, and then set a format mask for the cell indicating how you want it to be displayed.

在 查看全文