I’ve been trying to automate the template of my practice journal posts. I store all of the data (times, lengths, and statistics), in excel for easier calculation, but couldn’t figure out a way to access that data with PHP.
I found that you can export to XML (save as XML Spreadsheet 2003), so I could read the XML data with SimpleXML.
The problem is with the times. When there is just a time with no date, it stores it starting at December 31, 1899. PHP does not support dates that long ago, so I had to create a function to modernize the date a bit.
I just added 70 to the year, and 1 to the day, so as to start from January 1, 1970. Really, it would read December 32, 1969, but thankfully mktime() corrects such dates.
I actually had to use gmmktime(), because otherwise PHP would take the timezone into account, which I don’t want.
The function is below.
function excelTimeToTimestamp($time)
{
list($datePart, $timePart) = split('T', $time);
list($year, $month, $day) = split('-', $datePart);
list($hour, $minute, $second) = split(':', $timePart);
$year += 70;
$day += 1;
$second = round($second);
return gmmktime($hour, $minute, $second, $month, $day, $year);
}Similar Posts:
- Weekly Updates for July 13, 2008-July 19, 2008
- Adhering to a Schedule
- Practice Journal: Day #64
- Use XPath with Namespaces
- Adding Ordinal Suffixes to Numbers in .NET
If you enjoyed this post, make sure you subscribe to my RSS feed!
Tags: Excel, PHP, Programming, timestamps, XML