/** * Extract range values * * @param string &$pRange String based range representation * @param Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not * @throws Calculation\Exception */ public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true) { // Return value $returnValue = array(); // echo 'extractNamedRange('.$pRange.')<br />'; if ($pSheet !== null) { $pSheetName = $pSheet->getTitle(); // echo 'Current sheet name is '.$pSheetName.'<br />'; // echo 'Range reference is '.$pRange.'<br />'; if (strpos($pRange, '!') !== false) { // echo '$pRange reference includes sheet reference', PHP_EOL; list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true); // echo 'New sheet name is '.$pSheetName, PHP_EOL; // echo 'Adjusted Range reference is '.$pRange, PHP_EOL; $pSheet = $this->spreadsheet->getSheetByName($pSheetName); } // Named range? $namedRange = NamedRange::resolveRange($pRange, $pSheet); if ($namedRange !== null) { $pSheet = $namedRange->getWorksheet(); // echo 'Named Range '.$pRange.' ('; $pRange = $namedRange->getRange(); $splitRange = Cell::splitRange($pRange); // Convert row and column references if (ctype_alpha($splitRange[0][0])) { $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow(); } elseif (ctype_digit($splitRange[0][0])) { $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1]; } // echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />'; // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) { // if (!$namedRange->getLocalOnly()) { // $pSheet = $namedRange->getWorksheet(); // } else { // return $returnValue; // } // } } else { return Calculation\Functions::REF(); } // Extract range $aReferences = Cell::extractAllCellReferencesInRange($pRange); // var_dump($aReferences); if (!isset($aReferences[1])) { // Single cell (or single column or row) in range list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]); $cellValue = null; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } else { // Extract cell data for all cells in the range foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = Cell::coordinateFromString($reference); // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />'; $cellValue = null; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } } // print_r($returnValue); // echo '<br />'; } return $returnValue; }
/** * Store the MERGEDCELLS records for all ranges of merged cells */ private function _writeMergedCells() { $mergeCells = $this->_phpSheet->getMergeCells(); $countMergeCells = count($mergeCells); if ($countMergeCells == 0) { return; } // maximum allowed number of merged cells per record if ($this->_BIFF_version == 0x600) { $maxCountMergeCellsPerRecord = 1027; } else { $maxCountMergeCellsPerRecord = 259; } // record identifier $record = 0xe5; // counter for total number of merged cells treated so far by the writer $i = 0; // counter for number of merged cells written in record currently being written $j = 0; // initialize record data $recordData = ''; // loop through the merged cells foreach ($mergeCells as $mergeCell) { ++$i; ++$j; // extract the row and column indexes $range = Cell::splitRange($mergeCell); list($first, $last) = $range[0]; list($firstColumn, $firstRow) = Cell::coordinateFromString($first); list($lastColumn, $lastRow) = Cell::coordinateFromString($last); $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, Cell::columnIndexFromString($firstColumn) - 1, Cell::columnIndexFromString($lastColumn) - 1); // flush record if we have reached limit for number of merged cells, or reached final merged cell if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) { $recordData = pack('v', $j) . $recordData; $length = strlen($recordData); $header = pack('vv', $record, $length); $this->_append($header . $recordData); // initialize for next record, if any $recordData = ''; $j = 0; } } }
/** * Update cell range * * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = Cell::splitRange($pCellRange); $ic = count($range); for ($i = 0; $i < $ic; ++$i) { $jc = count($range[$i]); for ($j = 0; $j < $jc; ++$j) { if (ctype_alpha($range[$i][$j])) { $r = Cell::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[0]; } elseif (ctype_digit($range[$i][$j])) { $r = Cell::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[1]; } else { $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } } // Recreate range string return Cell::buildRange($range); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
/** * Select a range of cells. * * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6' * @throws Exception * @return Worksheet */ public function setSelectedCells($pCoordinate = 'A1') { // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); // Convert 'A' to 'A:A' $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); // Convert '1' to '1:1' $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate); // Convert 'A:C' to 'A1:C1048576' $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); // Convert '1:3' to 'A1:XFD3' $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { list($first, ) = Cell::splitRange($pCoordinate); $this->_activeCell = $first[0]; } else { $this->_activeCell = $pCoordinate; } $this->_selectedCells = $pCoordinate; return $this; }
/** * Update cell range * * @param string $pCellRange Cell range * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = Cell::splitRange($pCellRange); for ($i = 0; $i < count($range); ++$i) { for ($j = 0; $j < count($range[$i]); ++$j) { $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } // Recreate range string return Cell::buildRange($range); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
/** * Extract all cell references in range * * @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000) * @return array Array containing single cell references */ public static function extractAllCellReferencesInRange($pRange = 'A1') { // Returnvalue $returnValue = array(); // Explode spaces $aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange))); foreach ($aExplodeSpaces as $explodedSpaces) { // Single cell? if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) { $col = 'A'; $row = 1; list($col, $row) = Cell::coordinateFromString($explodedSpaces); if (strlen($col) <= 2) { $returnValue[] = $explodedSpaces; } continue; } // Range... $range = Cell::splitRange($explodedSpaces); for ($i = 0; $i < count($range); ++$i) { // Single cell? if (count($range[$i]) == 1) { $col = 'A'; $row = 1; list($col, $row) = Cell::coordinateFromString($range[$i]); if (strlen($col) <= 2) { $returnValue[] = $explodedSpaces; } } // Range... $rangeStart = $rangeEnd = ''; $startingCol = $startingRow = $endingCol = $endingRow = 0; list($rangeStart, $rangeEnd) = $range[$i]; list($startingCol, $startingRow) = Cell::coordinateFromString($rangeStart); list($endingCol, $endingRow) = Cell::coordinateFromString($rangeEnd); // Conversions... $startingCol = Cell::columnIndexFromString($startingCol); $endingCol = Cell::columnIndexFromString($endingCol); // Current data $currentCol = --$startingCol; $currentRow = $startingRow; // Loop cells while ($currentCol < $endingCol) { $loopColumn = Cell::stringFromColumnIndex($currentCol); while ($currentRow <= $endingRow) { $returnValue[] = $loopColumn . $currentRow; ++$currentRow; } ++$currentCol; $currentRow = $startingRow; } } } // Return value return $returnValue; }
/** * Write Defined Name for PrintTitles * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet $pSheet * @param int $pSheetId * @throws Exception */ private function _writeDefinedNameForPrintArea(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pSheetId = 0) { // definedName for PrintArea if ($pSheet->getPageSetup()->isPrintAreaSet()) { $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', '_xlnm.Print_Area'); $objWriter->writeAttribute('localSheetId', $pSheetId); // Setting string $settingString = ''; // Print area $printArea = Cell::splitRange($pSheet->getPageSetup()->getPrintArea()); $chunks = array(); foreach ($printArea as $printAreaRect) { $printAreaRect[0] = Cell::absoluteCoordinate($printAreaRect[0]); $printAreaRect[1] = Cell::absoluteCoordinate($printAreaRect[1]); $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect); } $objWriter->writeRaw(implode(',', $chunks)); $objWriter->endElement(); } }
/** * Calculate information about HTML colspan and rowspan which is not always the same as Excel's */ private function _calculateSpans() { // Identify all cells that should be omitted in HTML due to cell merge. // In HTML only the upper-left cell should be written and it should have // appropriate rowspan / colspan attribute $sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1); foreach ($sheetIndexes as $sheetIndex) { $sheet = $this->_phpExcel->getSheet($sheetIndex); $candidateSpannedRow = array(); // loop through all Excel merged cells foreach ($sheet->getMergeCells() as $cells) { list($cells, ) = Cell::splitRange($cells); $first = $cells[0]; $last = $cells[1]; list($fc, $fr) = Cell::coordinateFromString($first); $fc = Cell::columnIndexFromString($fc) - 1; list($lc, $lr) = Cell::coordinateFromString($last); $lc = Cell::columnIndexFromString($lc) - 1; // loop through the individual cells in the individual merge for ($r = $fr; $r <= $lr; ++$r) { // also, flag this row as a HTML row that is candidate to be omitted $candidateSpannedRow[$r] = $r; for ($c = $fc; $c <= $lc; ++$c) { if (!($c == $fc && $r == $fr)) { // not the upper-left cell (should not be written in HTML) $this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc)); } else { // upper-left is the base cell that should hold the colspan/rowspan attribute $this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1); } } } } // Identify which rows should be omitted in HTML. These are the rows where all the cells // participate in a merge and the where base cells are somewhere above. $countColumns = Cell::columnIndexFromString($sheet->getHighestColumn()); foreach ($candidateSpannedRow as $rowIndex) { if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) { if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) { $this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex; } } } // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1 if (isset($this->_isSpannedRow[$sheetIndex])) { foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) { $adjustedBaseCells = array(); for ($c = 0; $c < $countColumns; ++$c) { $baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell']; if (!in_array($baseCell, $adjustedBaseCells)) { // subtract rowspan by 1 --$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan']; $adjustedBaseCells[] = $baseCell; } } } } // TODO: Same for columns } // We have calculated the spans $this->_spansAreCalculated = true; }
/** * Is this cell the master (top left cell) in a merge range (that holds the actual data value) * * @return boolean */ public function isMergeRangeValueCell() { if ($mergeRange = $this->getMergeRange()) { $mergeRange = Cell::splitRange($mergeRange); list($startCell) = $mergeRange[0]; if ($this->getCoordinate() === $startCell) { return true; } } return false; }
/** * Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas */ private function _writeAllDefinedNamesBiff8() { $chunk = ''; // Named ranges if (count($this->_phpExcel->getNamedRanges()) > 0) { // Loop named ranges $namedRanges = $this->_phpExcel->getNamedRanges(); foreach ($namedRanges as $namedRange) { // Create absolute coordinate $range = Cell::splitRange($namedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]); } } $range = Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2 // parse formula try { $error = $this->_parser->parse($range); $formulaData = $this->_parser->toReversePolish(); // make sure tRef3d is of type tRef3dR (0x3A) if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) { $formulaData = ":" . substr($formulaData, 1); } if ($namedRange->getLocalOnly()) { // local scope $scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1; } else { // global scope $scope = 0; } $chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false)); } catch (Exception $e) { // do nothing } } } // total number of sheets $total_worksheets = $this->_phpExcel->getSheetCount(); // write the print titles (repeating rows, columns), if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); // simultaneous repeatColumns repeatRows if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = Cell::columnIndexFromString($repeat[0]) - 1; $colmax = Cell::columnIndexFromString($repeat[1]) - 1; $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; // construct formula data manually $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax); // tArea3d $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255); // tArea3d $formulaData .= pack('C', 0x10); // tList // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); // (exclusive) either repeatColumns or repeatRows } else { if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = Cell::columnIndexFromString($repeat[0]) - 1; $colmax = Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); } } } // write the print areas, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); if ($sheetSetup->isPrintAreaSet()) { // Print area, e.g. A3:J6,H1:X20 $printArea = Cell::splitRange($sheetSetup->getPrintArea()); $countPrintArea = count($printArea); $formulaData = ''; for ($j = 0; $j < $countPrintArea; ++$j) { $printAreaRect = $printArea[$j]; // e.g. A3:J6 $printAreaRect[0] = Cell::coordinateFromString($printAreaRect[0]); $printAreaRect[1] = Cell::coordinateFromString($printAreaRect[1]); $print_rowmin = $printAreaRect[0][1] - 1; $print_rowmax = $printAreaRect[1][1] - 1; $print_colmin = Cell::columnIndexFromString($printAreaRect[0][0]) - 1; $print_colmax = Cell::columnIndexFromString($printAreaRect[1][0]) - 1; // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); if ($j > 0) { $formulaData .= pack('C', 0x10); // list operator token ',' } } // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true)); } } return $chunk; }