/** * 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); } }
public static function convertCellData(\PHPExcel_Cell $cell) { $ret = array(); $datatype = $cell->getDataType(); if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) { $format = $cell->getStyle()->getNumberFormat()->getFormatCode(); if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) { $format = \excel2sql\Type::$numeric_convert_map[$format]; } $ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format]; $ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format); if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) { $ret['value'] = "'{$ret['value']}'"; } } else { $ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype]; if ($ret['value'] = $cell->getFormattedValue()) { if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) { $ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\""; } } else { $ret['value'] = "null"; } } return $ret; }
/** * 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); } }
/** * Format cell data type * @param \PHPExcel_Cell $cell * @return string */ protected function reformatCellDataType(\PHPExcel_Cell $cell) { $value = $cell->getValue(); //datetime if (\PHPExcel_Shared_Date::isDateTime($cell)) { $format = $this->cellFormat['dateTime']; return date($format, \PHPExcel_Shared_Date::ExcelToPHP($value)); } return $value; }
/** * 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."); } }
protected function readValue(ExcelCell $cell, &$isEmpty, $defaultValue = NULL) { $rawValue = $cell->getValue(); $type = $cell->getDataType(); switch ($type) { case ExcelDataType::TYPE_NULL: $isEmpty = TRUE; return $defaultValue; case ExcelDataType::TYPE_STRING2: case ExcelDataType::TYPE_STRING: case ExcelDataType::TYPE_INLINE: $value = (string) $rawValue; if ($this->isEmptyString($value)) { $isEmpty = TRUE; return $defaultValue; } break; case ExcelDataType::TYPE_NUMERIC: $value = (int) $rawValue; break; case ExcelDataType::TYPE_FORMULA: throw new DefaultException('Cannot read formular yet from cell, because getCalculatedValue is deprecated somehow. Please replace formula with value explicit.'); break; case ExcelDataType::TYPE_BOOL: $value = (bool) $rawValue; break; default: case ExcelDataType::TYPE_ERROR: throw new DefaultException('The DataType from Cell cannot be switched: ' . $type); } $isEmpty = FALSE; return $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; }
public function __construct(\PHPExcel_Cell $cell) { # SQL field names cannot contain spaces $this->name = preg_replace("#[^\\w]#", '_', $cell->getValue()); $this->members = array(); }
/** * 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."); } }