Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00%
88 / 88
100.00%
3 / 3
 CRAP
100.00%
1 / 1
ExcelExport
100.00%
88 / 88
100.00%
3 / 3
 13
100.00%
1 / 1
 streamFromSheets
100.00%
79 / 79
100.00%
1 / 1
 10
 getSupportedExtensions
100.00%
3 / 3
100.00%
1 / 1
 1
 getExcelColumnName
100.00%
6 / 6
100.00%
1 / 1
 2
1<?php
2namespace Apie\Export;
3
4use Apie\Export\Lists\FileExtensionList;
5use Apie\Export\ValueObjects\FileExtension;
6use Nyholm\Psr7\Stream;
7use Psr\Http\Message\StreamInterface;
8use ZipStream\ZipStream;
9
10class ExcelExport implements ExportInterface
11{
12    public function streamFromSheets(array $sheets, string $outputFilename = 'export.xlsx'): StreamInterface
13    {
14        // Sanitize and reindex sheet names
15        $sheetNames = [];
16        $i = 1;
17        foreach ($sheets as $name => $gen) {
18            $clean = substr(preg_replace('/[\\\\\/\?\*\[\]:]/', '', (string)$name), 0, 31) ?: "Sheet{$i}";
19            $sheetNames[] = $clean;
20            $i++;
21        }
22
23        // Initialize ZIP stream
24        $stream = fopen('php://temp', 'r+');
25        $outputStream = new Stream($stream);
26
27        $zip = new ZipStream(
28            outputName: $outputFilename,
29            outputStream: $outputStream,
30            sendHttpHeaders: false,
31        );
32
33        // === [Content_Types].xml ===
34        $types = [
35            '<?xml version="1.0" encoding="UTF-8"?>',
36            '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">',
37            '  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>',
38            '  <Default Extension="xml" ContentType="application/xml"/>',
39            '  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
40            '  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
41        ];
42        foreach ($sheetNames as $i => $_) {
43            $types[] = '  <Override PartName="/xl/worksheets/sheet' . ($i + 1) . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
44        }
45        $types[] = '</Types>';
46        $zip->addFile('[Content_Types].xml', implode("\n", $types));
47
48        // === _rels/.rels ===
49        $zip->addFile('_rels/.rels', <<<XML
50<?xml version="1.0" encoding="UTF-8"?>
51<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
52  <Relationship Id="rId1"
53    Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
54    Target="xl/workbook.xml"/>
55</Relationships>
56XML);
57
58        // === xl/workbook.xml ===
59        $sheetsXml = [];
60        foreach ($sheetNames as $i => $name) {
61            $sheetsXml[] = '    <sheet name="' . htmlspecialchars($name, ENT_XML1) . '" sheetId="' . ($i + 1) . '" r:id="rId' . ($i + 1) . '"/>';
62        }
63        $workbookXml = <<<XML
64<?xml version="1.0" encoding="UTF-8"?>
65<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
66          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
67  <sheets>
68    {SHEETS}
69  </sheets>
70</workbook>
71XML;
72        $workbookXml = str_replace('{SHEETS}', implode("\n", $sheetsXml), $workbookXml);
73        $zip->addFile('xl/workbook.xml', $workbookXml);
74
75        // === xl/_rels/workbook.xml.rels ===
76        $relsXml = [
77            '<?xml version="1.0" encoding="UTF-8"?>',
78            '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">',
79        ];
80        foreach ($sheetNames as $i => $_) {
81            $relsXml[] = '  <Relationship Id="rId' . ($i + 1) .
82                         '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"' .
83                         ' Target="worksheets/sheet' . ($i + 1) . '.xml"/>';
84        }
85        $relsXml[] = '  <Relationship Id="rId' . (count($sheetNames) + 1) .
86                     '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"' .
87                     ' Target="styles.xml"/>';
88        $relsXml[] = '</Relationships>';
89        $zip->addFile('xl/_rels/workbook.xml.rels', implode("\n", $relsXml));
90
91        // === xl/styles.xml (minimal) ===
92        $stylesXml = <<<XML
93<?xml version="1.0" encoding="UTF-8"?>
94<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
95  <fonts count="1">
96    <font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font>
97  </fonts>
98  <fills count="1">
99    <fill><patternFill patternType="none"/></fill>
100  </fills>
101  <borders count="1">
102    <border><left/><right/><top/><bottom/><diagonal/></border>
103  </borders>
104  <cellStyleXfs count="1">
105    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
106  </cellStyleXfs>
107  <cellXfs count="2">
108    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/> <!-- style 0: text -->
109    <xf numFmtId="1" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/> <!-- style 1: numeric -->
110  </cellXfs>
111</styleSheet>
112XML;
113        $zip->addFile('xl/styles.xml', $stylesXml);
114
115        // === Add each worksheet ===
116        $index = 1;
117        foreach ($sheets as $name => $rowsGenerator) {
118            $zip->addFileFromCallback("xl/worksheets/sheet{$index}.xml", function () use ($rowsGenerator) {
119                $stream = fopen('php://temp', 'r+');
120                fwrite($stream, '<?xml version="1.0" encoding="UTF-8"?>' . "\n");
121                fwrite($stream, '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' . "\n");
122                fwrite($stream, '<sheetData>' . "\n");
123
124                $rowIndex = 1;
125                foreach ($rowsGenerator as $row) {
126                    fwrite($stream, '<row r="' . $rowIndex . '">');
127                    $colIndex = 1;
128                    foreach ($row as $cellValue) {
129                        $colLetter = $this->getExcelColumnName($colIndex);
130                        if (is_numeric($cellValue)) {
131                            fwrite($stream, '<c r="' . $colLetter . $rowIndex . '" s="1"><v>' . $cellValue . '</v></c>');
132                        } else {
133                            $escaped = htmlspecialchars((string)$cellValue, ENT_XML1);
134                            fwrite($stream, '<c t="inlineStr" r="' . $colLetter . $rowIndex . '" s="0"><is><t>' . $escaped . '</t></is></c>');
135                        }
136                        $colIndex++;
137                    }
138                    fwrite($stream, '</row>' . "\n");
139                    $rowIndex++;
140                }
141
142                fwrite($stream, '</sheetData>' . "\n");
143                fwrite($stream, '</worksheet>' . "\n");
144                rewind($stream);
145                return $stream;
146            });
147            $index++;
148        }
149
150        $zip->finish();
151        rewind($stream);
152        return $outputStream;
153    }
154
155    public function getSupportedExtensions(): FileExtensionList
156    {
157        return new FileExtensionList([
158            new FileExtension('xlsx'),
159        ]);
160    }
161
162    /**
163     * Convert 1-based column index to Excel column name (A, B, ..., Z, AA, AB, ...).
164     */
165    private function getExcelColumnName(int $index): string
166    {
167        $name = '';
168        while ($index > 0) {
169            $index--;
170            $name = chr(65 + ($index % 26)) . $name;
171            $index = intdiv($index, 26);
172        }
173        return $name;
174    }
175}