189 parent::__construct();
194 $this->countryCode = -1;
196 $this->stringTotal = &$str_total;
197 $this->stringUnique = &$str_unique;
198 $this->stringTable = &$str_table;
204 $this->codepage = 0x04B0;
208 for (
$i = 0;
$i < $countSheets; ++
$i) {
211 $this->parser->setExtSheet($phpSheet->getTitle(),
$i);
213 $supbook_index = 0x00;
214 $ref = pack(
'vvv', $supbook_index,
$i,
$i);
215 $this->parser->references[] = $ref;
218 if ($phpSheet->isTabColorSet()) {
219 $this->
addColor($phpSheet->getTabColor()->getRGB());
234 $xfWriter->setIsStyleXf($isStyleXf);
240 $xfWriter->setFontIndex($fontIndex);
243 $xfWriter->setFgColor($this->
addColor($style->
getFill()->getStartColor()->getRGB()));
244 $xfWriter->setBgColor($this->
addColor($style->
getFill()->getEndColor()->getRGB()));
245 $xfWriter->setBottomColor($this->
addColor($style->
getBorders()->getBottom()->getColor()->getRGB()));
246 $xfWriter->setTopColor($this->
addColor($style->
getBorders()->getTop()->getColor()->getRGB()));
247 $xfWriter->setRightColor($this->
addColor($style->
getBorders()->getRight()->getColor()->getRGB()));
248 $xfWriter->setLeftColor($this->
addColor($style->
getBorders()->getLeft()->getColor()->getRGB()));
249 $xfWriter->setDiagColor($this->
addColor($style->
getBorders()->getDiagonal()->getColor()->getRGB()));
252 if (
$style->getNumberFormat()->getBuiltInFormatCode() ===
false) {
253 $numberFormatHashCode =
$style->getNumberFormat()->getHashCode();
255 if (isset($this->addedNumberFormats[$numberFormatHashCode])) {
256 $numberFormatIndex = $this->addedNumberFormats[$numberFormatHashCode];
258 $numberFormatIndex = 164 + count($this->numberFormats);
259 $this->numberFormats[$numberFormatIndex] =
$style->getNumberFormat();
260 $this->addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex;
263 $numberFormatIndex = (int)
$style->getNumberFormat()->getBuiltInFormatCode();
267 $xfWriter->setNumberFormatIndex($numberFormatIndex);
269 $this->xfWriters[] = $xfWriter;
271 return count($this->xfWriters) - 1;
281 $fontHashCode = $font->getHashCode();
282 if (isset($this->addedFonts[$fontHashCode])) {
283 $fontIndex = $this->addedFonts[$fontHashCode];
285 $countFonts = count($this->fontWriters);
286 $fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1;
288 $fontWriter =
new Font($font);
289 $fontWriter->setColorIndex($this->
addColor($font->getColor()->getRGB()));
290 $this->fontWriters[] = $fontWriter;
292 $this->addedFonts[$fontHashCode] = $fontIndex;
307 if (!isset($this->colors[$rgb])) {
310 hexdec(substr($rgb, 0, 2)),
311 hexdec(substr($rgb, 2, 2)),
312 hexdec(substr($rgb, 4)),
315 $colorIndex = array_search($color, $this->palette);
317 $this->colors[$rgb] = $colorIndex;
319 if (count($this->colors) === 0) {
322 $lastColor = end($this->colors);
324 if ($lastColor < 57) {
326 $colorIndex = $lastColor + 1;
327 $this->palette[$colorIndex] = $color;
328 $this->colors[$rgb] = $colorIndex;
336 $colorIndex = $this->colors[$rgb];
348 0x08 => [0x00, 0x00, 0x00, 0x00],
349 0x09 => [0xff, 0xff, 0xff, 0x00],
350 0x0A => [0xff, 0x00, 0x00, 0x00],
351 0x0B => [0x00, 0xff, 0x00, 0x00],
352 0x0C => [0x00, 0x00, 0xff, 0x00],
353 0x0D => [0xff, 0xff, 0x00, 0x00],
354 0x0E => [0xff, 0x00, 0xff, 0x00],
355 0x0F => [0x00, 0xff, 0xff, 0x00],
356 0x10 => [0x80, 0x00, 0x00, 0x00],
357 0x11 => [0x00, 0x80, 0x00, 0x00],
358 0x12 => [0x00, 0x00, 0x80, 0x00],
359 0x13 => [0x80, 0x80, 0x00, 0x00],
360 0x14 => [0x80, 0x00, 0x80, 0x00],
361 0x15 => [0x00, 0x80, 0x80, 0x00],
362 0x16 => [0xc0, 0xc0, 0xc0, 0x00],
363 0x17 => [0x80, 0x80, 0x80, 0x00],
364 0x18 => [0x99, 0x99, 0xff, 0x00],
365 0x19 => [0x99, 0x33, 0x66, 0x00],
366 0x1A => [0xff, 0xff, 0xcc, 0x00],
367 0x1B => [0xcc, 0xff, 0xff, 0x00],
368 0x1C => [0x66, 0x00, 0x66, 0x00],
369 0x1D => [0xff, 0x80, 0x80, 0x00],
370 0x1E => [0x00, 0x66, 0xcc, 0x00],
371 0x1F => [0xcc, 0xcc, 0xff, 0x00],
372 0x20 => [0x00, 0x00, 0x80, 0x00],
373 0x21 => [0xff, 0x00, 0xff, 0x00],
374 0x22 => [0xff, 0xff, 0x00, 0x00],
375 0x23 => [0x00, 0xff, 0xff, 0x00],
376 0x24 => [0x80, 0x00, 0x80, 0x00],
377 0x25 => [0x80, 0x00, 0x00, 0x00],
378 0x26 => [0x00, 0x80, 0x80, 0x00],
379 0x27 => [0x00, 0x00, 0xff, 0x00],
380 0x28 => [0x00, 0xcc, 0xff, 0x00],
381 0x29 => [0xcc, 0xff, 0xff, 0x00],
382 0x2A => [0xcc, 0xff, 0xcc, 0x00],
383 0x2B => [0xff, 0xff, 0x99, 0x00],
384 0x2C => [0x99, 0xcc, 0xff, 0x00],
385 0x2D => [0xff, 0x99, 0xcc, 0x00],
386 0x2E => [0xcc, 0x99, 0xff, 0x00],
387 0x2F => [0xff, 0xcc, 0x99, 0x00],
388 0x30 => [0x33, 0x66, 0xff, 0x00],
389 0x31 => [0x33, 0xcc, 0xcc, 0x00],
390 0x32 => [0x99, 0xcc, 0x00, 0x00],
391 0x33 => [0xff, 0xcc, 0x00, 0x00],
392 0x34 => [0xff, 0x99, 0x00, 0x00],
393 0x35 => [0xff, 0x66, 0x00, 0x00],
394 0x36 => [0x66, 0x66, 0x99, 0x00],
395 0x37 => [0x96, 0x96, 0x96, 0x00],
396 0x38 => [0x00, 0x33, 0x66, 0x00],
397 0x39 => [0x33, 0x99, 0x66, 0x00],
398 0x3A => [0x00, 0x33, 0x00, 0x00],
399 0x3B => [0x33, 0x33, 0x00, 0x00],
400 0x3C => [0x99, 0x33, 0x00, 0x00],
401 0x3D => [0x99, 0x33, 0x66, 0x00],
402 0x3E => [0x33, 0x33, 0x99, 0x00],
403 0x3F => [0x33, 0x33, 0x33, 0x00],
417 $this->worksheetSizes = $pWorksheetSizes;
421 $total_worksheets = $this->spreadsheet->getSheetCount();
437 if ($this->countryCode !== -1) {
454 for (
$i = 0;
$i < $total_worksheets; ++
$i) {
459 $this->_data .= $part3;
469 $boundsheet_length = 10;
475 $total_worksheets = count($this->spreadsheet->getAllSheets());
476 foreach ($this->spreadsheet->getWorksheetIterator() as $sheet) {
481 for (
$i = 0;
$i < $total_worksheets; ++
$i) {
482 $this->worksheetOffsets[
$i] = $offset;
483 $offset += $this->worksheetSizes[
$i];
485 $this->biffSize = $offset;
493 foreach ($this->fontWriters as $fontWriter) {
494 $this->
append($fontWriter->writeFont());
503 foreach ($this->numberFormats as $numberFormatIndex => $numberFormat) {
513 foreach ($this->xfWriters as $xfWriter) {
514 $this->
append($xfWriter->writeXf());
528 $definedRange = $pDefinedName->
getValue();
529 $splitCount = preg_match_all(
536 $lengths = array_map(
'strlen', array_column($splitRanges[0], 0));
537 $offsets = array_column($splitRanges[0], 1);
539 $worksheets = $splitRanges[2];
541 $rows = $splitRanges[7];
543 while ($splitCount > 0) {
545 $length = $lengths[$splitCount];
546 $offset = $offsets[$splitCount];
547 $worksheet = $worksheets[$splitCount][0];
552 if (empty($worksheet)) {
553 if (($offset === 0) || ($definedRange[$offset - 1] !==
':')) {
558 $worksheet = str_replace(
"''",
"'", trim($worksheet,
"'"));
560 if (!empty($worksheet)) {
561 $newRange =
"'" . str_replace(
"'",
"''", $worksheet) .
"'!";
564 if (!empty($column)) {
565 $newRange .=
"\${$column}";
568 $newRange .=
"\${$row}";
571 $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
574 return $definedRange;
586 $definedNames = $this->spreadsheet->getDefinedNames();
587 if (count($definedNames) > 0) {
589 foreach ($definedNames as $definedName) {
594 $error = $this->parser->parse($range);
595 $formulaData = $this->parser->toReversePolish();
598 if (isset($formulaData[0]) && ($formulaData[0] ==
"\x7A" || $formulaData[0] ==
"\x5A")) {
599 $formulaData =
"\x3A" . substr($formulaData, 1);
602 if ($definedName->getLocalOnly()) {
604 $scope = $this->spreadsheet->getIndex($definedName->getScope()) + 1;
610 }
catch (PhpSpreadsheetException $e) {
617 $total_worksheets = $this->spreadsheet->getSheetCount();
620 for (
$i = 0;
$i < $total_worksheets; ++
$i) {
621 $sheetSetup = $this->spreadsheet->getSheet(
$i)->getPageSetup();
623 if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
624 $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
628 $repeat = $sheetSetup->getRowsToRepeatAtTop();
633 $formulaData = pack(
'Cv', 0x29, 0x17);
634 $formulaData .= pack(
'Cvvvvv', 0x3B,
$i, 0, 65535, $colmin, $colmax);
635 $formulaData .= pack(
'Cvvvvv', 0x3B,
$i, $rowmin, $rowmax, 0, 255);
636 $formulaData .= pack(
'C', 0x10);
642 } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
644 if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
645 $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
653 if ($sheetSetup->isRowsToRepeatAtTopSet()) {
654 $repeat = $sheetSetup->getRowsToRepeatAtTop();
663 $formulaData = pack(
'Cvvvvv', 0x3B,
$i, $rowmin, $rowmax, $colmin, $colmax);
671 for (
$i = 0;
$i < $total_worksheets; ++
$i) {
672 $sheetSetup = $this->spreadsheet->getSheet(
$i)->getPageSetup();
673 if ($sheetSetup->isPrintAreaSet()) {
676 $countPrintArea = count($printArea);
679 for ($j = 0; $j < $countPrintArea; ++$j) {
680 $printAreaRect = $printArea[$j];
684 $print_rowmin = $printAreaRect[0][1] - 1;
685 $print_rowmax = $printAreaRect[1][1] - 1;
686 $print_colmin = $printAreaRect[0][0] - 1;
687 $print_colmax = $printAreaRect[1][0] - 1;
690 $formulaData .= pack(
'Cvvvvv', 0x3B,
$i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
693 $formulaData .= pack(
'C', 0x10);
703 for (
$i = 0;
$i < $total_worksheets; ++
$i) {
704 $sheetAutoFilter = $this->spreadsheet->getSheet(
$i)->getAutoFilter();
705 $autoFilterRange = $sheetAutoFilter->getRange();
706 if (!empty($autoFilterRange)) {
710 $name = pack(
'C', 0x0D);
734 $options = $isBuiltIn ? 0x20 : 0x00;
743 $sz = strlen($formulaData);
746 $data = pack(
'vCCvvvCCCC',
$options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0)
747 .
$name . $formulaData;
748 $length = strlen(
$data);
750 $header = pack(
'vv', $record, $length);
770 $options = ($isHidden ? 0x21 : 0x00);
776 $rangeBounds[0][1] - 1,
777 $rangeBounds[1][1] - 1,
778 $rangeBounds[0][0] - 1,
779 $rangeBounds[1][0] - 1
783 $sz = strlen($extra);
786 $data = pack(
'vCCvvvCCCCC',
$options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0)
788 $length = strlen(
$data);
790 $header = pack(
'vv', $record, $length);
804 $header = pack(
'vv', $record, $length);
805 $data = pack(
'v', $cv);
832 $itabCur = $this->spreadsheet->getActiveSheetIndex();
834 $header = pack(
'vv', $record, $length);
835 $data = pack(
'vvvvvvvvv', $xWn, $yWn, $dxWn, $dyWn, $grbit, $itabCur, $itabFirst, $ctabsel, $wTabRatio);
846 $sheetname = $sheet->getTitle();
850 switch ($sheet->getSheetState()) {
851 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE:
855 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN:
859 case \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN:
874 $data = pack(
'VCC', $offset, $ss, $st);
877 $length = strlen(
$data);
878 $header = pack(
'vv', $record, $length);
890 $header = pack(
'vv', $record, $length);
891 $data = pack(
'vv', $this->spreadsheet->getSheetCount(), 0x0401);
902 $totalReferences = count($this->parser->references);
904 $length = 2 + 6 * $totalReferences;
907 $header = pack(
'vv', $record, $length);
908 $data = pack(
'v', $totalReferences);
909 for (
$i = 0;
$i < $totalReferences; ++
$i) {
910 $data .= $this->parser->references[
$i];
928 $header = pack(
'vv', $record, $length);
929 $data = pack(
'vCC', $ixfe, $BuiltIn, $iLevel);
944 $length = 2 + strlen($numberFormatString);
946 $header = pack(
'vv', $record, $length);
947 $data = pack(
'v', $ifmt) . $numberFormatString;
963 $header = pack(
'vv', $record, $length);
964 $data = pack(
'v', $f1904);
978 $header = pack(
'vv', $record, $length);
980 $data = pack(
'vv', $this->countryCode, $this->countryCode);
995 $header = pack(
'vv', $record, $length);
998 $data = pack(
'VV', 0x000001C1, 0x00001E667);
1011 $length = 2 + 4 * count($aref);
1012 $ccv = count($aref);
1016 foreach ($aref as $color) {
1017 foreach ($color as $byte) {
1018 $data .= pack(
'C', $byte);
1022 $header = pack(
'vvv', $record, $length, $ccv);
1043 $continue_limit = 8224;
1049 $recordData = pack(
'VV', $this->stringTotal, $this->stringUnique);
1052 foreach (array_keys($this->stringTable) as $string) {
1056 $headerinfo = unpack(
'vlength/Cencoding', $string);
1059 $encoding = $headerinfo[
'encoding'];
1064 while ($finished ===
false) {
1069 if (strlen($recordData) + strlen($string) <= $continue_limit) {
1071 $recordData .= $string;
1073 if (strlen($recordData) + strlen($string) == $continue_limit) {
1075 $recordDatas[] = $recordData;
1086 $space_remaining = $continue_limit - strlen($recordData);
1091 $min_space_needed = ($encoding == 1) ? 5 : 4;
1100 if ($space_remaining < $min_space_needed) {
1102 $recordDatas[] = $recordData;
1110 $effective_space_remaining = $space_remaining;
1113 if ($encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1) {
1114 --$effective_space_remaining;
1118 $recordData .= substr($string, 0, $effective_space_remaining);
1120 $string = substr($string, $effective_space_remaining);
1121 $recordDatas[] = $recordData;
1124 $recordData = pack(
'C', $encoding);
1132 if (strlen($recordData) > 0) {
1133 $recordDatas[] = $recordData;
1138 foreach ($recordDatas as
$i => $recordData) {
1140 $record = (
$i == 0) ? 0x00FC : 0x003C;
1142 $header = pack(
'vv', $record, strlen($recordData));
1157 if (isset($this->escher)) {
1158 $writer =
new Escher($this->escher);
1159 $data = $writer->close();
1162 $length = strlen(
$data);
1163 $header = pack(
'vv', $record, $length);
1188 $this->escher = $pValue;
An exception for terminatinating execution or to throw for unit testing.
const CALCULATION_REGEXP_CELLREF
Helper class to manipulate cell coordinates.
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
static columnIndexFromString($pString)
Column index from string.
static splitRange($pRange)
Split range into coordinate strings.
static rangeBoundaries($pRange)
Calculate range boundaries.
getValue()
Get range or formula value.
getWorksheet()
Get worksheet.
static getExcelCalendar()
Return the Excel calendar (Windows 1900 or Mac 1904).
static countCharacters($value, $enc='UTF-8')
Get character count.
static UTF8toBIFF8UnicodeShort($value, $arrcRuns=[])
Converts a UTF-8 string into BIFF8 Unicode string data (8-bit string length) Writes the string using ...
static UTF8toBIFF8UnicodeLong($value)
Converts a UTF-8 string into BIFF8 Unicode string data (16-bit string length) Writes the string using...
storeBof($type)
Writes Excel BOF record to indicate the beginning of a stream or sub-stream in the BIFF file.
writeEof()
Writes Excel EOF record to indicate the end of a BIFF stream.
append($data)
General storage function.
writeData($data)
General storage function like append, but returns string instead of modifying $this->_data.
writeCodepage()
Stores the CODEPAGE biff record.
addColor($rgb)
Alter color palette adding a custom color.
addXfWriter(Style $style, $isStyleXf=false)
Add a new XF writer.
writeAllNumberFormats()
Store user defined numerical formats i.e.
writeMsoDrawingGroup()
Writes the MSODRAWINGGROUP record if needed.
writeAllFonts()
Store the Excel FONT records.
writeAllStyles()
Write all STYLE records.
writeRecalcId()
Write the RECALCID record.
writeBoundSheet(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, $offset)
Writes Excel BIFF BOUNDSHEET record.
calcSheetOffsets()
Calculate offsets for Worksheet BOF records.
writeAllDefinedNamesBiff8()
Writes all the DEFINEDNAME records (BIFF8).
parseDefinedNameValue(DefinedName $pDefinedName)
writeExternalsheetBiff8()
Writes the Excel BIFF EXTERNSHEET record.
writeNumberFormat($format, $ifmt)
Writes Excel FORMAT record for non "built-in" numerical formats.
writeDefinedNameBiff8($name, $formulaData, $sheetIndex=0, $isBuiltIn=false)
Write a DEFINEDNAME record for BIFF8 using explicit binary formula data.
setPaletteXl97()
Sets the colour palette to the Excel 97+ default.
addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font)
Add a font to added fonts.
writeAllXfs()
Write all XF records.
writeCountry()
Stores the COUNTRY record for localization.
writeDateMode()
Write DATEMODE record to indicate the date system in use (1904 or 1900).
writeWorkbook(array $pWorksheetSizes)
Assemble worksheets into a workbook and send the BIFF data to an OLE storage.
writeWindow1()
Write Excel BIFF WINDOW1 record.
writeSharedStringsTable()
Handling of the SST continue blocks is complicated by the need to include an additional continuation ...
writePalette()
Stores the PALETTE biff record.
getEscher()
Get Escher object.
writeStyle()
Write Excel BIFF STYLE records.
writeShortNameBiff8($name, $sheetIndex, $rangeBounds, $isHidden=false)
Write a short NAME record.
__construct(Spreadsheet $spreadsheet, &$str_total, &$str_unique, &$str_table, &$colors, Parser $parser)
Class constructor.
setEscher(?\PhpOffice\PhpSpreadsheet\Shared\Escher $pValue=null)
Set Escher object.
writeSupbookInternal()
Write Internal SUPBOOK record.