/** * VALUE * * @param mixed $value Value to check * @return boolean */ public static function VALUE($value = '') { $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); if (!is_numeric($value)) { $numberValue = str_replace(PHPExcel_Shared_String::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . PHPExcel_Shared_String::getCurrencyCode())); if (is_numeric($numberValue)) { return (double) $numberValue; } $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); if (strpos($value, ':') !== false) { $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value); if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) { PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return $timeValue; } } $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value); if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) { PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return $dateValue; } PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return PHPExcel_Calculation_Functions::VALUE(); } return (double) $value; }
/** * 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; } $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; } }
/** * 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 function testTIMEVALUEtoPHPObject() { PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT); $result = PHPExcel_Calculation_DateTime::TIMEVALUE('7:30:20'); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Must return an object... $this->assertTrue(is_object($result)); // ... of the correct type $this->assertTrue(is_a($result, 'DateTime')); // ... with the correct value $this->assertEquals($result->format('H:i:s'), '07:30:20'); }