PHPOffice /
PhpSpreadsheet
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like Workbook often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Workbook, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 68 | class Workbook extends BIFFwriter |
||
| 69 | { |
||
| 70 | /** |
||
| 71 | * Formula parser. |
||
| 72 | * |
||
| 73 | * @var \PhpOffice\PhpSpreadsheet\Writer\Xls\Parser |
||
| 74 | */ |
||
| 75 | private $parser; |
||
| 76 | |||
| 77 | /** |
||
| 78 | * The BIFF file size for the workbook. |
||
| 79 | * |
||
| 80 | * @var int |
||
| 81 | * |
||
| 82 | * @see calcSheetOffsets() |
||
| 83 | */ |
||
| 84 | private $biffSize; |
||
| 85 | |||
| 86 | /** |
||
| 87 | * XF Writers. |
||
| 88 | * |
||
| 89 | * @var \PhpOffice\PhpSpreadsheet\Writer\Xls\Xf[] |
||
| 90 | */ |
||
| 91 | private $xfWriters = []; |
||
| 92 | |||
| 93 | /** |
||
| 94 | * Array containing the colour palette. |
||
| 95 | * |
||
| 96 | * @var array |
||
| 97 | */ |
||
| 98 | private $palette; |
||
| 99 | |||
| 100 | /** |
||
| 101 | * The codepage indicates the text encoding used for strings. |
||
| 102 | * |
||
| 103 | * @var int |
||
| 104 | */ |
||
| 105 | private $codepage; |
||
| 106 | |||
| 107 | /** |
||
| 108 | * The country code used for localization. |
||
| 109 | * |
||
| 110 | * @var int |
||
| 111 | */ |
||
| 112 | private $countryCode; |
||
| 113 | |||
| 114 | /** |
||
| 115 | * Workbook. |
||
| 116 | * |
||
| 117 | * @var Spreadsheet |
||
| 118 | */ |
||
| 119 | private $spreadsheet; |
||
| 120 | |||
| 121 | /** |
||
| 122 | * Fonts writers. |
||
| 123 | * |
||
| 124 | * @var Font[] |
||
| 125 | */ |
||
| 126 | private $fontWriters = []; |
||
| 127 | |||
| 128 | /** |
||
| 129 | * Added fonts. Maps from font's hash => index in workbook. |
||
| 130 | * |
||
| 131 | * @var array |
||
| 132 | */ |
||
| 133 | private $addedFonts = []; |
||
| 134 | |||
| 135 | /** |
||
| 136 | * Shared number formats. |
||
| 137 | * |
||
| 138 | * @var array |
||
| 139 | */ |
||
| 140 | private $numberFormats = []; |
||
| 141 | |||
| 142 | /** |
||
| 143 | * Added number formats. Maps from numberFormat's hash => index in workbook. |
||
| 144 | * |
||
| 145 | * @var array |
||
| 146 | */ |
||
| 147 | private $addedNumberFormats = []; |
||
| 148 | |||
| 149 | /** |
||
| 150 | * Sizes of the binary worksheet streams. |
||
| 151 | * |
||
| 152 | * @var array |
||
| 153 | */ |
||
| 154 | private $worksheetSizes = []; |
||
| 155 | |||
| 156 | /** |
||
| 157 | * Offsets of the binary worksheet streams relative to the start of the global workbook stream. |
||
| 158 | * |
||
| 159 | * @var array |
||
| 160 | */ |
||
| 161 | private $worksheetOffsets = []; |
||
| 162 | |||
| 163 | /** |
||
| 164 | * Total number of shared strings in workbook. |
||
| 165 | * |
||
| 166 | * @var int |
||
| 167 | */ |
||
| 168 | private $stringTotal; |
||
| 169 | |||
| 170 | /** |
||
| 171 | * Number of unique shared strings in workbook. |
||
| 172 | * |
||
| 173 | * @var int |
||
| 174 | */ |
||
| 175 | private $stringUnique; |
||
| 176 | |||
| 177 | /** |
||
| 178 | * Array of unique shared strings in workbook. |
||
| 179 | * |
||
| 180 | * @var array |
||
| 181 | */ |
||
| 182 | private $stringTable; |
||
| 183 | |||
| 184 | /** |
||
| 185 | * Color cache. |
||
| 186 | */ |
||
| 187 | private $colors; |
||
| 188 | |||
| 189 | /** |
||
| 190 | * Escher object corresponding to MSODRAWINGGROUP. |
||
| 191 | * |
||
| 192 | * @var \PhpOffice\PhpSpreadsheet\Shared\Escher |
||
| 193 | */ |
||
| 194 | private $escher; |
||
| 195 | |||
| 196 | /** |
||
| 197 | * Class constructor. |
||
| 198 | * |
||
| 199 | * @param Spreadsheet $spreadsheet The Workbook |
||
| 200 | * @param int $str_total Total number of strings |
||
| 201 | * @param int $str_unique Total number of unique strings |
||
| 202 | * @param array $str_table String Table |
||
| 203 | * @param array $colors Colour Table |
||
| 204 | * @param Parser $parser The formula parser created for the Workbook |
||
| 205 | */ |
||
| 206 | 48 | public function __construct(Spreadsheet $spreadsheet, &$str_total, &$str_unique, &$str_table, &$colors, Parser $parser) |
|
| 243 | |||
| 244 | /** |
||
| 245 | * Add a new XF writer. |
||
| 246 | * |
||
| 247 | * @param \PhpOffice\PhpSpreadsheet\Style |
||
| 248 | * @param bool Is it a style XF? |
||
| 249 | * @param mixed $style |
||
| 250 | * @param mixed $isStyleXf |
||
| 251 | * |
||
| 252 | * @return int Index to XF record |
||
| 253 | */ |
||
| 254 | 39 | public function addXfWriter($style, $isStyleXf = false) |
|
| 298 | |||
| 299 | /** |
||
| 300 | * Add a font to added fonts. |
||
| 301 | * |
||
| 302 | * @param \PhpOffice\PhpSpreadsheet\Style\Font $font |
||
| 303 | * |
||
| 304 | * @return int Index to FONT record |
||
| 305 | */ |
||
| 306 | 39 | public function addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font) |
|
| 324 | |||
| 325 | /** |
||
| 326 | * Alter color palette adding a custom color. |
||
| 327 | * |
||
| 328 | * @param string $rgb E.g. 'FF00AA' |
||
| 329 | * |
||
| 330 | * @return int Color index |
||
| 331 | */ |
||
| 332 | 48 | private function addColor($rgb) |
|
| 333 | { |
||
| 334 | 48 | if (!isset($this->colors[$rgb])) { |
|
| 335 | $color = |
||
| 336 | [ |
||
| 337 | 48 | hexdec(substr($rgb, 0, 2)), |
|
| 338 | 48 | hexdec(substr($rgb, 2, 2)), |
|
| 339 | 48 | hexdec(substr($rgb, 4)), |
|
| 340 | 48 | 0, |
|
| 341 | ]; |
||
| 342 | 48 | $colorIndex = array_search($color, $this->palette); |
|
| 343 | 48 | if ($colorIndex) { |
|
| 344 | 45 | $this->colors[$rgb] = $colorIndex; |
|
| 345 | } else { |
||
| 346 | 17 | if (count($this->colors) == 0) { |
|
| 347 | 9 | $lastColor = 7; |
|
| 348 | } else { |
||
| 349 | 15 | $lastColor = end($this->colors); |
|
| 350 | } |
||
| 351 | 17 | if ($lastColor < 57) { |
|
| 352 | // then we add a custom color altering the palette |
||
| 353 | 15 | $colorIndex = $lastColor + 1; |
|
| 354 | 15 | $this->palette[$colorIndex] = $color; |
|
| 355 | 15 | $this->colors[$rgb] = $colorIndex; |
|
| 356 | } else { |
||
| 357 | // no room for more custom colors, just map to black |
||
| 358 | 48 | $colorIndex = 0; |
|
| 359 | } |
||
| 360 | } |
||
| 361 | } else { |
||
| 362 | // fetch already added custom color |
||
| 363 | 41 | $colorIndex = $this->colors[$rgb]; |
|
| 364 | } |
||
| 365 | |||
| 366 | 48 | return $colorIndex; |
|
| 367 | } |
||
| 368 | |||
| 369 | /** |
||
| 370 | * Sets the colour palette to the Excel 97+ default. |
||
| 371 | */ |
||
| 372 | 48 | private function setPaletteXl97() |
|
| 373 | { |
||
| 374 | 48 | $this->palette = [ |
|
| 375 | 0x08 => [0x00, 0x00, 0x00, 0x00], |
||
| 376 | 0x09 => [0xff, 0xff, 0xff, 0x00], |
||
| 377 | 0x0A => [0xff, 0x00, 0x00, 0x00], |
||
| 378 | 0x0B => [0x00, 0xff, 0x00, 0x00], |
||
| 379 | 0x0C => [0x00, 0x00, 0xff, 0x00], |
||
| 380 | 0x0D => [0xff, 0xff, 0x00, 0x00], |
||
| 381 | 0x0E => [0xff, 0x00, 0xff, 0x00], |
||
| 382 | 0x0F => [0x00, 0xff, 0xff, 0x00], |
||
| 383 | 0x10 => [0x80, 0x00, 0x00, 0x00], |
||
| 384 | 0x11 => [0x00, 0x80, 0x00, 0x00], |
||
| 385 | 0x12 => [0x00, 0x00, 0x80, 0x00], |
||
| 386 | 0x13 => [0x80, 0x80, 0x00, 0x00], |
||
| 387 | 0x14 => [0x80, 0x00, 0x80, 0x00], |
||
| 388 | 0x15 => [0x00, 0x80, 0x80, 0x00], |
||
| 389 | 0x16 => [0xc0, 0xc0, 0xc0, 0x00], |
||
| 390 | 0x17 => [0x80, 0x80, 0x80, 0x00], |
||
| 391 | 0x18 => [0x99, 0x99, 0xff, 0x00], |
||
| 392 | 0x19 => [0x99, 0x33, 0x66, 0x00], |
||
| 393 | 0x1A => [0xff, 0xff, 0xcc, 0x00], |
||
| 394 | 0x1B => [0xcc, 0xff, 0xff, 0x00], |
||
| 395 | 0x1C => [0x66, 0x00, 0x66, 0x00], |
||
| 396 | 0x1D => [0xff, 0x80, 0x80, 0x00], |
||
| 397 | 0x1E => [0x00, 0x66, 0xcc, 0x00], |
||
| 398 | 0x1F => [0xcc, 0xcc, 0xff, 0x00], |
||
| 399 | 0x20 => [0x00, 0x00, 0x80, 0x00], |
||
| 400 | 0x21 => [0xff, 0x00, 0xff, 0x00], |
||
| 401 | 0x22 => [0xff, 0xff, 0x00, 0x00], |
||
| 402 | 0x23 => [0x00, 0xff, 0xff, 0x00], |
||
| 403 | 0x24 => [0x80, 0x00, 0x80, 0x00], |
||
| 404 | 0x25 => [0x80, 0x00, 0x00, 0x00], |
||
| 405 | 0x26 => [0x00, 0x80, 0x80, 0x00], |
||
| 406 | 0x27 => [0x00, 0x00, 0xff, 0x00], |
||
| 407 | 0x28 => [0x00, 0xcc, 0xff, 0x00], |
||
| 408 | 0x29 => [0xcc, 0xff, 0xff, 0x00], |
||
| 409 | 0x2A => [0xcc, 0xff, 0xcc, 0x00], |
||
| 410 | 0x2B => [0xff, 0xff, 0x99, 0x00], |
||
| 411 | 0x2C => [0x99, 0xcc, 0xff, 0x00], |
||
| 412 | 0x2D => [0xff, 0x99, 0xcc, 0x00], |
||
| 413 | 0x2E => [0xcc, 0x99, 0xff, 0x00], |
||
| 414 | 0x2F => [0xff, 0xcc, 0x99, 0x00], |
||
| 415 | 0x30 => [0x33, 0x66, 0xff, 0x00], |
||
| 416 | 0x31 => [0x33, 0xcc, 0xcc, 0x00], |
||
| 417 | 0x32 => [0x99, 0xcc, 0x00, 0x00], |
||
| 418 | 0x33 => [0xff, 0xcc, 0x00, 0x00], |
||
| 419 | 0x34 => [0xff, 0x99, 0x00, 0x00], |
||
| 420 | 0x35 => [0xff, 0x66, 0x00, 0x00], |
||
| 421 | 0x36 => [0x66, 0x66, 0x99, 0x00], |
||
| 422 | 0x37 => [0x96, 0x96, 0x96, 0x00], |
||
| 423 | 0x38 => [0x00, 0x33, 0x66, 0x00], |
||
| 424 | 0x39 => [0x33, 0x99, 0x66, 0x00], |
||
| 425 | 0x3A => [0x00, 0x33, 0x00, 0x00], |
||
| 426 | 0x3B => [0x33, 0x33, 0x00, 0x00], |
||
| 427 | 0x3C => [0x99, 0x33, 0x00, 0x00], |
||
| 428 | 0x3D => [0x99, 0x33, 0x66, 0x00], |
||
| 429 | 0x3E => [0x33, 0x33, 0x99, 0x00], |
||
| 430 | 0x3F => [0x33, 0x33, 0x33, 0x00], |
||
| 431 | ]; |
||
| 432 | 48 | } |
|
| 433 | |||
| 434 | /** |
||
| 435 | * Assemble worksheets into a workbook and send the BIFF data to an OLE |
||
| 436 | * storage. |
||
| 437 | * |
||
| 438 | * @param array $pWorksheetSizes The sizes in bytes of the binary worksheet streams |
||
| 439 | * |
||
| 440 | * @return string Binary data for workbook stream |
||
| 441 | */ |
||
| 442 | 39 | public function writeWorkbook(array $pWorksheetSizes) |
|
| 443 | { |
||
| 444 | 39 | $this->worksheetSizes = $pWorksheetSizes; |
|
| 445 | |||
| 446 | // Calculate the number of selected worksheet tabs and call the finalization |
||
| 447 | // methods for each worksheet |
||
| 448 | 39 | $total_worksheets = $this->spreadsheet->getSheetCount(); |
|
| 449 | |||
| 450 | // Add part 1 of the Workbook globals, what goes before the SHEET records |
||
| 451 | 39 | $this->storeBof(0x0005); |
|
| 452 | 39 | $this->writeCodepage(); |
|
| 453 | 39 | $this->writeWindow1(); |
|
| 454 | |||
| 455 | 39 | $this->writeDateMode(); |
|
| 456 | 39 | $this->writeAllFonts(); |
|
| 457 | 39 | $this->writeAllNumberFormats(); |
|
| 458 | 39 | $this->writeAllXfs(); |
|
| 459 | 39 | $this->writeAllStyles(); |
|
| 460 | 39 | $this->writePalette(); |
|
| 461 | |||
| 462 | // Prepare part 3 of the workbook global stream, what goes after the SHEET records |
||
| 463 | 39 | $part3 = ''; |
|
| 464 | 39 | if ($this->countryCode != -1) { |
|
| 465 | $part3 .= $this->writeCountry(); |
||
| 466 | } |
||
| 467 | 39 | $part3 .= $this->writeRecalcId(); |
|
| 468 | |||
| 469 | 39 | $part3 .= $this->writeSupbookInternal(); |
|
| 470 | /* TODO: store external SUPBOOK records and XCT and CRN records |
||
| 471 | in case of external references for BIFF8 */ |
||
| 472 | 39 | $part3 .= $this->writeExternalsheetBiff8(); |
|
| 473 | 39 | $part3 .= $this->writeAllDefinedNamesBiff8(); |
|
| 474 | 39 | $part3 .= $this->writeMsoDrawingGroup(); |
|
| 475 | 39 | $part3 .= $this->writeSharedStringsTable(); |
|
| 476 | |||
| 477 | 39 | $part3 .= $this->writeEof(); |
|
| 478 | |||
| 479 | // Add part 2 of the Workbook globals, the SHEET records |
||
| 480 | 39 | $this->calcSheetOffsets(); |
|
| 481 | 39 | for ($i = 0; $i < $total_worksheets; ++$i) { |
|
| 482 | 39 | $this->writeBoundSheet($this->spreadsheet->getSheet($i), $this->worksheetOffsets[$i]); |
|
| 483 | } |
||
| 484 | |||
| 485 | // Add part 3 of the Workbook globals |
||
| 486 | 39 | $this->_data .= $part3; |
|
| 487 | |||
| 488 | 39 | return $this->_data; |
|
| 489 | } |
||
| 490 | |||
| 491 | /** |
||
| 492 | * Calculate offsets for Worksheet BOF records. |
||
| 493 | */ |
||
| 494 | 39 | private function calcSheetOffsets() |
|
| 495 | { |
||
| 496 | 39 | $boundsheet_length = 10; // fixed length for a BOUNDSHEET record |
|
| 497 | |||
| 498 | // size of Workbook globals part 1 + 3 |
||
| 499 | 39 | $offset = $this->_datasize; |
|
| 500 | |||
| 501 | // add size of Workbook globals part 2, the length of the SHEET records |
||
| 502 | 39 | $total_worksheets = count($this->spreadsheet->getAllSheets()); |
|
| 503 | 39 | foreach ($this->spreadsheet->getWorksheetIterator() as $sheet) { |
|
| 504 | 39 | $offset += $boundsheet_length + strlen(StringHelper::UTF8toBIFF8UnicodeShort($sheet->getTitle())); |
|
| 505 | } |
||
| 506 | |||
| 507 | // add the sizes of each of the Sheet substreams, respectively |
||
| 508 | 39 | for ($i = 0; $i < $total_worksheets; ++$i) { |
|
| 509 | 39 | $this->worksheetOffsets[$i] = $offset; |
|
| 510 | 39 | $offset += $this->worksheetSizes[$i]; |
|
| 511 | } |
||
| 512 | 39 | $this->biffSize = $offset; |
|
| 513 | 39 | } |
|
| 514 | |||
| 515 | /** |
||
| 516 | * Store the Excel FONT records. |
||
| 517 | */ |
||
| 518 | 39 | private function writeAllFonts() |
|
| 519 | { |
||
| 520 | 39 | foreach ($this->fontWriters as $fontWriter) { |
|
| 521 | 39 | $this->append($fontWriter->writeFont()); |
|
| 522 | } |
||
| 523 | 39 | } |
|
| 524 | |||
| 525 | /** |
||
| 526 | * Store user defined numerical formats i.e. FORMAT records. |
||
| 527 | */ |
||
| 528 | 39 | private function writeAllNumberFormats() |
|
| 529 | { |
||
| 530 | 39 | foreach ($this->numberFormats as $numberFormatIndex => $numberFormat) { |
|
| 531 | 14 | $this->writeNumberFormat($numberFormat->getFormatCode(), $numberFormatIndex); |
|
| 532 | } |
||
| 533 | 39 | } |
|
| 534 | |||
| 535 | /** |
||
| 536 | * Write all XF records. |
||
| 537 | */ |
||
| 538 | 39 | private function writeAllXfs() |
|
| 539 | { |
||
| 540 | 39 | foreach ($this->xfWriters as $xfWriter) { |
|
| 541 | 39 | $this->append($xfWriter->writeXf()); |
|
| 542 | } |
||
| 543 | 39 | } |
|
| 544 | |||
| 545 | /** |
||
| 546 | * Write all STYLE records. |
||
| 547 | */ |
||
| 548 | 39 | private function writeAllStyles() |
|
| 549 | { |
||
| 550 | 39 | $this->writeStyle(); |
|
| 551 | 39 | } |
|
| 552 | |||
| 553 | /** |
||
| 554 | * Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for |
||
| 555 | * the NAME records. |
||
| 556 | */ |
||
| 557 | private function writeExternals() |
||
|
Unused Code
introduced
by
|
|||
| 558 | { |
||
| 559 | $countSheets = $this->spreadsheet->getSheetCount(); |
||
| 560 | // Create EXTERNCOUNT with number of worksheets |
||
| 561 | $this->writeExternalCount($countSheets); |
||
| 562 | |||
| 563 | // Create EXTERNSHEET for each worksheet |
||
| 564 | for ($i = 0; $i < $countSheets; ++$i) { |
||
| 565 | $this->writeExternalSheet($this->spreadsheet->getSheet($i)->getTitle()); |
||
| 566 | } |
||
| 567 | } |
||
| 568 | |||
| 569 | /** |
||
| 570 | * Write the NAME record to define the print area and the repeat rows and cols. |
||
| 571 | */ |
||
| 572 | private function writeNames() |
||
| 573 | { |
||
| 574 | // total number of sheets |
||
| 575 | $total_worksheets = $this->spreadsheet->getSheetCount(); |
||
| 576 | |||
| 577 | // Create the print area NAME records |
||
| 578 | for ($i = 0; $i < $total_worksheets; ++$i) { |
||
| 579 | $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
||
| 580 | // Write a Name record if the print area has been defined |
||
| 581 | if ($sheetSetup->isPrintAreaSet()) { |
||
| 582 | // Print area |
||
| 583 | $printArea = Cell::splitRange($sheetSetup->getPrintArea()); |
||
| 584 | $printArea = $printArea[0]; |
||
| 585 | $printArea[0] = Cell::coordinateFromString($printArea[0]); |
||
| 586 | $printArea[1] = Cell::coordinateFromString($printArea[1]); |
||
| 587 | |||
| 588 | $print_rowmin = $printArea[0][1] - 1; |
||
| 589 | $print_rowmax = $printArea[1][1] - 1; |
||
| 590 | $print_colmin = Cell::columnIndexFromString($printArea[0][0]) - 1; |
||
| 591 | $print_colmax = Cell::columnIndexFromString($printArea[1][0]) - 1; |
||
| 592 | |||
| 593 | $this->writeNameShort( |
||
| 594 | $i, // sheet index |
||
| 595 | 0x06, // NAME type |
||
| 596 | $print_rowmin, |
||
| 597 | $print_rowmax, |
||
| 598 | $print_colmin, |
||
| 599 | $print_colmax |
||
| 600 | ); |
||
| 601 | } |
||
| 602 | } |
||
| 603 | |||
| 604 | // Create the print title NAME records |
||
| 605 | for ($i = 0; $i < $total_worksheets; ++$i) { |
||
| 606 | $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
||
| 607 | |||
| 608 | // simultaneous repeatColumns repeatRows |
||
| 609 | if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { |
||
| 610 | $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
||
| 611 | $colmin = Cell::columnIndexFromString($repeat[0]) - 1; |
||
| 612 | $colmax = Cell::columnIndexFromString($repeat[1]) - 1; |
||
| 613 | |||
| 614 | $repeat = $sheetSetup->getRowsToRepeatAtTop(); |
||
| 615 | $rowmin = $repeat[0] - 1; |
||
| 616 | $rowmax = $repeat[1] - 1; |
||
| 617 | |||
| 618 | $this->writeNameLong( |
||
| 619 | $i, // sheet index |
||
| 620 | 0x07, // NAME type |
||
| 621 | $rowmin, |
||
| 622 | $rowmax, |
||
| 623 | $colmin, |
||
| 624 | $colmax |
||
| 625 | ); |
||
| 626 | |||
| 627 | // (exclusive) either repeatColumns or repeatRows |
||
| 628 | } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { |
||
| 629 | // Columns to repeat |
||
| 630 | View Code Duplication | if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { |
|
| 631 | $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
||
| 632 | $colmin = Cell::columnIndexFromString($repeat[0]) - 1; |
||
| 633 | $colmax = Cell::columnIndexFromString($repeat[1]) - 1; |
||
| 634 | } else { |
||
| 635 | $colmin = 0; |
||
| 636 | $colmax = 255; |
||
| 637 | } |
||
| 638 | |||
| 639 | // Rows to repeat |
||
| 640 | View Code Duplication | if ($sheetSetup->isRowsToRepeatAtTopSet()) { |
|
| 641 | $repeat = $sheetSetup->getRowsToRepeatAtTop(); |
||
| 642 | $rowmin = $repeat[0] - 1; |
||
| 643 | $rowmax = $repeat[1] - 1; |
||
| 644 | } else { |
||
| 645 | $rowmin = 0; |
||
| 646 | $rowmax = 65535; |
||
| 647 | } |
||
| 648 | |||
| 649 | $this->writeNameShort( |
||
| 650 | $i, // sheet index |
||
| 651 | 0x07, // NAME type |
||
| 652 | $rowmin, |
||
| 653 | $rowmax, |
||
| 654 | $colmin, |
||
| 655 | $colmax |
||
| 656 | ); |
||
| 657 | } |
||
| 658 | } |
||
| 659 | } |
||
| 660 | |||
| 661 | /** |
||
| 662 | * Writes all the DEFINEDNAME records (BIFF8). |
||
| 663 | * So far this is only used for repeating rows/columns (print titles) and print areas. |
||
| 664 | */ |
||
| 665 | 39 | private function writeAllDefinedNamesBiff8() |
|
| 666 | { |
||
| 667 | 39 | $chunk = ''; |
|
| 668 | |||
| 669 | // Named ranges |
||
| 670 | 39 | if (count($this->spreadsheet->getNamedRanges()) > 0) { |
|
| 671 | // Loop named ranges |
||
| 672 | 4 | $namedRanges = $this->spreadsheet->getNamedRanges(); |
|
| 673 | 4 | foreach ($namedRanges as $namedRange) { |
|
| 674 | // Create absolute coordinate |
||
| 675 | 4 | $range = Cell::splitRange($namedRange->getRange()); |
|
| 676 | 4 | View Code Duplication | for ($i = 0; $i < count($range); ++$i) { |
| 677 | 4 | $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]); |
|
| 678 | 4 | if (isset($range[$i][1])) { |
|
| 679 | 3 | $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]); |
|
| 680 | } |
||
| 681 | } |
||
| 682 | 4 | $range = Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2 |
|
| 683 | |||
| 684 | // parse formula |
||
| 685 | try { |
||
| 686 | 4 | $error = $this->parser->parse($range); |
|
| 687 | 4 | $formulaData = $this->parser->toReversePolish(); |
|
| 688 | |||
| 689 | // make sure tRef3d is of type tRef3dR (0x3A) |
||
| 690 | 4 | if (isset($formulaData[0]) and ($formulaData[0] == "\x7A" or $formulaData[0] == "\x5A")) { |
|
| 691 | 1 | $formulaData = "\x3A" . substr($formulaData, 1); |
|
| 692 | } |
||
| 693 | |||
| 694 | 4 | if ($namedRange->getLocalOnly()) { |
|
| 695 | // local scope |
||
| 696 | $scope = $this->spreadsheet->getIndex($namedRange->getScope()) + 1; |
||
| 697 | } else { |
||
| 698 | // global scope |
||
| 699 | 4 | $scope = 0; |
|
| 700 | } |
||
| 701 | 4 | $chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false)); |
|
| 702 | 4 | } catch (PhpSpreadsheetException $e) { |
|
| 703 | // do nothing |
||
| 704 | } |
||
| 705 | } |
||
| 706 | } |
||
| 707 | |||
| 708 | // total number of sheets |
||
| 709 | 39 | $total_worksheets = $this->spreadsheet->getSheetCount(); |
|
| 710 | |||
| 711 | // write the print titles (repeating rows, columns), if any |
||
| 712 | 39 | for ($i = 0; $i < $total_worksheets; ++$i) { |
|
| 713 | 39 | $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
|
| 714 | // simultaneous repeatColumns repeatRows |
||
| 715 | 39 | if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { |
|
| 716 | $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
||
| 717 | $colmin = Cell::columnIndexFromString($repeat[0]) - 1; |
||
| 718 | $colmax = Cell::columnIndexFromString($repeat[1]) - 1; |
||
| 719 | |||
| 720 | $repeat = $sheetSetup->getRowsToRepeatAtTop(); |
||
| 721 | $rowmin = $repeat[0] - 1; |
||
| 722 | $rowmax = $repeat[1] - 1; |
||
| 723 | |||
| 724 | // construct formula data manually |
||
| 725 | $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc |
||
| 726 | $formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d |
||
| 727 | $formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d |
||
| 728 | $formulaData .= pack('C', 0x10); // tList |
||
| 729 | |||
| 730 | // store the DEFINEDNAME record |
||
| 731 | $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); |
||
| 732 | |||
| 733 | // (exclusive) either repeatColumns or repeatRows |
||
| 734 | 39 | } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { |
|
| 735 | // Columns to repeat |
||
| 736 | 1 | View Code Duplication | if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { |
| 737 | $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
||
| 738 | $colmin = Cell::columnIndexFromString($repeat[0]) - 1; |
||
| 739 | $colmax = Cell::columnIndexFromString($repeat[1]) - 1; |
||
| 740 | } else { |
||
| 741 | 1 | $colmin = 0; |
|
| 742 | 1 | $colmax = 255; |
|
| 743 | } |
||
| 744 | // Rows to repeat |
||
| 745 | 1 | View Code Duplication | if ($sheetSetup->isRowsToRepeatAtTopSet()) { |
| 746 | 1 | $repeat = $sheetSetup->getRowsToRepeatAtTop(); |
|
| 747 | 1 | $rowmin = $repeat[0] - 1; |
|
| 748 | 1 | $rowmax = $repeat[1] - 1; |
|
| 749 | } else { |
||
| 750 | $rowmin = 0; |
||
| 751 | $rowmax = 65535; |
||
| 752 | } |
||
| 753 | |||
| 754 | // construct formula data manually because parser does not recognize absolute 3d cell references |
||
| 755 | 1 | $formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax); |
|
| 756 | |||
| 757 | // store the DEFINEDNAME record |
||
| 758 | 1 | $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); |
|
| 759 | } |
||
| 760 | } |
||
| 761 | |||
| 762 | // write the print areas, if any |
||
| 763 | 39 | for ($i = 0; $i < $total_worksheets; ++$i) { |
|
| 764 | 39 | $sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
|
| 765 | 39 | if ($sheetSetup->isPrintAreaSet()) { |
|
| 766 | // Print area, e.g. A3:J6,H1:X20 |
||
| 767 | $printArea = Cell::splitRange($sheetSetup->getPrintArea()); |
||
| 768 | $countPrintArea = count($printArea); |
||
| 769 | |||
| 770 | $formulaData = ''; |
||
| 771 | for ($j = 0; $j < $countPrintArea; ++$j) { |
||
| 772 | $printAreaRect = $printArea[$j]; // e.g. A3:J6 |
||
| 773 | $printAreaRect[0] = Cell::coordinateFromString($printAreaRect[0]); |
||
| 774 | $printAreaRect[1] = Cell::coordinateFromString($printAreaRect[1]); |
||
| 775 | |||
| 776 | $print_rowmin = $printAreaRect[0][1] - 1; |
||
| 777 | $print_rowmax = $printAreaRect[1][1] - 1; |
||
| 778 | $print_colmin = Cell::columnIndexFromString($printAreaRect[0][0]) - 1; |
||
| 779 | $print_colmax = Cell::columnIndexFromString($printAreaRect[1][0]) - 1; |
||
| 780 | |||
| 781 | // construct formula data manually because parser does not recognize absolute 3d cell references |
||
| 782 | $formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); |
||
| 783 | |||
| 784 | if ($j > 0) { |
||
| 785 | $formulaData .= pack('C', 0x10); // list operator token ',' |
||
| 786 | } |
||
| 787 | } |
||
| 788 | |||
| 789 | // store the DEFINEDNAME record |
||
| 790 | $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true)); |
||
| 791 | } |
||
| 792 | } |
||
| 793 | |||
| 794 | // write autofilters, if any |
||
| 795 | 39 | for ($i = 0; $i < $total_worksheets; ++$i) { |
|
| 796 | 39 | $sheetAutoFilter = $this->spreadsheet->getSheet($i)->getAutoFilter(); |
|
| 797 | 39 | $autoFilterRange = $sheetAutoFilter->getRange(); |
|
| 798 | 39 | if (!empty($autoFilterRange)) { |
|
| 799 | 3 | $rangeBounds = Cell::rangeBoundaries($autoFilterRange); |
|
| 800 | |||
| 801 | //Autofilter built in name |
||
| 802 | 3 | $name = pack('C', 0x0D); |
|
| 803 | |||
| 804 | 3 | $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); |
|
| 805 | } |
||
| 806 | } |
||
| 807 | |||
| 808 | 39 | return $chunk; |
|
| 809 | } |
||
| 810 | |||
| 811 | /** |
||
| 812 | * Write a DEFINEDNAME record for BIFF8 using explicit binary formula data. |
||
| 813 | * |
||
| 814 | * @param string $name The name in UTF-8 |
||
| 815 | * @param string $formulaData The binary formula data |
||
| 816 | * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global |
||
| 817 | * @param bool $isBuiltIn Built-in name? |
||
| 818 | * |
||
| 819 | * @return string Complete binary record data |
||
| 820 | */ |
||
| 821 | 5 | private function writeDefinedNameBiff8($name, $formulaData, $sheetIndex = 0, $isBuiltIn = false) |
|
| 822 | { |
||
| 823 | 5 | $record = 0x0018; |
|
| 824 | |||
| 825 | // option flags |
||
| 826 | 5 | $options = $isBuiltIn ? 0x20 : 0x00; |
|
| 827 | |||
| 828 | // length of the name, character count |
||
| 829 | 5 | $nlen = StringHelper::countCharacters($name); |
|
| 830 | |||
| 831 | // name with stripped length field |
||
| 832 | 5 | $name = substr(StringHelper::UTF8toBIFF8UnicodeLong($name), 2); |
|
| 833 | |||
| 834 | // size of the formula (in bytes) |
||
| 835 | 5 | $sz = strlen($formulaData); |
|
| 836 | |||
| 837 | // combine the parts |
||
| 838 | 5 | $data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0) |
|
| 839 | 5 | . $name . $formulaData; |
|
| 840 | 5 | $length = strlen($data); |
|
| 841 | |||
| 842 | 5 | $header = pack('vv', $record, $length); |
|
| 843 | |||
| 844 | 5 | return $header . $data; |
|
| 845 | } |
||
| 846 | |||
| 847 | /** |
||
| 848 | * Write a short NAME record. |
||
| 849 | * |
||
| 850 | * @param string $name |
||
| 851 | * @param string $sheetIndex 1-based sheet index the defined name applies to. 0 = global |
||
| 852 | * @param integer[][] $rangeBounds range boundaries |
||
| 853 | * @param bool $isHidden |
||
| 854 | * |
||
| 855 | * @return string Complete binary record data |
||
| 856 | * */ |
||
| 857 | 3 | private function writeShortNameBiff8($name, $sheetIndex, $rangeBounds, $isHidden = false) |
|
| 858 | { |
||
| 859 | 3 | $record = 0x0018; |
|
| 860 | |||
| 861 | // option flags |
||
| 862 | 3 | $options = ($isHidden ? 0x21 : 0x00); |
|
| 863 | |||
| 864 | 3 | $extra = pack( |
|
| 865 | 3 | 'Cvvvvv', |
|
| 866 | 3 | 0x3B, |
|
| 867 | 3 | $sheetIndex - 1, |
|
| 868 | 3 | $rangeBounds[0][1] - 1, |
|
| 869 | 3 | $rangeBounds[1][1] - 1, |
|
| 870 | 3 | $rangeBounds[0][0] - 1, |
|
| 871 | 3 | $rangeBounds[1][0] - 1 |
|
| 872 | ); |
||
| 873 | |||
| 874 | // size of the formula (in bytes) |
||
| 875 | 3 | $sz = strlen($extra); |
|
| 876 | |||
| 877 | // combine the parts |
||
| 878 | 3 | $data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0) |
|
| 879 | 3 | . $name . $extra; |
|
| 880 | 3 | $length = strlen($data); |
|
| 881 | |||
| 882 | 3 | $header = pack('vv', $record, $length); |
|
| 883 | |||
| 884 | 3 | return $header . $data; |
|
| 885 | } |
||
| 886 | |||
| 887 | /** |
||
| 888 | * Stores the CODEPAGE biff record. |
||
| 889 | */ |
||
| 890 | 39 | private function writeCodepage() |
|
| 891 | { |
||
| 892 | 39 | $record = 0x0042; // Record identifier |
|
| 893 | 39 | $length = 0x0002; // Number of bytes to follow |
|
| 894 | 39 | $cv = $this->codepage; // The code page |
|
| 895 | |||
| 896 | 39 | $header = pack('vv', $record, $length); |
|
| 897 | 39 | $data = pack('v', $cv); |
|
| 898 | |||
| 899 | 39 | $this->append($header . $data); |
|
| 900 | 39 | } |
|
| 901 | |||
| 902 | /** |
||
| 903 | * Write Excel BIFF WINDOW1 record. |
||
| 904 | */ |
||
| 905 | 39 | private function writeWindow1() |
|
| 906 | { |
||
| 907 | 39 | $record = 0x003D; // Record identifier |
|
| 908 | 39 | $length = 0x0012; // Number of bytes to follow |
|
| 909 | |||
| 910 | 39 | $xWn = 0x0000; // Horizontal position of window |
|
| 911 | 39 | $yWn = 0x0000; // Vertical position of window |
|
| 912 | 39 | $dxWn = 0x25BC; // Width of window |
|
| 913 | 39 | $dyWn = 0x1572; // Height of window |
|
| 914 | |||
| 915 | 39 | $grbit = 0x0038; // Option flags |
|
| 916 | |||
| 917 | // not supported by PhpSpreadsheet, so there is only one selected sheet, the active |
||
| 918 | 39 | $ctabsel = 1; // Number of workbook tabs selected |
|
| 919 | |||
| 920 | 39 | $wTabRatio = 0x0258; // Tab to scrollbar ratio |
|
| 921 | |||
| 922 | // not supported by PhpSpreadsheet, set to 0 |
||
| 923 | 39 | $itabFirst = 0; // 1st displayed worksheet |
|
| 924 | 39 | $itabCur = $this->spreadsheet->getActiveSheetIndex(); // Active worksheet |
|
| 925 | |||
| 926 | 39 | $header = pack('vv', $record, $length); |
|
| 927 | 39 | $data = pack('vvvvvvvvv', $xWn, $yWn, $dxWn, $dyWn, $grbit, $itabCur, $itabFirst, $ctabsel, $wTabRatio); |
|
| 928 | 39 | $this->append($header . $data); |
|
| 929 | 39 | } |
|
| 930 | |||
| 931 | /** |
||
| 932 | * Writes Excel BIFF BOUNDSHEET record. |
||
| 933 | * |
||
| 934 | * @param \PhpOffice\PhpSpreadsheet\Worksheet $sheet Worksheet name |
||
| 935 | * @param int $offset Location of worksheet BOF |
||
| 936 | */ |
||
| 937 | 39 | private function writeBoundSheet($sheet, $offset) |
|
| 938 | { |
||
| 939 | 39 | $sheetname = $sheet->getTitle(); |
|
| 940 | 39 | $record = 0x0085; // Record identifier |
|
| 941 | |||
| 942 | // sheet state |
||
| 943 | 39 | switch ($sheet->getSheetState()) { |
|
| 944 | 39 | case \PhpOffice\PhpSpreadsheet\Worksheet::SHEETSTATE_VISIBLE: |
|
| 945 | 39 | $ss = 0x00; |
|
| 946 | 39 | break; |
|
| 947 | case \PhpOffice\PhpSpreadsheet\Worksheet::SHEETSTATE_HIDDEN: |
||
| 948 | $ss = 0x01; |
||
| 949 | break; |
||
| 950 | case \PhpOffice\PhpSpreadsheet\Worksheet::SHEETSTATE_VERYHIDDEN: |
||
| 951 | $ss = 0x02; |
||
| 952 | break; |
||
| 953 | default: |
||
| 954 | $ss = 0x00; |
||
| 955 | break; |
||
| 956 | } |
||
| 957 | |||
| 958 | // sheet type |
||
| 959 | 39 | $st = 0x00; |
|
| 960 | |||
| 961 | 39 | $grbit = 0x0000; // Visibility and sheet type |
|
| 962 | |||
| 963 | 39 | $data = pack('VCC', $offset, $ss, $st); |
|
| 964 | 39 | $data .= StringHelper::UTF8toBIFF8UnicodeShort($sheetname); |
|
| 965 | |||
| 966 | 39 | $length = strlen($data); |
|
| 967 | 39 | $header = pack('vv', $record, $length); |
|
| 968 | 39 | $this->append($header . $data); |
|
| 969 | 39 | } |
|
| 970 | |||
| 971 | /** |
||
| 972 | * Write Internal SUPBOOK record. |
||
| 973 | */ |
||
| 974 | 39 | View Code Duplication | private function writeSupbookInternal() |
| 975 | { |
||
| 976 | 39 | $record = 0x01AE; // Record identifier |
|
| 977 | 39 | $length = 0x0004; // Bytes to follow |
|
| 978 | |||
| 979 | 39 | $header = pack('vv', $record, $length); |
|
| 980 | 39 | $data = pack('vv', $this->spreadsheet->getSheetCount(), 0x0401); |
|
| 981 | |||
| 982 | 39 | return $this->writeData($header . $data); |
|
| 983 | } |
||
| 984 | |||
| 985 | /** |
||
| 986 | * Writes the Excel BIFF EXTERNSHEET record. These references are used by |
||
| 987 | * formulas. |
||
| 988 | */ |
||
| 989 | 39 | private function writeExternalsheetBiff8() |
|
| 990 | { |
||
| 991 | 39 | $totalReferences = count($this->parser->references); |
|
| 992 | 39 | $record = 0x0017; // Record identifier |
|
| 993 | 39 | $length = 2 + 6 * $totalReferences; // Number of bytes to follow |
|
| 994 | |||
| 995 | 39 | $supbook_index = 0; // FIXME: only using internal SUPBOOK record |
|
| 996 | 39 | $header = pack('vv', $record, $length); |
|
| 997 | 39 | $data = pack('v', $totalReferences); |
|
| 998 | 39 | for ($i = 0; $i < $totalReferences; ++$i) { |
|
| 999 | 39 | $data .= $this->parser->references[$i]; |
|
| 1000 | } |
||
| 1001 | |||
| 1002 | 39 | return $this->writeData($header . $data); |
|
| 1003 | } |
||
| 1004 | |||
| 1005 | /** |
||
| 1006 | * Write Excel BIFF STYLE records. |
||
| 1007 | */ |
||
| 1008 | 39 | private function writeStyle() |
|
| 1009 | { |
||
| 1010 | 39 | $record = 0x0293; // Record identifier |
|
| 1011 | 39 | $length = 0x0004; // Bytes to follow |
|
| 1012 | |||
| 1013 | 39 | $ixfe = 0x8000; // Index to cell style XF |
|
| 1014 | 39 | $BuiltIn = 0x00; // Built-in style |
|
| 1015 | 39 | $iLevel = 0xff; // Outline style level |
|
| 1016 | |||
| 1017 | 39 | $header = pack('vv', $record, $length); |
|
| 1018 | 39 | $data = pack('vCC', $ixfe, $BuiltIn, $iLevel); |
|
| 1019 | 39 | $this->append($header . $data); |
|
| 1020 | 39 | } |
|
| 1021 | |||
| 1022 | /** |
||
| 1023 | * Writes Excel FORMAT record for non "built-in" numerical formats. |
||
| 1024 | * |
||
| 1025 | * @param string $format Custom format string |
||
| 1026 | * @param int $ifmt Format index code |
||
| 1027 | */ |
||
| 1028 | 14 | View Code Duplication | private function writeNumberFormat($format, $ifmt) |
| 1029 | { |
||
| 1030 | 14 | $record = 0x041E; // Record identifier |
|
| 1031 | |||
| 1032 | 14 | $numberFormatString = StringHelper::UTF8toBIFF8UnicodeLong($format); |
|
| 1033 | 14 | $length = 2 + strlen($numberFormatString); // Number of bytes to follow |
|
| 1034 | |||
| 1035 | 14 | $header = pack('vv', $record, $length); |
|
| 1036 | 14 | $data = pack('v', $ifmt) . $numberFormatString; |
|
| 1037 | 14 | $this->append($header . $data); |
|
| 1038 | 14 | } |
|
| 1039 | |||
| 1040 | /** |
||
| 1041 | * Write DATEMODE record to indicate the date system in use (1904 or 1900). |
||
| 1042 | */ |
||
| 1043 | 39 | private function writeDateMode() |
|
| 1044 | { |
||
| 1045 | 39 | $record = 0x0022; // Record identifier |
|
| 1046 | 39 | $length = 0x0002; // Bytes to follow |
|
| 1047 | |||
| 1048 | 39 | $f1904 = (Date::getExcelCalendar() == Date::CALENDAR_MAC_1904) |
|
| 1049 | ? 1 |
||
| 1050 | 39 | : 0; // Flag for 1904 date system |
|
| 1051 | |||
| 1052 | 39 | $header = pack('vv', $record, $length); |
|
| 1053 | 39 | $data = pack('v', $f1904); |
|
| 1054 | 39 | $this->append($header . $data); |
|
| 1055 | 39 | } |
|
| 1056 | |||
| 1057 | /** |
||
| 1058 | * Write BIFF record EXTERNCOUNT to indicate the number of external sheet |
||
| 1059 | * references in the workbook. |
||
| 1060 | * |
||
| 1061 | * Excel only stores references to external sheets that are used in NAME. |
||
| 1062 | * The workbook NAME record is required to define the print area and the repeat |
||
| 1063 | * rows and columns. |
||
| 1064 | * |
||
| 1065 | * A similar method is used in Worksheet.php for a slightly different purpose. |
||
| 1066 | * |
||
| 1067 | * @param int $cxals Number of external references |
||
| 1068 | */ |
||
| 1069 | private function writeExternalCount($cxals) |
||
| 1070 | { |
||
| 1071 | $record = 0x0016; // Record identifier |
||
| 1072 | $length = 0x0002; // Number of bytes to follow |
||
| 1073 | |||
| 1074 | $header = pack('vv', $record, $length); |
||
| 1075 | $data = pack('v', $cxals); |
||
| 1076 | $this->append($header . $data); |
||
| 1077 | } |
||
| 1078 | |||
| 1079 | /** |
||
| 1080 | * Writes the Excel BIFF EXTERNSHEET record. These references are used by |
||
| 1081 | * formulas. NAME record is required to define the print area and the repeat |
||
| 1082 | * rows and columns. |
||
| 1083 | * |
||
| 1084 | * A similar method is used in Worksheet.php for a slightly different purpose. |
||
| 1085 | * |
||
| 1086 | * @param string $sheetname Worksheet name |
||
| 1087 | */ |
||
| 1088 | View Code Duplication | private function writeExternalSheet($sheetname) |
|
| 1089 | { |
||
| 1090 | $record = 0x0017; // Record identifier |
||
| 1091 | $length = 0x02 + strlen($sheetname); // Number of bytes to follow |
||
| 1092 | |||
| 1093 | $cch = strlen($sheetname); // Length of sheet name |
||
| 1094 | $rgch = 0x03; // Filename encoding |
||
| 1095 | |||
| 1096 | $header = pack('vv', $record, $length); |
||
| 1097 | $data = pack('CC', $cch, $rgch); |
||
| 1098 | $this->append($header . $data . $sheetname); |
||
| 1099 | } |
||
| 1100 | |||
| 1101 | /** |
||
| 1102 | * Store the NAME record in the short format that is used for storing the print |
||
| 1103 | * area, repeat rows only and repeat columns only. |
||
| 1104 | * |
||
| 1105 | * @param int $index Sheet index |
||
| 1106 | * @param int $type Built-in name type |
||
| 1107 | * @param int $rowmin Start row |
||
| 1108 | * @param int $rowmax End row |
||
| 1109 | * @param int $colmin Start colum |
||
| 1110 | * @param int $colmax End column |
||
| 1111 | */ |
||
| 1112 | private function writeNameShort($index, $type, $rowmin, $rowmax, $colmin, $colmax) |
||
| 1113 | { |
||
| 1114 | $record = 0x0018; // Record identifier |
||
| 1115 | $length = 0x0024; // Number of bytes to follow |
||
| 1116 | |||
| 1117 | $grbit = 0x0020; // Option flags |
||
| 1118 | $chKey = 0x00; // Keyboard shortcut |
||
| 1119 | $cch = 0x01; // Length of text name |
||
| 1120 | $cce = 0x0015; // Length of text definition |
||
| 1121 | $ixals = $index + 1; // Sheet index |
||
| 1122 | $itab = $ixals; // Equal to ixals |
||
| 1123 | $cchCustMenu = 0x00; // Length of cust menu text |
||
| 1124 | $cchDescription = 0x00; // Length of description text |
||
| 1125 | $cchHelptopic = 0x00; // Length of help topic text |
||
| 1126 | $cchStatustext = 0x00; // Length of status bar text |
||
| 1127 | $rgch = $type; // Built-in name type |
||
| 1128 | |||
| 1129 | $unknown03 = 0x3b; |
||
| 1130 | $unknown04 = 0xffff - $index; |
||
| 1131 | $unknown05 = 0x0000; |
||
| 1132 | $unknown06 = 0x0000; |
||
| 1133 | $unknown07 = 0x1087; |
||
| 1134 | $unknown08 = 0x8005; |
||
| 1135 | |||
| 1136 | $header = pack('vv', $record, $length); |
||
| 1137 | $data = pack('v', $grbit); |
||
| 1138 | $data .= pack('C', $chKey); |
||
| 1139 | $data .= pack('C', $cch); |
||
| 1140 | $data .= pack('v', $cce); |
||
| 1141 | $data .= pack('v', $ixals); |
||
| 1142 | $data .= pack('v', $itab); |
||
| 1143 | $data .= pack('C', $cchCustMenu); |
||
| 1144 | $data .= pack('C', $cchDescription); |
||
| 1145 | $data .= pack('C', $cchHelptopic); |
||
| 1146 | $data .= pack('C', $cchStatustext); |
||
| 1147 | $data .= pack('C', $rgch); |
||
| 1148 | $data .= pack('C', $unknown03); |
||
| 1149 | $data .= pack('v', $unknown04); |
||
| 1150 | $data .= pack('v', $unknown05); |
||
| 1151 | $data .= pack('v', $unknown06); |
||
| 1152 | $data .= pack('v', $unknown07); |
||
| 1153 | $data .= pack('v', $unknown08); |
||
| 1154 | $data .= pack('v', $index); |
||
| 1155 | $data .= pack('v', $index); |
||
| 1156 | $data .= pack('v', $rowmin); |
||
| 1157 | $data .= pack('v', $rowmax); |
||
| 1158 | $data .= pack('C', $colmin); |
||
| 1159 | $data .= pack('C', $colmax); |
||
| 1160 | $this->append($header . $data); |
||
| 1161 | } |
||
| 1162 | |||
| 1163 | /** |
||
| 1164 | * Store the NAME record in the long format that is used for storing the repeat |
||
| 1165 | * rows and columns when both are specified. This shares a lot of code with |
||
| 1166 | * writeNameShort() but we use a separate method to keep the code clean. |
||
| 1167 | * Code abstraction for reuse can be carried too far, and I should know. ;-). |
||
| 1168 | * |
||
| 1169 | * @param int $index Sheet index |
||
| 1170 | * @param int $type Built-in name type |
||
| 1171 | * @param int $rowmin Start row |
||
| 1172 | * @param int $rowmax End row |
||
| 1173 | * @param int $colmin Start colum |
||
| 1174 | * @param int $colmax End column |
||
| 1175 | */ |
||
| 1176 | private function writeNameLong($index, $type, $rowmin, $rowmax, $colmin, $colmax) |
||
| 1177 | { |
||
| 1178 | $record = 0x0018; // Record identifier |
||
| 1179 | $length = 0x003d; // Number of bytes to follow |
||
| 1180 | $grbit = 0x0020; // Option flags |
||
| 1181 | $chKey = 0x00; // Keyboard shortcut |
||
| 1182 | $cch = 0x01; // Length of text name |
||
| 1183 | $cce = 0x002e; // Length of text definition |
||
| 1184 | $ixals = $index + 1; // Sheet index |
||
| 1185 | $itab = $ixals; // Equal to ixals |
||
| 1186 | $cchCustMenu = 0x00; // Length of cust menu text |
||
| 1187 | $cchDescription = 0x00; // Length of description text |
||
| 1188 | $cchHelptopic = 0x00; // Length of help topic text |
||
| 1189 | $cchStatustext = 0x00; // Length of status bar text |
||
| 1190 | $rgch = $type; // Built-in name type |
||
| 1191 | |||
| 1192 | $unknown01 = 0x29; |
||
| 1193 | $unknown02 = 0x002b; |
||
| 1194 | $unknown03 = 0x3b; |
||
| 1195 | $unknown04 = 0xffff - $index; |
||
| 1196 | $unknown05 = 0x0000; |
||
| 1197 | $unknown06 = 0x0000; |
||
| 1198 | $unknown07 = 0x1087; |
||
| 1199 | $unknown08 = 0x8008; |
||
| 1200 | |||
| 1201 | $header = pack('vv', $record, $length); |
||
| 1202 | $data = pack('v', $grbit); |
||
| 1203 | $data .= pack('C', $chKey); |
||
| 1204 | $data .= pack('C', $cch); |
||
| 1205 | $data .= pack('v', $cce); |
||
| 1206 | $data .= pack('v', $ixals); |
||
| 1207 | $data .= pack('v', $itab); |
||
| 1208 | $data .= pack('C', $cchCustMenu); |
||
| 1209 | $data .= pack('C', $cchDescription); |
||
| 1210 | $data .= pack('C', $cchHelptopic); |
||
| 1211 | $data .= pack('C', $cchStatustext); |
||
| 1212 | $data .= pack('C', $rgch); |
||
| 1213 | $data .= pack('C', $unknown01); |
||
| 1214 | $data .= pack('v', $unknown02); |
||
| 1215 | // Column definition |
||
| 1216 | $data .= pack('C', $unknown03); |
||
| 1217 | $data .= pack('v', $unknown04); |
||
| 1218 | $data .= pack('v', $unknown05); |
||
| 1219 | $data .= pack('v', $unknown06); |
||
| 1220 | $data .= pack('v', $unknown07); |
||
| 1221 | $data .= pack('v', $unknown08); |
||
| 1222 | $data .= pack('v', $index); |
||
| 1223 | $data .= pack('v', $index); |
||
| 1224 | $data .= pack('v', 0x0000); |
||
| 1225 | $data .= pack('v', 0x3fff); |
||
| 1226 | $data .= pack('C', $colmin); |
||
| 1227 | $data .= pack('C', $colmax); |
||
| 1228 | // Row definition |
||
| 1229 | $data .= pack('C', $unknown03); |
||
| 1230 | $data .= pack('v', $unknown04); |
||
| 1231 | $data .= pack('v', $unknown05); |
||
| 1232 | $data .= pack('v', $unknown06); |
||
| 1233 | $data .= pack('v', $unknown07); |
||
| 1234 | $data .= pack('v', $unknown08); |
||
| 1235 | $data .= pack('v', $index); |
||
| 1236 | $data .= pack('v', $index); |
||
| 1237 | $data .= pack('v', $rowmin); |
||
| 1238 | $data .= pack('v', $rowmax); |
||
| 1239 | $data .= pack('C', 0x00); |
||
| 1240 | $data .= pack('C', 0xff); |
||
| 1241 | // End of data |
||
| 1242 | $data .= pack('C', 0x10); |
||
| 1243 | $this->append($header . $data); |
||
| 1244 | } |
||
| 1245 | |||
| 1246 | /** |
||
| 1247 | * Stores the COUNTRY record for localization. |
||
| 1248 | * |
||
| 1249 | * @return string |
||
| 1250 | */ |
||
| 1251 | View Code Duplication | private function writeCountry() |
|
| 1252 | { |
||
| 1253 | $record = 0x008C; // Record identifier |
||
| 1254 | $length = 4; // Number of bytes to follow |
||
| 1255 | |||
| 1256 | $header = pack('vv', $record, $length); |
||
| 1257 | /* using the same country code always for simplicity */ |
||
| 1258 | $data = pack('vv', $this->countryCode, $this->countryCode); |
||
| 1259 | |||
| 1260 | return $this->writeData($header . $data); |
||
| 1261 | } |
||
| 1262 | |||
| 1263 | /** |
||
| 1264 | * Write the RECALCID record. |
||
| 1265 | * |
||
| 1266 | * @return string |
||
| 1267 | */ |
||
| 1268 | 39 | View Code Duplication | private function writeRecalcId() |
| 1269 | { |
||
| 1270 | 39 | $record = 0x01C1; // Record identifier |
|
| 1271 | 39 | $length = 8; // Number of bytes to follow |
|
| 1272 | |||
| 1273 | 39 | $header = pack('vv', $record, $length); |
|
| 1274 | |||
| 1275 | // by inspection of real Excel files, MS Office Excel 2007 writes this |
||
| 1276 | 39 | $data = pack('VV', 0x000001C1, 0x00001E667); |
|
| 1277 | |||
| 1278 | 39 | return $this->writeData($header . $data); |
|
| 1279 | } |
||
| 1280 | |||
| 1281 | /** |
||
| 1282 | * Stores the PALETTE biff record. |
||
| 1283 | */ |
||
| 1284 | 39 | private function writePalette() |
|
| 1285 | { |
||
| 1286 | 39 | $aref = $this->palette; |
|
| 1287 | |||
| 1288 | 39 | $record = 0x0092; // Record identifier |
|
| 1289 | 39 | $length = 2 + 4 * count($aref); // Number of bytes to follow |
|
| 1290 | 39 | $ccv = count($aref); // Number of RGB values to follow |
|
| 1291 | 39 | $data = ''; // The RGB data |
|
| 1292 | |||
| 1293 | // Pack the RGB data |
||
| 1294 | 39 | foreach ($aref as $color) { |
|
| 1295 | 39 | foreach ($color as $byte) { |
|
| 1296 | 39 | $data .= pack('C', $byte); |
|
| 1297 | } |
||
| 1298 | } |
||
| 1299 | |||
| 1300 | 39 | $header = pack('vvv', $record, $length, $ccv); |
|
| 1301 | 39 | $this->append($header . $data); |
|
| 1302 | 39 | } |
|
| 1303 | |||
| 1304 | /** |
||
| 1305 | * Handling of the SST continue blocks is complicated by the need to include an |
||
| 1306 | * additional continuation byte depending on whether the string is split between |
||
| 1307 | * blocks or whether it starts at the beginning of the block. (There are also |
||
| 1308 | * additional complications that will arise later when/if Rich Strings are |
||
| 1309 | * supported). |
||
| 1310 | * |
||
| 1311 | * The Excel documentation says that the SST record should be followed by an |
||
| 1312 | * EXTSST record. The EXTSST record is a hash table that is used to optimise |
||
| 1313 | * access to SST. However, despite the documentation it doesn't seem to be |
||
| 1314 | * required so we will ignore it. |
||
| 1315 | * |
||
| 1316 | * @return string Binary data |
||
| 1317 | */ |
||
| 1318 | 39 | private function writeSharedStringsTable() |
|
| 1428 | |||
| 1429 | /** |
||
| 1430 | * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records. |
||
| 1431 | */ |
||
| 1432 | 39 | private function writeMsoDrawingGroup() |
|
| 1433 | { |
||
| 1434 | // write the Escher stream if necessary |
||
| 1435 | 39 | if (isset($this->escher)) { |
|
| 1436 | 10 | $writer = new Escher($this->escher); |
|
| 1437 | 10 | $data = $writer->close(); |
|
| 1438 | |||
| 1439 | 10 | $record = 0x00EB; |
|
| 1440 | 10 | $length = strlen($data); |
|
| 1441 | 10 | $header = pack('vv', $record, $length); |
|
| 1442 | |||
| 1443 | 10 | return $this->writeData($header . $data); |
|
| 1444 | } |
||
| 1445 | |||
| 1446 | 29 | return ''; |
|
| 1447 | } |
||
| 1448 | |||
| 1449 | /** |
||
| 1450 | * Get Escher object. |
||
| 1451 | * |
||
| 1452 | * @return \PhpOffice\PhpSpreadsheet\Shared\Escher |
||
| 1453 | */ |
||
| 1454 | public function getEscher() |
||
| 1455 | { |
||
| 1456 | return $this->escher; |
||
| 1457 | } |
||
| 1458 | |||
| 1459 | /** |
||
| 1460 | * Set Escher object. |
||
| 1461 | * |
||
| 1462 | * @param \PhpOffice\PhpSpreadsheet\Shared\Escher $pValue |
||
| 1463 | */ |
||
| 1464 | 10 | public function setEscher(\PhpOffice\PhpSpreadsheet\Shared\Escher $pValue = null) |
|
| 1468 | } |
||
| 1469 |
Loading history...
