/** * Run PHPExcel garabage collector. * * @return PHPExcel_Worksheet */ public function garbageCollect() { // Flush cache $this->_cellCollection->getCacheData('A1'); // Build a reference table from images // $imageCoordinates = array(); // $iterator = $this->getDrawingCollection()->getIterator(); // while ($iterator->valid()) { // $imageCoordinates[$iterator->current()->getCoordinates()] = true; // // $iterator->next(); // } // // Lookup highest column and highest row if cells are cleaned $colRow = $this->_cellCollection->getHighestRowAndColumn(); $highestRow = $colRow['row']; $highestColumn = PHPExcel_Cell::columnIndexFromString($colRow['column']); // Loop through column dimensions foreach ($this->_columnDimensions as $dimension) { $highestColumn = max($highestColumn, PHPExcel_Cell::columnIndexFromString($dimension->getColumnIndex())); } // Loop through row dimensions foreach ($this->_rowDimensions as $dimension) { $highestRow = max($highestRow, $dimension->getRowIndex()); } // Cache values if ($highestColumn < 0) { $this->_cachedHighestColumn = 'A'; } else { $this->_cachedHighestColumn = PHPExcel_Cell::stringFromColumnIndex(--$highestColumn); } $this->_cachedHighestRow = $highestRow; // Return return $this; }
/** * Run PHPExcel garabage collector. * * @return PHPExcel_Worksheet */ public function garbageCollect() { // Flush cache $this->_cellCollection->getCacheData('A1'); // Lookup highest column and highest row if cells are cleaned $colRow = $this->_cellCollection->getHighestRowAndColumn(); $highestRow = $colRow['row']; $highestColumn = PHPExcel_Cell::columnIndexFromString($colRow['column']); // Loop through column dimensions foreach ($this->_columnDimensions as $dimension) { $highestColumn = max($highestColumn, PHPExcel_Cell::columnIndexFromString($dimension->getColumnIndex())); } // Loop through row dimensions foreach ($this->_rowDimensions as $dimension) { $highestRow = max($highestRow, $dimension->getRowIndex()); } // Cache values if ($highestColumn < 0) { $this->_cachedHighestColumn = 'A'; } else { $this->_cachedHighestColumn = PHPExcel_Cell::stringFromColumnIndex(--$highestColumn); } $this->_cachedHighestRow = $highestRow; return $this; }
/** * Create array from a range of cells * * @param string $pRange * cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @param mixed $nullValue * in the array entry if a cell doesn't exist * @param boolean $calculateFormulas * be calculated? * @param boolean $formatData * be applied to cell values? * @param boolean $returnCellRef * Return a simple array of rows and columns indexed by number * counting from zero * True - Return rows and columns indexed by their actual row and * column IDs * @return array */ public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) { // Returnvalue $returnValue = array (); // Identify the range that we need to extract from the worksheet list ( $rangeStart, $rangeEnd ) = PHPExcel_Cell::rangeBoundaries ( $pRange ); $minCol = PHPExcel_Cell::stringFromColumnIndex ( $rangeStart [0] - 1 ); $minRow = $rangeStart [1]; $maxCol = PHPExcel_Cell::stringFromColumnIndex ( $rangeEnd [0] - 1 ); $maxRow = $rangeEnd [1]; $maxCol ++; // Loop through rows for($row = $minRow; $row <= $maxRow; ++ $row) { $c = - 1; // Loop through columns in the current row for($col = $minCol; $col != $maxCol; ++ $col) { $rRef = ($returnCellRef) ? $row : $row - 1; $cRef = ($returnCellRef) ? $col : ++ $c; // Using getCell() will create a new cell if it doesn't already // exist. We don't want that to happen // so we test and retrieve directly against _cellCollection if ($this->_cellCollection->isDataSet ( $col . $row )) { // Cell exists $cell = $this->_cellCollection->getCacheData ( $col . $row ); if ($cell->getValue () !== null) { if ($cell->getValue () instanceof PHPExcel_RichText) { $returnValue [$rRef] [$cRef] = $cell->getValue ()->getPlainText (); } else { if ($calculateFormulas) { $returnValue [$rRef] [$cRef] = $cell->getCalculatedValue (); } else { $returnValue [$rRef] [$cRef] = $cell->getValue (); } } if ($formatData) { $style = $this->_parent->getCellXfByIndex ( $cell->getXfIndex () ); $returnValue [$rRef] [$cRef] = PHPExcel_Style_NumberFormat::toFormattedString ( $returnValue [$rRef] [$cRef], $style->getNumberFormat ()->getFormatCode () ); } } else { // Cell holds a NULL $returnValue [$rRef] [$cRef] = $nullValue; } } else { // Cell doesn't exist $returnValue [$rRef] [$cRef] = $nullValue; } } } // Return return $returnValue; }
/** * Get cell at a specific coordinate by using numeric cell coordinates * * @param string $pColumn Numeric column coordinate of the cell * @param string $pRow Numeric row coordinate of the cell * @return PHPExcel_Cell Cell that was found */ public function getCellByColumnAndRow($pColumn = 0, $pRow = 0) { $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn); $coordinate = $columnLetter . $pRow; if (!$this->_cellCollection->isDataSet($coordinate)) { $cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType::TYPE_NULL, $this)); $this->_cellCollectionIsSorted = false; if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn) { $this->_cachedHighestColumn = $columnLetter; } $this->_cachedHighestRow = max($this->_cachedHighestRow, $pRow); return $cell; } return $this->_cellCollection->getCacheData($coordinate); }