/**
* 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.");
}
}
