Spreadsheet_excel_reader日期格式 [英] Spreadsheet_excel_reader date format
问题描述
$ filename ='test.xls';
$ reader = new Spreadsheet_Excel_Reader();
$ reader-> setUTFEncoder('iconv');
$ reader-> setOutputEncoding('CP1251');
$ reader-> read($ filename); ($ r = 1; $ r $ lt $ = $ reader-> sheets [0] ['numRows']; $ r ++)
{
for($ c = 1; $ c <= $ reader-> sheets [0] ['numCols']; $ c ++)
{
if(isset($ reader-> sheets [0] '] [$ r] [$ c]))
{
//我使用这个代码来获取值
echo $ reader-> sheets [0] ['cells '] [$ r] [$ c];
}
}
}
我的问题是,当我尝试阅读日期值,而不是显示'01 / 11/2014',它显示41944。任何人都可以帮助我吗?
您正在阅读的值实际上是一个MS Excel时间戳,通常是自1900年1月1日(虽然可能是1904年1月1日以后的天数,如果电子表格使用的是Mac 1904日历)
您需要将其转换为Unix Timestamp订单使用PHP日期功能....根据使用的日历,快速而肮脏的转换:
Windows 1900日历
$ unixTimestamp =($ excelTimestamp - 25569)* 86400;
和相反的
$ excelTimestamp =($ unixTimestamp / 86400)+ 25569;
Mac 1904日历
$ unixTimestamp =($ excelTimestamp - 24107)* 86400;
和相反的
$ excelTimestamp =($ unixTimestamp / 86400)+ 24107;
所以MS Excel 41944
=> $ unixTimestamp =(41944 - 25569)* 86400;
给 1414800000
这是 1的unix时间戳2014年11月
一旦你有一个unix时间戳,你可以使用PHP的标准date()函数或DateTime对象来操作和格式化为你希望
echo date('dM-Y',$ unixTimestamp);
I'm using spreadsheet_excel_reader to read xls file on php and insert the data into oracle database. I'm using the below code:
$filename = 'test.xls';
$reader=new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('CP1251');
$reader->read($filename);
for($r=1; $r<=$reader->sheets[0]['numRows']; $r++)
{
for($c=1; $c<=$reader->sheets[0]['numCols']; $c++)
{
if (isset($reader->sheets[0]['cells'][$r][$c]))
{
//I'm using this code to get the value
echo $reader->sheets[0]['cells'][$r][$c];
}
}
}
My question is, when I try to read the date value, instead of show '01/11/2014', it showing '41944'. Anyone could help me?
The value that you're reading is actually an MS Excel timestamp, typically the number of days since 1st January 1900 (though it could be number of days since 1st January 1904 if the spreadsheet is 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);
这篇关于Spreadsheet_excel_reader日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!