在php中使用simplexlsx读取excel xlsx文件

I am using simplexlsx.class.php to read xlsx file type. It's giving problems when the file contain date field in the excel file.

我使用simplexlsx.class.php来读取xlsx文件类型。当文件包含excel文件中的日期字段时，它会出现问题。

Sample output:

样本输出：

In the file data:

在文件数据中：

Day Date Thursday 2/2/2012 Friday 2/3/2012

日期星期四2012年2月2日星期五2012年3月3日星期五

Program output:

节目输出：

Day Date

日期

Thursday 40941
Friday 40942

周四40941周五40942

It's not give the correct date

它没有给出正确的日期

<?php

if (isset($_FILES['file'])) {

require_once "simplexlsx.class.php";

$xlsx = new SimpleXLSX( $_FILES['file']['tmp_name'] );

echo '<h1>Parsing Result</h1>';
echo '<table border="1" cellpadding="3" style="border-collapse: collapse">';

list($cols,) = $xlsx->dimension();

foreach( $xlsx->rows() as $k => $r) {
    if ($k == 0) continue; // skip first row
    echo '<tr>';
    for( $i = 0; $i < $cols; $i++)
    {

        echo '<td>'.( (isset($r[$i])) ? $r[$i] : '&nbsp;' ).'</td>';

    }
    echo '</tr>';
}
echo '</table>';
}

?>
<h1>Upload</h1>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file"  />&nbsp;&nbsp;<input type="submit" value="Parse" />

1 个解决方案

#1

Those are the correct dates, just in Excel's internal format: number of days since Jan 1st 1900 (allowing for 1900 being a leap year). Clearly something in the simplexlsx class is converting the xlsx date value to the Excel internal format.

这些是正确的日期，只是Excel的内部格式：自1900年1月1日以来的天数（允许1900年是闰年）。显然，simplexlsx类中的某些东西是将xlsx日期值转换为Excel内部格式。

I've not come across simplexlsx before (which surprises me as I thought I knew all the Excel file reader/writer libraries for PHP)... but somewhere in the code there must be a method to handle that conversion, so I'd imagine that there would also be a method for the reverse (converting Excel timestamp to PHP)

我之前没有遇到过simplexlsx（这让我感到惊讶，因为我认为我知道PHP的所有Excel文件读取器/编写器库）...但是代码中的某处必须有一个方法来处理转换，所以我会想象一下，还有一种反向方法（将Excel时间戳转换为PHP）

EDIT

编辑

The method you want is in the code:

您想要的方法是在代码中：

function unixstamp( $excelDateTime ) {
    $d = floor( $excelDateTime ); // seconds since 1900
    $t = $excelDateTime - $d;
    return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400;
}

I make no guarantees that it's accurate

我不保证它是准确的

EDIT FURTHER

编辑进一步

function unixstamp( $excelDateTime ) {
    $d = floor( $excelDateTime ); // seconds since 1900
    $t = $excelDateTime - $d;
    return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400;
}


$dateVal = 40941;
$unixDateVal = unixstamp($dateVal);
var_dump($unixDateVal);
echo date('d-M-Y',$unixDateVal);

gives

给

float 1328140800

which looks remarkably like a unix timestamp value in the correct range for this year, and sure enough:

它看起来非常像今年正确范围内的unix时间戳值，当然，这足够了：

02-Feb-2012

So looks like it works to me

所以看起来它对我有用

#1