/** * @dataProvider providerBinaryComparisonOperation */ public function testBinaryComparisonOperation($formula, $expectedResultExcel, $expectedResultOpenOffice) { PHPExcel_Calculation_Functions::setCompatibilityMode(PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL); $resultExcel = \PHPExcel_Calculation::getInstance()->_calculateFormulaValue($formula); $this->assertEquals($expectedResultExcel, $resultExcel, 'should be Excel compatible'); PHPExcel_Calculation_Functions::setCompatibilityMode(PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE); $resultOpenOffice = \PHPExcel_Calculation::getInstance()->_calculateFormulaValue($formula); $this->assertEquals($expectedResultOpenOffice, $resultOpenOffice, 'should be OpenOffice compatible'); }
/** * Save PHPExcel to file * * @param string $pFilename * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { if ($this->_spreadSheet !== NULL) { // garbage collect $this->_spreadSheet->garbageCollect(); // If $pFilename is php://output or php://stdout, make it a temporary file... $originalFilename = $pFilename; if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') { $pFilename = @tempnam(PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp'); if ($pFilename == '') { $pFilename = $originalFilename; } } $saveDebugLog = PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog(FALSE); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Create string lookup table $this->_stringTable = array(); for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable); } // Create styles dictionaries $this->_styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->_spreadSheet)); $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet)); $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet)); $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet)); $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet)); $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet)); // Create drawing dictionary $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet)); // Create new ZIP file and open it for writing $zipClass = PHPExcel_Settings::getZipClass(); $objZip = new $zipClass(); // Retrieve OVERWRITE and CREATE constants from the instantiated zip class // This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP $ro = new ReflectionObject($objZip); $zipOverWrite = $ro->getConstant('OVERWRITE'); $zipCreate = $ro->getConstant('CREATE'); if (file_exists($pFilename)) { unlink($pFilename); } // Try opening the ZIP file if ($objZip->open($pFilename, $zipOverWrite) !== true) { if ($objZip->open($pFilename, $zipCreate) !== true) { throw new PHPExcel_Writer_Exception("Could not open " . $pFilename . " for writing."); } } // Add [Content_Types].xml to ZIP file $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet, $this->_includeCharts)); //if hasMacros, add the vbaProject.bin file, Certificate file(if exists) if ($this->_spreadSheet->hasMacros()) { $macrosCode = $this->_spreadSheet->getMacrosCode(); if (!is_null($macrosCode)) { // we have the code ? $objZip->addFromString('xl/vbaProject.bin', $macrosCode); //allways in 'xl', allways named vbaProject.bin if ($this->_spreadSheet->hasMacrosCertificate()) { //signed macros ? // Yes : add the certificate file and the related rels file $objZip->addFromString('xl/vbaProjectSignature.bin', $this->_spreadSheet->getMacrosCertificate()); $objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->_spreadSheet)); } } } //a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels) if ($this->_spreadSheet->hasRibbon()) { $tmpRibbonTarget = $this->_spreadSheet->getRibbonXMLData('target'); $objZip->addFromString($tmpRibbonTarget, $this->_spreadSheet->getRibbonXMLData('data')); if ($this->_spreadSheet->hasRibbonBinObjects()) { $tmpRootPath = dirname($tmpRibbonTarget) . '/'; $ribbonBinObjects = $this->_spreadSheet->getRibbonBinObjects('data'); //the files to write foreach ($ribbonBinObjects as $aPath => $aContent) { $objZip->addFromString($tmpRootPath . $aPath, $aContent); } //the rels for files $objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->_spreadSheet)); } } // Add relationships to ZIP file $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet)); $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet)); // Add document properties to ZIP file $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet)); $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet)); $customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->_spreadSheet); if ($customPropertiesPart !== NULL) { $objZip->addFromString('docProps/custom.xml', $customPropertiesPart); } // Add theme to ZIP file $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet)); // Add string table to ZIP file $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable)); // Add styles to ZIP file $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet)); // Add workbook to ZIP file $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet, $this->_preCalculateFormulas)); $chartCount = 0; // Add worksheets for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable, $this->_includeCharts)); if ($this->_includeCharts) { $charts = $this->_spreadSheet->getSheet($i)->getChartCollection(); if (count($charts) > 0) { foreach ($charts as $chart) { $objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart)); $chartCount++; } } } } $chartRef1 = $chartRef2 = 0; // Add worksheet relationships (drawings, ...) for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { // Add relationships $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1, $this->_includeCharts)); $drawings = $this->_spreadSheet->getSheet($i)->getDrawingCollection(); $drawingCount = count($drawings); if ($this->_includeCharts) { $chartCount = $this->_spreadSheet->getSheet($i)->getChartCount(); } // Add drawing and image relationship parts if ($drawingCount > 0 || $chartCount > 0) { // Drawing relationships $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i), $chartRef1, $this->_includeCharts)); // Drawings $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i), $chartRef2, $this->_includeCharts)); } // Add comment relationship parts if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) { // VML Comments $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i))); // Comments $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i))); } // Add header/footer relationship parts if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) { // VML Drawings $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i))); // VML Drawing relationships $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i))); // Media foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) { $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath())); } } } // Add media for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) { $imageContents = null; $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath(); if (strpos($imagePath, 'zip://') !== false) { $imagePath = substr($imagePath, 6); $imagePathSplitted = explode('#', $imagePath); $imageZip = new ZipArchive(); $imageZip->open($imagePathSplitted[0]); $imageContents = $imageZip->getFromName($imagePathSplitted[1]); $imageZip->close(); unset($imageZip); } else { $imageContents = file_get_contents($imagePath); } $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } else { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) { ob_start(); call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource()); $imageContents = ob_get_contents(); ob_end_clean(); $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } } } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog); // Close file if ($objZip->close() === false) { throw new PHPExcel_Writer_Exception("Could not close zip file {$pFilename}."); } // If a temporary file was used, copy it to the correct file stream if ($originalFilename != $pFilename) { if (copy($pFilename, $originalFilename) === false) { throw new PHPExcel_Writer_Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}."); } @unlink($pFilename); } } else { throw new PHPExcel_Writer_Exception("PHPExcel object unassigned."); } }
/** * SUMIF * * Counts the number of cells that contain numbers within the list of arguments * * Excel Function: * SUMIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be summed. * @return float */ public static function SUMIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = 0; $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); if (empty($sumArgs)) { $sumArgs = $aArgs; } $condition = PHPExcel_Calculation_Functions::_ifCondition($condition); // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = str_replace('"', '""', $arg); $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { // Is it a value within our criteria $returnValue += $sumArgs[$key]; } } // Return return $returnValue; }
/** * Get calculated cell value * * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling * * @param boolean $resetLog Whether the calculation engine logger should be reset or not * @return mixed * @throws PHPExcel_Exception */ public function getCalculatedValue($resetLog = true) { if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) { try { $result = PHPExcel_Calculation::getInstance($this->getWorksheet()->getParent())->calculateCellValue($this, $resetLog); // We don't yet handle array returns if (is_array($result)) { while (is_array($result)) { $result = array_pop($result); } } } catch (PHPExcel_Exception $ex) { if ($ex->getMessage() === 'Unable to access External Workbook' && $this->_calculatedValue !== null) { return $this->_calculatedValue; // Fallback for calculations referencing external files. } $result = '#N/A'; throw new PHPExcel_Calculation_Exception($this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage()); } if ($result === '#Not Yet Implemented') { return $this->_calculatedValue; // Fallback if calculation engine does not support the formula. } return $result; } elseif ($this->_value instanceof PHPExcel_RichText) { return $this->_value->getPlainText(); } return $this->_value; }
/** * Save PHPExcel to file * * @param string $pFilename * * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(false); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); } // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook. $this->_buildWorksheetEschers(); $this->_buildWorkbookEscher(); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // add fonts from rich text eleemnts for ($i = 0; $i < $countSheets; ++$i) { foreach ($this->_writerWorksheets[$i]->_phpSheet->getCellCollection() as $cellID) { $cell = $this->_writerWorksheets[$i]->_phpSheet->getCell($cellID); $cVal = $cell->getValue(); if ($cVal instanceof PHPExcel_RichText) { $elements = $cVal->getRichTextElements(); foreach ($elements as $element) { if ($element instanceof PHPExcel_RichText_Run) { $font = $element->getFont(); $this->_writerWorksheets[$i]->_fntHashIndex[$font->getHashCode()] = $this->_writerWorkbook->_addFont($font); } } } } } // initialize OLE file $workbookStreamName = 'Workbook'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $this->_documentSummaryInformation = $this->_writeDocumentSummaryInformation(); // initialize OLE Document Summary Information if (isset($this->_documentSummaryInformation) && !empty($this->_documentSummaryInformation)) { $OLE_DocumentSummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'DocumentSummaryInformation')); $OLE_DocumentSummaryInformation->append($this->_documentSummaryInformation); } $this->_summaryInformation = $this->_writeSummaryInformation(); // initialize OLE Summary Information if (isset($this->_summaryInformation) && !empty($this->_summaryInformation)) { $OLE_SummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'SummaryInformation')); $OLE_SummaryInformation->append($this->_summaryInformation); } // define OLE Parts $arrRootData = array($OLE); // initialize OLE Properties file if (isset($OLE_SummaryInformation)) { $arrRootData[] = $OLE_SummaryInformation; } // initialize OLE Extended Properties file if (isset($OLE_DocumentSummaryInformation)) { $arrRootData[] = $OLE_DocumentSummaryInformation; } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), $arrRootData); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); } // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook. $this->_buildWorksheetEschers(); $this->_buildWorkbookEscher(); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE)); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
/** * Get caluclated cell value * * @return mixed */ public function getCalculatedValue() { if (is_null($this->_value) || $this->_value === '') { return ''; } else { if ($this->_dataType != PHPExcel_Cell_DataType::TYPE_FORMULA) { return $this->_value; } else { return PHPExcel_Calculation::getInstance()->calculate($this); } } }
public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE) { if ($this->_dataSource !== NULL) { $calcEngine = PHPExcel_Calculation::getInstance(); $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1'))); if ($flatten) { $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); } }
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); } }
public function refresh(PHPExcel_Worksheet $worksheet) { if ($this->_dataSource !== NULL) { $calcEngine = PHPExcel_Calculation::getInstance(); $newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1'))); $this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues); } }
/** * Apply the AutoFilter rules to the AutoFilter Range * * @throws PHPExcel_Exception * @return PHPExcel_Worksheet_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 PHPExcel_Worksheet_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 == PHPExcel_Worksheet_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[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') { $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_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 PHPExcel_Worksheet_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 PHPExcel_Worksheet_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 == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_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 === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; $ruleValues[] = array('operator' => $operator, 'value' => $average); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_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 PHPExcel_Worksheet_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 === PHPExcel_Worksheet_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 == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; $ruleValues[] = array('operator' => $operator, 'value' => $maxVal); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_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; }
/** * Get calculated cell value * * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling * * @param boolean $resetLog Whether the calculation engine logger should be reset or not * * @return mixed * @throws PHPExcel_Exception */ public function getCalculatedValue($resetLog = true) { //echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().PHP_EOL; if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) { try { //echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value'.PHP_EOL; $result = PHPExcel_Calculation::getInstance($this->getWorksheet()->getParent())->calculateCellValue($this, $resetLog); //echo $this->getCoordinate().' calculation result is '.$result.PHP_EOL; // We don't yet handle array returns if (is_array($result)) { while (is_array($result)) { $result = array_pop($result); } } } catch (PHPExcel_Exception $ex) { if ($ex->getMessage() === 'Unable to access External Workbook' && $this->_calculatedValue !== null) { //echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL; return $this->_calculatedValue; // Fallback for calculations referencing external files. } //echo 'Calculation Exception: '.$ex->getMessage().PHP_EOL; $result = '#N/A'; throw new PHPExcel_Calculation_Exception($this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage()); } if ($result === '#Not Yet Implemented') { //echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL; return $this->_calculatedValue; // Fallback if calculation engine does not support the formula. } //echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().PHP_EOL; return $result; } elseif ($this->_value instanceof PHPExcel_RichText) { // echo 'Cell value for '.$this->getCoordinate().' is rich text: Returning data value of '.$this->_value.'<br />'; return $this->_value->getPlainText(); } // echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />'; return $this->_value; }
/** * MINIF * * Returns the minimum value within a range of cells that contain numbers within the list of arguments * * Excel Function: * MINIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be checked. * @return float */ public static function MINIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = null; $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); if (count($sumArgs) == 0) { $sumArgs = $aArgs; } $condition = PHPExcel_Calculation_Functions::_ifCondition($condition); // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { if (is_null($returnValue) || $arg < $returnValue) { $returnValue = $arg; } } } // Return return $returnValue; }
/** * Save PHPExcel to file * * @param string $pFilename * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(FALSE); $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Build CSS $this->buildCSS(!$this->_useInlineCss); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new PHPExcel_Writer_Exception("Could not open file {$pFilename} for writing."); } // Write headers fwrite($fileHandle, $this->generateHTMLHeader(!$this->_useInlineCss)); // Write navigation (tabs) if (!$this->_isPdf && $this->_generateSheetNavigationBlock) { fwrite($fileHandle, $this->generateNavigation()); } // Write data fwrite($fileHandle, $this->generateSheetData()); // Write footer fwrite($fileHandle, $this->generateHTMLFooter()); // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
/** * Create a new PHPExcel with one Worksheet */ public function __construct() { $this->uniqueID = uniqid(); $this->calculationEngine = PHPExcel_Calculation::getInstance($this); // Initialise worksheet collection and add one worksheet $this->workSheetCollection = array(); $this->workSheetCollection[] = new PHPExcel_Worksheet($this); $this->activeSheetIndex = 0; // Create document properties $this->properties = new PHPExcel_DocumentProperties(); // Create document security $this->security = new PHPExcel_DocumentSecurity(); // Set named ranges $this->namedRanges = array(); // Create the cellXf supervisor $this->cellXfSupervisor = new PHPExcel_Style(true); $this->cellXfSupervisor->bindParent($this); // Create the default style $this->addCellXf(new PHPExcel_Style()); $this->addCellStyleXf(new PHPExcel_Style()); }
/** * Get calculated cell value * * @deprecated Since version 1.7.8 for planned changes to cell for array formula handling * * @return mixed * @throws PHPExcel_Exception */ public function getCalculatedValue($resetLog = TRUE) { // echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().'<br />'; if ($this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) { try { // echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value<br />'; $result = PHPExcel_Calculation::getInstance()->calculateCellValue($this, $resetLog); // echo $this->getCoordinate().' calculation result is '.$result.'<br />'; } catch (Exception $ex) { if ($ex->getMessage() === 'Unable to access External Workbook' && $this->_calculatedValue !== NULL) { // echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />'; return $this->_calculatedValue; // Fallback for calculations referencing external files. } // echo 'Calculation Exception: '.$ex->getMessage().'<br />'; $result = '#N/A'; throw new PHPExcel_Exception($this->getParent()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage()); } if ($result === '#Not Yet Implemented') { // echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />'; return $this->_calculatedValue; // Fallback if calculation engine does not support the formula. } // echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().'<br />'; return $result; } // if ($this->_value === NULL) { // echo 'Cell '.$this->getCoordinate().' has no value, formula or otherwise<br />'; // return NULL; // } // echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />'; return $this->_value; }
/** * Update references within formulas * * @param string $pFormula Formula to update * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @return string Updated formula * @throws Exception */ public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { // Parse formula into a tree of tokens $tokenisedFormula = PHPExcel_Calculation::getInstance()->parseFormula($pFormula); $newCellTokens = $cellTokens = array(); $adjustCount = 0; // Build the translation table of cell tokens foreach ($tokenisedFormula as $token) { $token = $token['value']; if (preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) { list($column, $row) = PHPExcel_Cell::coordinateFromString($token); // Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more $column = PHPExcel_Cell::columnIndexFromString($column) + 100000; $row += 10000000; $cellIndex = $column . $row; if (!isset($cellTokens[$cellIndex])) { $newReference = $this->updateCellReference($token, $pBefore, $pNumCols, $pNumRows); if ($newReference !== $token) { $newCellTokens[$cellIndex] = preg_quote($newReference); $cellTokens[$cellIndex] = '/(?<![A-Z])' . preg_quote($token) . '(?!\\d)/i'; ++$adjustCount; } } } } if ($adjustCount == 0) { return $pFormula; } krsort($cellTokens); krsort($newCellTokens); // Update cell references in the formula $formulaBlocks = explode('"', $pFormula); foreach ($formulaBlocks as $i => &$formulaBlock) { // Only count/replace in alternate array entries if ($i % 2 == 0) { $formulaBlock = preg_replace($cellTokens, $newCellTokens, $formulaBlock); } } unset($formulaBlock); // Then rebuild the formula string return implode('"', $formulaBlocks); }
/** * Identify whether a string contains a fractional numeric value, * and convert it to a numeric if it is * * @param string &$operand string value to test * @return boolean */ public static function convertToNumberIfFraction(&$operand) { if (preg_match('/^' . self::STRING_REGEXP_FRACTION . '$/i', $operand, $match)) { $sign = $match[1] == '-' ? '-' : '+'; $fractionFormula = '=' . $sign . $match[2] . $sign . $match[3]; $operand = PHPExcel_Calculation::getInstance()->_calculateFormulaValue($fractionFormula); return true; } return false; }
/** * SUMIF * * Counts the number of cells that contain numbers within the list of arguments * * Excel Function: * SUMIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be summed. * @return float */ public static function SUMIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = 0; $aArgs = self::flattenArray($aArgs); $sumArgs = self::flattenArray($sumArgs); if (count($sumArgs) == 0) { $sumArgs = $aArgs; } if (!in_array($condition[0], array('>', '<', '='))) { if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); } $condition = '=' . $condition; } // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { // Is it a value within our criteria $returnValue += $sumArgs[$key]; } } // Return return $returnValue; }
/** * Get caluclated cell value * * @return mixed */ public function getCalculatedValue($resetLog = true) { // echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().'<br />'; if (!is_null($this->_calculatedValue) && $this->_dataType == PHPExcel_Cell_DataType::TYPE_FORMULA) { try { // echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value<br />'; $result = PHPExcel_Calculation::getInstance()->calculateCellValue($this, $resetLog); } catch (Exception $ex) { // echo 'Calculation Exception: '.$ex->getMessage().'<br />'; $result = '#N/A'; } if (is_string($result) && $result == '#Not Yet Implemented') { // echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />'; return $this->_calculatedValue; // Fallback if calculation engine does not support the formula. } else { // echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().'<br />'; return $result; } } if (is_null($this->_value)) { // echo 'Cell '.$this->getCoordinate().' has no value, formula or otherwise<br />'; return null; } else { if ($this->_dataType != PHPExcel_Cell_DataType::TYPE_FORMULA) { // echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />'; return $this->_value; } else { // echo 'Cell value is a formula: Calculating value<br />'; return PHPExcel_Calculation::getInstance()->calculateCellValue($this, $resetLog); } } }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } if ($this->_useBOM) { // Write the UTF-8 BOM code fwrite($fileHandle, ""); } // Convert sheet to array $cellsArray = $sheet->toArray('', $this->_preCalculateFormulas); // Write rows to file foreach ($cellsArray as $row) { $this->_writeLine($fileHandle, $row); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
/** * OFFSET * * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and * the number of columns to be returned. * * @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or * range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value. * @param rows The number of rows, up or down, that you want the upper-left cell to refer to. * Using 5 as the rows argument specifies that the upper-left cell in the reference is * five rows below reference. Rows can be positive (which means below the starting reference) * or negative (which means above the starting reference). * @param cols The number of columns, to the left or right, that you want the upper-left cell of the result * to refer to. Using 5 as the cols argument specifies that the upper-left cell in the * reference is five columns to the right of reference. Cols can be positive (which means * to the right of the starting reference) or negative (which means to the left of the * starting reference). * @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number. * @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number. * @return string A reference to a cell or range of cells */ public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null) { $rows = PHPExcel_Calculation_Functions::flattenSingleValue($rows); $columns = PHPExcel_Calculation_Functions::flattenSingleValue($columns); $height = PHPExcel_Calculation_Functions::flattenSingleValue($height); $width = PHPExcel_Calculation_Functions::flattenSingleValue($width); if ($cellAddress == Null) { return 0; } $args = func_get_args(); $pCell = array_pop($args); if (!is_object($pCell)) { return PHPExcel_Calculation_Functions::REF(); } $sheetName = null; if (strpos($cellAddress, "!")) { list($sheetName, $cellAddress) = explode("!", $cellAddress); } if (strpos($cellAddress, ":")) { list($startCell, $endCell) = explode(":", $cellAddress); } else { $startCell = $endCell = $cellAddress; } list($startCellColumn, $startCellRow) = PHPExcel_Cell::coordinateFromString($startCell); list($endCellColumn, $endCellRow) = PHPExcel_Cell::coordinateFromString($endCell); $startCellRow += $rows; $startCellColumn = PHPExcel_Cell::columnIndexFromString($startCellColumn) - 1; $startCellColumn += $columns; if ($startCellRow <= 0 || $startCellColumn < 0) { return PHPExcel_Calculation_Functions::REF(); } $endCellColumn = PHPExcel_Cell::columnIndexFromString($endCellColumn) - 1; if ($width != null && !is_object($width)) { $endCellColumn = $startCellColumn + $width - 1; } else { $endCellColumn += $columns; } $startCellColumn = PHPExcel_Cell::stringFromColumnIndex($startCellColumn); if ($height != null && !is_object($height)) { $endCellRow = $startCellRow + $height - 1; } else { $endCellRow += $rows; } if ($endCellRow <= 0 || $endCellColumn < 0) { return PHPExcel_Calculation_Functions::REF(); } $endCellColumn = PHPExcel_Cell::stringFromColumnIndex($endCellColumn); $cellAddress = $startCellColumn . $startCellRow; if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) { $cellAddress .= ':' . $endCellColumn . $endCellRow; } if ($sheetName !== null) { $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName); } else { $pSheet = $pCell->getParent(); } return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False); }
/** * Set the locale code to use for formula translations and any special formatting * * @param string $locale The locale code to use (e.g. "fr" or "pt_br" or "en_uk") * @return boolean Success or failure */ public static function setLocale($locale = 'en_us') { return PHPExcel_Calculation::getInstance()->setLocale($locale); }
/** * Save PHPExcel to file * * @param string $pFilename * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(FALSE); $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new PHPExcel_Writer_Exception("Could not open file {$pFilename} for writing."); } if ($this->_excelCompatibility) { fwrite($fileHandle, ""); // Enforce UTF-8 BOM Header $this->setEnclosure('"'); // Set enclosure to " $this->setDelimiter(";"); // Set delimiter to a semi-colon $this->setLineEnding("\r\n"); fwrite($fileHandle, 'sep=' . $this->getDelimiter() . $this->_lineEnding); } elseif ($this->_useBOM) { // Write the UTF-8 BOM code if required fwrite($fileHandle, ""); } // Identify the range that we need to extract from the worksheet $maxCol = $sheet->getHighestDataColumn(); $maxRow = $sheet->getHighestDataRow(); // Write rows to file for ($row = 1; $row <= $maxRow; ++$row) { // Convert the row to an array... $cellsArray = $sheet->rangeToArray('A' . $row . ':' . $maxCol . $row, '', $this->_preCalculateFormulas); // ... and write to the file $this->_writeLine($fileHandle, $cellsArray[0]); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.8.0, 2014-03-02 */ /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Europe/London'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../PHPExcel/PHPExcel.php'; // List functions echo date('H:i:s'), " List implemented functions", EOL; $objCalc = PHPExcel_Calculation::getInstance(); print_r($objCalc->listFunctionNames()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new PHPExcel(); // Add some data, we will use some formulas here echo date('H:i:s'), " Add some data and formulas", EOL; $objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:')->setCellValue('A15', 'Sum:')->setCellValue('A16', 'Max:')->setCellValue('A17', 'Min:')->setCellValue('A18', 'Average:')->setCellValue('A19', 'Median:')->setCellValue('A20', 'Mode:'); $objPHPExcel->getActiveSheet()->setCellValue('A22', 'CountA:')->setCellValue('A23', 'MaxA:')->setCellValue('A24', 'MinA:'); $objPHPExcel->getActiveSheet()->setCellValue('A26', 'StDev:')->setCellValue('A27', 'StDevA:')->setCellValue('A28', 'StDevP:')->setCellValue('A29', 'StDevPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A31', 'DevSq:')->setCellValue('A32', 'Var:')->setCellValue('A33', 'VarA:')->setCellValue('A34', 'VarP:')->setCellValue('A35', 'VarPA:'); $objPHPExcel->getActiveSheet()->setCellValue('A37', 'Date:'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1')->setCellValue('B2', 2)->setCellValue('B3', 8)->setCellValue('B4', 10)->setCellValue('B5', True)->setCellValue('B6', False)->setCellValue('B7', 'Text String')->setCellValue('B9', '22')->setCellValue('B10', 4)->setCellValue('B11', 6)->setCellValue('B12', 12); $objPHPExcel->getActiveSheet()->setCellValue('B14', '=COUNT(B2:B12)')->setCellValue('B15', '=SUM(B2:B12)')->setCellValue('B16', '=MAX(B2:B12)')->setCellValue('B17', '=MIN(B2:B12)')->setCellValue('B18', '=AVERAGE(B2:B12)')->setCellValue('B19', '=MEDIAN(B2:B12)')->setCellValue('B20', '=MODE(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B22', '=COUNTA(B2:B12)')->setCellValue('B23', '=MAXA(B2:B12)')->setCellValue('B24', '=MINA(B2:B12)'); $objPHPExcel->getActiveSheet()->setCellValue('B26', '=STDEV(B2:B12)')->setCellValue('B27', '=STDEVA(B2:B12)')->setCellValue('B28', '=STDEVP(B2:B12)')->setCellValue('B29', '=STDEVPA(B2:B12)');
} catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } /* PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array( 'memoryCacheSize' => '1024MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); */ // Get worksheet dimensions $arts = "SELECT count(id) as cant FROM articulo WHERE active = 1"; $arts1 = dbQuery($arts); $arts2 = dbFetchAssoc($arts1); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); PHPExcel_Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 1; //$objReader->setPreCalculateFormulas(FALSE); $i = 1; //$arts2['cant'] /*while($i < 10){ $value = $objPHPExcel->getSheetByName("MERVEN")->getCell('F'.$i)->getCalculatedValue(); if( $value == 'OBS:'){ break; }else{ $i++; echo ' Valor loco: '. $i.'<br>'; } $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); }
/** * Set title * * @param string $pValue String containing the dimension of this worksheet * @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should * be updated to reflect the new sheet name. * This should be left as the default true, unless you are * certain that no formula cells on any worksheet contain * references to this worksheet * @return PHPExcel_Worksheet */ public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true) { // Is this a 'rename' or not? if ($this->getTitle() == $pValue) { return $this; } // Syntax check self::_checkSheetTitle($pValue); // Old title $oldTitle = $this->getTitle(); if ($this->_parent) { // Is there already such sheet name? if ($this->_parent->sheetNameExists($pValue)) { // Use name, but append with lowest possible integer if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29); } $i = 1; while ($this->_parent->sheetNameExists($pValue . ' ' . $i)) { ++$i; if ($i == 10) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28); } } elseif ($i == 100) { if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) { $pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27); } } } $altTitle = $pValue . ' ' . $i; return $this->setTitle($altTitle, $updateFormulaCellReferences); } } // Set title $this->_title = $pValue; $this->_dirty = true; if ($this->_parent) { // New title $newTitle = $this->getTitle(); PHPExcel_Calculation::getInstance($this->_parent)->renameCalculationCacheForWorksheet($oldTitle, $newTitle); if ($updateFormulaCellReferences) { PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle); } } return $this; }
/** * __filter * * Parses the selection criteria, extracts the database rows that match those criteria, and * returns that subset of rows. * * @access private * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param mixed[] $criteria The range of cells that contains the conditions you specify. * You can use any range for the criteria argument, as long as it * includes at least one column label and at least one cell below * the column label in which you specify a condition for the * column. * @return array of mixed * */ private static function __filter($database, $criteria) { $fieldNames = array_shift($database); $criteriaNames = array_shift($criteria); // Convert the criteria into a set of AND/OR conditions with [:placeholders] $testConditions = $testValues = array(); $testConditionsCount = 0; foreach ($criteriaNames as $key => $criteriaName) { $testCondition = array(); $testConditionCount = 0; foreach ($criteria as $row => $criterion) { if ($criterion[$key] > '') { $testCondition[] = '[:' . $criteriaName . ']' . PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]); $testConditionCount++; } } if ($testConditionCount > 1) { $testConditions[] = 'OR(' . implode(',', $testCondition) . ')'; $testConditionsCount++; } elseif ($testConditionCount == 1) { $testConditions[] = $testCondition[0]; $testConditionsCount++; } } if ($testConditionsCount > 1) { $testConditionSet = 'AND(' . implode(',', $testConditions) . ')'; } elseif ($testConditionsCount == 1) { $testConditionSet = $testConditions[0]; } // Loop through each row of the database foreach ($database as $dataRow => $dataValues) { // Substitute actual values from the database row for our [:placeholders] $testConditionList = $testConditionSet; foreach ($criteriaNames as $key => $criteriaName) { $k = array_search($criteriaName, $fieldNames); if (isset($dataValues[$k])) { $dataValue = $dataValues[$k]; $dataValue = is_string($dataValue) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue; $testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList); } } // evaluate the criteria against the row data $result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList); // If the row failed to meet the criteria, remove it from the database if (!$result) { unset($database[$dataRow]); } } return $database; }
/** * Apply the AutoFilter rules to the AutoFilter Range * * @throws PHPExcel_Exception * @return PHPExcel_Worksheet_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 PHPExcel_Worksheet_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 == PHPExcel_Worksheet_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(); foreach ($ruleDataSet as $ruleValue) { $date = $time = ''; if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') { $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') { $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); } if (isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') { $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_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 PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: $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); } $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); } $join = $filterColumn->getAndOr(); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join)); break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: $ruleValues = array(); //var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $dynamicRuleType = $rule->getGrouping(); echo '$dynamicRuleType is ', $dynamicRuleType, PHP_EOL; if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) { // Number based $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; echo 'Average Formula Result is ', $averageFormula, PHP_EOL; $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1')); $operator = $dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; $ruleValues[] = array('operator' => $operator, 'value' => $average); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_ANDOR_OR)); } else { // Date based $columnFilterTests[$columnID] = array('method' => '_filterTypeDynamicFilters', 'arguments' => $ruleValues); } } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: $ruleValues = array(); var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway } $columnFilterTests[$columnID] = array('method' => '_filterTypeTopTenFilters', 'arguments' => $ruleValues); break; } } echo 'Column Filter Test CRITERIA', PHP_EOL; var_dump($columnFilterTests); 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; } } echo $result ? 'SHOW' : 'HIDE', PHP_EOL; $this->_workSheet->getRowDimension($row)->setVisible($result); } return $this; }
/** * Save PHPExcel to file * * @param string $pFilename * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } if ($this->_useBOM) { // Write the UTF-8 BOM code fwrite($fileHandle, ""); } // Identify the range that we need to extract from the worksheet $maxCol = $sheet->getHighestColumn(); $maxRow = $sheet->getHighestRow(); // Write rows to file for ($row = 1; $row <= $maxRow; ++$row) { // Convert the row to an array... $cellsArray = $sheet->rangeToArray('A' . $row . ':' . $maxCol . $row, '', $this->_preCalculateFormulas); // ... and write to the file $this->_writeLine($fileHandle, $cellsArray[0]); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }