Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

<?php

/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package     PHPExcel_Shared
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license     http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt   LGPL
 * @version     ##VERSION##, ##DATE##
 */


/**
 * PHPExcel_Shared_Date
 *
 * @category   PHPExcel
 * @package     PHPExcel_Shared
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel_Shared_Date
{
        /** constants */
        const CALENDAR_WINDOWS_1900 = 1900;             //      Base date of 1st Jan 1900 = 1.0
        const CALENDAR_MAC_1904 = 1904;                 //      Base date of 2nd Jan 1904 = 1.0

        /*
         * Names of the months of the year, indexed by shortname
         * Planned usage for locale settings
         *
         * @public
         * @var string[]
         */
        public static $_monthNames = array(     'Jan' => 'January',
                                                                                'Feb' => 'February',
                                                                                'Mar' => 'March',
                                                                                'Apr' => 'April',
                                                                                'May' => 'May',
                                                                                'Jun' => 'June',
                                                                                'Jul' => 'July',
                                                                                'Aug' => 'August',
                                                                                'Sep' => 'September',
                                                                                'Oct' => 'October',
                                                                                'Nov' => 'November',
                                                                                'Dec' => 'December',
                                                                          );

        /*
         * Names of the months of the year, indexed by shortname
         * Planned usage for locale settings
         *
         * @public
         * @var string[]
         */
        public static $_numberSuffixes = array( 'st',
                                                                                        'nd',
                                                                                        'rd',
                                                                                        'th',
                                                                                  );

        /*
         * Base calendar year to use for calculations
         *
         * @private
         * @var int
         */
        protected static $_excelBaseDate        = self::CALENDAR_WINDOWS_1900;

        /**
         * Set the Excel calendar (Windows 1900 or Mac 1904)
         *
         * @param        integer        $baseDate                       Excel base date (1900 or 1904)
         * @return       boolean                                                Success or failure
         */
        public static function setExcelCalendar($baseDate) {
                if (($baseDate == self::CALENDAR_WINDOWS_1900) ||
                        ($baseDate == self::CALENDAR_MAC_1904)) {
                        self::$_excelBaseDate = $baseDate;
                        return TRUE;
                }
                return FALSE;
        }       //      function setExcelCalendar()


        /**
         * Return the Excel calendar (Windows 1900 or Mac 1904)
         *
         * @return       integer        Excel base date (1900 or 1904)
         */
        public static function getExcelCalendar() {
                return self::$_excelBaseDate;
        }       //      function getExcelCalendar()


        /**
         *      Convert a date from Excel to PHP
         *
         *      @param          long            $dateValue                      Excel date/time value
         *      @param          boolean         $adjustToTimezone       Flag indicating whether $dateValue should be treated as
         *                                                                                                      a UST timestamp, or adjusted to UST
         *      @param          string          $timezone                       The timezone for finding the adjustment from UST
         *      @return         long            PHP serialized date/time
         */
        public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
                if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
                        $my_excelBaseDate = 25569;
                        //      Adjust for the spurious 29-Feb-1900 (Day 60)
                        if ($dateValue < 60) {
                                --$my_excelBaseDate;
                        }
                } else {
                        $my_excelBaseDate = 24107;
                }

                // Perform conversion
                if ($dateValue >= 1) {
                        $utcDays = $dateValue - $my_excelBaseDate;
                        $returnValue = round($utcDays * 86400);
                        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
                                $returnValue = (integer) $returnValue;
                        }
                } else {
                        $hours = round($dateValue * 24);
                        $mins = round($dateValue * 1440) - round($hours * 60);
                        $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
                        $returnValue = (integer) gmmktime($hours, $mins, $secs);
                }

                $timezoneAdjustment = ($adjustToTimezone) ?
                    PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) :
                    0;

                // Return
                return $returnValue + $timezoneAdjustment;
        }       //      function ExcelToPHP()


        /**
         * Convert a date from Excel to a PHP Date/Time object
         *
         * @param       integer         $dateValue              Excel date/time value
         * @return      integer                                         PHP date/time object
         */
        public static function ExcelToPHPObject($dateValue = 0) {
                $dateTime = self::ExcelToPHP($dateValue);
                $days = floor($dateTime / 86400);
                $time = round((($dateTime / 86400) - $days) * 86400);
                $hours = round($time / 3600);
                $minutes = round($time / 60) - ($hours * 60);
                $seconds = round($time) - ($hours * 3600) - ($minutes * 60);

                $dateObj = date_create('1-Jan-1970+'.$days.' days');
                $dateObj->setTime($hours,$minutes,$seconds);

                return $dateObj;
        }       //      function ExcelToPHPObject()


        /**
         *      Convert a date from PHP to Excel
         *
         *      @param  mixed           $dateValue                      PHP serialized date/time or date object
         *      @param  boolean         $adjustToTimezone       Flag indicating whether $dateValue should be treated as
         *                                                                                                      a UST timestamp, or adjusted to UST
         *      @param  string          $timezone                       The timezone for finding the adjustment from UST
         *      @return mixed           Excel date/time value
         *                                                      or boolean FALSE on failure
         */
        public static function PHPToExcel($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
                $saveTimeZone = date_default_timezone_get();
                date_default_timezone_set('UTC');
                $retValue = FALSE;
                if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
                        $retValue = self::FormattedPHPToExcel( $dateValue->format('Y'), $dateValue->format('m'), $dateValue->format('d'),
                                                                                                   $dateValue->format('H'), $dateValue->format('i'), $dateValue->format('s')
                                                                                                 );
                } elseif (is_numeric($dateValue)) {
                        $retValue = self::FormattedPHPToExcel( date('Y',$dateValue), date('m',$dateValue), date('d',$dateValue),
                                                                                                   date('H',$dateValue), date('i',$dateValue), date('s',$dateValue)
                                                                                                 );
                }
                date_default_timezone_set($saveTimeZone);

                return $retValue;
        }       //      function PHPToExcel()


        /**
         * FormattedPHPToExcel
         *
         * @param       long    $year
         * @param       long    $month
         * @param       long    $day
         * @param       long    $hours
         * @param       long    $minutes
         * @param       long    $seconds
         * @return  long                                Excel date/time value
         */
        public static function FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) {
                if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
                        //
                        //      Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
                        //      This affects every date following 28th February 1900
                        //
                        $excel1900isLeapYear = TRUE;
                        if (($year == 1900) && ($month <= 2)) { $excel1900isLeapYear = FALSE; }
                        $my_excelBaseDate = 2415020;
                } else {
                        $my_excelBaseDate = 2416481;
                        $excel1900isLeapYear = FALSE;
                }

                //      Julian base date Adjustment
                if ($month > 2) {
                        $month -= 3;
                } else {
                        $month += 9;
                        --$year;
                }

                //      Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
                $century = substr($year,0,2);
                $decade = substr($year,2,2);
                $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $my_excelBaseDate + $excel1900isLeapYear;

                $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;

                return (float) $excelDate + $excelTime;
        }       //      function FormattedPHPToExcel()


        /**
         * Is a given cell a date/time?
         *
         * @param        PHPExcel_Cell  $pCell
         * @return       boolean
         */
        public static function isDateTime(PHPExcel_Cell $pCell) {
                return self::isDateTimeFormat(
                        $pCell->getWorksheet()->getStyle(
                                $pCell->getCoordinate()
                        )->getNumberFormat()
                );
        }       //      function isDateTime()


        /**
         * Is a given number format a date/time?
         *
         * @param        PHPExcel_Style_NumberFormat    $pFormat
         * @return       boolean
         */
        public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) {
                return self::isDateTimeFormatCode($pFormat->getFormatCode());
        }       //      function isDateTimeFormat()


        private static  $possibleDateFormatCharacters = 'eymdHs';

        /**
         * Is a given number format code a date/time?
         *
         * @param        string $pFormatCode
         * @return       boolean
         */
        public static function isDateTimeFormatCode($pFormatCode = '') {
                // Switch on formatcode
                switch ($pFormatCode) {
                        //      General contains an epoch letter 'e', so we trap for it explicitly here
                        case PHPExcel_Style_NumberFormat::FORMAT_GENERAL:
                                return FALSE;
                        //      Explicitly defined date formats
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
                        case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
                                return TRUE;
                }

                //      Typically number, currency or accounting (or occasionally fraction) formats
                if ((substr($pFormatCode,0,1) == '_') || (substr($pFormatCode,0,2) == '0 ')) {
                        return FALSE;
                }
                // Try checking for any of the date formatting characters that don't appear within square braces
                if (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$pFormatCode)) {
                        //      We might also have a format mask containing quoted strings...
                        //              we don't want to test for any of our characters within the quoted blocks
                        if (strpos($pFormatCode,'"') !== FALSE) {
                                $segMatcher = FALSE;
                                foreach(explode('"',$pFormatCode) as $subVal) {
                                        //      Only test in alternate array entries (the non-quoted blocks)
                                        if (($segMatcher = !$segMatcher) &&
                                                (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$subVal))) {
                                                return TRUE;
                                        }
                                }
                                return FALSE;
                        }
                        return TRUE;
                }

                // No date...
                return FALSE;
        }       //      function isDateTimeFormatCode()


        /**
         * Convert a date/time string to Excel time
         *
         * @param       string  $dateValue              Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
         * @return      float|FALSE             Excel date/time serial value
         */
        public static function stringToExcel($dateValue = '') {
                if (strlen($dateValue) < 2)
                        return FALSE;
                if (!preg_match('/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}(:\d{1,2})?)?$/iu', $dateValue))
                        return FALSE;

                $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);

                if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
                        return FALSE;
                } else {
                        if (strpos($dateValue, ':') !== FALSE) {
                                $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
                                if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
                                        return FALSE;
                                }
                                $dateValueNew += $timeValue;
                        }
                        return $dateValueNew;
                }


        }

    public static function monthStringToNumber($month) {
        $monthIndex = 1;
        foreach(self::$_monthNames as $shortMonthName => $longMonthName) {
            if (($month === $longMonthName) || ($month === $shortMonthName)) {
                return $monthIndex;
            }
            ++$monthIndex;
        }
        return $month;
    }

    public static function dayStringToNumber($day) {
                $strippedDayValue = (str_replace(self::$_numberSuffixes,'',$day));
                if (is_numeric($strippedDayValue)) {
                    return $strippedDayValue;
                }
                return $day;
    }

}