|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Writer\Xls; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\DefinedName; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
|
11
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
12
|
|
|
use PhpOffice\PhpSpreadsheet\Style\Style; |
|
13
|
|
|
|
|
14
|
|
|
// Original file header of PEAR::Spreadsheet_Excel_Writer_Workbook (used as the base for this class): |
|
15
|
|
|
// ----------------------------------------------------------------------------------------- |
|
16
|
|
|
// /* |
|
17
|
|
|
// * Module written/ported by Xavier Noguer <[email protected]> |
|
18
|
|
|
// * |
|
19
|
|
|
// * The majority of this is _NOT_ my code. I simply ported it from the |
|
20
|
|
|
// * PERL Spreadsheet::WriteExcel module. |
|
21
|
|
|
// * |
|
22
|
|
|
// * The author of the Spreadsheet::WriteExcel module is John McNamara |
|
23
|
|
|
// * <[email protected]> |
|
24
|
|
|
// * |
|
25
|
|
|
// * I _DO_ maintain this code, and John McNamara has nothing to do with the |
|
26
|
|
|
// * porting of this code to PHP. Any questions directly related to this |
|
27
|
|
|
// * class library should be directed to me. |
|
28
|
|
|
// * |
|
29
|
|
|
// * License Information: |
|
30
|
|
|
// * |
|
31
|
|
|
// * Spreadsheet_Excel_Writer: A library for generating Excel Spreadsheets |
|
32
|
|
|
// * Copyright (c) 2002-2003 Xavier Noguer [email protected] |
|
33
|
|
|
// * |
|
34
|
|
|
// * This library is free software; you can redistribute it and/or |
|
35
|
|
|
// * modify it under the terms of the GNU Lesser General Public |
|
36
|
|
|
// * License as published by the Free Software Foundation; either |
|
37
|
|
|
// * version 2.1 of the License, or (at your option) any later version. |
|
38
|
|
|
// * |
|
39
|
|
|
// * This library is distributed in the hope that it will be useful, |
|
40
|
|
|
// * but WITHOUT ANY WARRANTY; without even the implied warranty of |
|
41
|
|
|
// * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
|
42
|
|
|
// * Lesser General Public License for more details. |
|
43
|
|
|
// * |
|
44
|
|
|
// * You should have received a copy of the GNU Lesser General Public |
|
45
|
|
|
// * License along with this library; if not, write to the Free Software |
|
46
|
|
|
// * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
|
47
|
|
|
// */ |
|
48
|
|
|
class Workbook extends BIFFwriter |
|
49
|
|
|
{ |
|
50
|
|
|
/** |
|
51
|
|
|
* Formula parser. |
|
52
|
|
|
*/ |
|
53
|
|
|
private Parser $parser; |
|
54
|
|
|
|
|
55
|
|
|
/* |
|
56
|
|
|
* The BIFF file size for the workbook. Not currently used. |
|
57
|
|
|
* |
|
58
|
|
|
* @see calcSheetOffsets() |
|
59
|
|
|
*/ |
|
60
|
|
|
//private int $biffSize; |
|
61
|
|
|
|
|
62
|
|
|
/** |
|
63
|
|
|
* XF Writers. |
|
64
|
|
|
* |
|
65
|
|
|
* @var Xf[] |
|
66
|
|
|
*/ |
|
67
|
|
|
private array $xfWriters = []; |
|
68
|
|
|
|
|
69
|
|
|
/** |
|
70
|
|
|
* Array containing the colour palette. |
|
71
|
|
|
*/ |
|
72
|
|
|
private array $palette; |
|
73
|
|
|
|
|
74
|
|
|
/** |
|
75
|
|
|
* The codepage indicates the text encoding used for strings. |
|
76
|
|
|
*/ |
|
77
|
|
|
private int $codepage; |
|
78
|
|
|
|
|
79
|
|
|
/** |
|
80
|
|
|
* The country code used for localization. |
|
81
|
|
|
*/ |
|
82
|
|
|
private int $countryCode; |
|
83
|
|
|
|
|
84
|
|
|
/** |
|
85
|
|
|
* Workbook. |
|
86
|
|
|
*/ |
|
87
|
|
|
private Spreadsheet $spreadsheet; |
|
88
|
|
|
|
|
89
|
|
|
/** |
|
90
|
|
|
* Fonts writers. |
|
91
|
|
|
* |
|
92
|
|
|
* @var Font[] |
|
93
|
|
|
*/ |
|
94
|
|
|
private array $fontWriters = []; |
|
95
|
|
|
|
|
96
|
|
|
/** |
|
97
|
|
|
* Added fonts. Maps from font's hash => index in workbook. |
|
98
|
|
|
*/ |
|
99
|
|
|
private array $addedFonts = []; |
|
100
|
|
|
|
|
101
|
|
|
/** |
|
102
|
|
|
* Shared number formats. |
|
103
|
|
|
*/ |
|
104
|
|
|
private array $numberFormats = []; |
|
105
|
|
|
|
|
106
|
|
|
/** |
|
107
|
|
|
* Added number formats. Maps from numberFormat's hash => index in workbook. |
|
108
|
|
|
*/ |
|
109
|
|
|
private array $addedNumberFormats = []; |
|
110
|
|
|
|
|
111
|
|
|
/** |
|
112
|
|
|
* Sizes of the binary worksheet streams. |
|
113
|
|
|
*/ |
|
114
|
|
|
private array $worksheetSizes = []; |
|
115
|
|
|
|
|
116
|
|
|
/** |
|
117
|
|
|
* Offsets of the binary worksheet streams relative to the start of the global workbook stream. |
|
118
|
|
|
*/ |
|
119
|
|
|
private array $worksheetOffsets = []; |
|
120
|
|
|
|
|
121
|
|
|
/** |
|
122
|
|
|
* Total number of shared strings in workbook. |
|
123
|
|
|
*/ |
|
124
|
|
|
private int $stringTotal; |
|
125
|
|
|
|
|
126
|
|
|
/** |
|
127
|
|
|
* Number of unique shared strings in workbook. |
|
128
|
|
|
*/ |
|
129
|
|
|
private int $stringUnique; |
|
130
|
|
|
|
|
131
|
|
|
/** |
|
132
|
|
|
* Array of unique shared strings in workbook. |
|
133
|
|
|
*/ |
|
134
|
|
|
private array $stringTable; |
|
135
|
|
|
|
|
136
|
|
|
/** |
|
137
|
|
|
* Color cache. |
|
138
|
|
|
*/ |
|
139
|
|
|
private array $colors; |
|
140
|
|
|
|
|
141
|
|
|
/** |
|
142
|
|
|
* Escher object corresponding to MSODRAWINGGROUP. |
|
143
|
|
|
*/ |
|
144
|
|
|
private ?\PhpOffice\PhpSpreadsheet\Shared\Escher $escher = null; |
|
145
|
|
|
|
|
146
|
|
|
/** |
|
147
|
|
|
* Class constructor. |
|
148
|
|
|
* |
|
149
|
|
|
* @param Spreadsheet $spreadsheet The Workbook |
|
150
|
|
|
* @param int $str_total Total number of strings |
|
151
|
|
|
* @param int $str_unique Total number of unique strings |
|
152
|
|
|
* @param array $str_table String Table |
|
153
|
|
|
* @param array $colors Colour Table |
|
154
|
|
|
* @param Parser $parser The formula parser created for the Workbook |
|
155
|
|
|
*/ |
|
156
|
111 |
|
public function __construct(Spreadsheet $spreadsheet, int &$str_total, int &$str_unique, array &$str_table, array &$colors, Parser $parser) |
|
157
|
|
|
{ |
|
158
|
|
|
// It needs to call its parent's constructor explicitly |
|
159
|
111 |
|
parent::__construct(); |
|
160
|
|
|
|
|
161
|
111 |
|
$this->parser = $parser; |
|
162
|
|
|
//$this->biffSize = 0; |
|
163
|
111 |
|
$this->palette = []; |
|
164
|
111 |
|
$this->countryCode = -1; |
|
165
|
|
|
|
|
166
|
111 |
|
$this->stringTotal = &$str_total; |
|
167
|
111 |
|
$this->stringUnique = &$str_unique; |
|
168
|
111 |
|
$this->stringTable = &$str_table; |
|
169
|
111 |
|
$this->colors = &$colors; |
|
170
|
111 |
|
$this->setPaletteXl97(); |
|
171
|
|
|
|
|
172
|
111 |
|
$this->spreadsheet = $spreadsheet; |
|
173
|
|
|
|
|
174
|
111 |
|
$this->codepage = 0x04B0; |
|
175
|
|
|
|
|
176
|
|
|
// Add empty sheets and Build color cache |
|
177
|
111 |
|
$countSheets = $spreadsheet->getSheetCount(); |
|
178
|
111 |
|
for ($i = 0; $i < $countSheets; ++$i) { |
|
179
|
111 |
|
$phpSheet = $spreadsheet->getSheet($i); |
|
180
|
|
|
|
|
181
|
111 |
|
$this->parser->setExtSheet($phpSheet->getTitle(), $i); // Register worksheet name with parser |
|
182
|
|
|
|
|
183
|
111 |
|
$supbook_index = 0x00; |
|
184
|
111 |
|
$ref = pack('vvv', $supbook_index, $i, $i); |
|
185
|
111 |
|
$this->parser->references[] = $ref; // Register reference with parser |
|
186
|
|
|
|
|
187
|
|
|
// Sheet tab colors? |
|
188
|
111 |
|
if ($phpSheet->isTabColorSet()) { |
|
189
|
6 |
|
$this->addColor($phpSheet->getTabColor()->getRGB()); |
|
190
|
|
|
} |
|
191
|
|
|
} |
|
192
|
|
|
} |
|
193
|
|
|
|
|
194
|
|
|
/** |
|
195
|
|
|
* Add a new XF writer. |
|
196
|
|
|
* |
|
197
|
|
|
* @param bool $isStyleXf Is it a style XF? |
|
198
|
|
|
* |
|
199
|
|
|
* @return int Index to XF record |
|
200
|
|
|
*/ |
|
201
|
110 |
|
public function addXfWriter(Style $style, bool $isStyleXf = false): int |
|
202
|
|
|
{ |
|
203
|
110 |
|
$xfWriter = new Xf($style); |
|
204
|
110 |
|
$xfWriter->setIsStyleXf($isStyleXf); |
|
205
|
|
|
|
|
206
|
|
|
// Add the font if not already added |
|
207
|
110 |
|
$fontIndex = $this->addFont($style->getFont()); |
|
208
|
|
|
|
|
209
|
|
|
// Assign the font index to the xf record |
|
210
|
110 |
|
$xfWriter->setFontIndex($fontIndex); |
|
211
|
|
|
|
|
212
|
|
|
// Background colors, best to treat these after the font so black will come after white in custom palette |
|
213
|
110 |
|
if ($style->getFill()->getStartColor()->getRGB()) { |
|
214
|
110 |
|
$xfWriter->setFgColor( |
|
215
|
110 |
|
$this->addColor( |
|
216
|
110 |
|
$style->getFill()->getStartColor()->getRGB() |
|
217
|
110 |
|
) |
|
218
|
110 |
|
); |
|
219
|
|
|
} |
|
220
|
110 |
|
if ($style->getFill()->getEndColor()->getRGB()) { |
|
221
|
110 |
|
$xfWriter->setBgColor( |
|
222
|
110 |
|
$this->addColor( |
|
223
|
110 |
|
$style->getFill()->getEndColor()->getRGB() |
|
224
|
110 |
|
) |
|
225
|
110 |
|
); |
|
226
|
|
|
} |
|
227
|
110 |
|
$xfWriter->setBottomColor($this->addColor($style->getBorders()->getBottom()->getColor()->getRGB())); |
|
228
|
110 |
|
$xfWriter->setTopColor($this->addColor($style->getBorders()->getTop()->getColor()->getRGB())); |
|
229
|
110 |
|
$xfWriter->setRightColor($this->addColor($style->getBorders()->getRight()->getColor()->getRGB())); |
|
230
|
110 |
|
$xfWriter->setLeftColor($this->addColor($style->getBorders()->getLeft()->getColor()->getRGB())); |
|
231
|
110 |
|
$xfWriter->setDiagColor($this->addColor($style->getBorders()->getDiagonal()->getColor()->getRGB())); |
|
232
|
|
|
|
|
233
|
|
|
// Add the number format if it is not a built-in one and not already added |
|
234
|
110 |
|
if ($style->getNumberFormat()->getBuiltInFormatCode() === false) { |
|
235
|
22 |
|
$numberFormatHashCode = $style->getNumberFormat()->getHashCode(); |
|
236
|
|
|
|
|
237
|
22 |
|
if (isset($this->addedNumberFormats[$numberFormatHashCode])) { |
|
238
|
10 |
|
$numberFormatIndex = $this->addedNumberFormats[$numberFormatHashCode]; |
|
239
|
|
|
} else { |
|
240
|
22 |
|
$numberFormatIndex = 164 + count($this->numberFormats); |
|
241
|
22 |
|
$this->numberFormats[$numberFormatIndex] = $style->getNumberFormat(); |
|
242
|
22 |
|
$this->addedNumberFormats[$numberFormatHashCode] = $numberFormatIndex; |
|
243
|
|
|
} |
|
244
|
|
|
} else { |
|
245
|
110 |
|
$numberFormatIndex = (int) $style->getNumberFormat()->getBuiltInFormatCode(); |
|
246
|
|
|
} |
|
247
|
|
|
|
|
248
|
|
|
// Assign the number format index to xf record |
|
249
|
110 |
|
$xfWriter->setNumberFormatIndex($numberFormatIndex); |
|
250
|
|
|
|
|
251
|
110 |
|
$this->xfWriters[] = $xfWriter; |
|
252
|
|
|
|
|
253
|
110 |
|
return count($this->xfWriters) - 1; |
|
254
|
|
|
} |
|
255
|
|
|
|
|
256
|
|
|
/** |
|
257
|
|
|
* Add a font to added fonts. |
|
258
|
|
|
* |
|
259
|
|
|
* @return int Index to FONT record |
|
260
|
|
|
*/ |
|
261
|
110 |
|
public function addFont(\PhpOffice\PhpSpreadsheet\Style\Font $font): int |
|
262
|
|
|
{ |
|
263
|
110 |
|
$fontHashCode = $font->getHashCode(); |
|
264
|
110 |
|
if (isset($this->addedFonts[$fontHashCode])) { |
|
265
|
110 |
|
$fontIndex = $this->addedFonts[$fontHashCode]; |
|
266
|
|
|
} else { |
|
267
|
110 |
|
$countFonts = count($this->fontWriters); |
|
268
|
110 |
|
$fontIndex = ($countFonts < 4) ? $countFonts : $countFonts + 1; |
|
269
|
|
|
|
|
270
|
110 |
|
$fontWriter = new Font($font); |
|
271
|
110 |
|
$fontWriter->setColorIndex($this->addColor($font->getColor()->getRGB())); |
|
272
|
110 |
|
$this->fontWriters[] = $fontWriter; |
|
273
|
|
|
|
|
274
|
110 |
|
$this->addedFonts[$fontHashCode] = $fontIndex; |
|
275
|
|
|
} |
|
276
|
|
|
|
|
277
|
110 |
|
return $fontIndex; |
|
278
|
|
|
} |
|
279
|
|
|
|
|
280
|
|
|
/** |
|
281
|
|
|
* Alter color palette adding a custom color. |
|
282
|
|
|
* |
|
283
|
|
|
* @param string $rgb E.g. 'FF00AA' |
|
284
|
|
|
* |
|
285
|
|
|
* @return int Color index |
|
286
|
|
|
*/ |
|
287
|
111 |
|
public function addColor(string $rgb, int $default = 0): int |
|
288
|
|
|
{ |
|
289
|
111 |
|
if (!isset($this->colors[$rgb])) { |
|
290
|
111 |
|
$color |
|
291
|
111 |
|
= [ |
|
292
|
111 |
|
hexdec(substr($rgb, 0, 2)), |
|
293
|
111 |
|
hexdec(substr($rgb, 2, 2)), |
|
294
|
111 |
|
hexdec(substr($rgb, 4)), |
|
295
|
111 |
|
0, |
|
296
|
111 |
|
]; |
|
297
|
111 |
|
$colorIndex = array_search($color, $this->palette); |
|
298
|
111 |
|
if ($colorIndex) { |
|
299
|
111 |
|
$this->colors[$rgb] = $colorIndex; |
|
300
|
|
|
} else { |
|
301
|
18 |
|
if (count($this->colors) === 0) { |
|
302
|
7 |
|
$lastColor = 7; |
|
303
|
|
|
} else { |
|
304
|
18 |
|
$lastColor = end($this->colors); |
|
305
|
|
|
} |
|
306
|
18 |
|
if ($lastColor < 57) { |
|
307
|
|
|
// then we add a custom color altering the palette |
|
308
|
18 |
|
$colorIndex = $lastColor + 1; |
|
309
|
18 |
|
$this->palette[$colorIndex] = $color; |
|
310
|
18 |
|
$this->colors[$rgb] = $colorIndex; |
|
311
|
|
|
} else { |
|
312
|
|
|
// no room for more custom colors, just map to black |
|
313
|
1 |
|
$colorIndex = $default; |
|
314
|
|
|
} |
|
315
|
|
|
} |
|
316
|
|
|
} else { |
|
317
|
|
|
// fetch already added custom color |
|
318
|
111 |
|
$colorIndex = $this->colors[$rgb]; |
|
319
|
|
|
} |
|
320
|
|
|
|
|
321
|
111 |
|
return $colorIndex; |
|
322
|
|
|
} |
|
323
|
|
|
|
|
324
|
|
|
/** |
|
325
|
|
|
* Sets the colour palette to the Excel 97+ default. |
|
326
|
|
|
*/ |
|
327
|
111 |
|
private function setPaletteXl97(): void |
|
328
|
|
|
{ |
|
329
|
111 |
|
$this->palette = [ |
|
330
|
111 |
|
0x08 => [0x00, 0x00, 0x00, 0x00], |
|
331
|
111 |
|
0x09 => [0xFF, 0xFF, 0xFF, 0x00], |
|
332
|
111 |
|
0x0A => [0xFF, 0x00, 0x00, 0x00], |
|
333
|
111 |
|
0x0B => [0x00, 0xFF, 0x00, 0x00], |
|
334
|
111 |
|
0x0C => [0x00, 0x00, 0xFF, 0x00], |
|
335
|
111 |
|
0x0D => [0xFF, 0xFF, 0x00, 0x00], |
|
336
|
111 |
|
0x0E => [0xFF, 0x00, 0xFF, 0x00], |
|
337
|
111 |
|
0x0F => [0x00, 0xFF, 0xFF, 0x00], |
|
338
|
111 |
|
0x10 => [0x80, 0x00, 0x00, 0x00], |
|
339
|
111 |
|
0x11 => [0x00, 0x80, 0x00, 0x00], |
|
340
|
111 |
|
0x12 => [0x00, 0x00, 0x80, 0x00], |
|
341
|
111 |
|
0x13 => [0x80, 0x80, 0x00, 0x00], |
|
342
|
111 |
|
0x14 => [0x80, 0x00, 0x80, 0x00], |
|
343
|
111 |
|
0x15 => [0x00, 0x80, 0x80, 0x00], |
|
344
|
111 |
|
0x16 => [0xC0, 0xC0, 0xC0, 0x00], |
|
345
|
111 |
|
0x17 => [0x80, 0x80, 0x80, 0x00], |
|
346
|
111 |
|
0x18 => [0x99, 0x99, 0xFF, 0x00], |
|
347
|
111 |
|
0x19 => [0x99, 0x33, 0x66, 0x00], |
|
348
|
111 |
|
0x1A => [0xFF, 0xFF, 0xCC, 0x00], |
|
349
|
111 |
|
0x1B => [0xCC, 0xFF, 0xFF, 0x00], |
|
350
|
111 |
|
0x1C => [0x66, 0x00, 0x66, 0x00], |
|
351
|
111 |
|
0x1D => [0xFF, 0x80, 0x80, 0x00], |
|
352
|
111 |
|
0x1E => [0x00, 0x66, 0xCC, 0x00], |
|
353
|
111 |
|
0x1F => [0xCC, 0xCC, 0xFF, 0x00], |
|
354
|
111 |
|
0x20 => [0x00, 0x00, 0x80, 0x00], |
|
355
|
111 |
|
0x21 => [0xFF, 0x00, 0xFF, 0x00], |
|
356
|
111 |
|
0x22 => [0xFF, 0xFF, 0x00, 0x00], |
|
357
|
111 |
|
0x23 => [0x00, 0xFF, 0xFF, 0x00], |
|
358
|
111 |
|
0x24 => [0x80, 0x00, 0x80, 0x00], |
|
359
|
111 |
|
0x25 => [0x80, 0x00, 0x00, 0x00], |
|
360
|
111 |
|
0x26 => [0x00, 0x80, 0x80, 0x00], |
|
361
|
111 |
|
0x27 => [0x00, 0x00, 0xFF, 0x00], |
|
362
|
111 |
|
0x28 => [0x00, 0xCC, 0xFF, 0x00], |
|
363
|
111 |
|
0x29 => [0xCC, 0xFF, 0xFF, 0x00], |
|
364
|
111 |
|
0x2A => [0xCC, 0xFF, 0xCC, 0x00], |
|
365
|
111 |
|
0x2B => [0xFF, 0xFF, 0x99, 0x00], |
|
366
|
111 |
|
0x2C => [0x99, 0xCC, 0xFF, 0x00], |
|
367
|
111 |
|
0x2D => [0xFF, 0x99, 0xCC, 0x00], |
|
368
|
111 |
|
0x2E => [0xCC, 0x99, 0xFF, 0x00], |
|
369
|
111 |
|
0x2F => [0xFF, 0xCC, 0x99, 0x00], |
|
370
|
111 |
|
0x30 => [0x33, 0x66, 0xFF, 0x00], |
|
371
|
111 |
|
0x31 => [0x33, 0xCC, 0xCC, 0x00], |
|
372
|
111 |
|
0x32 => [0x99, 0xCC, 0x00, 0x00], |
|
373
|
111 |
|
0x33 => [0xFF, 0xCC, 0x00, 0x00], |
|
374
|
111 |
|
0x34 => [0xFF, 0x99, 0x00, 0x00], |
|
375
|
111 |
|
0x35 => [0xFF, 0x66, 0x00, 0x00], |
|
376
|
111 |
|
0x36 => [0x66, 0x66, 0x99, 0x00], |
|
377
|
111 |
|
0x37 => [0x96, 0x96, 0x96, 0x00], |
|
378
|
111 |
|
0x38 => [0x00, 0x33, 0x66, 0x00], |
|
379
|
111 |
|
0x39 => [0x33, 0x99, 0x66, 0x00], |
|
380
|
111 |
|
0x3A => [0x00, 0x33, 0x00, 0x00], |
|
381
|
111 |
|
0x3B => [0x33, 0x33, 0x00, 0x00], |
|
382
|
111 |
|
0x3C => [0x99, 0x33, 0x00, 0x00], |
|
383
|
111 |
|
0x3D => [0x99, 0x33, 0x66, 0x00], |
|
384
|
111 |
|
0x3E => [0x33, 0x33, 0x99, 0x00], |
|
385
|
111 |
|
0x3F => [0x33, 0x33, 0x33, 0x00], |
|
386
|
111 |
|
]; |
|
387
|
|
|
} |
|
388
|
|
|
|
|
389
|
|
|
/** |
|
390
|
|
|
* Assemble worksheets into a workbook and send the BIFF data to an OLE |
|
391
|
|
|
* storage. |
|
392
|
|
|
* |
|
393
|
|
|
* @param array $worksheetSizes The sizes in bytes of the binary worksheet streams |
|
394
|
|
|
* |
|
395
|
|
|
* @return string Binary data for workbook stream |
|
396
|
|
|
*/ |
|
397
|
109 |
|
public function writeWorkbook(array $worksheetSizes): string |
|
398
|
|
|
{ |
|
399
|
109 |
|
$this->worksheetSizes = $worksheetSizes; |
|
400
|
|
|
|
|
401
|
|
|
// Calculate the number of selected worksheet tabs and call the finalization |
|
402
|
|
|
// methods for each worksheet |
|
403
|
109 |
|
$total_worksheets = $this->spreadsheet->getSheetCount(); |
|
404
|
|
|
|
|
405
|
|
|
// Add part 1 of the Workbook globals, what goes before the SHEET records |
|
406
|
109 |
|
$this->storeBof(0x0005); |
|
407
|
109 |
|
$this->writeCodepage(); |
|
408
|
109 |
|
$this->writeWindow1(); |
|
409
|
|
|
|
|
410
|
109 |
|
$this->writeDateMode(); |
|
411
|
109 |
|
$this->writeAllFonts(); |
|
412
|
109 |
|
$this->writeAllNumberFormats(); |
|
413
|
109 |
|
$this->writeAllXfs(); |
|
414
|
109 |
|
$this->writeAllStyles(); |
|
415
|
109 |
|
$this->writePalette(); |
|
416
|
|
|
|
|
417
|
|
|
// Prepare part 3 of the workbook global stream, what goes after the SHEET records |
|
418
|
109 |
|
$part3 = ''; |
|
419
|
109 |
|
if ($this->countryCode !== -1) { |
|
420
|
|
|
$part3 .= $this->writeCountry(); |
|
421
|
|
|
} |
|
422
|
109 |
|
$part3 .= $this->writeRecalcId(); |
|
423
|
|
|
|
|
424
|
109 |
|
$part3 .= $this->writeSupbookInternal(); |
|
425
|
|
|
/* TODO: store external SUPBOOK records and XCT and CRN records |
|
426
|
|
|
in case of external references for BIFF8 */ |
|
427
|
109 |
|
$part3 .= $this->writeExternalsheetBiff8(); |
|
428
|
109 |
|
$part3 .= $this->writeAllDefinedNamesBiff8(); |
|
429
|
109 |
|
$part3 .= $this->writeMsoDrawingGroup(); |
|
430
|
109 |
|
$part3 .= $this->writeSharedStringsTable(); |
|
431
|
|
|
|
|
432
|
109 |
|
$part3 .= $this->writeEof(); |
|
433
|
|
|
|
|
434
|
|
|
// Add part 2 of the Workbook globals, the SHEET records |
|
435
|
109 |
|
$this->calcSheetOffsets(); |
|
436
|
109 |
|
for ($i = 0; $i < $total_worksheets; ++$i) { |
|
437
|
109 |
|
$this->writeBoundSheet($this->spreadsheet->getSheet($i), $this->worksheetOffsets[$i]); |
|
438
|
|
|
} |
|
439
|
|
|
|
|
440
|
|
|
// Add part 3 of the Workbook globals |
|
441
|
109 |
|
$this->_data .= $part3; |
|
442
|
|
|
|
|
443
|
109 |
|
return $this->_data; |
|
444
|
|
|
} |
|
445
|
|
|
|
|
446
|
|
|
/** |
|
447
|
|
|
* Calculate offsets for Worksheet BOF records. |
|
448
|
|
|
*/ |
|
449
|
109 |
|
private function calcSheetOffsets(): void |
|
450
|
|
|
{ |
|
451
|
109 |
|
$boundsheet_length = 10; // fixed length for a BOUNDSHEET record |
|
452
|
|
|
|
|
453
|
|
|
// size of Workbook globals part 1 + 3 |
|
454
|
109 |
|
$offset = $this->_datasize; |
|
455
|
|
|
|
|
456
|
|
|
// add size of Workbook globals part 2, the length of the SHEET records |
|
457
|
109 |
|
$total_worksheets = count($this->spreadsheet->getAllSheets()); |
|
458
|
109 |
|
foreach ($this->spreadsheet->getWorksheetIterator() as $sheet) { |
|
459
|
109 |
|
$offset += $boundsheet_length + strlen(StringHelper::UTF8toBIFF8UnicodeShort($sheet->getTitle())); |
|
460
|
|
|
} |
|
461
|
|
|
|
|
462
|
|
|
// add the sizes of each of the Sheet substreams, respectively |
|
463
|
109 |
|
for ($i = 0; $i < $total_worksheets; ++$i) { |
|
464
|
109 |
|
$this->worksheetOffsets[$i] = $offset; |
|
465
|
109 |
|
$offset += $this->worksheetSizes[$i]; |
|
466
|
|
|
} |
|
467
|
|
|
//$this->biffSize = $offset; |
|
468
|
|
|
} |
|
469
|
|
|
|
|
470
|
|
|
/** |
|
471
|
|
|
* Store the Excel FONT records. |
|
472
|
|
|
*/ |
|
473
|
109 |
|
private function writeAllFonts(): void |
|
474
|
|
|
{ |
|
475
|
109 |
|
foreach ($this->fontWriters as $fontWriter) { |
|
476
|
109 |
|
$this->append($fontWriter->writeFont()); |
|
477
|
|
|
} |
|
478
|
|
|
} |
|
479
|
|
|
|
|
480
|
|
|
/** |
|
481
|
|
|
* Store user defined numerical formats i.e. FORMAT records. |
|
482
|
|
|
*/ |
|
483
|
109 |
|
private function writeAllNumberFormats(): void |
|
484
|
|
|
{ |
|
485
|
109 |
|
foreach ($this->numberFormats as $numberFormatIndex => $numberFormat) { |
|
486
|
22 |
|
$this->writeNumberFormat($numberFormat->getFormatCode(), $numberFormatIndex); |
|
487
|
|
|
} |
|
488
|
|
|
} |
|
489
|
|
|
|
|
490
|
|
|
/** |
|
491
|
|
|
* Write all XF records. |
|
492
|
|
|
*/ |
|
493
|
109 |
|
private function writeAllXfs(): void |
|
494
|
|
|
{ |
|
495
|
109 |
|
foreach ($this->xfWriters as $xfWriter) { |
|
496
|
109 |
|
$this->append($xfWriter->writeXf()); |
|
497
|
|
|
} |
|
498
|
|
|
} |
|
499
|
|
|
|
|
500
|
|
|
/** |
|
501
|
|
|
* Write all STYLE records. |
|
502
|
|
|
*/ |
|
503
|
109 |
|
private function writeAllStyles(): void |
|
504
|
|
|
{ |
|
505
|
109 |
|
$this->writeStyle(); |
|
506
|
|
|
} |
|
507
|
|
|
|
|
508
|
7 |
|
private function parseDefinedNameValue(DefinedName $definedName): string |
|
509
|
|
|
{ |
|
510
|
7 |
|
$definedRange = $definedName->getValue(); |
|
511
|
7 |
|
$splitCount = preg_match_all( |
|
512
|
7 |
|
'/' . Calculation::CALCULATION_REGEXP_CELLREF . '/mui', |
|
513
|
7 |
|
$definedRange, |
|
514
|
7 |
|
$splitRanges, |
|
515
|
7 |
|
PREG_OFFSET_CAPTURE |
|
516
|
7 |
|
); |
|
517
|
|
|
|
|
518
|
7 |
|
$lengths = array_map('strlen', array_column($splitRanges[0], 0)); |
|
519
|
7 |
|
$offsets = array_column($splitRanges[0], 1); |
|
520
|
|
|
|
|
521
|
7 |
|
$worksheets = $splitRanges[2]; |
|
522
|
7 |
|
$columns = $splitRanges[6]; |
|
523
|
7 |
|
$rows = $splitRanges[7]; |
|
524
|
|
|
|
|
525
|
7 |
|
while ($splitCount > 0) { |
|
526
|
7 |
|
--$splitCount; |
|
527
|
7 |
|
$length = $lengths[$splitCount]; |
|
528
|
7 |
|
$offset = $offsets[$splitCount]; |
|
529
|
7 |
|
$worksheet = $worksheets[$splitCount][0]; |
|
530
|
7 |
|
$column = $columns[$splitCount][0]; |
|
531
|
7 |
|
$row = $rows[$splitCount][0]; |
|
532
|
|
|
|
|
533
|
7 |
|
$newRange = ''; |
|
534
|
7 |
|
if (empty($worksheet)) { |
|
535
|
6 |
|
if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) { |
|
536
|
|
|
// We should have a worksheet |
|
537
|
3 |
|
$worksheet = $definedName->getWorksheet() ? $definedName->getWorksheet()->getTitle() : null; |
|
538
|
|
|
} |
|
539
|
|
|
} else { |
|
540
|
4 |
|
$worksheet = str_replace("''", "'", trim($worksheet, "'")); |
|
541
|
|
|
} |
|
542
|
7 |
|
if (!empty($worksheet)) { |
|
543
|
7 |
|
$newRange = "'" . str_replace("'", "''", $worksheet) . "'!"; |
|
544
|
|
|
} |
|
545
|
|
|
|
|
546
|
7 |
|
if (!empty($column)) { |
|
547
|
7 |
|
$newRange .= "\${$column}"; |
|
548
|
|
|
} |
|
549
|
7 |
|
if (!empty($row)) { |
|
550
|
7 |
|
$newRange .= "\${$row}"; |
|
551
|
|
|
} |
|
552
|
|
|
|
|
553
|
7 |
|
$definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length); |
|
554
|
|
|
} |
|
555
|
|
|
|
|
556
|
7 |
|
return $definedRange; |
|
557
|
|
|
} |
|
558
|
|
|
|
|
559
|
|
|
/** |
|
560
|
|
|
* Writes all the DEFINEDNAME records (BIFF8). |
|
561
|
|
|
* So far this is only used for repeating rows/columns (print titles) and print areas. |
|
562
|
|
|
*/ |
|
563
|
109 |
|
private function writeAllDefinedNamesBiff8(): string |
|
564
|
|
|
{ |
|
565
|
109 |
|
$chunk = ''; |
|
566
|
|
|
|
|
567
|
|
|
// Named ranges |
|
568
|
109 |
|
$definedNames = $this->spreadsheet->getDefinedNames(); |
|
569
|
109 |
|
if (count($definedNames) > 0) { |
|
570
|
|
|
// Loop named ranges |
|
571
|
7 |
|
foreach ($definedNames as $definedName) { |
|
572
|
7 |
|
$range = $this->parseDefinedNameValue($definedName); |
|
573
|
|
|
|
|
574
|
|
|
// parse formula |
|
575
|
|
|
try { |
|
576
|
7 |
|
$this->parser->parse($range); |
|
577
|
7 |
|
$formulaData = $this->parser->toReversePolish(); |
|
578
|
|
|
|
|
579
|
|
|
// make sure tRef3d is of type tRef3dR (0x3A) |
|
580
|
6 |
|
if (isset($formulaData[0]) && ($formulaData[0] == "\x7A" || $formulaData[0] == "\x5A")) { |
|
581
|
5 |
|
$formulaData = "\x3A" . substr($formulaData, 1); |
|
582
|
|
|
} |
|
583
|
|
|
|
|
584
|
6 |
|
if ($definedName->getLocalOnly()) { |
|
585
|
|
|
// local scope |
|
586
|
1 |
|
$scopeWs = $definedName->getScope(); |
|
587
|
1 |
|
$scope = ($scopeWs === null) ? 0 : ($this->spreadsheet->getIndex($scopeWs) + 1); |
|
588
|
|
|
} else { |
|
589
|
|
|
// global scope |
|
590
|
6 |
|
$scope = 0; |
|
591
|
|
|
} |
|
592
|
6 |
|
$chunk .= $this->writeData($this->writeDefinedNameBiff8($definedName->getName(), $formulaData, $scope, false)); |
|
593
|
1 |
|
} catch (PhpSpreadsheetException) { |
|
594
|
|
|
// do nothing |
|
595
|
|
|
} |
|
596
|
|
|
} |
|
597
|
|
|
} |
|
598
|
|
|
|
|
599
|
|
|
// total number of sheets |
|
600
|
109 |
|
$total_worksheets = $this->spreadsheet->getSheetCount(); |
|
601
|
|
|
|
|
602
|
|
|
// write the print titles (repeating rows, columns), if any |
|
603
|
109 |
|
for ($i = 0; $i < $total_worksheets; ++$i) { |
|
604
|
109 |
|
$sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
|
605
|
|
|
// simultaneous repeatColumns repeatRows |
|
606
|
109 |
|
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { |
|
607
|
|
|
$repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
|
608
|
|
|
$colmin = Coordinate::columnIndexFromString($repeat[0]) - 1; |
|
609
|
|
|
$colmax = Coordinate::columnIndexFromString($repeat[1]) - 1; |
|
610
|
|
|
|
|
611
|
|
|
$repeat = $sheetSetup->getRowsToRepeatAtTop(); |
|
612
|
|
|
$rowmin = $repeat[0] - 1; |
|
613
|
|
|
$rowmax = $repeat[1] - 1; |
|
614
|
|
|
|
|
615
|
|
|
// construct formula data manually |
|
616
|
|
|
$formulaData = pack('Cv', 0x29, 0x17); // tMemFunc |
|
617
|
|
|
$formulaData .= pack('Cvvvvv', 0x3B, $i, 0, 65535, $colmin, $colmax); // tArea3d |
|
618
|
|
|
$formulaData .= pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, 0, 255); // tArea3d |
|
619
|
|
|
$formulaData .= pack('C', 0x10); // tList |
|
620
|
|
|
|
|
621
|
|
|
// store the DEFINEDNAME record |
|
622
|
|
|
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); |
|
623
|
109 |
|
} elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { |
|
624
|
|
|
// (exclusive) either repeatColumns or repeatRows. |
|
625
|
|
|
// Columns to repeat |
|
626
|
2 |
|
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { |
|
627
|
|
|
$repeat = $sheetSetup->getColumnsToRepeatAtLeft(); |
|
628
|
|
|
$colmin = Coordinate::columnIndexFromString($repeat[0]) - 1; |
|
629
|
|
|
$colmax = Coordinate::columnIndexFromString($repeat[1]) - 1; |
|
630
|
|
|
} else { |
|
631
|
2 |
|
$colmin = 0; |
|
632
|
2 |
|
$colmax = 255; |
|
633
|
|
|
} |
|
634
|
|
|
// Rows to repeat |
|
635
|
2 |
|
if ($sheetSetup->isRowsToRepeatAtTopSet()) { |
|
636
|
2 |
|
$repeat = $sheetSetup->getRowsToRepeatAtTop(); |
|
637
|
2 |
|
$rowmin = $repeat[0] - 1; |
|
638
|
2 |
|
$rowmax = $repeat[1] - 1; |
|
639
|
|
|
} else { |
|
640
|
|
|
$rowmin = 0; |
|
641
|
|
|
$rowmax = 65535; |
|
642
|
|
|
} |
|
643
|
|
|
|
|
644
|
|
|
// construct formula data manually because parser does not recognize absolute 3d cell references |
|
645
|
2 |
|
$formulaData = pack('Cvvvvv', 0x3B, $i, $rowmin, $rowmax, $colmin, $colmax); |
|
646
|
|
|
|
|
647
|
|
|
// store the DEFINEDNAME record |
|
648
|
2 |
|
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x07), $formulaData, $i + 1, true)); |
|
649
|
|
|
} |
|
650
|
|
|
} |
|
651
|
|
|
|
|
652
|
|
|
// write the print areas, if any |
|
653
|
109 |
|
for ($i = 0; $i < $total_worksheets; ++$i) { |
|
654
|
109 |
|
$sheetSetup = $this->spreadsheet->getSheet($i)->getPageSetup(); |
|
655
|
109 |
|
if ($sheetSetup->isPrintAreaSet()) { |
|
656
|
|
|
// Print area, e.g. A3:J6,H1:X20 |
|
657
|
4 |
|
$printArea = Coordinate::splitRange($sheetSetup->getPrintArea()); |
|
658
|
4 |
|
$countPrintArea = count($printArea); |
|
659
|
|
|
|
|
660
|
4 |
|
$formulaData = ''; |
|
661
|
4 |
|
for ($j = 0; $j < $countPrintArea; ++$j) { |
|
662
|
4 |
|
$printAreaRect = $printArea[$j]; // e.g. A3:J6 |
|
663
|
4 |
|
$printAreaRect[0] = Coordinate::indexesFromString($printAreaRect[0]); |
|
664
|
4 |
|
$printAreaRect[1] = Coordinate::indexesFromString($printAreaRect[1]); |
|
665
|
|
|
|
|
666
|
4 |
|
$print_rowmin = $printAreaRect[0][1] - 1; |
|
667
|
4 |
|
$print_rowmax = $printAreaRect[1][1] - 1; |
|
668
|
4 |
|
$print_colmin = $printAreaRect[0][0] - 1; |
|
669
|
4 |
|
$print_colmax = $printAreaRect[1][0] - 1; |
|
670
|
|
|
|
|
671
|
|
|
// construct formula data manually because parser does not recognize absolute 3d cell references |
|
672
|
4 |
|
$formulaData .= pack('Cvvvvv', 0x3B, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); |
|
673
|
|
|
|
|
674
|
4 |
|
if ($j > 0) { |
|
675
|
1 |
|
$formulaData .= pack('C', 0x10); // list operator token ',' |
|
676
|
|
|
} |
|
677
|
|
|
} |
|
678
|
|
|
|
|
679
|
|
|
// store the DEFINEDNAME record |
|
680
|
4 |
|
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x06), $formulaData, $i + 1, true)); |
|
681
|
|
|
} |
|
682
|
|
|
} |
|
683
|
|
|
|
|
684
|
|
|
// write autofilters, if any |
|
685
|
109 |
|
for ($i = 0; $i < $total_worksheets; ++$i) { |
|
686
|
109 |
|
$sheetAutoFilter = $this->spreadsheet->getSheet($i)->getAutoFilter(); |
|
687
|
109 |
|
$autoFilterRange = $sheetAutoFilter->getRange(); |
|
688
|
109 |
|
if (!empty($autoFilterRange)) { |
|
689
|
3 |
|
$rangeBounds = Coordinate::rangeBoundaries($autoFilterRange); |
|
690
|
|
|
|
|
691
|
|
|
//Autofilter built in name |
|
692
|
3 |
|
$name = pack('C', 0x0D); |
|
693
|
|
|
|
|
694
|
3 |
|
$chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); |
|
695
|
|
|
} |
|
696
|
|
|
} |
|
697
|
|
|
|
|
698
|
109 |
|
return $chunk; |
|
699
|
|
|
} |
|
700
|
|
|
|
|
701
|
|
|
/** |
|
702
|
|
|
* Write a DEFINEDNAME record for BIFF8 using explicit binary formula data. |
|
703
|
|
|
* |
|
704
|
|
|
* @param string $name The name in UTF-8 |
|
705
|
|
|
* @param string $formulaData The binary formula data |
|
706
|
|
|
* @param int $sheetIndex 1-based sheet index the defined name applies to. 0 = global |
|
707
|
|
|
* @param bool $isBuiltIn Built-in name? |
|
708
|
|
|
* |
|
709
|
|
|
* @return string Complete binary record data |
|
710
|
|
|
*/ |
|
711
|
10 |
|
private function writeDefinedNameBiff8(string $name, string $formulaData, int $sheetIndex = 0, bool $isBuiltIn = false): string |
|
712
|
|
|
{ |
|
713
|
10 |
|
$record = 0x0018; |
|
714
|
|
|
|
|
715
|
|
|
// option flags |
|
716
|
10 |
|
$options = $isBuiltIn ? 0x20 : 0x00; |
|
717
|
|
|
|
|
718
|
|
|
// length of the name, character count |
|
719
|
10 |
|
$nlen = StringHelper::countCharacters($name); |
|
720
|
|
|
|
|
721
|
|
|
// name with stripped length field |
|
722
|
10 |
|
$name = substr(StringHelper::UTF8toBIFF8UnicodeLong($name), 2); |
|
723
|
|
|
|
|
724
|
|
|
// size of the formula (in bytes) |
|
725
|
10 |
|
$sz = strlen($formulaData); |
|
726
|
|
|
|
|
727
|
|
|
// combine the parts |
|
728
|
10 |
|
$data = pack('vCCvvvCCCC', $options, 0, $nlen, $sz, 0, $sheetIndex, 0, 0, 0, 0) |
|
729
|
10 |
|
. $name . $formulaData; |
|
730
|
10 |
|
$length = strlen($data); |
|
731
|
|
|
|
|
732
|
10 |
|
$header = pack('vv', $record, $length); |
|
733
|
|
|
|
|
734
|
10 |
|
return $header . $data; |
|
735
|
|
|
} |
|
736
|
|
|
|
|
737
|
|
|
/** |
|
738
|
|
|
* Write a short NAME record. |
|
739
|
|
|
* |
|
740
|
|
|
* @param int $sheetIndex 1-based sheet index the defined name applies to. 0 = global |
|
741
|
|
|
* @param int[][] $rangeBounds range boundaries |
|
742
|
|
|
* |
|
743
|
|
|
* @return string Complete binary record data |
|
744
|
|
|
* */ |
|
745
|
3 |
|
private function writeShortNameBiff8(string $name, int $sheetIndex, array $rangeBounds, bool $isHidden = false): string |
|
746
|
|
|
{ |
|
747
|
3 |
|
$record = 0x0018; |
|
748
|
|
|
|
|
749
|
|
|
// option flags |
|
750
|
3 |
|
$options = ($isHidden ? 0x21 : 0x00); |
|
751
|
|
|
|
|
752
|
3 |
|
$extra = pack( |
|
753
|
3 |
|
'Cvvvvv', |
|
754
|
3 |
|
0x3B, |
|
755
|
3 |
|
$sheetIndex - 1, |
|
756
|
3 |
|
$rangeBounds[0][1] - 1, |
|
757
|
3 |
|
$rangeBounds[1][1] - 1, |
|
758
|
3 |
|
$rangeBounds[0][0] - 1, |
|
759
|
3 |
|
$rangeBounds[1][0] - 1 |
|
760
|
3 |
|
); |
|
761
|
|
|
|
|
762
|
|
|
// size of the formula (in bytes) |
|
763
|
3 |
|
$sz = strlen($extra); |
|
764
|
|
|
|
|
765
|
|
|
// combine the parts |
|
766
|
3 |
|
$data = pack('vCCvvvCCCCC', $options, 0, 1, $sz, 0, $sheetIndex, 0, 0, 0, 0, 0) |
|
767
|
3 |
|
. $name . $extra; |
|
768
|
3 |
|
$length = strlen($data); |
|
769
|
|
|
|
|
770
|
3 |
|
$header = pack('vv', $record, $length); |
|
771
|
|
|
|
|
772
|
3 |
|
return $header . $data; |
|
773
|
|
|
} |
|
774
|
|
|
|
|
775
|
|
|
/** |
|
776
|
|
|
* Stores the CODEPAGE biff record. |
|
777
|
|
|
*/ |
|
778
|
109 |
|
private function writeCodepage(): void |
|
779
|
|
|
{ |
|
780
|
109 |
|
$record = 0x0042; // Record identifier |
|
781
|
109 |
|
$length = 0x0002; // Number of bytes to follow |
|
782
|
109 |
|
$cv = $this->codepage; // The code page |
|
783
|
|
|
|
|
784
|
109 |
|
$header = pack('vv', $record, $length); |
|
785
|
109 |
|
$data = pack('v', $cv); |
|
786
|
|
|
|
|
787
|
109 |
|
$this->append($header . $data); |
|
788
|
|
|
} |
|
789
|
|
|
|
|
790
|
|
|
/** |
|
791
|
|
|
* Write Excel BIFF WINDOW1 record. |
|
792
|
|
|
*/ |
|
793
|
109 |
|
private function writeWindow1(): void |
|
794
|
|
|
{ |
|
795
|
109 |
|
$record = 0x003D; // Record identifier |
|
796
|
109 |
|
$length = 0x0012; // Number of bytes to follow |
|
797
|
|
|
|
|
798
|
109 |
|
$xWn = 0x0000; // Horizontal position of window |
|
799
|
109 |
|
$yWn = 0x0000; // Vertical position of window |
|
800
|
109 |
|
$dxWn = 0x25BC; // Width of window |
|
801
|
109 |
|
$dyWn = 0x1572; // Height of window |
|
802
|
|
|
|
|
803
|
109 |
|
$grbit = 0x0038; // Option flags |
|
804
|
|
|
|
|
805
|
|
|
// not supported by PhpSpreadsheet, so there is only one selected sheet, the active |
|
806
|
109 |
|
$ctabsel = 1; // Number of workbook tabs selected |
|
807
|
|
|
|
|
808
|
109 |
|
$wTabRatio = 0x0258; // Tab to scrollbar ratio |
|
809
|
|
|
|
|
810
|
|
|
// not supported by PhpSpreadsheet, set to 0 |
|
811
|
109 |
|
$itabFirst = 0; // 1st displayed worksheet |
|
812
|
109 |
|
$itabCur = $this->spreadsheet->getActiveSheetIndex(); // Active worksheet |
|
813
|
|
|
|
|
814
|
109 |
|
$header = pack('vv', $record, $length); |
|
815
|
109 |
|
$data = pack('vvvvvvvvv', $xWn, $yWn, $dxWn, $dyWn, $grbit, $itabCur, $itabFirst, $ctabsel, $wTabRatio); |
|
816
|
109 |
|
$this->append($header . $data); |
|
817
|
|
|
} |
|
818
|
|
|
|
|
819
|
|
|
/** |
|
820
|
|
|
* Writes Excel BIFF BOUNDSHEET record. |
|
821
|
|
|
* |
|
822
|
|
|
* @param int $offset Location of worksheet BOF |
|
823
|
|
|
*/ |
|
824
|
109 |
|
private function writeBoundSheet(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, int $offset): void |
|
825
|
|
|
{ |
|
826
|
109 |
|
$sheetname = $sheet->getTitle(); |
|
827
|
109 |
|
$record = 0x0085; // Record identifier |
|
828
|
109 |
|
$ss = match ($sheet->getSheetState()) { |
|
829
|
109 |
|
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE => 0x00, |
|
830
|
1 |
|
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN => 0x01, |
|
831
|
1 |
|
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN => 0x02, |
|
832
|
|
|
default => 0x00, |
|
833
|
109 |
|
}; |
|
834
|
|
|
|
|
835
|
|
|
// sheet type |
|
836
|
109 |
|
$st = 0x00; |
|
837
|
|
|
|
|
838
|
|
|
//$grbit = 0x0000; // Visibility and sheet type |
|
839
|
|
|
|
|
840
|
109 |
|
$data = pack('VCC', $offset, $ss, $st); |
|
841
|
109 |
|
$data .= StringHelper::UTF8toBIFF8UnicodeShort($sheetname); |
|
842
|
|
|
|
|
843
|
109 |
|
$length = strlen($data); |
|
844
|
109 |
|
$header = pack('vv', $record, $length); |
|
845
|
109 |
|
$this->append($header . $data); |
|
846
|
|
|
} |
|
847
|
|
|
|
|
848
|
|
|
/** |
|
849
|
|
|
* Write Internal SUPBOOK record. |
|
850
|
|
|
*/ |
|
851
|
109 |
|
private function writeSupbookInternal(): string |
|
852
|
|
|
{ |
|
853
|
109 |
|
$record = 0x01AE; // Record identifier |
|
854
|
109 |
|
$length = 0x0004; // Bytes to follow |
|
855
|
|
|
|
|
856
|
109 |
|
$header = pack('vv', $record, $length); |
|
857
|
109 |
|
$data = pack('vv', $this->spreadsheet->getSheetCount(), 0x0401); |
|
858
|
|
|
|
|
859
|
109 |
|
return $this->writeData($header . $data); |
|
860
|
|
|
} |
|
861
|
|
|
|
|
862
|
|
|
/** |
|
863
|
|
|
* Writes the Excel BIFF EXTERNSHEET record. These references are used by |
|
864
|
|
|
* formulas. |
|
865
|
|
|
*/ |
|
866
|
109 |
|
private function writeExternalsheetBiff8(): string |
|
867
|
|
|
{ |
|
868
|
109 |
|
$totalReferences = count($this->parser->references); |
|
869
|
109 |
|
$record = 0x0017; // Record identifier |
|
870
|
109 |
|
$length = 2 + 6 * $totalReferences; // Number of bytes to follow |
|
871
|
|
|
|
|
872
|
|
|
//$supbook_index = 0; // FIXME: only using internal SUPBOOK record |
|
873
|
109 |
|
$header = pack('vv', $record, $length); |
|
874
|
109 |
|
$data = pack('v', $totalReferences); |
|
875
|
109 |
|
for ($i = 0; $i < $totalReferences; ++$i) { |
|
876
|
109 |
|
$data .= $this->parser->references[$i]; |
|
877
|
|
|
} |
|
878
|
|
|
|
|
879
|
109 |
|
return $this->writeData($header . $data); |
|
880
|
|
|
} |
|
881
|
|
|
|
|
882
|
|
|
/** |
|
883
|
|
|
* Write Excel BIFF STYLE records. |
|
884
|
|
|
*/ |
|
885
|
109 |
|
private function writeStyle(): void |
|
886
|
|
|
{ |
|
887
|
109 |
|
$record = 0x0293; // Record identifier |
|
888
|
109 |
|
$length = 0x0004; // Bytes to follow |
|
889
|
|
|
|
|
890
|
109 |
|
$ixfe = 0x8000; // Index to cell style XF |
|
891
|
109 |
|
$BuiltIn = 0x00; // Built-in style |
|
892
|
109 |
|
$iLevel = 0xFF; // Outline style level |
|
893
|
|
|
|
|
894
|
109 |
|
$header = pack('vv', $record, $length); |
|
895
|
109 |
|
$data = pack('vCC', $ixfe, $BuiltIn, $iLevel); |
|
896
|
109 |
|
$this->append($header . $data); |
|
897
|
|
|
} |
|
898
|
|
|
|
|
899
|
|
|
/** |
|
900
|
|
|
* Writes Excel FORMAT record for non "built-in" numerical formats. |
|
901
|
|
|
* |
|
902
|
|
|
* @param string $format Custom format string |
|
903
|
|
|
* @param int $ifmt Format index code |
|
904
|
|
|
*/ |
|
905
|
22 |
|
private function writeNumberFormat(string $format, int $ifmt): void |
|
906
|
|
|
{ |
|
907
|
22 |
|
$record = 0x041E; // Record identifier |
|
908
|
|
|
|
|
909
|
22 |
|
$numberFormatString = StringHelper::UTF8toBIFF8UnicodeLong($format); |
|
910
|
22 |
|
$length = 2 + strlen($numberFormatString); // Number of bytes to follow |
|
911
|
|
|
|
|
912
|
22 |
|
$header = pack('vv', $record, $length); |
|
913
|
22 |
|
$data = pack('v', $ifmt) . $numberFormatString; |
|
914
|
22 |
|
$this->append($header . $data); |
|
915
|
|
|
} |
|
916
|
|
|
|
|
917
|
|
|
/** |
|
918
|
|
|
* Write DATEMODE record to indicate the date system in use (1904 or 1900). |
|
919
|
|
|
*/ |
|
920
|
109 |
|
private function writeDateMode(): void |
|
921
|
|
|
{ |
|
922
|
109 |
|
$record = 0x0022; // Record identifier |
|
923
|
109 |
|
$length = 0x0002; // Bytes to follow |
|
924
|
|
|
|
|
925
|
109 |
|
$f1904 = ($this->spreadsheet->getExcelCalendar() === Date::CALENDAR_MAC_1904) |
|
926
|
|
|
? 1 // Flag for 1904 date system |
|
927
|
109 |
|
: 0; // Flag for 1900 date system |
|
928
|
|
|
|
|
929
|
109 |
|
$header = pack('vv', $record, $length); |
|
930
|
109 |
|
$data = pack('v', $f1904); |
|
931
|
109 |
|
$this->append($header . $data); |
|
932
|
|
|
} |
|
933
|
|
|
|
|
934
|
|
|
/** |
|
935
|
|
|
* Stores the COUNTRY record for localization. |
|
936
|
|
|
*/ |
|
937
|
|
|
private function writeCountry(): string |
|
938
|
|
|
{ |
|
939
|
|
|
$record = 0x008C; // Record identifier |
|
940
|
|
|
$length = 4; // Number of bytes to follow |
|
941
|
|
|
|
|
942
|
|
|
$header = pack('vv', $record, $length); |
|
943
|
|
|
// using the same country code always for simplicity |
|
944
|
|
|
$data = pack('vv', $this->countryCode, $this->countryCode); |
|
945
|
|
|
|
|
946
|
|
|
return $this->writeData($header . $data); |
|
947
|
|
|
} |
|
948
|
|
|
|
|
949
|
|
|
/** |
|
950
|
|
|
* Write the RECALCID record. |
|
951
|
|
|
*/ |
|
952
|
109 |
|
private function writeRecalcId(): string |
|
953
|
|
|
{ |
|
954
|
109 |
|
$record = 0x01C1; // Record identifier |
|
955
|
109 |
|
$length = 8; // Number of bytes to follow |
|
956
|
|
|
|
|
957
|
109 |
|
$header = pack('vv', $record, $length); |
|
958
|
|
|
|
|
959
|
|
|
// by inspection of real Excel files, MS Office Excel 2007 writes this |
|
960
|
109 |
|
$data = pack('VV', 0x000001C1, 0x00001E667); |
|
961
|
|
|
|
|
962
|
109 |
|
return $this->writeData($header . $data); |
|
963
|
|
|
} |
|
964
|
|
|
|
|
965
|
|
|
/** |
|
966
|
|
|
* Stores the PALETTE biff record. |
|
967
|
|
|
*/ |
|
968
|
109 |
|
private function writePalette(): void |
|
969
|
|
|
{ |
|
970
|
109 |
|
$aref = $this->palette; |
|
971
|
|
|
|
|
972
|
109 |
|
$record = 0x0092; // Record identifier |
|
973
|
109 |
|
$length = 2 + 4 * count($aref); // Number of bytes to follow |
|
974
|
109 |
|
$ccv = count($aref); // Number of RGB values to follow |
|
975
|
109 |
|
$data = ''; // The RGB data |
|
976
|
|
|
|
|
977
|
|
|
// Pack the RGB data |
|
978
|
109 |
|
foreach ($aref as $color) { |
|
979
|
109 |
|
foreach ($color as $byte) { |
|
980
|
109 |
|
$data .= pack('C', $byte); |
|
981
|
|
|
} |
|
982
|
|
|
} |
|
983
|
|
|
|
|
984
|
109 |
|
$header = pack('vvv', $record, $length, $ccv); |
|
985
|
109 |
|
$this->append($header . $data); |
|
986
|
|
|
} |
|
987
|
|
|
|
|
988
|
|
|
/** |
|
989
|
|
|
* Handling of the SST continue blocks is complicated by the need to include an |
|
990
|
|
|
* additional continuation byte depending on whether the string is split between |
|
991
|
|
|
* blocks or whether it starts at the beginning of the block. (There are also |
|
992
|
|
|
* additional complications that will arise later when/if Rich Strings are |
|
993
|
|
|
* supported). |
|
994
|
|
|
* |
|
995
|
|
|
* The Excel documentation says that the SST record should be followed by an |
|
996
|
|
|
* EXTSST record. The EXTSST record is a hash table that is used to optimise |
|
997
|
|
|
* access to SST. However, despite the documentation it doesn't seem to be |
|
998
|
|
|
* required so we will ignore it. |
|
999
|
|
|
* |
|
1000
|
|
|
* @return string Binary data |
|
1001
|
|
|
*/ |
|
1002
|
109 |
|
private function writeSharedStringsTable(): string |
|
1003
|
|
|
{ |
|
1004
|
|
|
// maximum size of record data (excluding record header) |
|
1005
|
109 |
|
$continue_limit = 8224; |
|
1006
|
|
|
|
|
1007
|
|
|
// initialize array of record data blocks |
|
1008
|
109 |
|
$recordDatas = []; |
|
1009
|
|
|
|
|
1010
|
|
|
// start SST record data block with total number of strings, total number of unique strings |
|
1011
|
109 |
|
$recordData = pack('VV', $this->stringTotal, $this->stringUnique); |
|
1012
|
|
|
|
|
1013
|
|
|
// loop through all (unique) strings in shared strings table |
|
1014
|
109 |
|
foreach (array_keys($this->stringTable) as $string) { |
|
1015
|
|
|
// here $string is a BIFF8 encoded string |
|
1016
|
|
|
|
|
1017
|
|
|
// length = character count |
|
1018
|
72 |
|
$headerinfo = unpack('vlength/Cencoding', $string); |
|
1019
|
|
|
|
|
1020
|
|
|
// currently, this is always 1 = uncompressed |
|
1021
|
72 |
|
$encoding = $headerinfo['encoding'] ?? 1; |
|
1022
|
|
|
|
|
1023
|
|
|
// initialize finished writing current $string |
|
1024
|
72 |
|
$finished = false; |
|
1025
|
|
|
|
|
1026
|
72 |
|
while ($finished === false) { |
|
1027
|
|
|
// normally, there will be only one cycle, but if string cannot immediately be written as is |
|
1028
|
|
|
// there will be need for more than one cylcle, if string longer than one record data block, there |
|
1029
|
|
|
// may be need for even more cycles |
|
1030
|
|
|
|
|
1031
|
72 |
|
if (strlen($recordData) + strlen($string) <= $continue_limit) { |
|
1032
|
|
|
// then we can write the string (or remainder of string) without any problems |
|
1033
|
72 |
|
$recordData .= $string; |
|
1034
|
|
|
|
|
1035
|
72 |
|
if (strlen($recordData) + strlen($string) == $continue_limit) { |
|
1036
|
|
|
// we close the record data block, and initialize a new one |
|
1037
|
|
|
$recordDatas[] = $recordData; |
|
1038
|
|
|
$recordData = ''; |
|
1039
|
|
|
} |
|
1040
|
|
|
|
|
1041
|
|
|
// we are finished writing this string |
|
1042
|
72 |
|
$finished = true; |
|
1043
|
|
|
} else { |
|
1044
|
|
|
// special treatment writing the string (or remainder of the string) |
|
1045
|
|
|
// If the string is very long it may need to be written in more than one CONTINUE record. |
|
1046
|
|
|
|
|
1047
|
|
|
// check how many bytes more there is room for in the current record |
|
1048
|
1 |
|
$space_remaining = $continue_limit - strlen($recordData); |
|
1049
|
|
|
|
|
1050
|
|
|
// minimum space needed |
|
1051
|
|
|
// uncompressed: 2 byte string length length field + 1 byte option flags + 2 byte character |
|
1052
|
|
|
// compressed: 2 byte string length length field + 1 byte option flags + 1 byte character |
|
1053
|
1 |
|
$min_space_needed = ($encoding == 1) ? 5 : 4; |
|
1054
|
|
|
|
|
1055
|
|
|
// We have two cases |
|
1056
|
|
|
// 1. space remaining is less than minimum space needed |
|
1057
|
|
|
// here we must waste the space remaining and move to next record data block |
|
1058
|
|
|
// 2. space remaining is greater than or equal to minimum space needed |
|
1059
|
|
|
// here we write as much as we can in the current block, then move to next record data block |
|
1060
|
|
|
|
|
1061
|
1 |
|
if ($space_remaining < $min_space_needed) { |
|
1062
|
|
|
// 1. space remaining is less than minimum space needed. |
|
1063
|
|
|
// we close the block, store the block data |
|
1064
|
|
|
$recordDatas[] = $recordData; |
|
1065
|
|
|
|
|
1066
|
|
|
// and start new record data block where we start writing the string |
|
1067
|
|
|
$recordData = ''; |
|
1068
|
|
|
} else { |
|
1069
|
|
|
// 2. space remaining is greater than or equal to minimum space needed. |
|
1070
|
|
|
// initialize effective remaining space, for Unicode strings this may need to be reduced by 1, see below |
|
1071
|
1 |
|
$effective_space_remaining = $space_remaining; |
|
1072
|
|
|
|
|
1073
|
|
|
// for uncompressed strings, sometimes effective space remaining is reduced by 1 |
|
1074
|
1 |
|
if ($encoding == 1 && (strlen($string) - $space_remaining) % 2 == 1) { |
|
1075
|
1 |
|
--$effective_space_remaining; |
|
1076
|
|
|
} |
|
1077
|
|
|
|
|
1078
|
|
|
// one block fininshed, store the block data |
|
1079
|
1 |
|
$recordData .= substr($string, 0, $effective_space_remaining); |
|
1080
|
|
|
|
|
1081
|
1 |
|
$string = substr($string, $effective_space_remaining); // for next cycle in while loop |
|
1082
|
1 |
|
$recordDatas[] = $recordData; |
|
1083
|
|
|
|
|
1084
|
|
|
// start new record data block with the repeated option flags |
|
1085
|
1 |
|
$recordData = pack('C', $encoding); |
|
1086
|
|
|
} |
|
1087
|
|
|
} |
|
1088
|
|
|
} |
|
1089
|
|
|
} |
|
1090
|
|
|
|
|
1091
|
|
|
// Store the last record data block unless it is empty |
|
1092
|
|
|
// if there was no need for any continue records, this will be the for SST record data block itself |
|
1093
|
109 |
|
if ($recordData !== '') { |
|
1094
|
109 |
|
$recordDatas[] = $recordData; |
|
1095
|
|
|
} |
|
1096
|
|
|
|
|
1097
|
|
|
// combine into one chunk with all the blocks SST, CONTINUE,... |
|
1098
|
109 |
|
$chunk = ''; |
|
1099
|
109 |
|
foreach ($recordDatas as $i => $recordData) { |
|
1100
|
|
|
// first block should have the SST record header, remaing should have CONTINUE header |
|
1101
|
109 |
|
$record = ($i == 0) ? 0x00FC : 0x003C; |
|
1102
|
|
|
|
|
1103
|
109 |
|
$header = pack('vv', $record, strlen($recordData)); |
|
1104
|
109 |
|
$data = $header . $recordData; |
|
1105
|
|
|
|
|
1106
|
109 |
|
$chunk .= $this->writeData($data); |
|
1107
|
|
|
} |
|
1108
|
|
|
|
|
1109
|
109 |
|
return $chunk; |
|
1110
|
|
|
} |
|
1111
|
|
|
|
|
1112
|
|
|
/** |
|
1113
|
|
|
* Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records. |
|
1114
|
|
|
*/ |
|
1115
|
109 |
|
private function writeMsoDrawingGroup(): string |
|
1116
|
|
|
{ |
|
1117
|
|
|
// write the Escher stream if necessary |
|
1118
|
109 |
|
if (isset($this->escher)) { |
|
1119
|
15 |
|
$writer = new Escher($this->escher); |
|
1120
|
15 |
|
$data = $writer->close(); |
|
1121
|
|
|
|
|
1122
|
15 |
|
$record = 0x00EB; |
|
1123
|
15 |
|
$length = strlen($data); |
|
1124
|
15 |
|
$header = pack('vv', $record, $length); |
|
1125
|
|
|
|
|
1126
|
15 |
|
return $this->writeData($header . $data); |
|
1127
|
|
|
} |
|
1128
|
|
|
|
|
1129
|
94 |
|
return ''; |
|
1130
|
|
|
} |
|
1131
|
|
|
|
|
1132
|
|
|
/** |
|
1133
|
|
|
* Get Escher object. |
|
1134
|
|
|
*/ |
|
1135
|
|
|
public function getEscher(): ?\PhpOffice\PhpSpreadsheet\Shared\Escher |
|
1136
|
|
|
{ |
|
1137
|
|
|
return $this->escher; |
|
1138
|
|
|
} |
|
1139
|
|
|
|
|
1140
|
|
|
/** |
|
1141
|
|
|
* Set Escher object. |
|
1142
|
|
|
*/ |
|
1143
|
15 |
|
public function setEscher(?\PhpOffice\PhpSpreadsheet\Shared\Escher $escher): void |
|
1144
|
|
|
{ |
|
1145
|
15 |
|
$this->escher = $escher; |
|
1146
|
|
|
} |
|
1147
|
|
|
} |
|
1148
|
|
|
|
PHPOffice /
PhpSpreadsheet
