DefinedNames::writeNamedRangeForPrintTitles() - Code Metrics - Inspection of "Use Composer/Pcre Part 1 of Many" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#4323)

by Owen

created 2025-01-21 23:13 UTC

DefinedNames::writeNamedRangeForPrintTitles() A

↳ Parent: DefinedNames

Complexity

Conditions	6
Paths	7

Size

Total Lines	32
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	13
CRAP Score	6.2373

Importance

Changes

Metric	Value
cc	6
eloc	15
nc	7
nop	2
dl	0
loc	32
rs	9.2222
c	0
b	0
f	0
ccs	13
cts	16
cp	0.8125
crap	6.2373

<?php

namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use Composer\Pcre\Preg;
use Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\DefinedName;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as ActualWorksheet;

class DefinedNames
{
    private XMLWriter $objWriter;

    private Spreadsheet $spreadsheet;

    public function __construct(XMLWriter $objWriter, Spreadsheet $spreadsheet)
    {
        $this->objWriter = $objWriter;
        $this->spreadsheet = $spreadsheet;
    }

    public function write(): void
    {
        // Write defined names
        $this->objWriter->startElement('definedNames');

        // Named ranges
        if (count($this->spreadsheet->getDefinedNames()) > 0) {
            // Named ranges
            $this->writeNamedRangesAndFormulae();
        }

        // Other defined names
        $sheetCount = $this->spreadsheet->getSheetCount();
        for ($i = 0; $i < $sheetCount; ++$i) {
            // NamedRange for autoFilter
            $this->writeNamedRangeForAutofilter($this->spreadsheet->getSheet($i), $i);

            // NamedRange for Print_Titles
            $this->writeNamedRangeForPrintTitles($this->spreadsheet->getSheet($i), $i);

            // NamedRange for Print_Area
            $this->writeNamedRangeForPrintArea($this->spreadsheet->getSheet($i), $i);
        }

        $this->objWriter->endElement();
    }

    /**
     * Write defined names.
     */
    private function writeNamedRangesAndFormulae(): void
    {
        // Loop named ranges
        $definedNames = $this->spreadsheet->getDefinedNames();
        foreach ($definedNames as $definedName) {
            $this->writeDefinedName($definedName);
        }
    }

    /**
     * Write Defined Name for named range.
     */
    private function writeDefinedName(DefinedName $definedName): void
    {
        // definedName for named range
        $local = -1;
        if ($definedName->getLocalOnly() && $definedName->getScope() !== null) {
            try {
                $local = $definedName->getScope()->getParentOrThrow()->getIndex($definedName->getScope());
            } catch (Exception) {
                // See issue 2266 - deleting sheet which contains
                //     defined names will cause Exception above.
                return;
            }
        }
        $this->objWriter->startElement('definedName');
        $this->objWriter->writeAttribute('name', $definedName->getName());
        if ($local >= 0) {
            $this->objWriter->writeAttribute(
                'localSheetId',
                "$local"
            );
        }

        $definedRange = $this->getDefinedRange($definedName);

        $this->objWriter->writeRawData($definedRange);

        $this->objWriter->endElement();
    }

    /**
     * Write Defined Name for autoFilter.
     */
    private function writeNamedRangeForAutofilter(ActualWorksheet $worksheet, int $worksheetId = 0): void
    {
        // NamedRange for autoFilter
        $autoFilterRange = $worksheet->getAutoFilter()->getRange();
        if (!empty($autoFilterRange)) {
            $this->objWriter->startElement('definedName');
            $this->objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
            $this->objWriter->writeAttribute('localSheetId', "$worksheetId");
            $this->objWriter->writeAttribute('hidden', '1');

            // Create absolute coordinate and write as raw text
            $range = Coordinate::splitRange($autoFilterRange);
            $range = $range[0];
            //    Strip any worksheet ref so we can make the cell ref absolute
            [, $range[0]] = ActualWorksheet::extractSheetTitle($range[0], true);

            $range[0] = Coordinate::absoluteCoordinate($range[0] ?? '');
            if (count($range) > 1) {
                $range[1] = Coordinate::absoluteCoordinate($range[1]);
            }
            $range = implode(':', $range);

            $this->objWriter->writeRawData('\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . $range);

            $this->objWriter->endElement();
        }
    }

    /**
     * Write Defined Name for PrintTitles.
     */
    private function writeNamedRangeForPrintTitles(ActualWorksheet $worksheet, int $worksheetId = 0): void
    {
        // NamedRange for PrintTitles
        if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
            $this->objWriter->startElement('definedName');
            $this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
            $this->objWriter->writeAttribute('localSheetId', "$worksheetId");

            // Setting string
            $settingString = '';

            // Columns to repeat
            if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                $repeat = $worksheet->getPageSetup()->getColumnsToRepeatAtLeft();

                $settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
            }

            // Rows to repeat
            if ($worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
                if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                    $settingString .= ',';
                }

                $repeat = $worksheet->getPageSetup()->getRowsToRepeatAtTop();

                $settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
            }

            $this->objWriter->writeRawData($settingString);

            $this->objWriter->endElement();
        }
    }

    /**
     * Write Defined Name for PrintTitles.
     */
    private function writeNamedRangeForPrintArea(ActualWorksheet $worksheet, int $worksheetId = 0): void
    {
        // NamedRange for PrintArea
        if ($worksheet->getPageSetup()->isPrintAreaSet()) {
            $this->objWriter->startElement('definedName');
            $this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
            $this->objWriter->writeAttribute('localSheetId', "$worksheetId");

            // Print area
            $printArea = Coordinate::splitRange($worksheet->getPageSetup()->getPrintArea());

            $chunks = [];
            foreach ($printArea as $printAreaRect) {
                $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
                $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
                $chunks[] = '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
            }

            $this->objWriter->writeRawData(implode(',', $chunks));

            $this->objWriter->endElement();
        }
    }

    private function getDefinedRange(DefinedName $definedName): string
    {
        $definedRange = $definedName->getValue();
        $splitCount = Preg::matchAllWithOffsets(
            '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
            $definedRange,
            $splitRanges
        );

        $lengths = array_map([StringHelper::class, 'strlenAllowNull'], array_column($splitRanges[0], 0));
        $offsets = array_column($splitRanges[0], 1);

        $worksheets = $splitRanges[2];
        $columns = $splitRanges[6];
        $rows = $splitRanges[7];

        while ($splitCount > 0) {
            --$splitCount;
            $length = $lengths[$splitCount];
            $offset = $offsets[$splitCount];
            $worksheet = $worksheets[$splitCount][0];
            $column = $columns[$splitCount][0];
            $row = $rows[$splitCount][0];

            $newRange = '';
            if (empty($worksheet)) {
                if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
                    // We should have a worksheet
                    $ws = $definedName->getWorksheet();
                    $worksheet = ($ws === null) ? null : $ws->getTitle();
                }
            } else {
                $worksheet = str_replace("''", "'", trim($worksheet, "'"));
            }

            if (!empty($worksheet)) {
                $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
            }
            $newRange = "{$newRange}{$column}{$row}";

            $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
        }

        if (str_starts_with($definedRange, '=')) {
            $definedRange = substr($definedRange, 1);
        }

        return $definedRange;
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
4
5		use Composer\Pcre\Preg;
6		use Exception;
7		use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8		use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
9		use PhpOffice\PhpSpreadsheet\DefinedName;
10		use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
11		use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
12		use PhpOffice\PhpSpreadsheet\Spreadsheet;
13		use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as ActualWorksheet;
14
15		class DefinedNames
16		{
17		private XMLWriter $objWriter;
18
19		private Spreadsheet $spreadsheet;
20
21	365	public function __construct(XMLWriter $objWriter, Spreadsheet $spreadsheet)
22		{
23	365	$this->objWriter = $objWriter;
24	365	$this->spreadsheet = $spreadsheet;
25		}
26
27	365	public function write(): void
28		{
29		// Write defined names
30	365	$this->objWriter->startElement('definedNames');
31
32		// Named ranges
33	365	if (count($this->spreadsheet->getDefinedNames()) > 0) {
34		// Named ranges
35	21	$this->writeNamedRangesAndFormulae();
36		}
37
38		// Other defined names
39	365	$sheetCount = $this->spreadsheet->getSheetCount();
40	365	for ($i = 0; $i < $sheetCount; ++$i) {
41		// NamedRange for autoFilter
42	365	$this->writeNamedRangeForAutofilter($this->spreadsheet->getSheet($i), $i);
43
44		// NamedRange for Print_Titles
45	365	$this->writeNamedRangeForPrintTitles($this->spreadsheet->getSheet($i), $i);
46
47		// NamedRange for Print_Area
48	365	$this->writeNamedRangeForPrintArea($this->spreadsheet->getSheet($i), $i);
49		}
50
51	365	$this->objWriter->endElement();
52		}
53
54		/**
55		* Write defined names.
56		*/
57	21	private function writeNamedRangesAndFormulae(): void
58		{
59		// Loop named ranges
60	21	$definedNames = $this->spreadsheet->getDefinedNames();
61	21	foreach ($definedNames as $definedName) {
62	21	$this->writeDefinedName($definedName);
63		}
64		}
65
66		/**
67		* Write Defined Name for named range.
68		*/
69	21	private function writeDefinedName(DefinedName $definedName): void
70		{
71		// definedName for named range
72	21	$local = -1;
73	21	if ($definedName->getLocalOnly() && $definedName->getScope() !== null) {
74		try {
75	3	$local = $definedName->getScope()->getParentOrThrow()->getIndex($definedName->getScope());
76	1	} catch (Exception) {
77		// See issue 2266 - deleting sheet which contains
78		// defined names will cause Exception above.
79	1	return;
80		}
81		}
82	20	$this->objWriter->startElement('definedName');
83	20	$this->objWriter->writeAttribute('name', $definedName->getName());
84	20	if ($local >= 0) {
85	2	$this->objWriter->writeAttribute(
86	2	'localSheetId',
87	2	"$local"
88	2	);
89		}
90
91	20	$definedRange = $this->getDefinedRange($definedName);
92
93	20	$this->objWriter->writeRawData($definedRange);
94
95	20	$this->objWriter->endElement();
96		}
97
98		/**
99		* Write Defined Name for autoFilter.
100		*/
101	365	private function writeNamedRangeForAutofilter(ActualWorksheet $worksheet, int $worksheetId = 0): void
102		{
103		// NamedRange for autoFilter
104	365	$autoFilterRange = $worksheet->getAutoFilter()->getRange();
105	365	if (!empty($autoFilterRange)) {
106	10	$this->objWriter->startElement('definedName');
107	10	$this->objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
108	10	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
109	10	$this->objWriter->writeAttribute('hidden', '1');
110
111		// Create absolute coordinate and write as raw text
112	10	$range = Coordinate::splitRange($autoFilterRange);
113	10	$range = $range[0];
114		// Strip any worksheet ref so we can make the cell ref absolute
115	10	[, $range[0]] = ActualWorksheet::extractSheetTitle($range[0], true);
116
117	10	$range[0] = Coordinate::absoluteCoordinate($range[0] ?? '');
118	10	if (count($range) > 1) {
119	10	$range[1] = Coordinate::absoluteCoordinate($range[1]);
120		}
121	10	$range = implode(':', $range);
122
123	10	$this->objWriter->writeRawData('\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . $range);
124
125	10	$this->objWriter->endElement();
126		}
127		}
128
129		/**
130		* Write Defined Name for PrintTitles.
131		*/
132	365	private function writeNamedRangeForPrintTitles(ActualWorksheet $worksheet, int $worksheetId = 0): void
133		{
134		// NamedRange for PrintTitles
135	365	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet() \|\| $worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
136	2	$this->objWriter->startElement('definedName');
137	2	$this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
138	2	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
139
140		// Setting string
141	2	$settingString = '';
142
143		// Columns to repeat
144	2	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
145		$repeat = $worksheet->getPageSetup()->getColumnsToRepeatAtLeft();
146
147		$settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
148		}
149
150		// Rows to repeat
151	2	if ($worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
152	2	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
153		$settingString .= ',';
154		}
155
156	2	$repeat = $worksheet->getPageSetup()->getRowsToRepeatAtTop();
157
158	2	$settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
159		}
160
161	2	$this->objWriter->writeRawData($settingString);
162
163	2	$this->objWriter->endElement();
164		}
165		}
166
167		/**
168		* Write Defined Name for PrintTitles.
169		*/
170	365	private function writeNamedRangeForPrintArea(ActualWorksheet $worksheet, int $worksheetId = 0): void
171		{
172		// NamedRange for PrintArea
173	365	if ($worksheet->getPageSetup()->isPrintAreaSet()) {
174	5	$this->objWriter->startElement('definedName');
175	5	$this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
176	5	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
177
178		// Print area
179	5	$printArea = Coordinate::splitRange($worksheet->getPageSetup()->getPrintArea());
180
181	5	$chunks = [];
182	5	foreach ($printArea as $printAreaRect) {
183	5	$printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
184	5	$printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
185	5	$chunks[] = '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
186		}
187
188	5	$this->objWriter->writeRawData(implode(',', $chunks));
189
190	5	$this->objWriter->endElement();
191		}
192		}
193
194	20	private function getDefinedRange(DefinedName $definedName): string
195		{
196	20	$definedRange = $definedName->getValue();
197	20	$splitCount = Preg::matchAllWithOffsets(
198	20	'/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
199	20	$definedRange,
200	20	$splitRanges
201	20	);
202
203	20	$lengths = array_map([StringHelper::class, 'strlenAllowNull'], array_column($splitRanges[0], 0));
204	20	$offsets = array_column($splitRanges[0], 1);
205
206	20	$worksheets = $splitRanges[2];
207	20	$columns = $splitRanges[6];
208	20	$rows = $splitRanges[7];
209
210	20	while ($splitCount > 0) {
211	20	--$splitCount;
212	20	$length = $lengths[$splitCount];
213	20	$offset = $offsets[$splitCount];
214	20	$worksheet = $worksheets[$splitCount][0];
215	20	$column = $columns[$splitCount][0];
216	20	$row = $rows[$splitCount][0];
217
218	20	$newRange = '';
219	20	if (empty($worksheet)) {
220	19	if (($offset === 0) \|\| ($definedRange[$offset - 1] !== ':')) {
221		// We should have a worksheet
222	15	$ws = $definedName->getWorksheet();
223	15	$worksheet = ($ws === null) ? null : $ws->getTitle();
224		}
225		} else {
226	6	$worksheet = str_replace("''", "'", trim($worksheet, "'"));
227		}
228
229	20	if (!empty($worksheet)) {
230	20	$newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
231		}
232	20	$newRange = "{$newRange}{$column}{$row}";
233
234	20	$definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
235		}
236
237	20	if (str_starts_with($definedRange, '=')) {
238	10	$definedRange = substr($definedRange, 1);
239		}
240
241	20	return $definedRange;
242		}
243		}
244

PHPOffice / PhpSpreadsheet

Pull Request — master (#4323)

DefinedNames::writeNamedRangeForPrintTitles() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like