在php中使用simplexlsx读取excel xlsx文件 [英] Read excel xlsx file using simplexlsx in php
问题描述
我正在使用 simplexlsx.class.php
读取 xlsx 文件类型.当文件在excel文件中包含日期字段时会出现问题.
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.
示例输出:
在文件数据中:
日期星期四 2/2/2012星期五 2/3/2012
Day Date Thursday 2/2/2012 Friday 2/3/2012
程序输出:
日期
星期四 40941
星期五 40942
Thursday 40941
Friday 40942
它没有给出正确的日期
<?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] : ' ' ).'</td>';
}
echo '</tr>';
}
echo '</table>';
}
?>
<h1>Upload</h1>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file" /> <input type="submit" value="Parse" />
推荐答案
这些是正确的日期,只是在 Excel 的内部格式中:自 1900 年 1 月 1 日以来的天数(允许 1900 年为闰年).很明显,simplexlsx 类中的某些内容正在将 xlsx 日期值转换为 Excel 内部格式.
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.
我以前没有遇到过 simplexlsx(这让我感到惊讶,因为我以为我知道所有用于 PHP 的 Excel 文件读取器/写入器库)...但是在代码的某处必须有一种方法来处理这种转换,所以我想还有一种方法可以反向(将 Excel 时间戳转换为 PHP)
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)
编辑
你想要的方法在代码中:
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;
}
我不保证它是准确的
进一步编辑
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);
给予
float 1328140800
这看起来非常像今年正确范围内的 unix 时间戳值,果然:
which looks remarkably like a unix timestamp value in the correct range for this year, and sure enough:
02-Feb-2012
看起来对我有用
这篇关于在php中使用simplexlsx读取excel xlsx文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!