/** * 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 $maxCountMergeCellsPerRecord = 1027; // 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 = \PHPExcel\Cell::splitRange($mergeCell); list($first, $last) = $range[0]; list($firstColumn, $firstRow) = \PHPExcel\Cell::coordinateFromString($first); list($lastColumn, $lastRow) = \PHPExcel\Cell::coordinateFromString($last); $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, \PHPExcel\Cell::columnIndexFromString($firstColumn) - 1, \PHPExcel\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; } } }
/** * Write Defined Name for PrintTitles * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param \PHPExcel\Worksheet $pSheet * @param int $pSheetId * @throws \PHPExcel\Writer\Exception */ private function writeDefinedNameForPrintArea(\PHPExcel\Shared\XMLWriter $objWriter, \PHPExcel\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 = \PHPExcel\Cell::splitRange($pSheet->getPageSetup()->getPrintArea()); $chunks = array(); foreach ($printArea as $printAreaRect) { $printAreaRect[0] = \PHPExcel\Cell::absoluteReference($printAreaRect[0]); $printAreaRect[1] = \PHPExcel\Cell::absoluteReference($printAreaRect[1]); $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect); } $objWriter->writeRawData(implode(',', $chunks)); $objWriter->endElement(); } }
/** * 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 = \PHPExcel\Cell::splitRange($namedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . \PHPExcel\Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = \PHPExcel\Cell::absoluteCoordinate($range[$i][1]); } } $range = \PHPExcel\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 (\PHPExcel\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 = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1; $colmax = \PHPExcel\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 } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1; $colmax = \PHPExcel\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 = \PHPExcel\Cell::splitRange($sheetSetup->getPrintArea()); $countPrintArea = count($printArea); $formulaData = ''; for ($j = 0; $j < $countPrintArea; ++$j) { $printAreaRect = $printArea[$j]; // e.g. A3:J6 $printAreaRect[0] = \PHPExcel\Cell::coordinateFromString($printAreaRect[0]); $printAreaRect[1] = \PHPExcel\Cell::coordinateFromString($printAreaRect[1]); $print_rowmin = $printAreaRect[0][1] - 1; $print_rowmax = $printAreaRect[1][1] - 1; $print_colmin = \PHPExcel\Cell::columnIndexFromString($printAreaRect[0][0]) - 1; $print_colmax = \PHPExcel\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)); } } // write autofilters, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetAutoFilter = $this->phpExcel->getSheet($i)->getAutoFilter(); $autoFilterRange = $sheetAutoFilter->getRange(); if (!empty($autoFilterRange)) { $rangeBounds = \PHPExcel\Cell::rangeBoundaries($autoFilterRange); //Autofilter built in name $name = pack('C', 0xd); $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); } } return $chunk; }
/** * 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, ) = \PHPExcel\Cell::splitRange($cells); $first = $cells[0]; $last = $cells[1]; list($fc, $fr) = \PHPExcel\Cell::coordinateFromString($first); $fc = \PHPExcel\Cell::columnIndexFromString($fc) - 1; list($lc, $lr) = \PHPExcel\Cell::coordinateFromString($last); $lc = \PHPExcel\Cell::columnIndexFromString($lc) - 1; // loop through the individual cells in the individual merge $r = $fr - 1; while ($r++ < $lr) { // also, flag this row as a HTML row that is candidate to be omitted $candidateSpannedRow[$r] = $r; $c = $fc - 1; while ($c++ < $lc) { 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 = \PHPExcel\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(); $c = -1; $e = $countColumns - 1; while ($c++ < $e) { $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; }
/** * Write AutoFilter * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param \PHPExcel\Worksheet $pSheet Worksheet * @throws \PHPExcel\Writer\Exception */ private function writeAutoFilter(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null) { $autoFilterRange = $pSheet->getAutoFilter()->getRange(); if (!empty($autoFilterRange)) { // autoFilter $objWriter->startElement('autoFilter'); // Strip any worksheet reference from the filter coordinates $range = \PHPExcel\Cell::splitRange($autoFilterRange); $range = $range[0]; // Strip any worksheet ref if (strpos($range[0], '!') !== false) { list($ws, $range[0]) = explode('!', $range[0]); } $range = implode(':', $range); $objWriter->writeAttribute('ref', str_replace('$', '', $range)); $columns = $pSheet->getAutoFilter()->getColumns(); if (count($columns > 0)) { foreach ($columns as $columnID => $column) { $rules = $column->getRules(); if (count($rules) > 0) { $objWriter->startElement('filterColumn'); $objWriter->writeAttribute('colId', $pSheet->getAutoFilter()->getColumnOffset($columnID)); $objWriter->startElement($column->getFilterType()); if ($column->getJoin() == \PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND) { $objWriter->writeAttribute('and', 1); } foreach ($rules as $rule) { if ($column->getFilterType() === \PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER && $rule->getOperator() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL && $rule->getValue() === '') { // Filter rule for Blanks $objWriter->writeAttribute('blank', 1); } elseif ($rule->getRuleType() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER) { // Dynamic Filter Rule $objWriter->writeAttribute('type', $rule->getGrouping()); $val = $column->getAttribute('val'); if ($val !== null) { $objWriter->writeAttribute('val', $val); } $maxVal = $column->getAttribute('maxVal'); if ($maxVal !== null) { $objWriter->writeAttribute('maxVal', $maxVal); } } elseif ($rule->getRuleType() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER) { // Top 10 Filter Rule $objWriter->writeAttribute('val', $rule->getValue()); $objWriter->writeAttribute('percent', $rule->getOperator() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT ? '1' : '0'); $objWriter->writeAttribute('top', $rule->getGrouping() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? '1' : '0'); } else { // Filter, DateGroupItem or CustomFilter $objWriter->startElement($rule->getRuleType()); if ($rule->getOperator() !== \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL) { $objWriter->writeAttribute('operator', $rule->getOperator()); } if ($rule->getRuleType() === \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP) { // Date Group filters foreach ($rule->getValue() as $key => $value) { if ($value > '') { $objWriter->writeAttribute($key, $value); } } $objWriter->writeAttribute('dateTimeGrouping', $rule->getGrouping()); } else { $objWriter->writeAttribute('val', $rule->getValue()); } $objWriter->endElement(); } } $objWriter->endElement(); $objWriter->endElement(); } } } $objWriter->endElement(); } }