/** * Create worksheet stringtable * * @param \PHPExcel\Worksheet $pSheet Worksheet * @param string[] $pExistingTable Existing table to eventually merge with * @return string[] String table for worksheet * @throws \PHPExcel\Writer\Exception */ public function createStringTable($pSheet = null, $pExistingTable = null) { if ($pSheet !== null) { // Create string lookup table $aStringTable = array(); $cellCollection = null; $aFlippedStringTable = null; // For faster lookup // Is an existing table given? if ($pExistingTable !== null && is_array($pExistingTable)) { $aStringTable = $pExistingTable; } // Fill index array $aFlippedStringTable = $this->flipStringTable($aStringTable); // Loop through cells foreach ($pSheet->getCellCollection() as $cellID) { $cell = $pSheet->getCell($cellID); $cellValue = $cell->getValue(); if (!is_object($cellValue) && $cellValue !== null && $cellValue !== '' && !isset($aFlippedStringTable[$cellValue]) && ($cell->getDataType() == \PHPExcel\Cell\DataType::TYPE_STRING || $cell->getDataType() == \PHPExcel\Cell\DataType::TYPE_STRING2 || $cell->getDataType() == \PHPExcel\Cell\DataType::TYPE_NULL)) { $aStringTable[] = $cellValue; $aFlippedStringTable[$cellValue] = true; } elseif ($cellValue instanceof \PHPExcel\RichText && $cellValue !== null && !isset($aFlippedStringTable[$cellValue->getHashCode()])) { $aStringTable[] = $cellValue; $aFlippedStringTable[$cellValue->getHashCode()] = true; } } return $aStringTable; } else { throw new \PHPExcel\Writer\Exception("Invalid \\PHPExcel\\Worksheet object passed."); } }
/** * Set Worksheet * * @param \PHPExcel\Worksheet $pValue * @param boolean $pOverrideOld If a Worksheet has already been assigned, overwrite it and remove image from old Worksheet? * @throws \PHPExcel\Exception * @return BaseDrawing */ public function setWorksheet(\PHPExcel\Worksheet $pValue = null, $pOverrideOld = false) { if (is_null($this->worksheet)) { // Add drawing to \PHPExcel\Worksheet $this->worksheet = $pValue; $this->worksheet->getCell($this->coordinates); $this->worksheet->getDrawingCollection()->append($this); } else { if ($pOverrideOld) { // Remove drawing from old \PHPExcel\Worksheet $iterator = $this->worksheet->getDrawingCollection()->getIterator(); while ($iterator->valid()) { if ($iterator->current()->getHashCode() == $this->getHashCode()) { $this->worksheet->getDrawingCollection()->offsetUnset($iterator->key()); $this->worksheet = null; break; } } // Set new \PHPExcel\Worksheet $this->setWorksheet($pValue); } else { throw new \PHPExcel\Exception("A \\PHPExcel\\Worksheet has already been assigned. Drawings can only exist on one \\PHPExcel\\Worksheet."); } } return $this; }
/** * Apply the AutoFilter rules to the AutoFilter Range * * @throws \PHPExcel\Exception * @return AutoFilter */ public function showHideRows() { list($rangeStart, $rangeEnd) = \PHPExcel\Cell::rangeBoundaries($this->range); // The heading row should always be visible // echo 'AutoFilter Heading Row ', $rangeStart[1],' is always SHOWN',PHP_EOL; $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true); $columnFilterTests = array(); foreach ($this->columns as $columnID => $filterColumn) { $rules = $filterColumn->getRules(); switch ($filterColumn->getFilterType()) { case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER: $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValues[] = $rule->getValue(); } // Test if we want to include blanks in our filter criteria $blanks = false; $ruleDataSet = array_filter($ruleValues); if (count($ruleValues) != count($ruleDataSet)) { $blanks = true; } if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) { // Filter on absolute values $columnFilterTests[$columnID] = array('method' => 'filterTestInSimpleDataSet', 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks)); } else { // Filter on date group values $arguments = array('date' => array(), 'time' => array(), 'dateTime' => array()); foreach ($ruleDataSet as $ruleValue) { $date = $time = ''; if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') { $date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); } if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') { $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); } if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') { $date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); } if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') { $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); } if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') { $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); } if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') { $time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); } $dateTime = $date . $time; $arguments['date'][] = $date; $arguments['time'][] = $time; $arguments['dateTime'][] = $dateTime; } // Remove empty elements $arguments['date'] = array_filter($arguments['date']); $arguments['time'] = array_filter($arguments['time']); $arguments['dateTime'] = array_filter($arguments['dateTime']); $columnFilterTests[$columnID] = array('method' => 'filterTestInDateGroupSet', 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks)); } break; case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: $customRuleForBlanks = false; $ruleValues = array(); // Build a list of the filter value selections foreach ($rules as $rule) { $ruleType = $rule->getRuleType(); $ruleValue = $rule->getValue(); if (!is_numeric($ruleValue)) { // Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards $ruleValue = preg_quote($ruleValue); $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue); if (trim($ruleValue) == '') { $customRuleForBlanks = true; $ruleValue = trim($ruleValue); } } $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); } $join = $filterColumn->getJoin(); $columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks)); break; case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: $ruleValues = array(); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $dynamicRuleType = $rule->getGrouping(); if ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) { // Number (Average) based // Calculate the average $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; $average = \PHPExcel\Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1')); // Set above/below rule based on greaterThan or LessTan $operator = $dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; $ruleValues[] = array('operator' => $operator, 'value' => $average); $columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR)); } else { // Date based if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') { // Month or Quarter sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period); if ($periodType == 'M') { $ruleValues = array($period); } else { --$period; $periodEnd = (1 + $period) * 3; $periodStart = 1 + $period * 3; $ruleValues = range($periodStart, $periodEnd); } $columnFilterTests[$columnID] = array('method' => 'filterTestInPeriodDateSet', 'arguments' => $ruleValues); $filterColumn->setAttributes(array()); } else { // Date Range $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn); break; } } } break; case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: $ruleValues = array(); $dataRowCount = $rangeEnd[1] - $rangeStart[1]; foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $toptenRuleType = $rule->getGrouping(); $ruleValue = $rule->getValue(); $ruleOperator = $rule->getOperator(); } if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) { $ruleValue = floor($ruleValue * ($dataRowCount / 100)); } if ($ruleValue < 1) { $ruleValue = 1; } if ($ruleValue > 500) { $ruleValue = 500; } $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue); $operator = $toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; $ruleValues[] = array('operator' => $operator, 'value' => $maxVal); $columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR)); $filterColumn->setAttributes(array('maxVal' => $maxVal)); break; } } // echo 'Column Filter Test CRITERIA',PHP_EOL; // var_dump($columnFilterTests); // // Execute the column tests for each row in the autoFilter range to determine show/hide, for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) { // echo 'Testing Row = ', $row,PHP_EOL; $result = true; foreach ($columnFilterTests as $columnID => $columnFilterTest) { // echo 'Testing cell ', $columnID.$row,PHP_EOL; $cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue(); // echo 'Value is ', $cellValue,PHP_EOL; // Execute the filter test $result = $result && call_user_func_array(array('\\PHPExcel\\Worksheet\\AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments'])); // echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL; // If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests if (!$result) { break; } } // Set show/hide for the row based on the result of the autoFilter result // echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL; $this->workSheet->getRowDimension($row)->setVisible($result); } return $this; }
public function refresh(\PHPExcel\Worksheet $worksheet, $flatten = true) { if ($this->dataSource !== null) { $calcEngine = \PHPExcel\Calculation::getInstance($worksheet->getParent()); $newDataValues = \PHPExcel\Calculation::unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->dataSource, null, $worksheet->getCell('A1'))); if ($flatten) { $this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues); foreach ($this->dataValues as &$dataValue) { if (!empty($dataValue) && $dataValue[0] == '#') { $dataValue = 0.0; } } unset($dataValue); } else { $cellRange = explode('!', $this->dataSource); if (count($cellRange) > 1) { list(, $cellRange) = $cellRange; } $dimensions = \PHPExcel\Cell::rangeDimension(str_replace('$', '', $cellRange)); if ($dimensions[0] == 1 || $dimensions[1] == 1) { $this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues); } else { $newArray = array_values(array_shift($newDataValues)); foreach ($newArray as $i => $newDataSet) { $newArray[$i] = array($newDataSet); } foreach ($newDataValues as $newDataSet) { $i = 0; foreach ($newDataSet as $newDataVal) { array_unshift($newArray[$i++], $newDataVal); } } $this->dataValues = $newArray; } } $this->pointCount = count($this->dataValues); } }
/** * Write Cell * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param \PHPExcel\Worksheet $pSheet Worksheet * @param \PHPExcel\Cell $pCellAddress Cell Address * @param string[] $pStringTable String table * @param string[] $pFlippedStringTable String table (flipped), for faster index searching * @throws \PHPExcel\Writer\Exception */ private function writeCell(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null) { if (is_array($pStringTable) && is_array($pFlippedStringTable)) { // Cell $pCell = $pSheet->getCell($pCellAddress); $objWriter->startElement('c'); $objWriter->writeAttribute('r', $pCellAddress); // Sheet styles if ($pCell->getXfIndex() != '') { $objWriter->writeAttribute('s', $pCell->getXfIndex()); } // If cell value is supplied, write cell value $cellValue = $pCell->getValue(); if (is_object($cellValue) || $cellValue !== '') { // Map type $mappedType = $pCell->getDataType(); // Write data type depending on its type switch (strtolower($mappedType)) { case 'inlinestr': // Inline string // Inline string case 's': // String // String case 'b': // Boolean $objWriter->writeAttribute('t', $mappedType); break; case 'f': // Formula $calculatedValue = $this->getParentWriter()->getPreCalculateFormulas() ? $pCell->getCalculatedValue() : $cellValue; 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 (!$cellValue instanceof \PHPExcel\RichText) { $objWriter->writeElement('t', \PHPExcel\Shared\StringHelper::controlCharacterPHP2OOXML(htmlspecialchars($cellValue))); } elseif ($cellValue instanceof \PHPExcel\RichText) { $objWriter->startElement('is'); $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $cellValue); $objWriter->endElement(); } break; case 's': // String if (!$cellValue instanceof \PHPExcel\RichText) { if (isset($pFlippedStringTable[$cellValue])) { $objWriter->writeElement('v', $pFlippedStringTable[$cellValue]); } } elseif ($cellValue instanceof \PHPExcel\RichText) { $objWriter->writeElement('v', $pFlippedStringTable[$cellValue->getHashCode()]); } break; case 'f': // Formula $attributes = $pCell->getFormulaAttributes(); if ($attributes['t'] == 'array') { $objWriter->startElement('f'); $objWriter->writeAttribute('t', 'array'); $objWriter->writeAttribute('ref', $pCellAddress); $objWriter->writeAttribute('aca', '1'); $objWriter->writeAttribute('ca', '1'); $objWriter->text(substr($cellValue, 1)); $objWriter->endElement(); } else { $objWriter->writeElement('f', substr($cellValue, 1)); } if ($this->getParentWriter()->getOffice2003Compatibility() === false) { if ($this->getParentWriter()->getPreCalculateFormulas()) { // $calculatedValue = $pCell->getCalculatedValue(); if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') { $objWriter->writeElement('v', \PHPExcel\Shared\StringHelper::formatNumber($calculatedValue)); } 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 $objWriter->writeElement('v', str_replace(',', '.', $cellValue)); break; case 'b': // Boolean $objWriter->writeElement('v', $cellValue ? '1' : '0'); break; case 'e': // Error if (substr($cellValue, 0, 1) == '=') { $objWriter->writeElement('f', substr($cellValue, 1)); $objWriter->writeElement('v', substr($cellValue, 1)); } else { $objWriter->writeElement('v', $cellValue); } break; } } $objWriter->endElement(); } else { throw new \PHPExcel\Writer\Exception("Invalid parameters passed."); } }