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