我的excel日期在php中转换为文本

Question

My excel has start date as header and the value is 05/30/16 but when i am reading this value in php, it is saying 'MayMay/MonMon/20162016'. Why is it happening. I want real value.

$excel = new Spreadsheet_Excel_Reader();
$excel->read('sample.xls');
$x=1;
while($x<=$excel->sheets[0]['numRows']) {
  echo "\t<tr>
";
  $y=1;
  while($y<=$excel->sheets[0]['numCols']) {
    $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
    echo "\t\t<td>$cell</td>
"; 
    $y++;
  } 
  echo "\t</tr>
";
  $x++;
}

Answer 1

To get dates, use

$cell = isset($excel->sheets[0]['cells'][$x][$y])
    ? $excel->sheets[0]['cells'][$x][$y]['raw']
    : '';

This will return the "raw" MS Excel serialized timestamp value, a number like 41944.

An MS Excel timestamp is typically the number of days since 1st January 1900 (though it could be number of days since 1st January 1904 if the spreadsheet was created using the Mac 1904 calendar)

You need to convert this to a Unix Timestamp in order to use PHP date functions with it.... depending on which Calendar is being used, a quick and dirty conversion:

Windows 1900 Calendar

$unixTimestamp = ($excelTimestamp - 25569) * 86400;

and for the reverse

$excelTimestamp = ($unixTimestamp / 86400) + 25569;

Mac 1904 Calendar

$unixTimestamp = ($excelTimestamp - 24107) * 86400;

and for the reverse

$excelTimestamp =  ($unixTimestamp / 86400) + 24107;

So MS Excel 41944 => $unixTimestamp = (41944 - 25569) * 86400; giving 1414800000 which is the unix timestamp for 1st November 2014

Once you have a unix timestamp, you can then use PHP's standard date() function or DateTime objects to manipulate and format it as you wish

echo date('d-M-Y', $unixTimestamp);