/**
* 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();
}
}
