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); }
/** * 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); } }
/** * 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); }
/** * 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); } }
/** * 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); } } } }
/** * 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); }
/** * 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()); }
/** * 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."); } }
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; }
/** * 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__))); }
/** * 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); }
/** * 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); }
/** * 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 $cellData = $this->_convertNbsp($cellData); // convert newline "\n" to '<br>' $cellData = str_replace("\n", '<br/>', $cellData); // Check value if ($cellData == '') { $cellData = ' '; } // 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."); } }
/** * 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 = ' '; $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 = ' '; } // 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."); } }
/** * 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."); } }
/** * 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 = ' '; $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 = ' '; } // 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."); } }