Пример #1
0
 public function setUp()
 {
     if (!defined('PHPEXCEL_ROOT')) {
         define('PHPEXCEL_ROOT', APPLICATION_PATH . '/');
     }
     require_once PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php';
     PHPExcel_Calculation_Functions::setCompatibilityMode(PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL);
 }
Пример #2
0
 /**
  * @dataProvider providerBinaryComparisonOperation
  */
 public function testBinaryComparisonOperation($formula, $expectedResultExcel, $expectedResultOpenOffice)
 {
     PHPExcel_Calculation_Functions::setCompatibilityMode(PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL);
     $resultExcel = \PHPExcel_Calculation::getInstance()->_calculateFormulaValue($formula);
     $this->assertEquals($expectedResultExcel, $resultExcel, 'should be Excel compatible');
     PHPExcel_Calculation_Functions::setCompatibilityMode(PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE);
     $resultOpenOffice = \PHPExcel_Calculation::getInstance()->_calculateFormulaValue($formula);
     $this->assertEquals($expectedResultOpenOffice, $resultOpenOffice, 'should be OpenOffice compatible');
 }
Пример #3
0
 public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
 {
     if ($this->_dataSource !== NULL) {
         $calcEngine = PHPExcel_Calculation::getInstance();
         $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
         if ($flatten) {
             $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
         } else {
             $newArray = array_values(array_shift($newDataValues));
             foreach ($newArray as $i => $newDataSet) {
                 $newArray[$i] = array($newDataSet);
             }
             foreach ($newDataValues as $newDataSet) {
                 $i = 0;
                 foreach ($newDataSet as $newDataVal) {
                     array_unshift($newArray[$i++], $newDataVal);
                 }
             }
             $this->_dataValues = $newArray;
         }
         $this->_pointCount = count($this->_dataValues);
     }
 }
Пример #4
0
 /**
  * ZTEST
  *
  * Returns the Weibull distribution. Use this distribution in reliability
  * analysis, such as calculating a device's mean time to failure.
  *
  * @param	float		$value
  * @param	float		$alpha		Alpha Parameter
  * @param	float		$beta		Beta Parameter
  * @param	boolean		$cumulative
  * @return	float
  *
  */
 public static function ZTEST($dataSet, $m0, $sigma = null)
 {
     $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet);
     $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0);
     $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma);
     if (is_null($sigma)) {
         $sigma = self::STDEV($dataSet);
     }
     $n = count($dataSet);
     return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / SQRT($n)));
 }
Пример #5
0
 /**
  *	TRIMNONPRINTABLE
  *
  *	@param	mixed	$value	Value to check
  *	@return	string
  */
 public static function TRIMNONPRINTABLE($stringValue = '')
 {
     $stringValue = self::flattenSingleValue($stringValue);
     if (self::$_invalidChars == Null) {
         self::$_invalidChars = range(chr(0), chr(31));
     }
     if (is_string($stringValue) || is_numeric($stringValue)) {
         return str_replace(self::$_invalidChars, '', trim($stringValue, ".."));
     }
     return Null;
 }
Пример #6
0
 /**
  * IFERROR
  *
  * Excel Function:
  *		=IFERROR(testValue,errorpart)
  *
  * @access	public
  * @category Logical Functions
  * @param	mixed	$testValue	Value to check, is also the value returned when no error
  * @param	mixed	$errorpart	Value to return when testValue is an error condition
  * @return	mixed	The value of errorpart or testValue determined by error condition
  */
 public static function IFERROR($testValue = '', $errorpart = '')
 {
     $testValue = is_null($testValue) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
     $errorpart = is_null($errorpart) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($errorpart);
     return self::STATEMENT_IF(PHPExcel_Calculation_Functions::IS_ERROR($testValue), $errorpart, $testValue);
 }
Пример #7
0
 /**
  *    power
  *
  *    A = A ^ B
  *    @param mixed $B Matrix/Array
  *    @return Matrix Sum
  */
 public function power()
 {
     if (func_num_args() > 0) {
         $args = func_get_args();
         $match = implode(",", array_map('gettype', $args));
         switch ($match) {
             case 'object':
                 if ($args[0] instanceof PHPExcel_Shared_JAMA_Matrix) {
                     $M = $args[0];
                 } else {
                     throw new PHPExcel_Calculation_Exception(self::ARGUMENT_TYPE_EXCEPTION);
                 }
                 break;
             case 'array':
                 $M = new PHPExcel_Shared_JAMA_Matrix($args[0]);
                 break;
             default:
                 throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
                 break;
         }
         $this->checkMatrixDimensions($M);
         for ($i = 0; $i < $this->m; ++$i) {
             for ($j = 0; $j < $this->n; ++$j) {
                 $validValues = true;
                 $value = $M->get($i, $j);
                 if (is_string($this->A[$i][$j]) && strlen($this->A[$i][$j]) > 0 && !is_numeric($this->A[$i][$j])) {
                     $this->A[$i][$j] = trim($this->A[$i][$j], '"');
                     $validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($this->A[$i][$j]);
                 }
                 if (is_string($value) && strlen($value) > 0 && !is_numeric($value)) {
                     $value = trim($value, '"');
                     $validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($value);
                 }
                 if ($validValues) {
                     $this->A[$i][$j] = pow($this->A[$i][$j], $value);
                 } else {
                     $this->A[$i][$j] = PHPExcel_Calculation_Functions::NaN();
                 }
             }
         }
         return $this;
     } else {
         throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
     }
 }
Пример #8
0
 /**
  * Save PHPExcel to file
  *
  * @param	string		$pFileName
  * @throws	Exception
  */
 public function save($pFilename = null)
 {
     // check mbstring.func_overload
     if (ini_get('mbstring.func_overload') != 0) {
         throw new Exception('Multibyte string function overloading in PHP must be disabled.');
     }
     // garbage collect
     $this->_phpExcel->garbageCollect();
     $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
     // Initialise workbook writer
     $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_tempDir);
     // Initialise worksheet writers
     $countSheets = count($this->_phpExcel->getAllSheets());
     for ($i = 0; $i < $countSheets; ++$i) {
         $phpSheet = $this->_phpExcel->getSheet($i);
         $writerWorksheet = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_tempDir, $phpSheet);
         $this->_writerWorksheets[$i] = $writerWorksheet;
     }
     // add 15 identical cell style Xfs
     // for now, we use the first cellXf instead of cellStyleXf
     $cellXfCollection = $this->_phpExcel->getCellXfCollection();
     for ($i = 0; $i < 15; ++$i) {
         $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
     }
     // add all the cell Xfs
     foreach ($this->_phpExcel->getCellXfCollection() as $style) {
         $this->_writerWorkbook->addXfWriter($style, false);
     }
     // initialize OLE file
     $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book';
     $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
     if ($this->_tempDir != '') {
         $OLE->setTempDir($this->_tempDir);
     }
     $res = $OLE->init();
     // Write the worksheet streams before the global workbook stream,
     // because the byte sizes of these are needed in the global workbook stream
     $worksheetSizes = array();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i]->close();
         $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
     }
     // add binary data for global workbook stream
     $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
     // add binary data for sheet streams
     for ($i = 0; $i < $countSheets; ++$i) {
         while (($tmp = $this->_writerWorksheets[$i]->getData()) !== false) {
             $OLE->append($tmp);
         }
     }
     $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE));
     if ($this->_tempDir != '') {
         $root->setTempDir($this->_tempDir);
     }
     // save the OLE file
     $res = $root->save($pFilename);
     PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
     // clean up
     foreach ($this->_writerWorksheets as $sheet) {
         $sheet->cleanup();
     }
 }
Пример #9
0
 /**
  * TRUNC
  *
  * Truncates value to the number of fractional digits by number_digits.
  *
  * @param	float		$value
  * @param	int			$digits
  * @return	float		Truncated value
  */
 public static function TRUNC($value = 0, $digits = 0)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
     // Validate parameters
     if (!is_numeric($value) || !is_numeric($digits)) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     $digits = floor($digits);
     // Truncate
     $adjust = pow(10, $digits);
     if ($digits > 0 && rtrim(intval((abs($value) - abs(intval($value))) * $adjust), '0') < $adjust / 10) {
         return $value;
     }
     return intval($value * $adjust) / $adjust;
 }
Пример #10
0
 /**
  * Calculate cell value (using formula)
  *
  * @param	PHPExcel_Cell	$pCell	Cell to calculate
  * @return	mixed
  * @throws	Exception
  */
 public function calculate(PHPExcel_Cell $pCell = null)
 {
     // Return value
     $returnValue = '';
     // Is the value present in calculation cache?
     if ($this->getCalculationCacheEnabled()) {
         if (isset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()])) {
             if (time() + microtime() - $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] < $this->_calculationCacheExpirationTime) {
                 // Return result
                 $returnValue = $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'];
                 if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
                     return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
                 }
                 return $returnValue;
             } else {
                 unset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]);
             }
         }
     }
     // Formula
     $formula = $pCell->getValue();
     // Executable formula array
     $executableFormulaArray = array();
     // Parse formula into a tree of tokens
     $objParser = new PHPExcel_Calculation_FormulaParser($formula);
     // Loop trough parsed tokens and create an executable formula
     $inFunction = false;
     $token = null;
     $tokenCount = $objParser->getTokenCount();
     for ($i = 0; $i < $tokenCount; ++$i) {
         $token = $objParser->getToken($i);
         $tokenType = $token->getTokenType();
         $tokenSubType = $token->getTokenSubType();
         $tokenValue = $token->getValue();
         // Is it a cell reference?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE) {
             // Adjust reference
             $reference = str_replace('$', '', $tokenValue);
             // Add to executable formula array
             $executableFormulaArray[] = '$this->extractRange("' . $reference . '", $pCell->getParent())';
             continue;
         }
         // Is it a concatenation operator?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_CONCATENATION) {
             // Add to executable formula array
             $executableFormulaArray[] = '.';
             continue;
         }
         // Is it a logical operator?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL) {
             // Temporary variable
             $tmp = '';
             switch ($tokenValue) {
                 case '=':
                     $tmp = '==';
                     break;
                 case '<>':
                     $tmp = '!=';
                     break;
                 default:
                     $tmp = $tokenValue;
             }
             // Add to executable formula array
             $executableFormulaArray[] = $tmp;
             continue;
         }
         // Is it a subexpression?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) {
             // Temporary variable
             $tmp = '';
             switch ($tokenSubType) {
                 case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
                     $tmp = '(';
                     break;
                 case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
                     $tmp = ')';
                     break;
             }
             // Add to executable formula array
             $executableFormulaArray[] = $tmp;
             continue;
         }
         // Is it a function?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
             // Temporary variable
             $tmp = '';
             // Check the function type
             if ($tokenValue == 'ARRAY' || $tokenValue == 'ARRAYROW') {
                 // An array or an array row...
                 $tmp = 'array(';
             } else {
                 // A regular function call...
                 switch ($tokenSubType) {
                     case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
                         // Check if the function call is allowed...
                         if (!isset($this->_functionMappings[strtoupper($tokenValue)])) {
                             return '#NAME?';
                         }
                         // Map the function call
                         $tmp = $this->_functionMappings[strtoupper($tokenValue)]->getPHPExcelName() . '(';
                         $inFunction = true;
                         break;
                     case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
                         $tmp = ')';
                         break;
                 }
             }
             // Add to executable formula array
             $executableFormulaArray[] = $tmp;
             continue;
         }
         // Is it text?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT) {
             // Temporary variable
             $tmp = $tokenValue;
             $tmp = str_replace('"', '\\"', $tmp);
             // Add to executable formula array
             $executableFormulaArray[] = '"' . $tmp . '"';
             continue;
         }
         // Is it a number?
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NUMBER) {
             // Add to executable formula array
             $executableFormulaArray[] = $tokenValue;
             continue;
         }
         // Is it an error? Add it as text...
         if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_ERROR) {
             // Add to executable formula array
             $executableFormulaArray[] = '"' . $tokenValue . '"';
             continue;
         }
         // Is it something else?
         $executableFormulaArray[] = $tokenValue;
     }
     $fromArray = array('(,', ',,', ',)', '( ,', ', ,', ', )', '$this');
     $toArray = array('(null,', ',null,', ',null)', '(null,', ',null,', ',null)', '$pThat');
     // Evaluate formula
     try {
         $formula = implode(' ', $executableFormulaArray);
         $formula = str_replace($fromArray, $toArray, $formula);
         /*
          * The following code block can cause an error like:
          *	  Fatal error: Unsupported operand types in ...: runtime-created function on line 1
          *
          * This is due to the fact that a FATAL error is an E_ERROR,
          * and it can not be caught using try/catch or any other
          * Exception/error handling feature in PHP.
          *
          * A feature request seems to be made once, but it has been
          * closed without any deliverables:
          *	  http://bugs.php.net/bug.php?id=40014
          */
         $temporaryCalculationFunction = @create_function('$pThat, $pCell', "return {$formula};");
         if ($temporaryCalculationFunction === FALSE) {
             $returnValue = '#N/A';
         } else {
             $calculationExceptionHandler = new PHPExcel_Calculation_ExceptionHandler();
             $returnValue = $temporaryCalculationFunction($this, $pCell);
         }
     } catch (Exception $ex) {
         $returnValue = '#N/A';
     }
     // Save to calculation cache
     if ($this->getCalculationCacheEnabled()) {
         $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] = time() + microtime();
         $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'] = $returnValue;
     }
     // Return result
     if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
         return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
     }
     return $returnValue;
 }
Пример #11
0
 /**
  * Save PHPExcel to file
  *
  * @param    string $pFilename
  *
  * @throws    PHPExcel_Writer_Exception
  */
 public function save($pFilename = null)
 {
     // garbage collect
     $this->_phpExcel->garbageCollect();
     $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog();
     PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(false);
     $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
     // initialize colors array
     $this->_colors = array();
     // Initialise workbook writer
     $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser);
     // Initialise worksheet writers
     $countSheets = $this->_phpExcel->getSheetCount();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i));
     }
     // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook.
     $this->_buildWorksheetEschers();
     $this->_buildWorkbookEscher();
     // add 15 identical cell style Xfs
     // for now, we use the first cellXf instead of cellStyleXf
     $cellXfCollection = $this->_phpExcel->getCellXfCollection();
     for ($i = 0; $i < 15; ++$i) {
         $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
     }
     // add all the cell Xfs
     foreach ($this->_phpExcel->getCellXfCollection() as $style) {
         $this->_writerWorkbook->addXfWriter($style, false);
     }
     // add fonts from rich text eleemnts
     for ($i = 0; $i < $countSheets; ++$i) {
         foreach ($this->_writerWorksheets[$i]->_phpSheet->getCellCollection() as $cellID) {
             $cell = $this->_writerWorksheets[$i]->_phpSheet->getCell($cellID);
             $cVal = $cell->getValue();
             if ($cVal instanceof PHPExcel_RichText) {
                 $elements = $cVal->getRichTextElements();
                 foreach ($elements as $element) {
                     if ($element instanceof PHPExcel_RichText_Run) {
                         $font = $element->getFont();
                         $this->_writerWorksheets[$i]->_fntHashIndex[$font->getHashCode()] = $this->_writerWorkbook->_addFont($font);
                     }
                 }
             }
         }
     }
     // initialize OLE file
     $workbookStreamName = 'Workbook';
     $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
     // Write the worksheet streams before the global workbook stream,
     // because the byte sizes of these are needed in the global workbook stream
     $worksheetSizes = array();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i]->close();
         $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
     }
     // add binary data for global workbook stream
     $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
     // add binary data for sheet streams
     for ($i = 0; $i < $countSheets; ++$i) {
         $OLE->append($this->_writerWorksheets[$i]->getData());
     }
     $this->_documentSummaryInformation = $this->_writeDocumentSummaryInformation();
     // initialize OLE Document Summary Information
     if (isset($this->_documentSummaryInformation) && !empty($this->_documentSummaryInformation)) {
         $OLE_DocumentSummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'DocumentSummaryInformation'));
         $OLE_DocumentSummaryInformation->append($this->_documentSummaryInformation);
     }
     $this->_summaryInformation = $this->_writeSummaryInformation();
     // initialize OLE Summary Information
     if (isset($this->_summaryInformation) && !empty($this->_summaryInformation)) {
         $OLE_SummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'SummaryInformation'));
         $OLE_SummaryInformation->append($this->_summaryInformation);
     }
     // define OLE Parts
     $arrRootData = array($OLE);
     // initialize OLE Properties file
     if (isset($OLE_SummaryInformation)) {
         $arrRootData[] = $OLE_SummaryInformation;
     }
     // initialize OLE Extended Properties file
     if (isset($OLE_DocumentSummaryInformation)) {
         $arrRootData[] = $OLE_DocumentSummaryInformation;
     }
     $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), $arrRootData);
     // save the OLE file
     $res = $root->save($pFilename);
     PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
     PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
 }
Пример #12
0
 /**
  *	CONVERTUOM
  *
  *	Converts a number from one measurement system to another.
  *	For example, CONVERT can translate a table of distances in miles to a table of distances
  *	in kilometers.
  *
  *	Excel Function:
  *		CONVERT(value,fromUOM,toUOM)
  *
  *	@param	float		$value		The value in fromUOM to convert.
  *	@param	string		$fromUOM	The units for value.
  *	@param	string		$toUOM		The units for the result.
  *
  *	@return	float
  */
 public static function CONVERTUOM($value, $fromUOM, $toUOM)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue($fromUOM);
     $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue($toUOM);
     if (!is_numeric($value)) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     $fromMultiplier = 1.0;
     if (isset(self::$_conversionUnits[$fromUOM])) {
         $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
     } else {
         $fromMultiplier = substr($fromUOM, 0, 1);
         $fromUOM = substr($fromUOM, 1);
         if (isset(self::$_conversionMultipliers[$fromMultiplier])) {
             $fromMultiplier = self::$_conversionMultipliers[$fromMultiplier]['multiplier'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
         if (isset(self::$_conversionUnits[$fromUOM]) && self::$_conversionUnits[$fromUOM]['AllowPrefix']) {
             $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
     }
     $value *= $fromMultiplier;
     $toMultiplier = 1.0;
     if (isset(self::$_conversionUnits[$toUOM])) {
         $unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
     } else {
         $toMultiplier = substr($toUOM, 0, 1);
         $toUOM = substr($toUOM, 1);
         if (isset(self::$_conversionMultipliers[$toMultiplier])) {
             $toMultiplier = self::$_conversionMultipliers[$toMultiplier]['multiplier'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
         if (isset(self::$_conversionUnits[$toUOM]) && self::$_conversionUnits[$toUOM]['AllowPrefix']) {
             $unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
     }
     if ($unitGroup1 != $unitGroup2) {
         return PHPExcel_Calculation_Functions::NA();
     }
     if ($fromUOM == $toUOM && $fromMultiplier == $toMultiplier) {
         //	We've already factored $fromMultiplier into the value, so we need
         //		to reverse it again
         return $value / $fromMultiplier;
     } elseif ($unitGroup1 == 'Temperature') {
         if ($fromUOM == 'F' || $fromUOM == 'fah') {
             if ($toUOM == 'F' || $toUOM == 'fah') {
                 return $value;
             } else {
                 $value = ($value - 32) / 1.8;
                 if ($toUOM == 'K' || $toUOM == 'kel') {
                     $value += 273.15;
                 }
                 return $value;
             }
         } elseif (($fromUOM == 'K' || $fromUOM == 'kel') && ($toUOM == 'K' || $toUOM == 'kel')) {
             return $value;
         } elseif (($fromUOM == 'C' || $fromUOM == 'cel') && ($toUOM == 'C' || $toUOM == 'cel')) {
             return $value;
         }
         if ($toUOM == 'F' || $toUOM == 'fah') {
             if ($fromUOM == 'K' || $fromUOM == 'kel') {
                 $value -= 273.15;
             }
             return $value * 1.8 + 32;
         }
         if ($toUOM == 'C' || $toUOM == 'cel') {
             return $value - 273.15;
         }
         return $value + 273.15;
     }
     return $value * self::$_unitConversions[$unitGroup1][$fromUOM][$toUOM] / $toMultiplier;
 }
Пример #13
0
 /**
  * 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;
     }
 }
Пример #14
0
 /**
  * Save PHPExcel to file
  *
  * @param	string		$pFileName
  * @throws	Exception
  */
 public function save($pFilename = null)
 {
     // garbage collect
     $this->_phpExcel->garbageCollect();
     $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog;
     PHPExcel_Calculation::getInstance()->writeDebugLog = false;
     $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
     // initialize colors array
     $this->_colors = array();
     // Initialise workbook writer
     $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser);
     // Initialise worksheet writers
     $countSheets = $this->_phpExcel->getSheetCount();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i));
     }
     // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook.
     $this->_buildWorksheetEschers();
     $this->_buildWorkbookEscher();
     // add 15 identical cell style Xfs
     // for now, we use the first cellXf instead of cellStyleXf
     $cellXfCollection = $this->_phpExcel->getCellXfCollection();
     for ($i = 0; $i < 15; ++$i) {
         $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
     }
     // add all the cell Xfs
     foreach ($this->_phpExcel->getCellXfCollection() as $style) {
         $this->_writerWorkbook->addXfWriter($style, false);
     }
     // initialize OLE file
     $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book';
     $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
     // Write the worksheet streams before the global workbook stream,
     // because the byte sizes of these are needed in the global workbook stream
     $worksheetSizes = array();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i]->close();
         $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
     }
     // add binary data for global workbook stream
     $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
     // add binary data for sheet streams
     for ($i = 0; $i < $countSheets; ++$i) {
         $OLE->append($this->_writerWorksheets[$i]->getData());
     }
     $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE));
     // save the OLE file
     $res = $root->save($pFilename);
     PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
     PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog;
 }
Пример #15
0
 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     if (!is_null($this->_spreadSheet)) {
         // garbage collect
         $this->_spreadSheet->garbageCollect();
         // If $pFilename is php://output or php://stdout, make it a temporary file...
         $originalFilename = $pFilename;
         if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
             $pFilename = @tempnam('./', 'phpxltmp');
             if ($pFilename == '') {
                 $pFilename = $originalFilename;
             }
         }
         $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
         PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
         // Create string lookup table
         $this->_stringTable = array();
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable);
         }
         // Create styles dictionaries
         $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet));
         $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet));
         $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet));
         $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet));
         $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet));
         // Create drawing dictionary
         $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet));
         // Create new ZIP file and open it for writing
         $objZip = new ZipArchive();
         // Try opening the ZIP file
         if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
             if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) {
                 throw new Exception("Could not open " . $pFilename . " for writing.");
             }
         }
         // Add [Content_Types].xml to ZIP file
         $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet));
         // Add relationships to ZIP file
         $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet));
         $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet));
         // Add document properties to ZIP file
         $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet));
         $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet));
         // Add theme to ZIP file
         $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet));
         // Add string table to ZIP file
         $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable));
         // Add styles to ZIP file
         $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet));
         // Add workbook to ZIP file
         $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet));
         // Add worksheets
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable));
         }
         // Add worksheet relationships (drawings, ...)
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             // Add relationships
             $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1));
             // Add drawing relationship parts
             if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->count() > 0) {
                 // Drawing relationships
                 $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i)));
                 // Drawings
                 $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i)));
             }
             // Add comment relationship parts
             if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) {
                 // VML Comments
                 $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i)));
                 // Comments
                 $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i)));
             }
             // Add header/footer relationship parts
             if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) {
                 // VML Drawings
                 $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i)));
                 // VML Drawing relationships
                 $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i)));
                 // Media
                 foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) {
                     $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath()));
                 }
             }
         }
         // Add media
         for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) {
             if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
                 $imageContents = null;
                 $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath();
                 if (strpos($imagePath, 'zip://') !== false) {
                     $imagePath = substr($imagePath, 6);
                     $imagePathSplitted = explode('#', $imagePath);
                     $imageZip = new ZipArchive();
                     $imageZip->open($imagePathSplitted[0]);
                     $imageContents = $imageZip->getFromName($imagePathSplitted[1]);
                     $imageZip->close();
                     unset($imageZip);
                 } else {
                     $imageContents = file_get_contents($imagePath);
                 }
                 $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
             } else {
                 if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
                     ob_start();
                     call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource());
                     $imageContents = ob_get_contents();
                     ob_end_clean();
                     $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
                 }
             }
         }
         PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
         // Close file
         if ($objZip->close() === false) {
             throw new Exception("Could not close zip file {$pFilename}.");
         }
         // If a temporary file was used, copy it to the correct file stream
         if ($originalFilename != $pFilename) {
             if (copy($pFilename, $originalFilename) === false) {
                 throw new Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
             }
             @unlink($pFilename);
         }
     } else {
         throw new Exception("PHPExcel object unassigned.");
     }
 }
Пример #16
0
 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFilename
  * @throws 	PHPExcel_Writer_Exception
  */
 public function save($pFilename = null)
 {
     if ($this->_spreadSheet !== NULL) {
         // garbage collect
         $this->_spreadSheet->garbageCollect();
         // If $pFilename is php://output or php://stdout, make it a temporary file...
         $originalFilename = $pFilename;
         if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
             $pFilename = @tempnam(PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp');
             if ($pFilename == '') {
                 $pFilename = $originalFilename;
             }
         }
         $saveDebugLog = PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->getWriteDebugLog();
         PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog(FALSE);
         $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
         PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
         // Create string lookup table
         $this->_stringTable = array();
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable);
         }
         // Create styles dictionaries
         $this->_styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->_spreadSheet));
         $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet));
         $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet));
         $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet));
         $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet));
         $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet));
         // Create drawing dictionary
         $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet));
         // Create new ZIP file and open it for writing
         $zipClass = PHPExcel_Settings::getZipClass();
         $objZip = new $zipClass();
         //	Retrieve OVERWRITE and CREATE constants from the instantiated zip class
         //	This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP
         $ro = new ReflectionObject($objZip);
         $zipOverWrite = $ro->getConstant('OVERWRITE');
         $zipCreate = $ro->getConstant('CREATE');
         if (file_exists($pFilename)) {
             unlink($pFilename);
         }
         // Try opening the ZIP file
         if ($objZip->open($pFilename, $zipOverWrite) !== true) {
             if ($objZip->open($pFilename, $zipCreate) !== true) {
                 throw new PHPExcel_Writer_Exception("Could not open " . $pFilename . " for writing.");
             }
         }
         // Add [Content_Types].xml to ZIP file
         $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet, $this->_includeCharts));
         //if hasMacros, add the vbaProject.bin file, Certificate file(if exists)
         if ($this->_spreadSheet->hasMacros()) {
             $macrosCode = $this->_spreadSheet->getMacrosCode();
             if (!is_null($macrosCode)) {
                 // we have the code ?
                 $objZip->addFromString('xl/vbaProject.bin', $macrosCode);
                 //allways in 'xl', allways named vbaProject.bin
                 if ($this->_spreadSheet->hasMacrosCertificate()) {
                     //signed macros ?
                     // Yes : add the certificate file and the related rels file
                     $objZip->addFromString('xl/vbaProjectSignature.bin', $this->_spreadSheet->getMacrosCertificate());
                     $objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->_spreadSheet));
                 }
             }
         }
         //a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels)
         if ($this->_spreadSheet->hasRibbon()) {
             $tmpRibbonTarget = $this->_spreadSheet->getRibbonXMLData('target');
             $objZip->addFromString($tmpRibbonTarget, $this->_spreadSheet->getRibbonXMLData('data'));
             if ($this->_spreadSheet->hasRibbonBinObjects()) {
                 $tmpRootPath = dirname($tmpRibbonTarget) . '/';
                 $ribbonBinObjects = $this->_spreadSheet->getRibbonBinObjects('data');
                 //the files to write
                 foreach ($ribbonBinObjects as $aPath => $aContent) {
                     $objZip->addFromString($tmpRootPath . $aPath, $aContent);
                 }
                 //the rels for files
                 $objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->_spreadSheet));
             }
         }
         // Add relationships to ZIP file
         $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet));
         $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet));
         // Add document properties to ZIP file
         $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet));
         $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet));
         $customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->_spreadSheet);
         if ($customPropertiesPart !== NULL) {
             $objZip->addFromString('docProps/custom.xml', $customPropertiesPart);
         }
         // Add theme to ZIP file
         $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet));
         // Add string table to ZIP file
         $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable));
         // Add styles to ZIP file
         $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet));
         // Add workbook to ZIP file
         $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet, $this->_preCalculateFormulas));
         $chartCount = 0;
         // Add worksheets
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable, $this->_includeCharts));
             if ($this->_includeCharts) {
                 $charts = $this->_spreadSheet->getSheet($i)->getChartCollection();
                 if (count($charts) > 0) {
                     foreach ($charts as $chart) {
                         $objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart));
                         $chartCount++;
                     }
                 }
             }
         }
         $chartRef1 = $chartRef2 = 0;
         // Add worksheet relationships (drawings, ...)
         for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
             // Add relationships
             $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1, $this->_includeCharts));
             $drawings = $this->_spreadSheet->getSheet($i)->getDrawingCollection();
             $drawingCount = count($drawings);
             if ($this->_includeCharts) {
                 $chartCount = $this->_spreadSheet->getSheet($i)->getChartCount();
             }
             // Add drawing and image relationship parts
             if ($drawingCount > 0 || $chartCount > 0) {
                 // Drawing relationships
                 $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i), $chartRef1, $this->_includeCharts));
                 // Drawings
                 $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i), $chartRef2, $this->_includeCharts));
             }
             // Add comment relationship parts
             if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) {
                 // VML Comments
                 $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i)));
                 // Comments
                 $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i)));
             }
             // Add header/footer relationship parts
             if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) {
                 // VML Drawings
                 $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i)));
                 // VML Drawing relationships
                 $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i)));
                 // Media
                 foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) {
                     $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath()));
                 }
             }
         }
         // Add media
         for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) {
             if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
                 $imageContents = null;
                 $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath();
                 if (strpos($imagePath, 'zip://') !== false) {
                     $imagePath = substr($imagePath, 6);
                     $imagePathSplitted = explode('#', $imagePath);
                     $imageZip = new ZipArchive();
                     $imageZip->open($imagePathSplitted[0]);
                     $imageContents = $imageZip->getFromName($imagePathSplitted[1]);
                     $imageZip->close();
                     unset($imageZip);
                 } else {
                     $imageContents = file_get_contents($imagePath);
                 }
                 $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
             } else {
                 if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
                     ob_start();
                     call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource());
                     $imageContents = ob_get_contents();
                     ob_end_clean();
                     $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
                 }
             }
         }
         PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
         PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog);
         // Close file
         if ($objZip->close() === false) {
             throw new PHPExcel_Writer_Exception("Could not close zip file {$pFilename}.");
         }
         // If a temporary file was used, copy it to the correct file stream
         if ($originalFilename != $pFilename) {
             if (copy($pFilename, $originalFilename) === false) {
                 throw new PHPExcel_Writer_Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
             }
             @unlink($pFilename);
         }
     } else {
         throw new PHPExcel_Writer_Exception("PHPExcel object unassigned.");
     }
 }
 /**
  * TEXTFORMAT
  *
  * @param	mixed	$value	Value to check
  * @param	string	$format	Format mask to use
  * @return	boolean
  */
 public static function TEXTFORMAT($value, $format)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
     if (is_string($value) && !is_numeric($value) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
         $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
     }
     return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
 }
Пример #18
0
 /**
  * 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;
 }
Пример #19
0
 /**
  * LOOKUP
  * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
  * @param	lookup_value	The value that you want to match in lookup_array
  * @param	lookup_vector	The range of cells being searched
  * @param	result_vector	The column from which the matching value must be returned
  * @return	mixed			The value of the found cell
  */
 public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
 {
     $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
     if (!is_array($lookup_vector)) {
         return PHPExcel_Calculation_Functions::NA();
     }
     $lookupRows = count($lookup_vector);
     $l = array_keys($lookup_vector);
     $l = array_shift($l);
     $lookupColumns = count($lookup_vector[$l]);
     if ($lookupRows == 1 && $lookupColumns > 1 || $lookupRows == 2 && $lookupColumns != 2) {
         $lookup_vector = self::TRANSPOSE($lookup_vector);
         $lookupRows = count($lookup_vector);
         $l = array_keys($lookup_vector);
         $lookupColumns = count($lookup_vector[array_shift($l)]);
     }
     if (is_null($result_vector)) {
         $result_vector = $lookup_vector;
     }
     $resultRows = count($result_vector);
     $l = array_keys($result_vector);
     $l = array_shift($l);
     $resultColumns = count($result_vector[$l]);
     if ($resultRows == 1 && $resultColumns > 1 || $resultRows == 2 && $resultColumns != 2) {
         $result_vector = self::TRANSPOSE($result_vector);
         $resultRows = count($result_vector);
         $r = array_keys($result_vector);
         $resultColumns = count($result_vector[array_shift($r)]);
     }
     if ($lookupRows == 2) {
         $result_vector = array_pop($lookup_vector);
         $lookup_vector = array_shift($lookup_vector);
     }
     if ($lookupColumns != 2) {
         foreach ($lookup_vector as &$value) {
             if (is_array($value)) {
                 $k = array_keys($value);
                 $key1 = $key2 = array_shift($k);
                 $key2++;
                 $dataValue1 = $value[$key1];
             } else {
                 $key1 = 0;
                 $key2 = 1;
                 $dataValue1 = $value;
             }
             $dataValue2 = array_shift($result_vector);
             if (is_array($dataValue2)) {
                 $dataValue2 = array_shift($dataValue2);
             }
             $value = array($key1 => $dataValue1, $key2 => $dataValue2);
         }
         unset($value);
     }
     return self::VLOOKUP($lookup_value, $lookup_vector, 2);
 }
 /**
  * Extract range values
  *
  * @param	string				&$pRange	String based range representation
  * @param	PHPExcel_Worksheet	$pSheet		Worksheet
  * @return  mixed				Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  * @throws	Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = null, $resetLog = true)
 {
     // Return value
     $returnValue = array();
     //		echo 'extractNamedRange('.$pRange.')<br />';
     if (!is_null($pSheet)) {
         //			echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
         //			echo 'Range reference is '.$pRange.'<br />';
         if (strpos($pRange, '!') !== false) {
             //				echo '$pRange reference includes sheet reference<br />';
             $worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             $pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
             //				echo 'New sheet name is '.$pSheet->getTitle().'<br />';
             $pRange = $worksheetReference[1];
             //				echo 'Adjusted Range reference is '.$pRange.'<br />';
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if (!is_null($namedRange)) {
             $pSheet = $namedRange->getWorksheet();
             ////			echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             ////				echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
             //				if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
             //					if (!$namedRange->getLocalOnly()) {
             //						$pSheet = $namedRange->getWorksheet();
             //					} else {
             //						return $returnValue;
             //					}
             //				}
         } else {
             return PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         if (count($aReferences) == 1) {
             list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = NULL;
             }
         } else {
             // Extract cell data
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
                 //					echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = NULL;
                 }
             }
         }
         //				print_r($returnValue);
         //			echo '<br />';
     }
     // Return
     return $returnValue;
 }
Пример #21
0
 /**
  * EOMONTH
  *
  * Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
  * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
  *
  * @param	long	$dateValue			Excel date serial value or a standard date string
  * @param	int		$adjustmentMonths	Number of months to adjust by
  * @return	long	Excel date serial value
  */
 public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
 {
     $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
     $adjustmentMonths = floor(PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths));
     if (!is_numeric($adjustmentMonths)) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     if (is_string($dateValue = self::_getDateValue($dateValue))) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     // Execute function
     $PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1);
     $adjustDays = (int) $PHPDateObject->format('d');
     $adjustDaysString = '-' . $adjustDays . ' days';
     $PHPDateObject->modify($adjustDaysString);
     switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
         case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
             return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
             break;
         case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
             return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
             break;
         case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
             return $PHPDateObject;
             break;
     }
 }
Пример #22
0
 /**
  * Convert a value in a pre-defined format to a PHP string
  *
  * @param mixed 	$value		Value to format
  * @param string 	$format		Format code
  * @param array		$callBack	Callback function for additional formatting of string
  * @return string	Formatted string
  */
 public static function toFormattedString($value = '', $format = '', $callBack = null)
 {
     // For now we do not treat strings although section 4 of a format code affects strings
     if (!is_numeric($value)) {
         return $value;
     }
     // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
     // it seems to round numbers to a total of 10 digits.
     if ($format === 'General') {
         return $value;
     }
     // Get the sections, there can be up to four sections
     $sections = explode(';', $format);
     // Fetch the relevant section depending on whether number is positive, negative, or zero?
     // Text not supported yet.
     // Here is how the sections apply to various values in Excel:
     //   1 section:   [POSITIVE/NEGATIVE/ZERO/TEXT]
     //   2 sections:  [POSITIVE/ZERO/TEXT] [NEGATIVE]
     //   3 sections:  [POSITIVE/TEXT] [NEGATIVE] [ZERO]
     //   4 sections:  [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
     switch (count($sections)) {
         case 1:
             $format = $sections[0];
             break;
         case 2:
             $format = $value >= 0 ? $sections[0] : $sections[1];
             $value = abs($value);
             // Use the absolute value
             break;
         case 3:
             $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
             $value = abs($value);
             // Use the absolute value
             break;
         case 4:
             $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
             $value = abs($value);
             // Use the absolute value
             break;
         default:
             // something is wrong, just use first section
             $format = $sections[0];
             break;
     }
     // Save format with color information for later use below
     $formatColor = $format;
     // Strip color information
     $color_regex = '/^\\[[a-zA-Z]+\\]/';
     $format = preg_replace($color_regex, '', $format);
     // Let's begin inspecting the format and converting the value to a formatted string
     if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {
         // datetime format
         // dvc: convert Excel formats to PHP date formats
         // strip off first part containing e.g. [$-F800] or [$USD-409]
         // general syntax: [$<Currency string>-<language info>]
         // language info is in hexadecimal
         $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
         // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
         $format = strtolower($format);
         $format = strtr($format, self::$_dateFormatReplacements);
         if (!strpos($format, 'A')) {
             // 24-hour time format
             $format = strtr($format, self::$_dateFormatReplacements24);
         } else {
             // 12-hour time format
             $format = strtr($format, self::$_dateFormatReplacements12);
         }
         $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value);
         $value = $dateObj->format($format);
     } else {
         if (preg_match('/%$/', $format)) {
             // % number format
             if ($format === self::FORMAT_PERCENTAGE) {
                 $value = round(100 * $value, 0) . '%';
             } else {
                 if (preg_match('/\\.[#0]+/i', $format, $m)) {
                     $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
                     $format = str_replace($m[0], $s, $format);
                 }
                 if (preg_match('/^[#0]+/', $format, $m)) {
                     $format = str_replace($m[0], strlen($m[0]), $format);
                 }
                 $format = '%' . str_replace('%', 'f%%', $format);
                 $value = sprintf($format, 100 * $value);
             }
         } else {
             if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
                 $value = 'EUR ' . sprintf('%1.2f', $value);
             } else {
                 // In Excel formats, "_" is used to add spacing, which we can't do in HTML
                 $format = preg_replace('/_./', '', $format);
                 // Some non-number characters are escaped with \, which we don't need
                 $format = preg_replace("/\\\\/", '', $format);
                 // Some non-number strings are quoted, so we'll get rid of the quotes
                 $format = preg_replace('/"/', '', $format);
                 // Find out if we need thousands separator
                 // This is indicated by a comma enclosed by a digit placeholder:
                 //		#,#   or   0,0
                 $useThousands = preg_match('/(#,#|0,0)/', $format);
                 if ($useThousands) {
                     $format = preg_replace('/0,0/', '00', $format);
                     $format = preg_replace('/#,#/', '##', $format);
                 }
                 // Scale thousands, millions,...
                 // This is indicated by a number of commas after a digit placeholder:
                 //		#,   or    0.0,,
                 $scale = 1;
                 // same as no scale
                 $matches = array();
                 if (preg_match('/(#|0)(,+)/', $format, $matches)) {
                     $scale = pow(1000, strlen($matches[2]));
                     // strip the commas
                     $format = preg_replace('/0,+/', '0', $format);
                     $format = preg_replace('/#,+/', '#', $format);
                 }
                 if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) {
                     //echo 'Format mask is fractional '.$format.' <br />';
                     if ($value != (int) $value) {
                         $sign = $value < 0 ? '-' : '';
                         $integerPart = floor(abs($value));
                         $decimalPart = trim(fmod(abs($value), 1), '0.');
                         $decimalLength = strlen($decimalPart);
                         $decimalDivisor = pow(10, $decimalLength);
                         $GCD = PHPExcel_Calculation_Functions::GCD($decimalPart, $decimalDivisor);
                         $adjustedDecimalPart = $decimalPart / $GCD;
                         $adjustedDecimalDivisor = $decimalDivisor / $GCD;
                         if (strpos($format, '0') !== false || substr($format, 0, 3) == '? ?') {
                             if ($integerPart == 0) {
                                 $integerPart = '';
                             }
                             $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         } else {
                             $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
                             $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         }
                     }
                 } else {
                     // Handle the number itself
                     // scale number
                     $value = $value / $scale;
                     // Strip #
                     $format = preg_replace('/\\#/', '', $format);
                     $number_regex = "/(0+)(\\.?)(0*)/";
                     $matches = array();
                     if (preg_match($number_regex, $format, $matches)) {
                         $left = $matches[1];
                         $dec = $matches[2];
                         $right = $matches[3];
                         // minimun width of formatted number (including dot)
                         $minWidth = strlen($left) + strlen($dec) + strlen($right);
                         if ($useThousands) {
                             $value = number_format($value, strlen($right), PHPExcel_Shared_String::getDecimalSeparator(), PHPExcel_Shared_String::getThousandsSeparator());
                         } else {
                             $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f";
                             $value = sprintf($sprintf_pattern, $value);
                         }
                         $value = preg_replace($number_regex, $value, $format);
                     }
                 }
             }
         }
     }
     // Additional formatting provided by callback function
     if ($callBack !== null) {
         list($writerInstance, $function) = $callBack;
         $value = $writerInstance->{$function}($value, $formatColor);
     }
     return $value;
 }
Пример #23
0
 /**
  * Extract range values
  *
  * @param	string				&$pRange	String based range representation
  * @param	PHPExcel_Worksheet	$pSheet		Worksheet
  * @return  mixed				Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  * @param	boolean				$resetLog	Flag indicating whether calculation log should be reset or not
  * @throws	PHPExcel_Calculation_Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE)
 {
     // Return value
     $returnValue = array();
     //		echo 'extractNamedRange('.$pRange.')<br />';
     if ($pSheet !== NULL) {
         $pSheetName = $pSheet->getTitle();
         //			echo 'Current sheet name is '.$pSheetName.'<br />';
         //			echo 'Range reference is '.$pRange.'<br />';
         if (strpos($pRange, '!') !== false) {
             //				echo '$pRange reference includes sheet reference',PHP_EOL;
             list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             //				echo 'New sheet name is '.$pSheetName,PHP_EOL;
             //				echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
             $pSheet = $this->_workbook->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== NULL) {
             $pSheet = $namedRange->getWorksheet();
             //				echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             $splitRange = PHPExcel_Cell::splitRange($pRange);
             //	Convert row and column references
             if (ctype_alpha($splitRange[0][0])) {
                 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
             } elseif (ctype_digit($splitRange[0][0])) {
                 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
             }
             //				echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
             //				if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
             //					if (!$namedRange->getLocalOnly()) {
             //						$pSheet = $namedRange->getWorksheet();
             //					} else {
             //						return $returnValue;
             //					}
             //				}
         } else {
             return PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         //			var_dump($aReferences);
         if (!isset($aReferences[1])) {
             //	Single cell (or single column or row) in range
             list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
             $cellValue = NULL;
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = NULL;
             }
         } else {
             // Extract cell data for all cells in the range
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
                 //					echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
                 $cellValue = NULL;
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = NULL;
                 }
             }
         }
         //				print_r($returnValue);
         //			echo '<br />';
     }
     // Return
     return $returnValue;
 }
Пример #24
0
 private function _calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue)
 {
     $range = $columnID . $startRow . ':' . $columnID . $endRow;
     $dataValues = PHPExcel_Calculation_Functions::flattenArray($this->_workSheet->rangeToArray($range, NULL, TRUE, FALSE));
     $dataValues = array_filter($dataValues);
     if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
         rsort($dataValues);
     } else {
         sort($dataValues);
     }
     return array_pop(array_slice($dataValues, 0, $ruleValue));
 }
Пример #25
0
 /**
  * DGET
  *
  * Extracts a single value from a column of a list or database that matches conditions that you
  * specify.
  *
  * Excel Function:
  *        DGET(database,field,criteria)
  *
  * @access    public
  * @category Database Functions
  * @param    mixed[] $database The range of cells that makes up the list or database.
  *                                        A database is a list of related data in which rows of related
  *                                        information are records, and columns of data are fields. The
  *                                        first row of the list contains labels for each column.
  * @param    string|integer $field Indicates which column is used in the function. Enter the
  *                                        column label enclosed between double quotation marks, such as
  *                                        "Age" or "Yield," or a number (without quotation marks) that
  *                                        represents the position of the column within the list: 1 for
  *                                        the first column, 2 for the second column, and so on.
  * @param    mixed[] $criteria The range of cells that contains the conditions you specify.
  *                                        You can use any range for the criteria argument, as long as it
  *                                        includes at least one column label and at least one cell below
  *                                        the column label in which you specify a condition for the
  *                                        column.
  * @return    mixed
  *
  */
 public static function DGET($database, $field, $criteria)
 {
     $field = self::__fieldExtract($database, $field);
     if (is_null($field)) {
         return NULL;
     }
     //	reduce the database to a set of rows that match all the criteria
     $database = self::__filter($database, $criteria);
     //	extract an array of values for the requested column
     $colData = array();
     foreach ($database as $row) {
         $colData[] = $row[$field];
     }
     // Return
     if (count($colData) > 1) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     return $colData[0];
 }
Пример #26
0
 public static function _ifCondition($condition)
 {
     $condition = PHPExcel_Calculation_Functions::flattenSingleValue($condition);
     if (!in_array($condition[0], array('>', '<', '='))) {
         if (!is_numeric($condition)) {
             $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition));
         }
         return '=' . $condition;
     } else {
         preg_match('/([<>=]+)(.*)/', $condition, $matches);
         list(, $operator, $operand) = $matches;
         if (!is_numeric($operand)) {
             $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand));
         }
         return $operator . $operand;
     }
 }
Пример #27
0
 /**
  * STATEMENT_IF
  *
  * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
  *
  * Excel Function:
  *        =IF(condition[,returnIfTrue[,returnIfFalse]])
  *
  *        Condition is any value or expression that can be evaluated to TRUE or FALSE.
  *            For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
  *            the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
  *            This argument can use any comparison calculation operator.
  *        ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
  *            For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE,
  *            then the IF function returns the text "Within budget"
  *            If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use
  *            the logical value TRUE for this argument.
  *            ReturnIfTrue can be another formula.
  *        ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
  *            For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE,
  *            then the IF function returns the text "Over budget".
  *            If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
  *            If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
  *            ReturnIfFalse can be another formula.
  *
  * @access    public
  * @category  Logical Functions
  *
  * @param    mixed $condition     Condition to evaluate
  * @param    mixed $returnIfTrue  Value to return when condition is true
  * @param    mixed $returnIfFalse Optional value to return when condition is false
  *
  * @return    mixed    The value of returnIfTrue or returnIfFalse determined by condition
  */
 public static function STATEMENT_IF($condition = true, $returnIfTrue = 0, $returnIfFalse = false)
 {
     $condition = is_null($condition) ? true : (bool) PHPExcel_Calculation_Functions::flattenSingleValue($condition);
     $returnIfTrue = is_null($returnIfTrue) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfTrue);
     $returnIfFalse = is_null($returnIfFalse) ? false : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfFalse);
     return $condition ? $returnIfTrue : $returnIfFalse;
 }
Пример #28
0
 public function testVALUE()
 {
     $result = PHPExcel_Calculation_Functions::VALUE();
     $this->assertEquals('#VALUE!', $result);
 }
Пример #29
0
 /**
  * YIELDMAT
  *
  * Returns the annual yield of a security that pays interest at maturity.
  *
  * @param	mixed	settlement	The security's settlement date.
  *								The security's settlement date is the date after the issue date when the security is traded to the buyer.
  * @param	mixed	maturity	The security's maturity date.
  *								The maturity date is the date when the security expires.
  * @param	mixed	issue		The security's issue date.
  * @param	int		rate		The security's interest rate at date of issue.
  * @param	int		price		The security's price per $100 face value.
  * @param	int		basis		The type of day count to use.
  *										0 or omitted	US (NASD) 30/360
  *										1				Actual/actual
  *										2				Actual/360
  *										3				Actual/365
  *										4				European 30/360
  * @return	float
  */
 public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0)
 {
     $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
     $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
     $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
     $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
     $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
     $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
     //	Validate
     if (is_numeric($rate) && is_numeric($price)) {
         if ($rate <= 0 || $price <= 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement), $basis);
         if (!is_numeric($daysPerYear)) {
             return $daysPerYear;
         }
         $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
         if (!is_numeric($daysBetweenIssueAndSettlement)) {
             //	return date error
             return $daysBetweenIssueAndSettlement;
         }
         $daysBetweenIssueAndSettlement *= $daysPerYear;
         $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
         if (!is_numeric($daysBetweenIssueAndMaturity)) {
             //	return date error
             return $daysBetweenIssueAndMaturity;
         }
         $daysBetweenIssueAndMaturity *= $daysPerYear;
         $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
         if (!is_numeric($daysBetweenSettlementAndMaturity)) {
             //	return date error
             return $daysBetweenSettlementAndMaturity;
         }
         $daysBetweenSettlementAndMaturity *= $daysPerYear;
         return (1 + $daysBetweenIssueAndMaturity / $daysPerYear * $rate - ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate)) / ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }
 public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
 {
     if ($this->_dataSource !== NULL) {
         $calcEngine = PHPExcel_Calculation::getInstance($worksheet->getParent());
         $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
         if ($flatten) {
             $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
             foreach ($this->_dataValues as &$dataValue) {
                 if (!empty($dataValue) && $dataValue[0] == '#') {
                     $dataValue = 0.0;
                 }
             }
             unset($dataValue);
         } else {
             $cellRange = explode('!', $this->_dataSource);
             if (count($cellRange) > 1) {
                 list(, $cellRange) = $cellRange;
             }
             $dimensions = PHPExcel_Cell::rangeDimension(str_replace('$', '', $cellRange));
             if ($dimensions[0] == 1 || $dimensions[1] == 1) {
                 $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
             } else {
                 $newArray = array_values(array_shift($newDataValues));
                 foreach ($newArray as $i => $newDataSet) {
                     $newArray[$i] = array($newDataSet);
                 }
                 foreach ($newDataValues as $newDataSet) {
                     $i = 0;
                     foreach ($newDataSet as $newDataVal) {
                         array_unshift($newArray[$i++], $newDataVal);
                     }
                 }
                 $this->_dataValues = $newArray;
             }
         }
         $this->_pointCount = count($this->_dataValues);
     }
 }