Search moodle.org's
Developer Documentation
Developer Documentation
See Release Notes
Long Term Support Release
- Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
- Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
- PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx; 4 5 use PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue; 6 use PhpOffice\PhpSpreadsheet\Calculation\Information\Value; 7 use PhpOffice\PhpSpreadsheet\Cell\Cell; 8 use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 9 use PhpOffice\PhpSpreadsheet\RichText\RichText; 10 use PhpOffice\PhpSpreadsheet\Settings; 11 use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 12 use PhpOffice\PhpSpreadsheet\Shared\XMLWriter; 13 use PhpOffice\PhpSpreadsheet\Style\Conditional; 14 use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalDataBar; 15 use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\ConditionalFormattingRuleExtension; 16 use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column; 17 use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule; 18 use PhpOffice\PhpSpreadsheet\Worksheet\SheetView; 19 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as PhpspreadsheetWorksheet; 20 21 class Worksheet extends WriterPart 22 { 23 /** 24 * Write worksheet to XML format. 25 * 26 * @param string[] $stringTable 27 * @param bool $includeCharts Flag indicating if we should write charts 28 * 29 * @return string XML Output 30 */ 31 public function writeWorksheet(PhpspreadsheetWorksheet $worksheet, $stringTable = [], $includeCharts = false) 32 { 33 // Create XML writer 34 $objWriter = null; 35 if ($this->getParentWriter()->getUseDiskCaching()) { 36 $objWriter = new XMLWriter(XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); 37 } else { 38 $objWriter = new XMLWriter(XMLWriter::STORAGE_MEMORY); 39 } 40 41 // XML header 42 $objWriter->startDocument('1.0', 'UTF-8', 'yes'); 43 44 // Worksheet 45 $objWriter->startElement('worksheet'); 46 $objWriter->writeAttribute('xml:space', 'preserve'); 47 $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'); 48 $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'); 49 50 $objWriter->writeAttribute('xmlns:xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing'); 51 $objWriter->writeAttribute('xmlns:x14', 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/main'); 52 $objWriter->writeAttribute('xmlns:xm', 'http://schemas.microsoft.com/office/excel/2006/main'); 53 $objWriter->writeAttribute('xmlns:mc', 'http://schemas.openxmlformats.org/markup-compatibility/2006'); 54 $objWriter->writeAttribute('mc:Ignorable', 'x14ac'); 55 $objWriter->writeAttribute('xmlns:x14ac', 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac'); 56 57 // sheetPr 58 $this->writeSheetPr($objWriter, $worksheet); 59 60 // Dimension 61 $this->writeDimension($objWriter, $worksheet); 62 63 // sheetViews 64 $this->writeSheetViews($objWriter, $worksheet); 65 66 // sheetFormatPr 67 $this->writeSheetFormatPr($objWriter, $worksheet); 68 69 // cols 70 $this->writeCols($objWriter, $worksheet); 71 72 // sheetData 73 $this->writeSheetData($objWriter, $worksheet, $stringTable); 74 75 // sheetProtection 76 $this->writeSheetProtection($objWriter, $worksheet); 77 78 // protectedRanges 79 $this->writeProtectedRanges($objWriter, $worksheet); 80 81 // autoFilter 82 $this->writeAutoFilter($objWriter, $worksheet); 83 84 // mergeCells 85 $this->writeMergeCells($objWriter, $worksheet); 86 87 // conditionalFormatting 88 $this->writeConditionalFormatting($objWriter, $worksheet); 89 90 // dataValidations 91 $this->writeDataValidations($objWriter, $worksheet); 92 93 // hyperlinks 94 $this->writeHyperlinks($objWriter, $worksheet); 95 96 // Print options 97 $this->writePrintOptions($objWriter, $worksheet); 98 99 // Page margins 100 $this->writePageMargins($objWriter, $worksheet); 101 102 // Page setup 103 $this->writePageSetup($objWriter, $worksheet); 104 105 // Header / footer 106 $this->writeHeaderFooter($objWriter, $worksheet); 107 108 // Breaks 109 $this->writeBreaks($objWriter, $worksheet); 110 111 // Drawings and/or Charts 112 $this->writeDrawings($objWriter, $worksheet, $includeCharts); 113 114 // LegacyDrawing 115 $this->writeLegacyDrawing($objWriter, $worksheet); 116 117 // LegacyDrawingHF 118 $this->writeLegacyDrawingHF($objWriter, $worksheet); 119 120 // AlternateContent 121 $this->writeAlternateContent($objWriter, $worksheet); 122 123 // Table 124 $this->writeTable($objWriter, $worksheet); 125 126 // ConditionalFormattingRuleExtensionList 127 // (Must be inserted last. Not insert last, an Excel parse error will occur) 128 $this->writeExtLst($objWriter, $worksheet); 129 130 $objWriter->endElement(); 131 132 // Return 133 return $objWriter->getData(); 134 } 135 136 /** 137 * Write SheetPr. 138 */ 139 private function writeSheetPr(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 140 { 141 // sheetPr 142 $objWriter->startElement('sheetPr'); 143 if ($worksheet->getParent()->hasMacros()) { 144 //if the workbook have macros, we need to have codeName for the sheet 145 if (!$worksheet->hasCodeName()) { 146 $worksheet->setCodeName($worksheet->getTitle()); 147 } 148 self::writeAttributeNotNull($objWriter, 'codeName', $worksheet->getCodeName()); 149 } 150 $autoFilterRange = $worksheet->getAutoFilter()->getRange(); 151 if (!empty($autoFilterRange)) { 152 $objWriter->writeAttribute('filterMode', '1'); 153 if (!$worksheet->getAutoFilter()->getEvaluated()) { 154 $worksheet->getAutoFilter()->showHideRows(); 155 } 156 } 157 158 // tabColor 159 if ($worksheet->isTabColorSet()) { 160 $objWriter->startElement('tabColor'); 161 $objWriter->writeAttribute('rgb', $worksheet->getTabColor()->getARGB() ?? ''); 162 $objWriter->endElement(); 163 } 164 165 // outlinePr 166 $objWriter->startElement('outlinePr'); 167 $objWriter->writeAttribute('summaryBelow', ($worksheet->getShowSummaryBelow() ? '1' : '0')); 168 $objWriter->writeAttribute('summaryRight', ($worksheet->getShowSummaryRight() ? '1' : '0')); 169 $objWriter->endElement(); 170 171 // pageSetUpPr 172 if ($worksheet->getPageSetup()->getFitToPage()) { 173 $objWriter->startElement('pageSetUpPr'); 174 $objWriter->writeAttribute('fitToPage', '1'); 175 $objWriter->endElement(); 176 } 177 178 $objWriter->endElement(); 179 } 180 181 /** 182 * Write Dimension. 183 */ 184 private function writeDimension(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 185 { 186 // dimension 187 $objWriter->startElement('dimension'); 188 $objWriter->writeAttribute('ref', $worksheet->calculateWorksheetDimension()); 189 $objWriter->endElement(); 190 } 191 192 /** 193 * Write SheetViews. 194 */ 195 private function writeSheetViews(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 196 { 197 // sheetViews 198 $objWriter->startElement('sheetViews'); 199 200 // Sheet selected? 201 $sheetSelected = false; 202 if ($this->getParentWriter()->getSpreadsheet()->getIndex($worksheet) == $this->getParentWriter()->getSpreadsheet()->getActiveSheetIndex()) { 203 $sheetSelected = true; 204 } 205 206 // sheetView 207 $objWriter->startElement('sheetView'); 208 $objWriter->writeAttribute('tabSelected', $sheetSelected ? '1' : '0'); 209 $objWriter->writeAttribute('workbookViewId', '0'); 210 211 // Zoom scales 212 if ($worksheet->getSheetView()->getZoomScale() != 100) { 213 $objWriter->writeAttribute('zoomScale', (string) $worksheet->getSheetView()->getZoomScale()); 214 } 215 if ($worksheet->getSheetView()->getZoomScaleNormal() != 100) { 216 $objWriter->writeAttribute('zoomScaleNormal', (string) $worksheet->getSheetView()->getZoomScaleNormal()); 217 } 218 219 // Show zeros (Excel also writes this attribute only if set to false) 220 if ($worksheet->getSheetView()->getShowZeros() === false) { 221 $objWriter->writeAttribute('showZeros', '0'); 222 } 223 224 // View Layout Type 225 if ($worksheet->getSheetView()->getView() !== SheetView::SHEETVIEW_NORMAL) { 226 $objWriter->writeAttribute('view', $worksheet->getSheetView()->getView()); 227 } 228 229 // Gridlines 230 if ($worksheet->getShowGridlines()) { 231 $objWriter->writeAttribute('showGridLines', 'true'); 232 } else { 233 $objWriter->writeAttribute('showGridLines', 'false'); 234 } 235 236 // Row and column headers 237 if ($worksheet->getShowRowColHeaders()) { 238 $objWriter->writeAttribute('showRowColHeaders', '1'); 239 } else { 240 $objWriter->writeAttribute('showRowColHeaders', '0'); 241 } 242 243 // Right-to-left 244 if ($worksheet->getRightToLeft()) { 245 $objWriter->writeAttribute('rightToLeft', 'true'); 246 } 247 248 $topLeftCell = $worksheet->getTopLeftCell(); 249 $activeCell = $worksheet->getActiveCell(); 250 $sqref = $worksheet->getSelectedCells(); 251 252 // Pane 253 $pane = ''; 254 if ($worksheet->getFreezePane()) { 255 [$xSplit, $ySplit] = Coordinate::coordinateFromString($worksheet->getFreezePane()); 256 $xSplit = Coordinate::columnIndexFromString($xSplit); 257 --$xSplit; 258 --$ySplit; 259 260 // pane 261 $pane = 'topRight'; 262 $objWriter->startElement('pane'); 263 if ($xSplit > 0) { 264 $objWriter->writeAttribute('xSplit', "$xSplit"); 265 } 266 if ($ySplit > 0) { 267 $objWriter->writeAttribute('ySplit', $ySplit); 268 $pane = ($xSplit > 0) ? 'bottomRight' : 'bottomLeft'; 269 } 270 self::writeAttributeNotNull($objWriter, 'topLeftCell', $topLeftCell); 271 $objWriter->writeAttribute('activePane', $pane); 272 $objWriter->writeAttribute('state', 'frozen'); 273 $objWriter->endElement(); 274 275 if (($xSplit > 0) && ($ySplit > 0)) { 276 // Write additional selections if more than two panes (ie both an X and a Y split) 277 $objWriter->startElement('selection'); 278 $objWriter->writeAttribute('pane', 'topRight'); 279 $objWriter->endElement(); 280 $objWriter->startElement('selection'); 281 $objWriter->writeAttribute('pane', 'bottomLeft'); 282 $objWriter->endElement(); 283 } 284 } else { 285 self::writeAttributeNotNull($objWriter, 'topLeftCell', $topLeftCell); 286 } 287 288 // Selection 289 // Only need to write selection element if we have a split pane 290 // We cheat a little by over-riding the active cell selection, setting it to the split cell 291 $objWriter->startElement('selection'); 292 if ($pane != '') { 293 $objWriter->writeAttribute('pane', $pane); 294 } 295 $objWriter->writeAttribute('activeCell', $activeCell); 296 $objWriter->writeAttribute('sqref', $sqref); 297 $objWriter->endElement(); 298 299 $objWriter->endElement(); 300 301 $objWriter->endElement(); 302 } 303 304 /** 305 * Write SheetFormatPr. 306 */ 307 private function writeSheetFormatPr(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 308 { 309 // sheetFormatPr 310 $objWriter->startElement('sheetFormatPr'); 311 312 // Default row height 313 if ($worksheet->getDefaultRowDimension()->getRowHeight() >= 0) { 314 $objWriter->writeAttribute('customHeight', 'true'); 315 $objWriter->writeAttribute('defaultRowHeight', StringHelper::formatNumber($worksheet->getDefaultRowDimension()->getRowHeight())); 316 } else { 317 $objWriter->writeAttribute('defaultRowHeight', '14.4'); 318 } 319 320 // Set Zero Height row 321 if ($worksheet->getDefaultRowDimension()->getZeroHeight()) { 322 $objWriter->writeAttribute('zeroHeight', '1'); 323 } 324 325 // Default column width 326 if ($worksheet->getDefaultColumnDimension()->getWidth() >= 0) { 327 $objWriter->writeAttribute('defaultColWidth', StringHelper::formatNumber($worksheet->getDefaultColumnDimension()->getWidth())); 328 } 329 330 // Outline level - row 331 $outlineLevelRow = 0; 332 foreach ($worksheet->getRowDimensions() as $dimension) { 333 if ($dimension->getOutlineLevel() > $outlineLevelRow) { 334 $outlineLevelRow = $dimension->getOutlineLevel(); 335 } 336 } 337 $objWriter->writeAttribute('outlineLevelRow', (string) (int) $outlineLevelRow); 338 339 // Outline level - column 340 $outlineLevelCol = 0; 341 foreach ($worksheet->getColumnDimensions() as $dimension) { 342 if ($dimension->getOutlineLevel() > $outlineLevelCol) { 343 $outlineLevelCol = $dimension->getOutlineLevel(); 344 } 345 } 346 $objWriter->writeAttribute('outlineLevelCol', (string) (int) $outlineLevelCol); 347 348 $objWriter->endElement(); 349 } 350 351 /** 352 * Write Cols. 353 */ 354 private function writeCols(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 355 { 356 // cols 357 if (count($worksheet->getColumnDimensions()) > 0) { 358 $objWriter->startElement('cols'); 359 360 $worksheet->calculateColumnWidths(); 361 362 // Loop through column dimensions 363 foreach ($worksheet->getColumnDimensions() as $colDimension) { 364 // col 365 $objWriter->startElement('col'); 366 $objWriter->writeAttribute('min', (string) Coordinate::columnIndexFromString($colDimension->getColumnIndex())); 367 $objWriter->writeAttribute('max', (string) Coordinate::columnIndexFromString($colDimension->getColumnIndex())); 368 369 if ($colDimension->getWidth() < 0) { 370 // No width set, apply default of 10 371 $objWriter->writeAttribute('width', '9.10'); 372 } else { 373 // Width set 374 $objWriter->writeAttribute('width', StringHelper::formatNumber($colDimension->getWidth())); 375 } 376 377 // Column visibility 378 if ($colDimension->getVisible() === false) { 379 $objWriter->writeAttribute('hidden', 'true'); 380 } 381 382 // Auto size? 383 if ($colDimension->getAutoSize()) { 384 $objWriter->writeAttribute('bestFit', 'true'); 385 } 386 387 // Custom width? 388 if ($colDimension->getWidth() != $worksheet->getDefaultColumnDimension()->getWidth()) { 389 $objWriter->writeAttribute('customWidth', 'true'); 390 } 391 392 // Collapsed 393 if ($colDimension->getCollapsed() === true) { 394 $objWriter->writeAttribute('collapsed', 'true'); 395 } 396 397 // Outline level 398 if ($colDimension->getOutlineLevel() > 0) { 399 $objWriter->writeAttribute('outlineLevel', (string) $colDimension->getOutlineLevel()); 400 } 401 402 // Style 403 $objWriter->writeAttribute('style', (string) $colDimension->getXfIndex()); 404 405 $objWriter->endElement(); 406 } 407 408 $objWriter->endElement(); 409 } 410 } 411 412 /** 413 * Write SheetProtection. 414 */ 415 private function writeSheetProtection(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 416 { 417 // sheetProtection 418 $objWriter->startElement('sheetProtection'); 419 420 $protection = $worksheet->getProtection(); 421 422 if ($protection->getAlgorithm()) { 423 $objWriter->writeAttribute('algorithmName', $protection->getAlgorithm()); 424 $objWriter->writeAttribute('hashValue', $protection->getPassword()); 425 $objWriter->writeAttribute('saltValue', $protection->getSalt()); 426 $objWriter->writeAttribute('spinCount', (string) $protection->getSpinCount()); 427 } elseif ($protection->getPassword() !== '') { 428 $objWriter->writeAttribute('password', $protection->getPassword()); 429 } 430 431 $objWriter->writeAttribute('sheet', ($protection->getSheet() ? 'true' : 'false')); 432 $objWriter->writeAttribute('objects', ($protection->getObjects() ? 'true' : 'false')); 433 $objWriter->writeAttribute('scenarios', ($protection->getScenarios() ? 'true' : 'false')); 434 $objWriter->writeAttribute('formatCells', ($protection->getFormatCells() ? 'true' : 'false')); 435 $objWriter->writeAttribute('formatColumns', ($protection->getFormatColumns() ? 'true' : 'false')); 436 $objWriter->writeAttribute('formatRows', ($protection->getFormatRows() ? 'true' : 'false')); 437 $objWriter->writeAttribute('insertColumns', ($protection->getInsertColumns() ? 'true' : 'false')); 438 $objWriter->writeAttribute('insertRows', ($protection->getInsertRows() ? 'true' : 'false')); 439 $objWriter->writeAttribute('insertHyperlinks', ($protection->getInsertHyperlinks() ? 'true' : 'false')); 440 $objWriter->writeAttribute('deleteColumns', ($protection->getDeleteColumns() ? 'true' : 'false')); 441 $objWriter->writeAttribute('deleteRows', ($protection->getDeleteRows() ? 'true' : 'false')); 442 $objWriter->writeAttribute('selectLockedCells', ($protection->getSelectLockedCells() ? 'true' : 'false')); 443 $objWriter->writeAttribute('sort', ($protection->getSort() ? 'true' : 'false')); 444 $objWriter->writeAttribute('autoFilter', ($protection->getAutoFilter() ? 'true' : 'false')); 445 $objWriter->writeAttribute('pivotTables', ($protection->getPivotTables() ? 'true' : 'false')); 446 $objWriter->writeAttribute('selectUnlockedCells', ($protection->getSelectUnlockedCells() ? 'true' : 'false')); 447 $objWriter->endElement(); 448 } 449 450 private static function writeAttributeIf(XMLWriter $objWriter, ?bool $condition, string $attr, string $val): void 451 { 452 if ($condition) { 453 $objWriter->writeAttribute($attr, $val); 454 } 455 } 456 457 private static function writeAttributeNotNull(XMLWriter $objWriter, string $attr, ?string $val): void 458 { 459 if ($val !== null) { 460 $objWriter->writeAttribute($attr, $val); 461 } 462 } 463 464 private static function writeElementIf(XMLWriter $objWriter, bool $condition, string $attr, string $val): void 465 { 466 if ($condition) { 467 $objWriter->writeElement($attr, $val); 468 } 469 } 470 471 private static function writeOtherCondElements(XMLWriter $objWriter, Conditional $conditional, string $cellCoordinate): void 472 { 473 $conditions = $conditional->getConditions(); 474 if ( 475 $conditional->getConditionType() == Conditional::CONDITION_CELLIS 476 || $conditional->getConditionType() == Conditional::CONDITION_EXPRESSION 477 || !empty($conditions) 478 ) { 479 foreach ($conditions as $formula) { 480 // Formula 481 if (is_bool($formula)) { 482 $formula = $formula ? 'TRUE' : 'FALSE'; 483 } 484 $objWriter->writeElement('formula', Xlfn::addXlfn("$formula")); 485 } 486 } else { 487 if ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSBLANKS) { 488 // formula copied from ms xlsx xml source file 489 $objWriter->writeElement('formula', 'LEN(TRIM(' . $cellCoordinate . '))=0'); 490 } elseif ($conditional->getConditionType() == Conditional::CONDITION_NOTCONTAINSBLANKS) { 491 // formula copied from ms xlsx xml source file 492 $objWriter->writeElement('formula', 'LEN(TRIM(' . $cellCoordinate . '))>0'); 493 } elseif ($conditional->getConditionType() == Conditional::CONDITION_CONTAINSERRORS) { 494 // formula copied from ms xlsx xml source file 495 $objWriter->writeElement('formula', 'ISERROR(' . $cellCoordinate . ')'); 496 } elseif ($conditional->getConditionType() == Conditional::CONDITION_NOTCONTAINSERRORS) { 497 // formula copied from ms xlsx xml source file 498 $objWriter->writeElement('formula', 'NOT(ISERROR(' . $cellCoordinate . '))'); 499 } 500 } 501 } 502 503 private static function writeTimePeriodCondElements(XMLWriter $objWriter, Conditional $conditional, string $cellCoordinate): void 504 { 505 $txt = $conditional->getText(); 506 if (!empty($txt)) { 507 $objWriter->writeAttribute('timePeriod', $txt); 508 if (empty($conditional->getConditions())) { 509 if ($conditional->getOperatorType() == Conditional::TIMEPERIOD_TODAY) { 510 $objWriter->writeElement('formula', 'FLOOR(' . $cellCoordinate . ')=TODAY()'); 511 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_TOMORROW) { 512 $objWriter->writeElement('formula', 'FLOOR(' . $cellCoordinate . ')=TODAY()+1'); 513 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_YESTERDAY) { 514 $objWriter->writeElement('formula', 'FLOOR(' . $cellCoordinate . ')=TODAY()-1'); 515 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_LAST_7_DAYS) { 516 $objWriter->writeElement('formula', 'AND(TODAY()-FLOOR(' . $cellCoordinate . ',1)<=6,FLOOR(' . $cellCoordinate . ',1)<=TODAY())'); 517 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_LAST_WEEK) { 518 $objWriter->writeElement('formula', 'AND(TODAY()-ROUNDDOWN(' . $cellCoordinate . ',0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(' . $cellCoordinate . ',0)<(WEEKDAY(TODAY())+7))'); 519 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_THIS_WEEK) { 520 $objWriter->writeElement('formula', 'AND(TODAY()-ROUNDDOWN(' . $cellCoordinate . ',0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(' . $cellCoordinate . ',0)-TODAY()<=7-WEEKDAY(TODAY()))'); 521 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_NEXT_WEEK) { 522 $objWriter->writeElement('formula', 'AND(ROUNDDOWN(' . $cellCoordinate . ',0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(' . $cellCoordinate . ',0)-TODAY()<(15-WEEKDAY(TODAY())))'); 523 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_LAST_MONTH) { 524 $objWriter->writeElement('formula', 'AND(MONTH(' . $cellCoordinate . ')=MONTH(EDATE(TODAY(),0-1)),YEAR(' . $cellCoordinate . ')=YEAR(EDATE(TODAY(),0-1)))'); 525 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_THIS_MONTH) { 526 $objWriter->writeElement('formula', 'AND(MONTH(' . $cellCoordinate . ')=MONTH(TODAY()),YEAR(' . $cellCoordinate . ')=YEAR(TODAY()))'); 527 } elseif ($conditional->getOperatorType() == Conditional::TIMEPERIOD_NEXT_MONTH) { 528 $objWriter->writeElement('formula', 'AND(MONTH(' . $cellCoordinate . ')=MONTH(EDATE(TODAY(),0+1)),YEAR(' . $cellCoordinate . ')=YEAR(EDATE(TODAY(),0+1)))'); 529 } 530 } else { 531 $objWriter->writeElement('formula', (string) ($conditional->getConditions()[0])); 532 } 533 } 534 } 535 536 private static function writeTextCondElements(XMLWriter $objWriter, Conditional $conditional, string $cellCoordinate): void 537 { 538 $txt = $conditional->getText(); 539 if (!empty($txt)) { 540 $objWriter->writeAttribute('text', $txt); 541 if (empty($conditional->getConditions())) { 542 if ($conditional->getOperatorType() == Conditional::OPERATOR_CONTAINSTEXT) { 543 $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $txt . '",' . $cellCoordinate . ')))'); 544 } elseif ($conditional->getOperatorType() == Conditional::OPERATOR_BEGINSWITH) { 545 $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',LEN("' . $txt . '"))="' . $txt . '"'); 546 } elseif ($conditional->getOperatorType() == Conditional::OPERATOR_ENDSWITH) { 547 $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',LEN("' . $txt . '"))="' . $txt . '"'); 548 } elseif ($conditional->getOperatorType() == Conditional::OPERATOR_NOTCONTAINS) { 549 $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $txt . '",' . $cellCoordinate . '))'); 550 } 551 } else { 552 $objWriter->writeElement('formula', (string) ($conditional->getConditions()[0])); 553 } 554 } 555 } 556 557 private static function writeExtConditionalFormattingElements(XMLWriter $objWriter, ConditionalFormattingRuleExtension $ruleExtension): void 558 { 559 $prefix = 'x14'; 560 $objWriter->startElementNs($prefix, 'conditionalFormatting', null); 561 562 $objWriter->startElementNs($prefix, 'cfRule', null); 563 $objWriter->writeAttribute('type', $ruleExtension->getCfRule()); 564 $objWriter->writeAttribute('id', $ruleExtension->getId()); 565 $objWriter->startElementNs($prefix, 'dataBar', null); 566 $dataBar = $ruleExtension->getDataBarExt(); 567 foreach ($dataBar->getXmlAttributes() as $attrKey => $val) { 568 $objWriter->writeAttribute($attrKey, $val); 569 } 570 $minCfvo = $dataBar->getMinimumConditionalFormatValueObject(); 571 if ($minCfvo !== null) { 572 $objWriter->startElementNs($prefix, 'cfvo', null); 573 $objWriter->writeAttribute('type', $minCfvo->getType()); 574 if ($minCfvo->getCellFormula()) { 575 $objWriter->writeElement('xm:f', $minCfvo->getCellFormula()); 576 } 577 $objWriter->endElement(); //end cfvo 578 } 579 580 $maxCfvo = $dataBar->getMaximumConditionalFormatValueObject(); 581 if ($maxCfvo !== null) { 582 $objWriter->startElementNs($prefix, 'cfvo', null); 583 $objWriter->writeAttribute('type', $maxCfvo->getType()); 584 if ($maxCfvo->getCellFormula()) { 585 $objWriter->writeElement('xm:f', $maxCfvo->getCellFormula()); 586 } 587 $objWriter->endElement(); //end cfvo 588 } 589 590 foreach ($dataBar->getXmlElements() as $elmKey => $elmAttr) { 591 $objWriter->startElementNs($prefix, $elmKey, null); 592 foreach ($elmAttr as $attrKey => $attrVal) { 593 $objWriter->writeAttribute($attrKey, $attrVal); 594 } 595 $objWriter->endElement(); //end elmKey 596 } 597 $objWriter->endElement(); //end dataBar 598 $objWriter->endElement(); //end cfRule 599 $objWriter->writeElement('xm:sqref', $ruleExtension->getSqref()); 600 $objWriter->endElement(); //end conditionalFormatting 601 } 602 603 private static function writeDataBarElements(XMLWriter $objWriter, ?ConditionalDataBar $dataBar): void 604 { 605 if ($dataBar) { 606 $objWriter->startElement('dataBar'); 607 self::writeAttributeIf($objWriter, null !== $dataBar->getShowValue(), 'showValue', $dataBar->getShowValue() ? '1' : '0'); 608 609 $minCfvo = $dataBar->getMinimumConditionalFormatValueObject(); 610 if ($minCfvo) { 611 $objWriter->startElement('cfvo'); 612 self::writeAttributeIf($objWriter, $minCfvo->getType(), 'type', (string) $minCfvo->getType()); 613 self::writeAttributeIf($objWriter, $minCfvo->getValue(), 'val', (string) $minCfvo->getValue()); 614 $objWriter->endElement(); 615 } 616 $maxCfvo = $dataBar->getMaximumConditionalFormatValueObject(); 617 if ($maxCfvo) { 618 $objWriter->startElement('cfvo'); 619 self::writeAttributeIf($objWriter, $maxCfvo->getType(), 'type', (string) $maxCfvo->getType()); 620 self::writeAttributeIf($objWriter, $maxCfvo->getValue(), 'val', (string) $maxCfvo->getValue()); 621 $objWriter->endElement(); 622 } 623 if ($dataBar->getColor()) { 624 $objWriter->startElement('color'); 625 $objWriter->writeAttribute('rgb', $dataBar->getColor()); 626 $objWriter->endElement(); 627 } 628 $objWriter->endElement(); // end dataBar 629 630 if ($dataBar->getConditionalFormattingRuleExt()) { 631 $objWriter->startElement('extLst'); 632 $extension = $dataBar->getConditionalFormattingRuleExt(); 633 $objWriter->startElement('ext'); 634 $objWriter->writeAttribute('uri', '{B025F937-C7B1-47D3-B67F-A62EFF666E3E}'); 635 $objWriter->startElementNs('x14', 'id', null); 636 $objWriter->text($extension->getId()); 637 $objWriter->endElement(); 638 $objWriter->endElement(); 639 $objWriter->endElement(); //end extLst 640 } 641 } 642 } 643 644 /** 645 * Write ConditionalFormatting. 646 */ 647 private function writeConditionalFormatting(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 648 { 649 // Conditional id 650 $id = 1; 651 652 // Loop through styles in the current worksheet 653 foreach ($worksheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) { 654 $objWriter->startElement('conditionalFormatting'); 655 $objWriter->writeAttribute('sqref', $cellCoordinate); 656 657 foreach ($conditionalStyles as $conditional) { 658 // WHY was this again? 659 // if ($this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()) == '') { 660 // continue; 661 // } 662 // cfRule 663 $objWriter->startElement('cfRule'); 664 $objWriter->writeAttribute('type', $conditional->getConditionType()); 665 self::writeAttributeIf( 666 $objWriter, 667 ($conditional->getConditionType() != Conditional::CONDITION_DATABAR), 668 'dxfId', 669 (string) $this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode($conditional->getHashCode()) 670 ); 671 $objWriter->writeAttribute('priority', (string) $id++); 672 673 self::writeAttributeif( 674 $objWriter, 675 ( 676 $conditional->getConditionType() === Conditional::CONDITION_CELLIS 677 || $conditional->getConditionType() === Conditional::CONDITION_CONTAINSTEXT 678 || $conditional->getConditionType() === Conditional::CONDITION_NOTCONTAINSTEXT 679 || $conditional->getConditionType() === Conditional::CONDITION_BEGINSWITH 680 || $conditional->getConditionType() === Conditional::CONDITION_ENDSWITH 681 ) && $conditional->getOperatorType() !== Conditional::OPERATOR_NONE, 682 'operator', 683 $conditional->getOperatorType() 684 ); 685 686 self::writeAttributeIf($objWriter, $conditional->getStopIfTrue(), 'stopIfTrue', '1'); 687 688 $cellRange = Coordinate::splitRange(str_replace('$', '', strtoupper($cellCoordinate))); 689 [$topLeftCell] = $cellRange[0]; 690 691 if ( 692 $conditional->getConditionType() === Conditional::CONDITION_CONTAINSTEXT 693 || $conditional->getConditionType() === Conditional::CONDITION_NOTCONTAINSTEXT 694 || $conditional->getConditionType() === Conditional::CONDITION_BEGINSWITH 695 || $conditional->getConditionType() === Conditional::CONDITION_ENDSWITH 696 ) { 697 self::writeTextCondElements($objWriter, $conditional, $topLeftCell); 698 } elseif ($conditional->getConditionType() === Conditional::CONDITION_TIMEPERIOD) { 699 self::writeTimePeriodCondElements($objWriter, $conditional, $topLeftCell); 700 } else { 701 self::writeOtherCondElements($objWriter, $conditional, $topLeftCell); 702 } 703 704 //<dataBar> 705 self::writeDataBarElements($objWriter, $conditional->getDataBar()); 706 707 $objWriter->endElement(); //end cfRule 708 } 709 710 $objWriter->endElement(); //end conditionalFormatting 711 } 712 } 713 714 /** 715 * Write DataValidations. 716 */ 717 private function writeDataValidations(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 718 { 719 // Datavalidation collection 720 $dataValidationCollection = $worksheet->getDataValidationCollection(); 721 722 // Write data validations? 723 if (!empty($dataValidationCollection)) { 724 $dataValidationCollection = Coordinate::mergeRangesInCollection($dataValidationCollection); 725 $objWriter->startElement('dataValidations'); 726 $objWriter->writeAttribute('count', (string) count($dataValidationCollection)); 727 728 foreach ($dataValidationCollection as $coordinate => $dv) { 729 $objWriter->startElement('dataValidation'); 730 731 if ($dv->getType() != '') { 732 $objWriter->writeAttribute('type', $dv->getType()); 733 } 734 735 if ($dv->getErrorStyle() != '') { 736 $objWriter->writeAttribute('errorStyle', $dv->getErrorStyle()); 737 } 738 739 if ($dv->getOperator() != '') { 740 $objWriter->writeAttribute('operator', $dv->getOperator()); 741 } 742 743 $objWriter->writeAttribute('allowBlank', ($dv->getAllowBlank() ? '1' : '0')); 744 $objWriter->writeAttribute('showDropDown', (!$dv->getShowDropDown() ? '1' : '0')); 745 $objWriter->writeAttribute('showInputMessage', ($dv->getShowInputMessage() ? '1' : '0')); 746 $objWriter->writeAttribute('showErrorMessage', ($dv->getShowErrorMessage() ? '1' : '0')); 747 748 if ($dv->getErrorTitle() !== '') { 749 $objWriter->writeAttribute('errorTitle', $dv->getErrorTitle()); 750 } 751 if ($dv->getError() !== '') { 752 $objWriter->writeAttribute('error', $dv->getError()); 753 } 754 if ($dv->getPromptTitle() !== '') { 755 $objWriter->writeAttribute('promptTitle', $dv->getPromptTitle()); 756 } 757 if ($dv->getPrompt() !== '') { 758 $objWriter->writeAttribute('prompt', $dv->getPrompt()); 759 } 760 761 $objWriter->writeAttribute('sqref', $dv->getSqref() ?? $coordinate); 762 763 if ($dv->getFormula1() !== '') { 764 $objWriter->writeElement('formula1', $dv->getFormula1()); 765 } 766 if ($dv->getFormula2() !== '') { 767 $objWriter->writeElement('formula2', $dv->getFormula2()); 768 } 769 770 $objWriter->endElement(); 771 } 772 773 $objWriter->endElement(); 774 } 775 } 776 777 /** 778 * Write Hyperlinks. 779 */ 780 private function writeHyperlinks(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 781 { 782 // Hyperlink collection 783 $hyperlinkCollection = $worksheet->getHyperlinkCollection(); 784 785 // Relation ID 786 $relationId = 1; 787 788 // Write hyperlinks? 789 if (!empty($hyperlinkCollection)) { 790 $objWriter->startElement('hyperlinks'); 791 792 foreach ($hyperlinkCollection as $coordinate => $hyperlink) { 793 $objWriter->startElement('hyperlink'); 794 795 $objWriter->writeAttribute('ref', $coordinate); 796 if (!$hyperlink->isInternal()) { 797 $objWriter->writeAttribute('r:id', 'rId_hyperlink_' . $relationId); 798 ++$relationId; 799 } else { 800 $objWriter->writeAttribute('location', str_replace('sheet://', '', $hyperlink->getUrl())); 801 } 802 803 if ($hyperlink->getTooltip() !== '') { 804 $objWriter->writeAttribute('tooltip', $hyperlink->getTooltip()); 805 $objWriter->writeAttribute('display', $hyperlink->getTooltip()); 806 } 807 808 $objWriter->endElement(); 809 } 810 811 $objWriter->endElement(); 812 } 813 } 814 815 /** 816 * Write ProtectedRanges. 817 */ 818 private function writeProtectedRanges(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 819 { 820 if (count($worksheet->getProtectedCells()) > 0) { 821 // protectedRanges 822 $objWriter->startElement('protectedRanges'); 823 824 // Loop protectedRanges 825 foreach ($worksheet->getProtectedCells() as $protectedCell => $passwordHash) { 826 // protectedRange 827 $objWriter->startElement('protectedRange'); 828 $objWriter->writeAttribute('name', 'p' . md5($protectedCell)); 829 $objWriter->writeAttribute('sqref', $protectedCell); 830 if (!empty($passwordHash)) { 831 $objWriter->writeAttribute('password', $passwordHash); 832 } 833 $objWriter->endElement(); 834 } 835 836 $objWriter->endElement(); 837 } 838 } 839 840 /** 841 * Write MergeCells. 842 */ 843 private function writeMergeCells(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 844 { 845 if (count($worksheet->getMergeCells()) > 0) { 846 // mergeCells 847 $objWriter->startElement('mergeCells'); 848 849 // Loop mergeCells 850 foreach ($worksheet->getMergeCells() as $mergeCell) { 851 // mergeCell 852 $objWriter->startElement('mergeCell'); 853 $objWriter->writeAttribute('ref', $mergeCell); 854 $objWriter->endElement(); 855 } 856 857 $objWriter->endElement(); 858 } 859 } 860 861 /** 862 * Write PrintOptions. 863 */ 864 private function writePrintOptions(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 865 { 866 // printOptions 867 $objWriter->startElement('printOptions'); 868 869 $objWriter->writeAttribute('gridLines', ($worksheet->getPrintGridlines() ? 'true' : 'false')); 870 $objWriter->writeAttribute('gridLinesSet', 'true'); 871 872 if ($worksheet->getPageSetup()->getHorizontalCentered()) { 873 $objWriter->writeAttribute('horizontalCentered', 'true'); 874 } 875 876 if ($worksheet->getPageSetup()->getVerticalCentered()) { 877 $objWriter->writeAttribute('verticalCentered', 'true'); 878 } 879 880 $objWriter->endElement(); 881 } 882 883 /** 884 * Write PageMargins. 885 */ 886 private function writePageMargins(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 887 { 888 // pageMargins 889 $objWriter->startElement('pageMargins'); 890 $objWriter->writeAttribute('left', StringHelper::formatNumber($worksheet->getPageMargins()->getLeft())); 891 $objWriter->writeAttribute('right', StringHelper::formatNumber($worksheet->getPageMargins()->getRight())); 892 $objWriter->writeAttribute('top', StringHelper::formatNumber($worksheet->getPageMargins()->getTop())); 893 $objWriter->writeAttribute('bottom', StringHelper::formatNumber($worksheet->getPageMargins()->getBottom())); 894 $objWriter->writeAttribute('header', StringHelper::formatNumber($worksheet->getPageMargins()->getHeader())); 895 $objWriter->writeAttribute('footer', StringHelper::formatNumber($worksheet->getPageMargins()->getFooter())); 896 $objWriter->endElement(); 897 } 898 899 /** 900 * Write AutoFilter. 901 */ 902 private function writeAutoFilter(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 903 { 904 $autoFilterRange = $worksheet->getAutoFilter()->getRange(); 905 if (!empty($autoFilterRange)) { 906 // autoFilter 907 $objWriter->startElement('autoFilter'); 908 909 // Strip any worksheet reference from the filter coordinates 910 $range = Coordinate::splitRange($autoFilterRange); 911 $range = $range[0]; 912 // Strip any worksheet ref 913 [$ws, $range[0]] = PhpspreadsheetWorksheet::extractSheetTitle($range[0], true); 914 $range = implode(':', $range); 915 916 $objWriter->writeAttribute('ref', str_replace('$', '', $range)); 917 918 $columns = $worksheet->getAutoFilter()->getColumns(); 919 if (count($columns) > 0) { 920 foreach ($columns as $columnID => $column) { 921 $rules = $column->getRules(); 922 if (count($rules) > 0) { 923 $objWriter->startElement('filterColumn'); 924 $objWriter->writeAttribute('colId', (string) $worksheet->getAutoFilter()->getColumnOffset($columnID)); 925 926 $objWriter->startElement($column->getFilterType()); 927 if ($column->getJoin() == Column::AUTOFILTER_COLUMN_JOIN_AND) { 928 $objWriter->writeAttribute('and', '1'); 929 } 930 931 foreach ($rules as $rule) { 932 if ( 933 ($column->getFilterType() === Column::AUTOFILTER_FILTERTYPE_FILTER) && 934 ($rule->getOperator() === Rule::AUTOFILTER_COLUMN_RULE_EQUAL) && 935 ($rule->getValue() === '') 936 ) { 937 // Filter rule for Blanks 938 $objWriter->writeAttribute('blank', '1'); 939 } elseif ($rule->getRuleType() === Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER) { 940 // Dynamic Filter Rule 941 $objWriter->writeAttribute('type', $rule->getGrouping()); 942 $val = $column->getAttribute('val'); 943 if ($val !== null) { 944 $objWriter->writeAttribute('val', "$val"); 945 } 946 $maxVal = $column->getAttribute('maxVal'); 947 if ($maxVal !== null) { 948 $objWriter->writeAttribute('maxVal', "$maxVal"); 949 } 950 } elseif ($rule->getRuleType() === Rule::AUTOFILTER_RULETYPE_TOPTENFILTER) { 951 // Top 10 Filter Rule 952 $ruleValue = $rule->getValue(); 953 if (!is_array($ruleValue)) { 954 $objWriter->writeAttribute('val', "$ruleValue"); 955 } 956 $objWriter->writeAttribute('percent', (($rule->getOperator() === Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) ? '1' : '0')); 957 $objWriter->writeAttribute('top', (($rule->getGrouping() === Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) ? '1' : '0')); 958 } else { 959 // Filter, DateGroupItem or CustomFilter 960 $objWriter->startElement($rule->getRuleType()); 961 962 if ($rule->getOperator() !== Rule::AUTOFILTER_COLUMN_RULE_EQUAL) { 963 $objWriter->writeAttribute('operator', $rule->getOperator()); 964 } 965 if ($rule->getRuleType() === Rule::AUTOFILTER_RULETYPE_DATEGROUP) { 966 // Date Group filters 967 $ruleValue = $rule->getValue(); 968 if (is_array($ruleValue)) { 969 foreach ($ruleValue as $key => $value) { 970 $objWriter->writeAttribute($key, "$value"); 971 } 972 } 973 $objWriter->writeAttribute('dateTimeGrouping', $rule->getGrouping()); 974 } else { 975 $ruleValue = $rule->getValue(); 976 if (!is_array($ruleValue)) { 977 $objWriter->writeAttribute('val', "$ruleValue"); 978 } 979 } 980 981 $objWriter->endElement(); 982 } 983 } 984 985 $objWriter->endElement(); 986 987 $objWriter->endElement(); 988 } 989 } 990 } 991 $objWriter->endElement(); 992 } 993 } 994 995 /** 996 * Write Table. 997 */ 998 private function writeTable(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 999 { 1000 $tableCount = $worksheet->getTableCollection()->count(); 1001 1002 $objWriter->startElement('tableParts'); 1003 $objWriter->writeAttribute('count', (string) $tableCount); 1004 1005 for ($t = 1; $t <= $tableCount; ++$t) { 1006 $objWriter->startElement('tablePart'); 1007 $objWriter->writeAttribute('r:id', 'rId_table_' . $t); 1008 $objWriter->endElement(); 1009 } 1010 1011 $objWriter->endElement(); 1012 } 1013 1014 /** 1015 * Write PageSetup. 1016 */ 1017 private function writePageSetup(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1018 { 1019 // pageSetup 1020 $objWriter->startElement('pageSetup'); 1021 $objWriter->writeAttribute('paperSize', (string) $worksheet->getPageSetup()->getPaperSize()); 1022 $objWriter->writeAttribute('orientation', $worksheet->getPageSetup()->getOrientation()); 1023 1024 if ($worksheet->getPageSetup()->getScale() !== null) { 1025 $objWriter->writeAttribute('scale', (string) $worksheet->getPageSetup()->getScale()); 1026 } 1027 if ($worksheet->getPageSetup()->getFitToHeight() !== null) { 1028 $objWriter->writeAttribute('fitToHeight', (string) $worksheet->getPageSetup()->getFitToHeight()); 1029 } else { 1030 $objWriter->writeAttribute('fitToHeight', '0'); 1031 } 1032 if ($worksheet->getPageSetup()->getFitToWidth() !== null) { 1033 $objWriter->writeAttribute('fitToWidth', (string) $worksheet->getPageSetup()->getFitToWidth()); 1034 } else { 1035 $objWriter->writeAttribute('fitToWidth', '0'); 1036 } 1037 if (!empty($worksheet->getPageSetup()->getFirstPageNumber())) { 1038 $objWriter->writeAttribute('firstPageNumber', (string) $worksheet->getPageSetup()->getFirstPageNumber()); 1039 $objWriter->writeAttribute('useFirstPageNumber', '1'); 1040 } 1041 $objWriter->writeAttribute('pageOrder', $worksheet->getPageSetup()->getPageOrder()); 1042 1043 $getUnparsedLoadedData = $worksheet->getParent()->getUnparsedLoadedData(); 1044 if (isset($getUnparsedLoadedData['sheets'][$worksheet->getCodeName()]['pageSetupRelId'])) { 1045 $objWriter->writeAttribute('r:id', $getUnparsedLoadedData['sheets'][$worksheet->getCodeName()]['pageSetupRelId']); 1046 } 1047 1048 $objWriter->endElement(); 1049 } 1050 1051 /** 1052 * Write Header / Footer. 1053 */ 1054 private function writeHeaderFooter(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1055 { 1056 // headerFooter 1057 $objWriter->startElement('headerFooter'); 1058 $objWriter->writeAttribute('differentOddEven', ($worksheet->getHeaderFooter()->getDifferentOddEven() ? 'true' : 'false')); 1059 $objWriter->writeAttribute('differentFirst', ($worksheet->getHeaderFooter()->getDifferentFirst() ? 'true' : 'false')); 1060 $objWriter->writeAttribute('scaleWithDoc', ($worksheet->getHeaderFooter()->getScaleWithDocument() ? 'true' : 'false')); 1061 $objWriter->writeAttribute('alignWithMargins', ($worksheet->getHeaderFooter()->getAlignWithMargins() ? 'true' : 'false')); 1062 1063 $objWriter->writeElement('oddHeader', $worksheet->getHeaderFooter()->getOddHeader()); 1064 $objWriter->writeElement('oddFooter', $worksheet->getHeaderFooter()->getOddFooter()); 1065 $objWriter->writeElement('evenHeader', $worksheet->getHeaderFooter()->getEvenHeader()); 1066 $objWriter->writeElement('evenFooter', $worksheet->getHeaderFooter()->getEvenFooter()); 1067 $objWriter->writeElement('firstHeader', $worksheet->getHeaderFooter()->getFirstHeader()); 1068 $objWriter->writeElement('firstFooter', $worksheet->getHeaderFooter()->getFirstFooter()); 1069 $objWriter->endElement(); 1070 } 1071 1072 /** 1073 * Write Breaks. 1074 */ 1075 private function writeBreaks(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1076 { 1077 // Get row and column breaks 1078 $aRowBreaks = []; 1079 $aColumnBreaks = []; 1080 foreach ($worksheet->getBreaks() as $cell => $breakType) { 1081 if ($breakType == PhpspreadsheetWorksheet::BREAK_ROW) { 1082 $aRowBreaks[] = $cell; 1083 } elseif ($breakType == PhpspreadsheetWorksheet::BREAK_COLUMN) { 1084 $aColumnBreaks[] = $cell; 1085 } 1086 } 1087 1088 // rowBreaks 1089 if (!empty($aRowBreaks)) { 1090 $objWriter->startElement('rowBreaks'); 1091 $objWriter->writeAttribute('count', (string) count($aRowBreaks)); 1092 $objWriter->writeAttribute('manualBreakCount', (string) count($aRowBreaks)); 1093 1094 foreach ($aRowBreaks as $cell) { 1095 $coords = Coordinate::coordinateFromString($cell); 1096 1097 $objWriter->startElement('brk'); 1098 $objWriter->writeAttribute('id', $coords[1]); 1099 $objWriter->writeAttribute('man', '1'); 1100 $objWriter->endElement(); 1101 } 1102 1103 $objWriter->endElement(); 1104 } 1105 1106 // Second, write column breaks 1107 if (!empty($aColumnBreaks)) { 1108 $objWriter->startElement('colBreaks'); 1109 $objWriter->writeAttribute('count', (string) count($aColumnBreaks)); 1110 $objWriter->writeAttribute('manualBreakCount', (string) count($aColumnBreaks)); 1111 1112 foreach ($aColumnBreaks as $cell) { 1113 $coords = Coordinate::coordinateFromString($cell); 1114 1115 $objWriter->startElement('brk'); 1116 $objWriter->writeAttribute('id', (string) (Coordinate::columnIndexFromString($coords[0]) - 1)); 1117 $objWriter->writeAttribute('man', '1'); 1118 $objWriter->endElement(); 1119 } 1120 1121 $objWriter->endElement(); 1122 } 1123 } 1124 1125 /** 1126 * Write SheetData. 1127 * 1128 * @param string[] $stringTable String table 1129 */ 1130 private function writeSheetData(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet, array $stringTable): void 1131 { 1132 // Flipped stringtable, for faster index searching 1133 $aFlippedStringTable = $this->getParentWriter()->getWriterPartstringtable()->flipStringTable($stringTable); 1134 1135 // sheetData 1136 $objWriter->startElement('sheetData'); 1137 1138 // Get column count 1139 $colCount = Coordinate::columnIndexFromString($worksheet->getHighestColumn()); 1140 1141 // Highest row number 1142 $highestRow = $worksheet->getHighestRow(); 1143 1144 // Loop through cells building a comma-separated list of the columns in each row 1145 // This is a trade-off between the memory usage that is required for a full array of columns, 1146 // and execution speed 1147 /** @var array<int, string> $cellsByRow */ 1148 $cellsByRow = []; 1149 foreach ($worksheet->getCoordinates() as $coordinate) { 1150 [$column, $row] = Coordinate::coordinateFromString($coordinate); 1151 $cellsByRow[$row] = $cellsByRow[$row] ?? ''; 1152 $cellsByRow[$row] .= "{$column},"; 1153 } 1154 1155 $currentRow = 0; 1156 while ($currentRow++ < $highestRow) { 1157 $isRowSet = isset($cellsByRow[$currentRow]); 1158 if ($isRowSet || $worksheet->rowDimensionExists($currentRow)) { 1159 // Get row dimension 1160 $rowDimension = $worksheet->getRowDimension($currentRow); 1161 1162 // Write current row? 1163 $writeCurrentRow = $isRowSet || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() === false || $rowDimension->getCollapsed() === true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null; 1164 1165 if ($writeCurrentRow) { 1166 // Start a new row 1167 $objWriter->startElement('row'); 1168 $objWriter->writeAttribute('r', "$currentRow"); 1169 $objWriter->writeAttribute('spans', '1:' . $colCount); 1170 1171 // Row dimensions 1172 if ($rowDimension->getRowHeight() >= 0) { 1173 $objWriter->writeAttribute('customHeight', '1'); 1174 $objWriter->writeAttribute('ht', StringHelper::formatNumber($rowDimension->getRowHeight())); 1175 } 1176 1177 // Row visibility 1178 if (!$rowDimension->getVisible() === true) { 1179 $objWriter->writeAttribute('hidden', 'true'); 1180 } 1181 1182 // Collapsed 1183 if ($rowDimension->getCollapsed() === true) { 1184 $objWriter->writeAttribute('collapsed', 'true'); 1185 } 1186 1187 // Outline level 1188 if ($rowDimension->getOutlineLevel() > 0) { 1189 $objWriter->writeAttribute('outlineLevel', (string) $rowDimension->getOutlineLevel()); 1190 } 1191 1192 // Style 1193 if ($rowDimension->getXfIndex() !== null) { 1194 $objWriter->writeAttribute('s', (string) $rowDimension->getXfIndex()); 1195 $objWriter->writeAttribute('customFormat', '1'); 1196 } 1197 1198 // Write cells 1199 if (isset($cellsByRow[$currentRow])) { 1200 // We have a comma-separated list of column names (with a trailing entry); split to an array 1201 $columnsInRow = explode(',', $cellsByRow[$currentRow]); 1202 array_pop($columnsInRow); 1203 foreach ($columnsInRow as $column) { 1204 // Write cell 1205 $this->writeCell($objWriter, $worksheet, "{$column}{$currentRow}", $aFlippedStringTable); 1206 } 1207 } 1208 1209 // End row 1210 $objWriter->endElement(); 1211 } 1212 } 1213 } 1214 1215 $objWriter->endElement(); 1216 } 1217 1218 /** 1219 * @param RichText|string $cellValue 1220 */ 1221 private function writeCellInlineStr(XMLWriter $objWriter, string $mappedType, $cellValue): void 1222 { 1223 $objWriter->writeAttribute('t', $mappedType); 1224 if (!$cellValue instanceof RichText) { 1225 $objWriter->startElement('is'); 1226 $objWriter->writeElement( 1227 't', 1228 StringHelper::controlCharacterPHP2OOXML(htmlspecialchars($cellValue, Settings::htmlEntityFlags())) 1229 ); 1230 $objWriter->endElement(); 1231 } else { 1232 $objWriter->startElement('is'); 1233 $this->getParentWriter()->getWriterPartstringtable()->writeRichText($objWriter, $cellValue); 1234 $objWriter->endElement(); 1235 } 1236 } 1237 1238 /** 1239 * @param RichText|string $cellValue 1240 * @param string[] $flippedStringTable 1241 */ 1242 private function writeCellString(XMLWriter $objWriter, string $mappedType, $cellValue, array $flippedStringTable): void 1243 { 1244 $objWriter->writeAttribute('t', $mappedType); 1245 if (!$cellValue instanceof RichText) { 1246 self::writeElementIf($objWriter, isset($flippedStringTable[$cellValue]), 'v', $flippedStringTable[$cellValue] ?? ''); 1247 } else { 1248 $objWriter->writeElement('v', $flippedStringTable[$cellValue->getHashCode()]); 1249 } 1250 } 1251 1252 /** 1253 * @param float|int $cellValue 1254 */ 1255 private function writeCellNumeric(XMLWriter $objWriter, $cellValue): void 1256 { 1257 //force a decimal to be written if the type is float 1258 if (is_float($cellValue)) { 1259 // force point as decimal separator in case current locale uses comma 1260 $cellValue = str_replace(',', '.', (string) $cellValue); 1261 if (strpos($cellValue, '.') === false) { 1262 $cellValue = $cellValue . '.0'; 1263 } 1264 } 1265 $objWriter->writeElement('v', "$cellValue"); 1266 } 1267 1268 private function writeCellBoolean(XMLWriter $objWriter, string $mappedType, bool $cellValue): void 1269 { 1270 $objWriter->writeAttribute('t', $mappedType); 1271 $objWriter->writeElement('v', $cellValue ? '1' : '0'); 1272 } 1273 1274 private function writeCellError(XMLWriter $objWriter, string $mappedType, string $cellValue, string $formulaerr = '#NULL!'): void 1275 { 1276 $objWriter->writeAttribute('t', $mappedType); 1277 $cellIsFormula = substr($cellValue, 0, 1) === '='; 1278 self::writeElementIf($objWriter, $cellIsFormula, 'f', Xlfn::addXlfnStripEquals($cellValue)); 1279 $objWriter->writeElement('v', $cellIsFormula ? $formulaerr : $cellValue); 1280 } 1281 1282 private function writeCellFormula(XMLWriter $objWriter, string $cellValue, Cell $cell): void 1283 { 1284 $calculatedValue = $this->getParentWriter()->getPreCalculateFormulas() ? $cell->getCalculatedValue() : $cellValue; 1285 if (is_string($calculatedValue)) { 1286 if (ErrorValue::isError($calculatedValue)) { 1287 $this->writeCellError($objWriter, 'e', $cellValue, $calculatedValue); 1288 1289 return; 1290 } 1291 $objWriter->writeAttribute('t', 'str'); 1292 $calculatedValue = StringHelper::controlCharacterPHP2OOXML($calculatedValue); 1293 } elseif (is_bool($calculatedValue)) { 1294 $objWriter->writeAttribute('t', 'b'); 1295 $calculatedValue = (int) $calculatedValue; 1296 } 1297 1298 $attributes = $cell->getFormulaAttributes(); 1299 if (($attributes['t'] ?? null) === 'array') { 1300 $objWriter->startElement('f'); 1301 $objWriter->writeAttribute('t', 'array'); 1302 $objWriter->writeAttribute('ref', $cell->getCoordinate()); 1303 $objWriter->writeAttribute('aca', '1'); 1304 $objWriter->writeAttribute('ca', '1'); 1305 $objWriter->text(substr($cellValue, 1)); 1306 $objWriter->endElement(); 1307 } else { 1308 $objWriter->writeElement('f', Xlfn::addXlfnStripEquals($cellValue)); 1309 self::writeElementIf( 1310 $objWriter, 1311 $this->getParentWriter()->getOffice2003Compatibility() === false, 1312 'v', 1313 ($this->getParentWriter()->getPreCalculateFormulas() && !is_array($calculatedValue) && substr($calculatedValue ?? '', 0, 1) !== '#') 1314 ? StringHelper::formatNumber($calculatedValue) : '0' 1315 ); 1316 } 1317 } 1318 1319 /** 1320 * Write Cell. 1321 * 1322 * @param string $cellAddress Cell Address 1323 * @param string[] $flippedStringTable String table (flipped), for faster index searching 1324 */ 1325 private function writeCell(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet, string $cellAddress, array $flippedStringTable): void 1326 { 1327 // Cell 1328 $pCell = $worksheet->getCell($cellAddress); 1329 $objWriter->startElement('c'); 1330 $objWriter->writeAttribute('r', $cellAddress); 1331 1332 // Sheet styles 1333 $xfi = $pCell->getXfIndex(); 1334 self::writeAttributeIf($objWriter, (bool) $xfi, 's', "$xfi"); 1335 1336 // If cell value is supplied, write cell value 1337 $cellValue = $pCell->getValue(); 1338 if (is_object($cellValue) || $cellValue !== '') { 1339 // Map type 1340 $mappedType = $pCell->getDataType(); 1341 1342 // Write data depending on its type 1343 switch (strtolower($mappedType)) { 1344 case 'inlinestr': // Inline string 1345 $this->writeCellInlineStr($objWriter, $mappedType, $cellValue); 1346 1347 break; 1348 case 's': // String 1349 $this->writeCellString($objWriter, $mappedType, $cellValue, $flippedStringTable); 1350 1351 break; 1352 case 'f': // Formula 1353 $this->writeCellFormula($objWriter, $cellValue, $pCell); 1354 1355 break; 1356 case 'n': // Numeric 1357 $this->writeCellNumeric($objWriter, $cellValue); 1358 1359 break; 1360 case 'b': // Boolean 1361 $this->writeCellBoolean($objWriter, $mappedType, $cellValue); 1362 1363 break; 1364 case 'e': // Error 1365 $this->writeCellError($objWriter, $mappedType, $cellValue); 1366 } 1367 } 1368 1369 $objWriter->endElement(); 1370 } 1371 1372 /** 1373 * Write Drawings. 1374 * 1375 * @param bool $includeCharts Flag indicating if we should include drawing details for charts 1376 */ 1377 private function writeDrawings(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet, $includeCharts = false): void 1378 { 1379 $unparsedLoadedData = $worksheet->getParent()->getUnparsedLoadedData(); 1380 $hasUnparsedDrawing = isset($unparsedLoadedData['sheets'][$worksheet->getCodeName()]['drawingOriginalIds']); 1381 $chartCount = ($includeCharts) ? $worksheet->getChartCollection()->count() : 0; 1382 if ($chartCount == 0 && $worksheet->getDrawingCollection()->count() == 0 && !$hasUnparsedDrawing) { 1383 return; 1384 } 1385 1386 // If sheet contains drawings, add the relationships 1387 $objWriter->startElement('drawing'); 1388 1389 $rId = 'rId1'; 1390 if (isset($unparsedLoadedData['sheets'][$worksheet->getCodeName()]['drawingOriginalIds'])) { 1391 $drawingOriginalIds = $unparsedLoadedData['sheets'][$worksheet->getCodeName()]['drawingOriginalIds']; 1392 // take first. In future can be overriten 1393 // (! synchronize with \PhpOffice\PhpSpreadsheet\Writer\Xlsx\Rels::writeWorksheetRelationships) 1394 $rId = reset($drawingOriginalIds); 1395 } 1396 1397 $objWriter->writeAttribute('r:id', $rId); 1398 $objWriter->endElement(); 1399 } 1400 1401 /** 1402 * Write LegacyDrawing. 1403 */ 1404 private function writeLegacyDrawing(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1405 { 1406 // If sheet contains comments, add the relationships 1407 if (count($worksheet->getComments()) > 0) { 1408 $objWriter->startElement('legacyDrawing'); 1409 $objWriter->writeAttribute('r:id', 'rId_comments_vml1'); 1410 $objWriter->endElement(); 1411 } 1412 } 1413 1414 /** 1415 * Write LegacyDrawingHF. 1416 */ 1417 private function writeLegacyDrawingHF(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1418 { 1419 // If sheet contains images, add the relationships 1420 if (count($worksheet->getHeaderFooter()->getImages()) > 0) { 1421 $objWriter->startElement('legacyDrawingHF'); 1422 $objWriter->writeAttribute('r:id', 'rId_headerfooter_vml1'); 1423 $objWriter->endElement(); 1424 } 1425 } 1426 1427 private function writeAlternateContent(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1428 { 1429 if (empty($worksheet->getParent()->getUnparsedLoadedData()['sheets'][$worksheet->getCodeName()]['AlternateContents'])) { 1430 return; 1431 } 1432 1433 foreach ($worksheet->getParent()->getUnparsedLoadedData()['sheets'][$worksheet->getCodeName()]['AlternateContents'] as $alternateContent) { 1434 $objWriter->writeRaw($alternateContent); 1435 } 1436 } 1437 1438 /** 1439 * write <ExtLst> 1440 * only implementation conditionalFormattings. 1441 * 1442 * @url https://docs.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/07d607af-5618-4ca2-b683-6a78dc0d9627 1443 */ 1444 private function writeExtLst(XMLWriter $objWriter, PhpspreadsheetWorksheet $worksheet): void 1445 { 1446 $conditionalFormattingRuleExtList = []; 1447 foreach ($worksheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) { 1448 /** @var Conditional $conditional */ 1449 foreach ($conditionalStyles as $conditional) { 1450 $dataBar = $conditional->getDataBar(); 1451 if ($dataBar && $dataBar->getConditionalFormattingRuleExt()) { 1452 $conditionalFormattingRuleExtList[] = $dataBar->getConditionalFormattingRuleExt(); 1453 } 1454 } 1455 } 1456 1457 if (count($conditionalFormattingRuleExtList) > 0) { 1458 $conditionalFormattingRuleExtNsPrefix = 'x14'; 1459 $objWriter->startElement('extLst'); 1460 $objWriter->startElement('ext'); 1461 $objWriter->writeAttribute('uri', '{78C0D931-6437-407d-A8EE-F0AAD7539E65}'); 1462 $objWriter->startElementNs($conditionalFormattingRuleExtNsPrefix, 'conditionalFormattings', null); 1463 foreach ($conditionalFormattingRuleExtList as $extension) { 1464 self::writeExtConditionalFormattingElements($objWriter, $extension); 1465 } 1466 $objWriter->endElement(); //end conditionalFormattings 1467 $objWriter->endElement(); //end ext 1468 $objWriter->endElement(); //end extLst 1469 } 1470 } 1471 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
