Exemplo n.º 1
0
 public static function write(PHPExcel_Shared_XMLWriter $objWriter, PHPExcel_Cell $cell)
 {
     $comments = $cell->getWorksheet()->getComments();
     if (! isset($comments[$cell->getCoordinate()])) {
         return;
     }
     $comment = $comments[$cell->getCoordinate()];
     
     $objWriter->startElement('office:annotation');
     // $objWriter->writeAttribute('draw:style-name', 'gr1');
     // $objWriter->writeAttribute('draw:text-style-name', 'P1');
     $objWriter->writeAttribute('svg:width', $comment->getWidth());
     $objWriter->writeAttribute('svg:height', $comment->getHeight());
     $objWriter->writeAttribute('svg:x', $comment->getMarginLeft());
     $objWriter->writeAttribute('svg:y', $comment->getMarginTop());
     // $objWriter->writeAttribute('draw:caption-point-x', $comment->getMarginLeft());
     // $objWriter->writeAttribute('draw:caption-point-y', $comment->getMarginTop());
     $objWriter->writeElement('dc:creator', $comment->getAuthor());
     // TODO: Not realized in PHPExcel_Comment yet.
     // $objWriter->writeElement('dc:date', $comment->getDate());
     $objWriter->writeElement('text:p', $comment->getText()
         ->getPlainText());
     // $objWriter->writeAttribute('draw:text-style-name', 'P1');
     $objWriter->endElement();
 }
 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for time e.g. '9:45', '09:45'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
             list($h, $m) = explode(':', $value);
             $days = $h / 24 + $m / 1440;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
             return true;
         }
         // Check for date
         if (strtotime($value) !== false) {
             // make sure we have UTC for the sake of strtotime
             $saveTimeZone = date_default_timezone_get();
             date_default_timezone_set('UTC');
             // Convert value to Excel date
             $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             // restore original value for timezone
             date_default_timezone_set($saveTimeZone);
             return true;
         }
     }
     // Style logic - Numbers
     if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         // Leading zeroes?
         if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
             // Convert value to string
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }
Exemplo n.º 3
0
 /**
  * Create a new PHPExcel_RichText instance
  *
  * @param 	PHPExcel_Cell	$pParent
  * @throws	Exception
  */
 public function __construct(PHPExcel_Cell $pCell = null)
 {
     // Initialise variables
     $this->_richTextElements = array();
     // Set parent?
     if (!is_null($pCell)) {
         // Set parent cell
         $this->_parent = $pCell;
         // Add cell text and style
         if ($this->_parent->getValue() != "") {
             $objRun = new PHPExcel_RichText_Run($this->_parent->getValue());
             $objRun->setFont(clone $this->_parent->getParent()->getStyle($this->_parent->getCoordinate())->getFont());
             $this->addText($objRun);
         }
         // Set parent value
         $this->_parent->setValue($this);
     }
 }
Exemplo n.º 4
0
 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for date
         if (strtotime($value) !== false) {
             // Convert value to Excel date
             $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             return true;
         }
     }
     // Style logic - Numbers
     if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         // Leading zeroes?
         if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
             // Convert value to string
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }
Exemplo n.º 5
0
 /**
  * Create a new PHPExcel_RichText instance
  *
  * @param 	PHPExcel_Cell	$pParent
  * @throws	PHPExcel_Exception
  */
 public function __construct(PHPExcel_Cell $pCell = null)
 {
     // Initialise variables
     $this->_richTextElements = array();
     // Rich-Text string attached to cell?
     if ($pCell !== NULL) {
         // Add cell text and style
         if ($pCell->getValue() != "") {
             $objRun = new PHPExcel_RichText_Run($pCell->getValue());
             $objRun->setFont(clone $pCell->getParent()->getStyle($pCell->getCoordinate())->getFont());
             $this->addText($objRun);
         }
         // Set parent value
         $pCell->setValueExplicit($this, PHPExcel_Cell_DataType::TYPE_STRING);
     }
 }
Exemplo n.º 6
0
 /**
  * Set parent
  *
  * @param PHPExcel_Cell	$value
  */
 public function setParent(PHPExcel_Cell $value)
 {
     // Set parent
     $this->_parent = $value;
     // Set parent value
     $this->_parent->setValue($this);
     // Verify style information
     $sheet = $this->_parent->getParent();
     $cellFont = $sheet->getStyle($this->_parent->getCoordinate())->getFont();
     foreach ($this->getRichTextElements() as $element) {
         if (!$element instanceof PHPExcel_RichText_Run) {
             continue;
         }
         if ($element->getFont()->getHashCode() == $sheet->getDefaultStyle()->getFont()->getHashCode()) {
             if ($element->getFont()->getHashCode() != $cellFont->getHashCode()) {
                 $element->setFont(clone $cellFont);
             }
         }
     }
 }
Exemplo n.º 7
0
 /**
  *	Add or Update a cell in cache
  *
  *	@param	PHPExcel_Cell	$cell		Cell to update
  *	@return	void
  *	@throws	Exception
  */
 public function updateCacheData(PHPExcel_Cell $cell)
 {
     $pCoord = $cell->getCoordinate();
     return $this->addCacheData($pCoord, $cell);
 }
Exemplo n.º 8
0
 /**
  * Is a given cell a date/time?
  *
  * @param	 PHPExcel_Cell	$pCell
  * @return	 boolean
  */
 public static function isDateTime(PHPExcel_Cell $pCell)
 {
     return self::isDateTimeFormat($pCell->getParent()->getStyle($pCell->getCoordinate())->getNumberFormat());
 }
Exemplo n.º 9
0
 /**
  * Write Cell
  *
  * @param	PHPExcel_Shared_XMLWriter	$objWriter				XML Writer
  * @param	PHPExcel_Worksheet			$pSheet					Worksheet
  * @param	PHPExcel_Cell				$pCell					Cell
  * @param	string[]					$pStringTable			String table
  * @param	string[]					$pFlippedStringTable	String table (flipped), for faster index searching
  * @throws	Exception
  */
 private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null)
 {
     if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
         // Cell
         $objWriter->startElement('c');
         $objWriter->writeAttribute('r', $pCell->getCoordinate());
         // Sheet styles
         if ($pCell->getXfIndex() != '') {
             $objWriter->writeAttribute('s', $pCell->getXfIndex());
         }
         // If cell value is supplied, write cell value
         if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
             // Map type
             $mappedType = $pCell->getDataType();
             // Write data type depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 's':
                     // String
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'f':
                     // Formula
                     $calculatedValue = null;
                     if ($this->getParentWriter()->getPreCalculateFormulas()) {
                         $calculatedValue = $pCell->getCalculatedValue();
                     } else {
                         $calculatedValue = $pCell->getValue();
                     }
                     if (is_string($calculatedValue)) {
                         $objWriter->writeAttribute('t', 'str');
                     }
                     break;
                 case 'e':
                     // Error
                     $objWriter->writeAttribute('t', $mappedType);
             }
             // Write data depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($pCell->getValue())));
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->startElement('is');
                             $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
                             $objWriter->endElement();
                         }
                     }
                     break;
                 case 's':
                     // String
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         if (isset($pFlippedStringTable[$pCell->getValue()])) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
                         }
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
                         }
                     }
                     break;
                 case 'f':
                     // Formula
                     $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                     if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
                         if ($this->getParentWriter()->getPreCalculateFormulas()) {
                             $calculatedValue = $pCell->getCalculatedValue();
                             if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
                                 $v = PHPExcel_Shared_String::FormatNumber($calculatedValue);
                                 $objWriter->writeElement('v', $v);
                             } else {
                                 $objWriter->writeElement('v', '0');
                             }
                         } else {
                             $objWriter->writeElement('v', '0');
                         }
                     }
                     break;
                 case 'n':
                     // Numeric
                     // force point as decimal separator in case current locale uses comma
                     $v = str_replace(',', '.', $pCell->getValue());
                     $objWriter->writeElement('v', $v);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
                     break;
                 case 'e':
                     // Error
                     if (substr($pCell->getValue(), 0, 1) == '=') {
                         $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                         $objWriter->writeElement('v', substr($pCell->getValue(), 1));
                     } else {
                         $objWriter->writeElement('v', $pCell->getValue());
                     }
                     break;
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
Exemplo n.º 10
0
 private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL)
 {
     if ($tokens == FALSE) {
         return FALSE;
     }
     //	If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
     //		so we store the parent cell collection so that we can re-attach it when necessary
     $pCellWorksheet = $pCell !== NULL ? $pCell->getWorksheet() : NULL;
     $pCellParent = $pCell !== NULL ? $pCell->getParent() : null;
     $stack = new PHPExcel_Calculation_Token_Stack();
     //	Loop through each token in turn
     foreach ($tokens as $tokenData) {
         //			print_r($tokenData);
         //			echo '<br />';
         $token = $tokenData['value'];
         //			echo '<b>Token is '.$token.'</b><br />';
         // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
         if (isset(self::$_binaryOperators[$token])) {
             //				echo 'Token is a binary operator<br />';
             //	We must have two operands, error if we don't
             if (($operand2Data = $stack->pop()) === NULL) {
                 return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
             }
             if (($operand1Data = $stack->pop()) === NULL) {
                 return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
             }
             $operand1 = self::_dataTestReference($operand1Data);
             $operand2 = self::_dataTestReference($operand2Data);
             //	Log what we're doing
             if ($token == ':') {
                 $this->_debugLog->writeDebugLog('Evaluating Range ', $this->_showValue($operand1Data['reference']), ' ', $token, ' ', $this->_showValue($operand2Data['reference']));
             } else {
                 $this->_debugLog->writeDebugLog('Evaluating ', $this->_showValue($operand1), ' ', $token, ' ', $this->_showValue($operand2));
             }
             //	Process the operation in the appropriate manner
             switch ($token) {
                 //	Comparison (Boolean) Operators
                 case '>':
                     //	Greater than
                 //	Greater than
                 case '<':
                     //	Less than
                 //	Less than
                 case '>=':
                     //	Greater than or Equal to
                 //	Greater than or Equal to
                 case '<=':
                     //	Less than or Equal to
                 //	Less than or Equal to
                 case '=':
                     //	Equality
                 //	Equality
                 case '<>':
                     //	Inequality
                     $this->_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
                     break;
                     //	Binary Operators
                 //	Binary Operators
                 case ':':
                     //	Range
                     $sheet1 = $sheet2 = '';
                     if (strpos($operand1Data['reference'], '!') !== FALSE) {
                         list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']);
                     } else {
                         $sheet1 = $pCellParent !== NULL ? $pCellWorksheet->getTitle() : '';
                     }
                     if (strpos($operand2Data['reference'], '!') !== FALSE) {
                         list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']);
                     } else {
                         $sheet2 = $sheet1;
                     }
                     if ($sheet1 == $sheet2) {
                         if ($operand1Data['reference'] === NULL) {
                             if (trim($operand1Data['value']) != '' && is_numeric($operand1Data['value'])) {
                                 $operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value'];
                             } elseif (trim($operand1Data['reference']) == '') {
                                 $operand1Data['reference'] = $pCell->getCoordinate();
                             } else {
                                 $operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow();
                             }
                         }
                         if ($operand2Data['reference'] === NULL) {
                             if (trim($operand2Data['value']) != '' && is_numeric($operand2Data['value'])) {
                                 $operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value'];
                             } elseif (trim($operand2Data['reference']) == '') {
                                 $operand2Data['reference'] = $pCell->getCoordinate();
                             } else {
                                 $operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow();
                             }
                         }
                         $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference']));
                         $oCol = $oRow = array();
                         foreach ($oData as $oDatum) {
                             $oCR = PHPExcel_Cell::coordinateFromString($oDatum);
                             $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1;
                             $oRow[] = $oCR[1];
                         }
                         $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
                         if ($pCellParent !== NULL) {
                             $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($sheet1), FALSE);
                         } else {
                             return $this->_raiseFormulaError('Unable to access Cell Reference');
                         }
                         $stack->push('Cell Reference', $cellValue, $cellRef);
                     } else {
                         $stack->push('Error', PHPExcel_Calculation_Functions::REF(), NULL);
                     }
                     break;
                 case '+':
                     //	Addition
                     $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'plusEquals', $stack);
                     break;
                 case '-':
                     //	Subtraction
                     $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'minusEquals', $stack);
                     break;
                 case '*':
                     //	Multiplication
                     $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayTimesEquals', $stack);
                     break;
                 case '/':
                     //	Division
                     $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayRightDivide', $stack);
                     break;
                 case '^':
                     //	Exponential
                     $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'power', $stack);
                     break;
                 case '&':
                     //	Concatenation
                     //	If either of the operands is a matrix, we need to treat them both as matrices
                     //		(converting the other operand to a matrix if need be); then perform the required
                     //		matrix operation
                     if (is_bool($operand1)) {
                         $operand1 = $operand1 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
                     }
                     if (is_bool($operand2)) {
                         $operand2 = $operand2 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
                     }
                     if (is_array($operand1) || is_array($operand2)) {
                         //	Ensure that both operands are arrays/matrices
                         self::_checkMatrixOperands($operand1, $operand2, 2);
                         try {
                             //	Convert operand 1 from a PHP array to a matrix
                             $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
                             //	Perform the required operation against the operand 1 matrix, passing in operand 2
                             $matrixResult = $matrix->concat($operand2);
                             $result = $matrixResult->getArray();
                         } catch (PHPExcel_Exception $ex) {
                             $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
                             $result = '#VALUE!';
                         }
                     } else {
                         $result = '"' . str_replace('""', '"', self::_unwrapResult($operand1, '"') . self::_unwrapResult($operand2, '"')) . '"';
                     }
                     $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
                     $stack->push('Value', $result);
                     break;
                 case '|':
                     //	Intersect
                     $rowIntersect = array_intersect_key($operand1, $operand2);
                     $cellIntersect = $oCol = $oRow = array();
                     foreach (array_keys($rowIntersect) as $row) {
                         $oRow[] = $row;
                         foreach ($rowIntersect[$row] as $col => $data) {
                             $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1;
                             $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]);
                         }
                     }
                     $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
                     $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($cellIntersect));
                     $stack->push('Value', $cellIntersect, $cellRef);
                     break;
             }
             // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
         } elseif ($token === '~' || $token === '%') {
             //				echo 'Token is a unary operator<br />';
             if (($arg = $stack->pop()) === NULL) {
                 return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
             }
             $arg = $arg['value'];
             if ($token === '~') {
                 //					echo 'Token is a negation operator<br />';
                 $this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->_showValue($arg));
                 $multiplier = -1;
             } else {
                 //					echo 'Token is a percentile operator<br />';
                 $this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->_showValue($arg));
                 $multiplier = 0.01;
             }
             if (is_array($arg)) {
                 self::_checkMatrixOperands($arg, $multiplier, 2);
                 try {
                     $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg);
                     $matrixResult = $matrix1->arrayTimesEquals($multiplier);
                     $result = $matrixResult->getArray();
                 } catch (PHPExcel_Exception $ex) {
                     $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
                     $result = '#VALUE!';
                 }
                 $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
                 $stack->push('Value', $result);
             } else {
                 $this->_executeNumericBinaryOperation($cellID, $multiplier, $arg, '*', 'arrayTimesEquals', $stack);
             }
         } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) {
             $cellRef = NULL;
             //				echo 'Element '.$token.' is a Cell reference<br />';
             if (isset($matches[8])) {
                 //					echo 'Reference is a Range of cells<br />';
                 if ($pCell === NULL) {
                     //						We can't access the range, so return a REF error
                     $cellValue = PHPExcel_Calculation_Functions::REF();
                 } else {
                     $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10];
                     if ($matches[2] > '') {
                         $matches[2] = trim($matches[2], "\"'");
                         if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) {
                             //	It's a Reference to an external workbook (not currently supported)
                             return $this->_raiseFormulaError('Unable to access External Workbook');
                         }
                         $matches[2] = trim($matches[2], "\"'");
                         //							echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
                         $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
                         if ($pCellParent !== NULL) {
                             $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
                         } else {
                             return $this->_raiseFormulaError('Unable to access Cell Reference');
                         }
                         $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
                         //							$cellRef = $matches[2].'!'.$cellRef;
                     } else {
                         //							echo '$cellRef='.$cellRef.' in current worksheet<br />';
                         $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
                         if ($pCellParent !== NULL) {
                             $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
                         } else {
                             return $this->_raiseFormulaError('Unable to access Cell Reference');
                         }
                         $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
                     }
                 }
             } else {
                 //					echo 'Reference is a single Cell<br />';
                 if ($pCell === NULL) {
                     //						We can't access the cell, so return a REF error
                     $cellValue = PHPExcel_Calculation_Functions::REF();
                 } else {
                     $cellRef = $matches[6] . $matches[7];
                     if ($matches[2] > '') {
                         $matches[2] = trim($matches[2], "\"'");
                         if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) {
                             //	It's a Reference to an external workbook (not currently supported)
                             return $this->_raiseFormulaError('Unable to access External Workbook');
                         }
                         //							echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
                         $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
                         if ($pCellParent !== NULL) {
                             $cellSheet = $this->_workbook->getSheetByName($matches[2]);
                             if ($cellSheet && $cellSheet->cellExists($cellRef)) {
                                 $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
                                 $pCell->attach($pCellParent);
                             } else {
                                 $cellValue = NULL;
                             }
                         } else {
                             return $this->_raiseFormulaError('Unable to access Cell Reference');
                         }
                         $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
                         //							$cellRef = $matches[2].'!'.$cellRef;
                     } else {
                         //							echo '$cellRef='.$cellRef.' in current worksheet<br />';
                         $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
                         if ($pCellParent->isDataSet($cellRef)) {
                             $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
                             $pCell->attach($pCellParent);
                         } else {
                             $cellValue = NULL;
                         }
                         $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
                     }
                 }
             }
             $stack->push('Value', $cellValue, $cellRef);
             // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
         } elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) {
             //				echo 'Token is a function<br />';
             $functionName = $matches[1];
             $argCount = $stack->pop();
             $argCount = $argCount['value'];
             if ($functionName != 'MKMATRIX') {
                 $this->_debugLog->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', $argCount == 0 ? 'no' : $argCount, ' argument', $argCount == 1 ? '' : 's');
             }
             if (isset(self::$_PHPExcelFunctions[$functionName]) || isset(self::$_controlFunctions[$functionName])) {
                 // function
                 if (isset(self::$_PHPExcelFunctions[$functionName])) {
                     $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
                     $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']);
                     $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']);
                 } elseif (isset(self::$_controlFunctions[$functionName])) {
                     $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
                     $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']);
                     $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']);
                 }
                 // get the arguments for this function
                 //					echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
                 $args = $argArrayVals = array();
                 for ($i = 0; $i < $argCount; ++$i) {
                     $arg = $stack->pop();
                     $a = $argCount - $i - 1;
                     if ($passByReference && isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) && self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) {
                         if ($arg['reference'] === NULL) {
                             $args[] = $cellID;
                             if ($functionName != 'MKMATRIX') {
                                 $argArrayVals[] = $this->_showValue($cellID);
                             }
                         } else {
                             $args[] = $arg['reference'];
                             if ($functionName != 'MKMATRIX') {
                                 $argArrayVals[] = $this->_showValue($arg['reference']);
                             }
                         }
                     } else {
                         $args[] = self::_unwrapResult($arg['value']);
                         if ($functionName != 'MKMATRIX') {
                             $argArrayVals[] = $this->_showValue($arg['value']);
                         }
                     }
                 }
                 //	Reverse the order of the arguments
                 krsort($args);
                 if ($passByReference && $argCount == 0) {
                     $args[] = $cellID;
                     $argArrayVals[] = $this->_showValue($cellID);
                 }
                 //					echo 'Arguments are: ';
                 //					print_r($args);
                 //					echo '<br />';
                 if ($functionName != 'MKMATRIX') {
                     if ($this->_debugLog->getWriteDebugLog()) {
                         krsort($argArrayVals);
                         $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator . ' ', PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )');
                     }
                 }
                 //	Process each argument in turn, building the return value as an array
                 //					if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) {
                 //						$operand1 = $args[1];
                 //						$this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1));
                 //						$result = array();
                 //						$row = 0;
                 //						foreach($operand1 as $args) {
                 //							if (is_array($args)) {
                 //								foreach($args as $arg) {
                 //									$this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )');
                 //									$r = call_user_func_array($functionCall,$arg);
                 //									$this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
                 //									$result[$row][] = $r;
                 //								}
                 //								++$row;
                 //							} else {
                 //								$this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )');
                 //								$r = call_user_func_array($functionCall,$args);
                 //								$this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
                 //								$result[] = $r;
                 //							}
                 //						}
                 //					} else {
                 //	Process the argument with the appropriate function call
                 if ($passCellReference) {
                     $args[] = $pCell;
                 }
                 if (strpos($functionCall, '::') !== FALSE) {
                     $result = call_user_func_array(explode('::', $functionCall), $args);
                 } else {
                     foreach ($args as &$arg) {
                         $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg);
                     }
                     unset($arg);
                     $result = call_user_func_array($functionCall, $args);
                 }
                 //					}
                 if ($functionName != 'MKMATRIX') {
                     $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($result));
                 }
                 $stack->push('Value', self::_wrapResult($result));
             }
         } else {
             // if the token is a number, boolean, string or an Excel error, push it onto the stack
             if (isset(self::$_ExcelConstants[strtoupper($token)])) {
                 $excelConstant = strtoupper($token);
                 //					echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
                 $stack->push('Constant Value', self::$_ExcelConstants[$excelConstant]);
                 $this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->_showTypeDetails(self::$_ExcelConstants[$excelConstant]));
             } elseif (is_numeric($token) || $token === NULL || is_bool($token) || $token == '' || $token[0] == '"' || $token[0] == '#') {
                 //					echo 'Token is a number, boolean, string, null or an Excel error<br />';
                 $stack->push('Value', $token);
                 // if the token is a named range, push the named range name onto the stack
             } elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) {
                 //					echo 'Token is a named range<br />';
                 $namedRange = $matches[6];
                 //					echo 'Named Range is '.$namedRange.'<br />';
                 $this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange);
                 $cellValue = $this->extractNamedRange($namedRange, NULL !== $pCell ? $pCellWorksheet : NULL, FALSE);
                 $pCell->attach($pCellParent);
                 $this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->_showTypeDetails($cellValue));
                 $stack->push('Named Range', $cellValue, $namedRange);
             } else {
                 return $this->_raiseFormulaError("undefined variable '{$token}'");
             }
         }
     }
     // when we're out of tokens, the stack should have a single element, the final result
     if ($stack->count() != 1) {
         return $this->_raiseFormulaError("internal error");
     }
     $output = $stack->pop();
     $output = $output['value'];
     //		if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
     //			return array_shift(PHPExcel_Calculation_Functions::flattenArray($output));
     //		}
     return $output;
 }
Exemplo n.º 11
0
 /**
  * Get hash code
  *
  * @return string	Hash code
  */
 public function getHashCode()
 {
     return md5($this->_formula1 . $this->_formula2 . ($this->_type = PHPExcel_Cell_DataValidation::TYPE_NONE . ($this->_errorStyle = PHPExcel_Cell_DataValidation::STYLE_STOP . $this->_operator . ($this->_allowBlank ? 't' : 'f') . ($this->_showDropDown ? 't' : 'f') . ($this->_showInputMessage ? 't' : 'f') . ($this->_showErrorMessage ? 't' : 'f') . $this->_errorTitle . $this->_error . $this->_promptTitle . $this->_prompt . $this->_parent->getCoordinate() . __CLASS__)));
 }
Exemplo n.º 12
0
 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // sanitize UTF-8 strings
     if (is_string($value)) {
         $value = PHPExcel_Shared_String::SanitizeUTF8($value);
     }
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for time without seconds e.g. '9:45', '09:45'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
             list($h, $m) = explode(':', $value);
             $days = $h / 24 + $m / 1440;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
             return true;
         }
         // Check for time with seconds '9:45:59', '09:45:59'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) {
             list($h, $m, $s) = explode(':', $value);
             $days = $h / 24 + $m / 1440 + $s / 86400;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
             return true;
         }
         // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
         if (($v = PHPExcel_Shared_Date::stringToExcel($value)) !== false) {
             // Convert value to Excel date
             $cell->setValueExplicit($v, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style. Either there is a time part or not. Look for ':'
             if (strpos($value, ':') !== false) {
                 $formatCode = 'yyyy-mm-dd h:mm';
             } else {
                 $formatCode = 'yyyy-mm-dd';
             }
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode);
             return true;
         }
         // Check for newline character "\n"
         if (strpos($value, "\n") !== false) {
             $value = PHPExcel_Shared_String::SanitizeUTF8($value);
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(true);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }
Exemplo n.º 13
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;
 }
 /**
  *	Parse a cell formula and calculate its value
  *
  *	@param	string			$formula	The formula to parse and calculate
  *	@param	string			$cellID		The ID (e.g. A3) of the cell that we are calculating
  *	@param	PHPExcel_Cell	$pCell		Cell to calculate
  *	@return	mixed
  *	@throws	Exception
  */
 public function _calculateFormulaValue($formula, $cellID = null, PHPExcel_Cell $pCell = null)
 {
     //		echo '<b>'.$cellID.'</b><br />';
     $cellValue = '';
     //	Basic validation that this is indeed a formula
     //	We simply return the "cell value" (formula) if not
     $formula = trim($formula);
     if ($formula[0] != '=') {
         return self::_wrapResult($formula);
     }
     $formula = trim(substr($formula, 1));
     $formulaLength = strlen($formula);
     if ($formulaLength < 1) {
         return self::_wrapResult($formula);
     }
     // Is calculation cacheing enabled?
     if (!is_null($cellID)) {
         if ($this->_calculationCacheEnabled) {
             // Is the value present in calculation cache?
             //				echo 'Testing cache value<br />';
             if (isset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()])) {
                 //					echo 'Value is in cache<br />';
                 $this->_writeDebug($cellID, 'Testing cache value');
                 //	Is cache still valid?
                 if (time() + microtime() - $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] < $this->_calculationCacheExpirationTime) {
                     //						echo 'Cache time is still valid<br />';
                     $this->_writeDebug($cellID, 'Retrieving value from cache');
                     // Return the cached result
                     $returnValue = $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'];
                     //						echo 'Retrieving data value of '.$returnValue.' for '.$pCell->getCoordinate().' from cache<br />';
                     if (is_array($returnValue)) {
                         return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
                     }
                     return $returnValue;
                 } else {
                     //						echo 'Cache has expired<br />';
                     $this->_writeDebug($cellID, 'Cache value has expired');
                     //	Clear the cache if it's no longer valid
                     unset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]);
                 }
             }
         }
     }
     $this->debugLogStack[] = $cellID;
     //	Parse the formula onto the token stack and calculate the value
     $cellValue = $this->_processTokenStack($this->_parseFormula($formula), $cellID, $pCell);
     array_pop($this->debugLogStack);
     // Save to calculation cache
     if (!is_null($cellID)) {
         if ($this->_calculationCacheEnabled) {
             $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] = time() + microtime();
             $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'] = $cellValue;
         }
     }
     //	Return the calculated value
     if (is_array($cellValue)) {
         $cellValue = array_shift(PHPExcel_Calculation_Functions::flattenArray($cellValue));
     }
     return $cellValue;
 }
 /**
  * Bind value to a cell
  *
  * @param  PHPExcel_Cell  $cell  Cell to bind value to
  * @param  mixed $value          Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // sanitize UTF-8 strings
     if (is_string($value)) {
         $value = PHPExcel_Shared_String::SanitizeUTF8($value);
     }
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         //    Test for booleans using locale-setting
         if ($value == PHPExcel_Calculation::getTRUE()) {
             $cell->setValueExplicit(TRUE, PHPExcel_Cell_DataType::TYPE_BOOL);
             return true;
         } elseif ($value == PHPExcel_Calculation::getFALSE()) {
             $cell->setValueExplicit(FALSE, PHPExcel_Cell_DataType::TYPE_BOOL);
             return true;
         }
         // Check for number in scientific format
         if (preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER . '$/', $value)) {
             $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             return true;
         }
         // Check for fraction
         if (preg_match('/^([+-]?) *([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) {
             // Convert value to number
             $value = $matches[2] / $matches[3];
             if ($matches[1] == '-') {
                 $value = 0 - $value;
             }
             $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('??/??');
             return true;
         } elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) {
             // Convert value to number
             $value = $matches[2] + $matches[3] / $matches[4];
             if ($matches[1] == '-') {
                 $value = 0 - $value;
             }
             $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('# ??/??');
             return true;
         }
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $value = (double) str_replace('%', '', $value) / 100;
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
             return true;
         }
         // Check for currency
         $currencyCode = PHPExcel_Shared_String::getCurrencyCode();
         $decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator();
         $thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator();
         if (preg_match('/^' . preg_quote($currencyCode) . ' *(\\d{1,3}(' . preg_quote($thousandsSeparator) . '\\d{3})*|(\\d+))(' . preg_quote($decimalSeparator) . '\\d{2})?$/', $value)) {
             // Convert value to number
             $value = (double) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value));
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE));
             return true;
         } elseif (preg_match('/^\\$ *(\\d{1,3}(\\,\\d{3})*|(\\d+))(\\.\\d{2})?$/', $value)) {
             // Convert value to number
             $value = (double) trim(str_replace(array('$', ','), '', $value));
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
             return true;
         }
         // Check for time without seconds e.g. '9:45', '09:45'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
             // Convert value to number
             list($h, $m) = explode(':', $value);
             $days = $h / 24 + $m / 1440;
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
             return true;
         }
         // Check for time with seconds '9:45:59', '09:45:59'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) {
             // Convert value to number
             list($h, $m, $s) = explode(':', $value);
             $days = $h / 24 + $m / 1440 + $s / 86400;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
             return true;
         }
         // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
         if (($d = PHPExcel_Shared_Date::stringToExcel($value)) !== false) {
             // Convert value to number
             $cell->setValueExplicit($d, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Determine style. Either there is a time part or not. Look for ':'
             if (strpos($value, ':') !== false) {
                 $formatCode = 'yyyy-mm-dd h:mm';
             } else {
                 $formatCode = 'yyyy-mm-dd';
             }
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode);
             return true;
         }
         // Check for newline character "\n"
         if (strpos($value, "\n") !== FALSE) {
             $value = PHPExcel_Shared_String::SanitizeUTF8($value);
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getWorksheet()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(TRUE);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }
Exemplo n.º 16
0
 /**
  * Generate row
  *
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @param	array				$pValues		Array containing cells in a row
  * @param	int					$pRow			Row number
  * @return	string
  * @throws	Exception
  */
 private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
 {
     if (is_array($pValues)) {
         // Construct HTML
         $html = '';
         // Sheet index
         $sheetIndex = $pSheet->getParent()->getIndex($pSheet);
         // Write row start
         if (!$this->_useInlineCss) {
             $html .= '        <tr class="row' . $pRow . '">' . "\r\n";
         } else {
             $style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
             $html .= '        <tr style="' . $style . '">' . "\r\n";
         }
         // Write cells
         $colNum = 0;
         foreach ($pValues as $cell) {
             if (!$this->_useInlineCss) {
                 $cssClass = '';
                 $cssClass = 'column' . $colNum;
             } else {
                 $cssClass = array();
                 if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
                     $this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
                 }
             }
             $colSpan = 1;
             $rowSpan = 1;
             $writeCell = true;
             // Write cell
             // initialize
             $cellData = '';
             // PHPExcel_Cell
             if ($cell instanceof PHPExcel_Cell) {
                 // Value
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     // Loop trough rich text elements
                     $elements = $cell->getValue()->getRichTextElements();
                     foreach ($elements as $element) {
                         // Rich text start?
                         if ($element instanceof PHPExcel_RichText_Run) {
                             $cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">';
                             if ($element->getFont()->getSuperScript()) {
                                 $cellData .= '<sup>';
                             } else {
                                 if ($element->getFont()->getSubScript()) {
                                     $cellData .= '<sub>';
                                 }
                             }
                         }
                         // Convert UTF8 data to PCDATA
                         $cellText = $element->getText();
                         $cellData .= htmlspecialchars($cellText);
                         if ($element instanceof PHPExcel_RichText_Run) {
                             if ($element->getFont()->getSuperScript()) {
                                 $cellData .= '</sup>';
                             } else {
                                 if ($element->getFont()->getSubScript()) {
                                     $cellData .= '</sub>';
                                 }
                             }
                             $cellData .= '</span>';
                         }
                     }
                 } else {
                     if ($this->_preCalculateFormulas) {
                         $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
                     } else {
                         $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
                     }
                     // Convert UTF8 data to PCDATA
                     $cellData = htmlspecialchars($cellData);
                 }
                 // replace leading spaces on each line with &nbsp;
                 $cellData = $this->_convertNbsp($cellData);
                 // convert newline "\n" to '<br>'
                 $cellData = str_replace("\n", '<br/>', $cellData);
                 // Check value
                 if ($cellData == '') {
                     $cellData = '&nbsp;';
                 }
                 // Extend CSS class?
                 if (!$this->_useInlineCss) {
                     $cssClass .= ' style' . $cell->getXfIndex();
                     $cssClass .= ' ' . $cell->getDataType();
                 } else {
                     if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) {
                         $cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]);
                     }
                     // General horizontal alignment: Actual horizontal alignment depends on dataType
                     $sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
                     if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) {
                         $cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align'];
                     }
                 }
             } else {
                 $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', PHPExcel_Cell_DataType::TYPE_NULL, $pSheet);
             }
             // Hyperlink?
             if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
                 $cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
             }
             // Column/rowspan
             foreach ($pSheet->getMergeCells() as $cells) {
                 if ($cell->isInRange($cells)) {
                     list($first, ) = PHPExcel_Cell::splitRange($cells);
                     if ($first[0] == $cell->getCoordinate()) {
                         list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
                     } else {
                         $writeCell = false;
                     }
                     break;
                 }
             }
             // Write
             if ($writeCell) {
                 // Column start
                 $html .= '          <td';
                 if (!$this->_useInlineCss) {
                     $html .= ' class="' . $cssClass . '"';
                 } else {
                     //** Necessary redundant code for the sake of PHPExcel_Writer_PDF **
                     // We must explicitly write the width of the <td> element because TCPDF
                     // does not recognize e.g. <col style="width:42pt">
                     $width = 0;
                     $columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
                     for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) {
                         if (isset($this->_columnWidths[$sheetIndex][$i])) {
                             $width += $this->_columnWidths[$sheetIndex][$i];
                         }
                     }
                     $cssClass['width'] = $width . 'pt';
                     // We must also explicitly write the height of the <td> element because TCPDF
                     // does not recognize e.g. <tr style="height:50pt">
                     if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
                         $height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
                         $cssClass['height'] = $height;
                     }
                     //** end of redundant code **
                     $html .= ' style="' . $this->_assembleCSS($cssClass) . '"';
                 }
                 if ($colSpan > 1) {
                     $html .= ' colspan="' . $colSpan . '"';
                 }
                 if ($rowSpan > 1) {
                     $html .= ' rowspan="' . $rowSpan . '"';
                 }
                 $html .= '>';
                 // Image?
                 $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
                 // Cell data
                 $html .= $cellData;
                 // Column end
                 $html .= '</td>' . "\r\n";
             }
             // Next column
             ++$colNum;
         }
         // Write row end
         $html .= '        </tr>' . "\r\n";
         // Return
         return $html;
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
Exemplo n.º 17
0
 /**
  * Write row to HTML file
  * 
  * @param	mixed				$pFileHandle	PHP filehandle
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @param	array				$pValues		Array containing cells in a row
  * @param	int					$pRow			Row number
  * @throws	Exception
  */
 private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
 {
     if (!is_null($pFileHandle) && is_array($pValues)) {
         // Write row start
         fwrite($pFileHandle, '        <tr class="row' . $pRow . '">' . "\r\n");
         // Write cells
         $colNum = 0;
         foreach ($pValues as $cell) {
             $cellData = '&nbsp;';
             $cssClass = 'column' . $colNum;
             $colSpan = 1;
             $rowSpan = 1;
             $writeCell = true;
             // Write cell
             // PHPExcel_Cell
             if ($cell instanceof PHPExcel_Cell) {
                 // Value
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     // Loop trough rich text elements
                     $elements = $cell->getValue()->getRichTextElements();
                     foreach ($elements as $element) {
                         // Rich text start?
                         if ($element instanceof PHPExcel_RichText_Run) {
                             $cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
                         }
                         $cellData .= $element->getText();
                         if ($element instanceof PHPExcel_RichText_Run) {
                             $cellData .= '</span>';
                         }
                     }
                 } else {
                     if ($this->_preCalculateFormulas) {
                         $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
                     } else {
                         $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
                     }
                 }
                 // Check value
                 if ($cellData == '') {
                     $cellData = '&nbsp;';
                 }
                 // Extend CSS class?
                 if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
                     $cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode();
                 }
             } else {
                 $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
             }
             // Hyperlink?
             if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
                 $cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>';
             }
             // Column/rowspan
             foreach ($pSheet->getMergeCells() as $cells) {
                 if ($cell->isInRange($cells)) {
                     list($first, ) = PHPExcel_Cell::splitRange($cells);
                     if ($first == $cell->getCoordinate()) {
                         list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
                     } else {
                         $writeCell = false;
                     }
                     break;
                 }
             }
             // Write
             if ($writeCell) {
                 // Column start
                 fwrite($pFileHandle, '          <td');
                 fwrite($pFileHandle, ' class="' . $cssClass . '"');
                 if ($colSpan > 1) {
                     fwrite($pFileHandle, ' colspan="' . $colSpan . '"');
                 }
                 if ($rowSpan > 1) {
                     fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"');
                 }
                 fwrite($pFileHandle, '>');
                 // Image?
                 $this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate());
                 // Cell data
                 fwrite($pFileHandle, $cellData);
                 // Column end
                 fwrite($pFileHandle, '</td>' . "\r\n");
             }
             // Next column
             $colNum++;
         }
         // Write row end
         fwrite($pFileHandle, '        </tr>' . "\r\n");
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
Exemplo n.º 18
-1
 /**
  * Write Cell
  *
  * @param	PHPExcel_Shared_XMLWriter	$objWriter				XML Writer
  * @param	PHPExcel_Worksheet			$pSheet					Worksheet
  * @param	PHPExcel_Cell				$pCell					Cell
  * @param	string[]					$pStringTable			String table
  * @param	string[]					$pFlippedStringTable	String table (flipped), for faster index searching
  * @throws	Exception
  */
 private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null)
 {
     if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
         // Cell
         $objWriter->startElement('c');
         $objWriter->writeAttribute('r', $pCell->getCoordinate());
         // Sheet styles
         $aStyles = $pSheet->getStyles();
         if (isset($aStyles[$pCell->getCoordinate()])) {
             $styleIndex = $this->getParentWriter()->getStylesHashTable()->getIndexForHashCode($aStyles[$pCell->getCoordinate()]->getHashCode());
             if ($styleIndex != '') {
                 $objWriter->writeAttribute('s', $styleIndex);
             }
         }
         // If cell value is supplied, write cell value
         if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
             // Map type
             $mappedType = $pCell->getDataType();
             // Write data type depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 's':
                     // String
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'f':
                     // Formula
                     $calculatedValue = null;
                     if ($this->getParentWriter()->getPreCalculateFormulas()) {
                         $calculatedValue = $pCell->getCalculatedValue();
                     } else {
                         $calculatedValue = $pCell->getValue();
                     }
                     if (is_string($calculatedValue)) {
                         $objWriter->writeAttribute('t', 'str');
                     }
                     break;
             }
             // Write data depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML($pCell->getValue()));
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->startElement('is');
                             $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
                             $objWriter->endElement();
                         }
                     }
                     break;
                 case 's':
                     // String
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         if (isset($pFlippedStringTable[$pCell->getValue()])) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
                         }
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
                         }
                     }
                     break;
                 case 'f':
                     // Formula
                     $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                     if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
                         if ($this->getParentWriter()->getPreCalculateFormulas()) {
                             $calculatedValue = $pCell->getCalculatedValue();
                             if (substr($calculatedValue, 0, 1) != '#') {
                                 $objWriter->writeElement('v', $calculatedValue);
                             } else {
                                 $objWriter->writeElement('v', '0');
                             }
                         } else {
                             $objWriter->writeElement('v', '0');
                         }
                     }
                     break;
                 case 'n':
                     // Numeric
                     if (PHPExcel_Shared_Date::isDateTime($pCell)) {
                         $dateValue = $pCell->getValue();
                         if (is_string($dateValue)) {
                             //	Error string
                             $objWriter->writeElement('v', $pFlippedStringTable[$dateValue]);
                         } elseif (!is_float($dateValue)) {
                             //	PHP serialized date/time or date/time object
                             $objWriter->writeElement('v', PHPExcel_Shared_Date::PHPToExcel($dateValue));
                         } else {
                             //	Excel serialized date/time
                             $objWriter->writeElement('v', $dateValue);
                         }
                     } else {
                         $objWriter->writeElement('v', $pCell->getValue());
                     }
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
                     break;
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }
Exemplo n.º 19
-1
 /**
  * Generate row
  *
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @param	array				$pValues		Array containing cells in a row
  * @param	int					$pRow			Row number
  * @return	string
  * @throws	Exception
  */
 private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
 {
     if (is_array($pValues)) {
         // Construct HTML
         $html = '';
         // Sheet hashcode
         $sheetHash = $pSheet->getHashCode();
         // Write row start
         if (!$this->_useInlineCss) {
             $html .= '        <tr class="row' . $pRow . '">' . "\r\n";
         } else {
             $style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : '';
             $html .= '        <tr style="' . $style . '">' . "\r\n";
         }
         // Write cells
         $colNum = 0;
         foreach ($pValues as $cell) {
             $cellData = '&nbsp;';
             $cssClass = '';
             if (!$this->_useInlineCss) {
                 $cssClass = 'column' . $colNum;
             } else {
                 $cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : '';
             }
             $colSpan = 1;
             $rowSpan = 1;
             $writeCell = true;
             // Write cell
             // PHPExcel_Cell
             if ($cell instanceof PHPExcel_Cell) {
                 // Value
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     // Loop trough rich text elements
                     $elements = $cell->getValue()->getRichTextElements();
                     foreach ($elements as $element) {
                         // Rich text start?
                         if ($element instanceof PHPExcel_RichText_Run) {
                             $cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
                             if ($element->getFont()->getSuperScript()) {
                                 $cellData .= '<sup>';
                             } else {
                                 if ($element->getFont()->getSubScript()) {
                                     $cellData .= '<sub>';
                                 }
                             }
                         }
                         // Convert UTF8 data to PCDATA
                         $cellText = $element->getText();
                         $cellData .= htmlspecialchars($cellText);
                         if ($element instanceof PHPExcel_RichText_Run) {
                             if ($element->getFont()->getSuperScript()) {
                                 $cellData .= '</sup>';
                             } else {
                                 if ($element->getFont()->getSubScript()) {
                                     $cellData .= '</sub>';
                                 }
                             }
                             $cellData .= '</span>';
                         }
                     }
                 } else {
                     if ($this->_preCalculateFormulas) {
                         $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
                     } else {
                         $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
                     }
                     // Convert UTF8 data to PCDATA
                     $cellData = htmlspecialchars($cellData);
                 }
                 // Check value
                 if ($cellData == '') {
                     $cellData = '&nbsp;';
                 }
                 // Extend CSS class?
                 if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
                     if (!$this->_useInlineCss) {
                         $cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex();
                     } else {
                         $cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : '';
                     }
                 }
             } else {
                 $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
             }
             // Hyperlink?
             if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
                 $cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
             }
             // Column/rowspan
             foreach ($pSheet->getMergeCells() as $cells) {
                 if ($cell->isInRange($cells)) {
                     list($first, ) = PHPExcel_Cell::splitRange($cells);
                     if ($first == $cell->getCoordinate()) {
                         list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
                     } else {
                         $writeCell = false;
                     }
                     break;
                 }
             }
             // Write
             if ($writeCell) {
                 // Column start
                 $html .= '          <td';
                 if (!$this->_useInlineCss) {
                     $html .= ' class="' . $cssClass . '"';
                 } else {
                     $html .= ' style="' . $cssClass . '"';
                 }
                 if ($colSpan > 1) {
                     $html .= ' colspan="' . $colSpan . '"';
                 }
                 if ($rowSpan > 1) {
                     $html .= ' rowspan="' . $rowSpan . '"';
                 }
                 $html .= '>';
                 // Image?
                 $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
                 // Cell data
                 if ($this->_useInlineCss) {
                     $html .= '<span style="' . $cssClass . '">';
                 }
                 $html .= $cellData;
                 if ($this->_useInlineCss) {
                     $html .= '</span>';
                 }
                 // Column end
                 $html .= '</td>' . "\r\n";
             }
             // Next column
             ++$colNum;
         }
         // Write row end
         $html .= '        </tr>' . "\r\n";
         // Return
         return $html;
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }