|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Reader\Xls; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\NamedRange; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xls; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\CodePage; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Escher as SharedEscher; |
|
11
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Escher\DgContainer\SpgrContainer\SpContainer; |
|
12
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Escher\DggContainer\BstoreContainer\BSE; |
|
13
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Xls as SharedXls; |
|
14
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
15
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing; |
|
16
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
|
17
|
|
|
|
|
18
|
|
|
class LoadSpreadsheet extends Xls |
|
19
|
|
|
{ |
|
20
|
|
|
/** |
|
21
|
|
|
* Loads PhpSpreadsheet from file. |
|
22
|
|
|
*/ |
|
23
|
|
|
protected function loadSpreadsheetFromFile2(string $filename, Xls $xls): Spreadsheet |
|
24
|
|
|
{ |
|
25
|
|
|
// Read the OLE file |
|
26
|
|
|
$xls->loadOLE($filename); |
|
27
|
|
|
|
|
28
|
|
|
// Initialisations |
|
29
|
|
|
$xls->spreadsheet = new Spreadsheet(); |
|
30
|
|
|
$xls->spreadsheet->removeSheetByIndex(0); // remove 1st sheet |
|
31
|
|
|
if (!$xls->readDataOnly) { |
|
32
|
|
|
$xls->spreadsheet->removeCellStyleXfByIndex(0); // remove the default style |
|
33
|
|
|
$xls->spreadsheet->removeCellXfByIndex(0); // remove the default style |
|
34
|
|
|
} |
|
35
|
|
|
|
|
36
|
|
|
// Read the summary information stream (containing meta data) |
|
37
|
|
|
$xls->readSummaryInformation(); |
|
38
|
|
|
|
|
39
|
|
|
// Read the Additional document summary information stream (containing application-specific meta data) |
|
40
|
|
|
$xls->readDocumentSummaryInformation(); |
|
41
|
|
|
|
|
42
|
|
|
// total byte size of Excel data (workbook global substream + sheet substreams) |
|
43
|
|
|
$xls->dataSize = strlen($xls->data); |
|
44
|
|
|
|
|
45
|
|
|
// initialize |
|
46
|
|
|
$xls->pos = 0; |
|
47
|
|
|
$xls->codepage = $xls->codepage ?: CodePage::DEFAULT_CODE_PAGE; |
|
48
|
|
|
$xls->formats = []; |
|
49
|
|
|
$xls->objFonts = []; |
|
50
|
|
|
$xls->palette = []; |
|
51
|
|
|
$xls->sheets = []; |
|
52
|
|
|
$xls->externalBooks = []; |
|
53
|
|
|
$xls->ref = []; |
|
54
|
|
|
$xls->definedname = []; |
|
55
|
|
|
$xls->sst = []; |
|
56
|
|
|
$xls->drawingGroupData = ''; |
|
57
|
|
|
$xls->xfIndex = 0; |
|
58
|
|
|
$xls->mapCellXfIndex = []; |
|
59
|
|
|
$xls->mapCellStyleXfIndex = []; |
|
60
|
|
|
|
|
61
|
|
|
// Parse Workbook Global Substream |
|
62
|
|
|
while ($xls->pos < $xls->dataSize) { |
|
63
|
|
|
$code = self::getUInt2d($xls->data, $xls->pos); |
|
64
|
|
|
|
|
65
|
|
|
match ($code) { |
|
66
|
|
|
self::XLS_TYPE_BOF => $xls->readBof(), |
|
67
|
|
|
self::XLS_TYPE_FILEPASS => $xls->readFilepass(), |
|
68
|
|
|
self::XLS_TYPE_CODEPAGE => $xls->readCodepage(), |
|
69
|
|
|
self::XLS_TYPE_DATEMODE => $xls->readDateMode(), |
|
70
|
|
|
self::XLS_TYPE_FONT => $xls->readFont(), |
|
71
|
|
|
self::XLS_TYPE_FORMAT => $xls->readFormat(), |
|
72
|
|
|
self::XLS_TYPE_XF => $xls->readXf(), |
|
73
|
|
|
self::XLS_TYPE_XFEXT => $xls->readXfExt(), |
|
74
|
|
|
self::XLS_TYPE_STYLE => $xls->readStyle(), |
|
75
|
|
|
self::XLS_TYPE_PALETTE => $xls->readPalette(), |
|
76
|
|
|
self::XLS_TYPE_SHEET => $xls->readSheet(), |
|
77
|
|
|
self::XLS_TYPE_EXTERNALBOOK => $xls->readExternalBook(), |
|
78
|
|
|
self::XLS_TYPE_EXTERNNAME => $xls->readExternName(), |
|
79
|
|
|
self::XLS_TYPE_EXTERNSHEET => $xls->readExternSheet(), |
|
80
|
|
|
self::XLS_TYPE_DEFINEDNAME => $xls->readDefinedName(), |
|
81
|
|
|
self::XLS_TYPE_MSODRAWINGGROUP => $xls->readMsoDrawingGroup(), |
|
82
|
|
|
self::XLS_TYPE_SST => $xls->readSst(), |
|
83
|
|
|
self::XLS_TYPE_EOF => $xls->readDefault(), |
|
84
|
|
|
default => $xls->readDefault(), |
|
85
|
|
|
}; |
|
86
|
|
|
|
|
87
|
|
|
if ($code === self::XLS_TYPE_EOF) { |
|
88
|
|
|
break; |
|
89
|
|
|
} |
|
90
|
|
|
} |
|
91
|
|
|
|
|
92
|
|
|
// Resolve indexed colors for font, fill, and border colors |
|
93
|
|
|
// Cannot be resolved already in XF record, because PALETTE record comes afterwards |
|
94
|
|
|
if (!$xls->readDataOnly) { |
|
95
|
|
|
foreach ($xls->objFonts as $objFont) { |
|
96
|
|
|
if (isset($objFont->colorIndex)) { |
|
97
|
|
|
$color = Color::map($objFont->colorIndex, $xls->palette, $xls->version); |
|
98
|
|
|
$objFont->getColor()->setRGB($color['rgb']); |
|
99
|
|
|
} |
|
100
|
|
|
} |
|
101
|
|
|
|
|
102
|
|
|
foreach ($xls->spreadsheet->getCellXfCollection() as $objStyle) { |
|
103
|
|
|
// fill start and end color |
|
104
|
|
|
$fill = $objStyle->getFill(); |
|
105
|
|
|
|
|
106
|
|
|
if (isset($fill->startcolorIndex)) { |
|
107
|
|
|
$startColor = Color::map($fill->startcolorIndex, $xls->palette, $xls->version); |
|
108
|
|
|
$fill->getStartColor()->setRGB($startColor['rgb']); |
|
109
|
|
|
} |
|
110
|
|
|
if (isset($fill->endcolorIndex)) { |
|
111
|
|
|
$endColor = Color::map($fill->endcolorIndex, $xls->palette, $xls->version); |
|
112
|
|
|
$fill->getEndColor()->setRGB($endColor['rgb']); |
|
113
|
|
|
} |
|
114
|
|
|
|
|
115
|
|
|
// border colors |
|
116
|
|
|
$top = $objStyle->getBorders()->getTop(); |
|
117
|
|
|
$right = $objStyle->getBorders()->getRight(); |
|
118
|
|
|
$bottom = $objStyle->getBorders()->getBottom(); |
|
119
|
|
|
$left = $objStyle->getBorders()->getLeft(); |
|
120
|
|
|
$diagonal = $objStyle->getBorders()->getDiagonal(); |
|
121
|
|
|
|
|
122
|
|
|
if (isset($top->colorIndex)) { |
|
123
|
|
|
$borderTopColor = Color::map($top->colorIndex, $xls->palette, $xls->version); |
|
124
|
|
|
$top->getColor()->setRGB($borderTopColor['rgb']); |
|
125
|
|
|
} |
|
126
|
|
|
if (isset($right->colorIndex)) { |
|
127
|
|
|
$borderRightColor = Color::map($right->colorIndex, $xls->palette, $xls->version); |
|
128
|
|
|
$right->getColor()->setRGB($borderRightColor['rgb']); |
|
129
|
|
|
} |
|
130
|
|
|
if (isset($bottom->colorIndex)) { |
|
131
|
|
|
$borderBottomColor = Color::map($bottom->colorIndex, $xls->palette, $xls->version); |
|
132
|
|
|
$bottom->getColor()->setRGB($borderBottomColor['rgb']); |
|
133
|
|
|
} |
|
134
|
|
|
if (isset($left->colorIndex)) { |
|
135
|
|
|
$borderLeftColor = Color::map($left->colorIndex, $xls->palette, $xls->version); |
|
136
|
|
|
$left->getColor()->setRGB($borderLeftColor['rgb']); |
|
137
|
|
|
} |
|
138
|
|
|
if (isset($diagonal->colorIndex)) { |
|
139
|
|
|
$borderDiagonalColor = Color::map($diagonal->colorIndex, $xls->palette, $xls->version); |
|
140
|
|
|
$diagonal->getColor()->setRGB($borderDiagonalColor['rgb']); |
|
141
|
|
|
} |
|
142
|
|
|
} |
|
143
|
|
|
} |
|
144
|
|
|
|
|
145
|
|
|
// treat MSODRAWINGGROUP records, workbook-level Escher |
|
146
|
|
|
$escherWorkbook = null; |
|
147
|
|
|
if (!$xls->readDataOnly && $xls->drawingGroupData) { |
|
148
|
|
|
$escher = new SharedEscher(); |
|
149
|
|
|
$reader = new Escher($escher); |
|
150
|
|
|
$escherWorkbook = $reader->load($xls->drawingGroupData); |
|
151
|
|
|
} |
|
152
|
|
|
|
|
153
|
|
|
// Parse the individual sheets |
|
154
|
|
|
$xls->activeSheetSet = false; |
|
155
|
|
|
foreach ($xls->sheets as $sheet) { |
|
156
|
|
|
$selectedCells = ''; |
|
157
|
|
|
if ($sheet['sheetType'] != 0x00) { |
|
158
|
|
|
// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module |
|
159
|
|
|
continue; |
|
160
|
|
|
} |
|
161
|
|
|
|
|
162
|
|
|
// check if sheet should be skipped |
|
163
|
|
|
if (isset($xls->loadSheetsOnly) && !in_array($sheet['name'], $xls->loadSheetsOnly)) { |
|
164
|
|
|
continue; |
|
165
|
|
|
} |
|
166
|
|
|
|
|
167
|
|
|
// add sheet to PhpSpreadsheet object |
|
168
|
|
|
$xls->phpSheet = $xls->spreadsheet->createSheet(); |
|
169
|
|
|
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula |
|
170
|
|
|
// cells... during the load, all formulae should be correct, and we're simply bringing the worksheet |
|
171
|
|
|
// name in line with the formula, not the reverse |
|
172
|
|
|
$xls->phpSheet->setTitle($sheet['name'], false, false); |
|
173
|
|
|
$xls->phpSheet->setSheetState($sheet['sheetState']); |
|
174
|
|
|
|
|
175
|
|
|
$xls->pos = $sheet['offset']; |
|
176
|
|
|
|
|
177
|
|
|
// Initialize isFitToPages. May change after reading SHEETPR record. |
|
178
|
|
|
$xls->isFitToPages = false; |
|
179
|
|
|
|
|
180
|
|
|
// Initialize drawingData |
|
181
|
|
|
$xls->drawingData = ''; |
|
182
|
|
|
|
|
183
|
|
|
// Initialize objs |
|
184
|
|
|
$xls->objs = []; |
|
185
|
|
|
|
|
186
|
|
|
// Initialize shared formula parts |
|
187
|
|
|
$xls->sharedFormulaParts = []; |
|
188
|
|
|
|
|
189
|
|
|
// Initialize shared formulas |
|
190
|
|
|
$xls->sharedFormulas = []; |
|
191
|
|
|
|
|
192
|
|
|
// Initialize text objs |
|
193
|
|
|
$xls->textObjects = []; |
|
194
|
|
|
|
|
195
|
|
|
// Initialize cell annotations |
|
196
|
|
|
$xls->cellNotes = []; |
|
197
|
|
|
$xls->textObjRef = -1; |
|
198
|
|
|
|
|
199
|
|
|
while ($xls->pos <= $xls->dataSize - 4) { |
|
200
|
|
|
$code = self::getUInt2d($xls->data, $xls->pos); |
|
201
|
|
|
|
|
202
|
|
|
switch ($code) { |
|
203
|
|
|
case self::XLS_TYPE_BOF: |
|
204
|
|
|
$xls->readBof(); |
|
205
|
|
|
|
|
206
|
|
|
break; |
|
207
|
|
|
case self::XLS_TYPE_PRINTGRIDLINES: |
|
208
|
|
|
$xls->readPrintGridlines(); |
|
209
|
|
|
|
|
210
|
|
|
break; |
|
211
|
|
|
case self::XLS_TYPE_DEFAULTROWHEIGHT: |
|
212
|
|
|
$xls->readDefaultRowHeight(); |
|
213
|
|
|
|
|
214
|
|
|
break; |
|
215
|
|
|
case self::XLS_TYPE_SHEETPR: |
|
216
|
|
|
$xls->readSheetPr(); |
|
217
|
|
|
|
|
218
|
|
|
break; |
|
219
|
|
|
case self::XLS_TYPE_HORIZONTALPAGEBREAKS: |
|
220
|
|
|
$xls->readHorizontalPageBreaks(); |
|
221
|
|
|
|
|
222
|
|
|
break; |
|
223
|
|
|
case self::XLS_TYPE_VERTICALPAGEBREAKS: |
|
224
|
|
|
$xls->readVerticalPageBreaks(); |
|
225
|
|
|
|
|
226
|
|
|
break; |
|
227
|
|
|
case self::XLS_TYPE_HEADER: |
|
228
|
|
|
$xls->readHeader(); |
|
229
|
|
|
|
|
230
|
|
|
break; |
|
231
|
|
|
case self::XLS_TYPE_FOOTER: |
|
232
|
|
|
$xls->readFooter(); |
|
233
|
|
|
|
|
234
|
|
|
break; |
|
235
|
|
|
case self::XLS_TYPE_HCENTER: |
|
236
|
|
|
$xls->readHcenter(); |
|
237
|
|
|
|
|
238
|
|
|
break; |
|
239
|
|
|
case self::XLS_TYPE_VCENTER: |
|
240
|
|
|
$xls->readVcenter(); |
|
241
|
|
|
|
|
242
|
|
|
break; |
|
243
|
|
|
case self::XLS_TYPE_LEFTMARGIN: |
|
244
|
|
|
$xls->readLeftMargin(); |
|
245
|
|
|
|
|
246
|
|
|
break; |
|
247
|
|
|
case self::XLS_TYPE_RIGHTMARGIN: |
|
248
|
|
|
$xls->readRightMargin(); |
|
249
|
|
|
|
|
250
|
|
|
break; |
|
251
|
|
|
case self::XLS_TYPE_TOPMARGIN: |
|
252
|
|
|
$xls->readTopMargin(); |
|
253
|
|
|
|
|
254
|
|
|
break; |
|
255
|
|
|
case self::XLS_TYPE_BOTTOMMARGIN: |
|
256
|
|
|
$xls->readBottomMargin(); |
|
257
|
|
|
|
|
258
|
|
|
break; |
|
259
|
|
|
case self::XLS_TYPE_PAGESETUP: |
|
260
|
|
|
$xls->readPageSetup(); |
|
261
|
|
|
|
|
262
|
|
|
break; |
|
263
|
|
|
case self::XLS_TYPE_PROTECT: |
|
264
|
|
|
$xls->readProtect(); |
|
265
|
|
|
|
|
266
|
|
|
break; |
|
267
|
|
|
case self::XLS_TYPE_SCENPROTECT: |
|
268
|
|
|
$xls->readScenProtect(); |
|
269
|
|
|
|
|
270
|
|
|
break; |
|
271
|
|
|
case self::XLS_TYPE_OBJECTPROTECT: |
|
272
|
|
|
$xls->readObjectProtect(); |
|
273
|
|
|
|
|
274
|
|
|
break; |
|
275
|
|
|
case self::XLS_TYPE_PASSWORD: |
|
276
|
|
|
$xls->readPassword(); |
|
277
|
|
|
|
|
278
|
|
|
break; |
|
279
|
|
|
case self::XLS_TYPE_DEFCOLWIDTH: |
|
280
|
|
|
$xls->readDefColWidth(); |
|
281
|
|
|
|
|
282
|
|
|
break; |
|
283
|
|
|
case self::XLS_TYPE_COLINFO: |
|
284
|
|
|
$xls->readColInfo(); |
|
285
|
|
|
|
|
286
|
|
|
break; |
|
287
|
|
|
case self::XLS_TYPE_DIMENSION: |
|
288
|
|
|
$xls->readDefault(); |
|
289
|
|
|
|
|
290
|
|
|
break; |
|
291
|
|
|
case self::XLS_TYPE_ROW: |
|
292
|
|
|
$xls->readRow(); |
|
293
|
|
|
|
|
294
|
|
|
break; |
|
295
|
|
|
case self::XLS_TYPE_DBCELL: |
|
296
|
|
|
$xls->readDefault(); |
|
297
|
|
|
|
|
298
|
|
|
break; |
|
299
|
|
|
case self::XLS_TYPE_RK: |
|
300
|
|
|
$xls->readRk(); |
|
301
|
|
|
|
|
302
|
|
|
break; |
|
303
|
|
|
case self::XLS_TYPE_LABELSST: |
|
304
|
|
|
$xls->readLabelSst(); |
|
305
|
|
|
|
|
306
|
|
|
break; |
|
307
|
|
|
case self::XLS_TYPE_MULRK: |
|
308
|
|
|
$xls->readMulRk(); |
|
309
|
|
|
|
|
310
|
|
|
break; |
|
311
|
|
|
case self::XLS_TYPE_NUMBER: |
|
312
|
|
|
$xls->readNumber(); |
|
313
|
|
|
|
|
314
|
|
|
break; |
|
315
|
|
|
case self::XLS_TYPE_FORMULA: |
|
316
|
|
|
$xls->readFormula(); |
|
317
|
|
|
|
|
318
|
|
|
break; |
|
319
|
|
|
case self::XLS_TYPE_SHAREDFMLA: |
|
320
|
|
|
$xls->readSharedFmla(); |
|
321
|
|
|
|
|
322
|
|
|
break; |
|
323
|
|
|
case self::XLS_TYPE_BOOLERR: |
|
324
|
|
|
$xls->readBoolErr(); |
|
325
|
|
|
|
|
326
|
|
|
break; |
|
327
|
|
|
case self::XLS_TYPE_MULBLANK: |
|
328
|
|
|
$xls->readMulBlank(); |
|
329
|
|
|
|
|
330
|
|
|
break; |
|
331
|
|
|
case self::XLS_TYPE_LABEL: |
|
332
|
|
|
$xls->readLabel(); |
|
333
|
|
|
|
|
334
|
|
|
break; |
|
335
|
|
|
case self::XLS_TYPE_BLANK: |
|
336
|
|
|
$xls->readBlank(); |
|
337
|
|
|
|
|
338
|
|
|
break; |
|
339
|
|
|
case self::XLS_TYPE_MSODRAWING: |
|
340
|
|
|
$xls->readMsoDrawing(); |
|
341
|
|
|
|
|
342
|
|
|
break; |
|
343
|
|
|
case self::XLS_TYPE_OBJ: |
|
344
|
|
|
$xls->readObj(); |
|
345
|
|
|
|
|
346
|
|
|
break; |
|
347
|
|
|
case self::XLS_TYPE_WINDOW2: |
|
348
|
|
|
$xls->readWindow2(); |
|
349
|
|
|
|
|
350
|
|
|
break; |
|
351
|
|
|
case self::XLS_TYPE_PAGELAYOUTVIEW: |
|
352
|
|
|
$xls->readPageLayoutView(); |
|
353
|
|
|
|
|
354
|
|
|
break; |
|
355
|
|
|
case self::XLS_TYPE_SCL: |
|
356
|
|
|
$xls->readScl(); |
|
357
|
|
|
|
|
358
|
|
|
break; |
|
359
|
|
|
case self::XLS_TYPE_PANE: |
|
360
|
|
|
$xls->readPane(); |
|
361
|
|
|
|
|
362
|
|
|
break; |
|
363
|
|
|
case self::XLS_TYPE_SELECTION: |
|
364
|
|
|
$selectedCells = $xls->readSelection(); |
|
365
|
|
|
|
|
366
|
|
|
break; |
|
367
|
|
|
case self::XLS_TYPE_MERGEDCELLS: |
|
368
|
|
|
$xls->readMergedCells(); |
|
369
|
|
|
|
|
370
|
|
|
break; |
|
371
|
|
|
case self::XLS_TYPE_HYPERLINK: |
|
372
|
|
|
$xls->readHyperLink(); |
|
373
|
|
|
|
|
374
|
|
|
break; |
|
375
|
|
|
case self::XLS_TYPE_DATAVALIDATIONS: |
|
376
|
|
|
$xls->readDataValidations(); |
|
377
|
|
|
|
|
378
|
|
|
break; |
|
379
|
|
|
case self::XLS_TYPE_DATAVALIDATION: |
|
380
|
|
|
$xls->readDataValidation(); |
|
381
|
|
|
|
|
382
|
|
|
break; |
|
383
|
|
|
case self::XLS_TYPE_CFHEADER: |
|
384
|
|
|
$cellRangeAddresses = $xls->readCFHeader(); |
|
385
|
|
|
|
|
386
|
|
|
break; |
|
387
|
|
|
case self::XLS_TYPE_CFRULE: |
|
388
|
|
|
$xls->readCFRule($cellRangeAddresses ?? []); |
|
389
|
|
|
|
|
390
|
|
|
break; |
|
391
|
|
|
case self::XLS_TYPE_SHEETLAYOUT: |
|
392
|
|
|
$xls->readSheetLayout(); |
|
393
|
|
|
|
|
394
|
|
|
break; |
|
395
|
|
|
case self::XLS_TYPE_SHEETPROTECTION: |
|
396
|
|
|
$xls->readSheetProtection(); |
|
397
|
|
|
|
|
398
|
|
|
break; |
|
399
|
|
|
case self::XLS_TYPE_RANGEPROTECTION: |
|
400
|
|
|
$xls->readRangeProtection(); |
|
401
|
|
|
|
|
402
|
|
|
break; |
|
403
|
|
|
case self::XLS_TYPE_NOTE: |
|
404
|
|
|
$xls->readNote(); |
|
405
|
|
|
|
|
406
|
|
|
break; |
|
407
|
|
|
case self::XLS_TYPE_TXO: |
|
408
|
|
|
$xls->readTextObject(); |
|
409
|
|
|
|
|
410
|
|
|
break; |
|
411
|
|
|
case self::XLS_TYPE_CONTINUE: |
|
412
|
|
|
$xls->readContinue(); |
|
413
|
|
|
|
|
414
|
|
|
break; |
|
415
|
|
|
case self::XLS_TYPE_EOF: |
|
416
|
|
|
$xls->readDefault(); |
|
417
|
|
|
|
|
418
|
|
|
break 2; |
|
419
|
|
|
default: |
|
420
|
|
|
$xls->readDefault(); |
|
421
|
|
|
|
|
422
|
|
|
break; |
|
423
|
|
|
} |
|
424
|
|
|
} |
|
425
|
|
|
|
|
426
|
|
|
// treat MSODRAWING records, sheet-level Escher |
|
427
|
|
|
if (!$xls->readDataOnly && $xls->drawingData) { |
|
428
|
|
|
$escherWorksheet = new SharedEscher(); |
|
429
|
|
|
$reader = new Escher($escherWorksheet); |
|
430
|
|
|
$escherWorksheet = $reader->load($xls->drawingData); |
|
431
|
|
|
|
|
432
|
|
|
// get all spContainers in one long array, so they can be mapped to OBJ records |
|
433
|
|
|
/** @var SpContainer[] $allSpContainers */ |
|
434
|
|
|
$allSpContainers = method_exists($escherWorksheet, 'getDgContainer') ? $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers() : []; |
|
435
|
|
|
} |
|
436
|
|
|
|
|
437
|
|
|
// treat OBJ records |
|
438
|
|
|
foreach ($xls->objs as $n => $obj) { |
|
439
|
|
|
// the first shape container never has a corresponding OBJ record, hence $n + 1 |
|
440
|
|
|
if (isset($allSpContainers[$n + 1])) { |
|
441
|
|
|
$spContainer = $allSpContainers[$n + 1]; |
|
442
|
|
|
|
|
443
|
|
|
// we skip all spContainers that are a part of a group shape since we cannot yet handle those |
|
444
|
|
|
if ($spContainer->getNestingLevel() > 1) { |
|
445
|
|
|
continue; |
|
446
|
|
|
} |
|
447
|
|
|
|
|
448
|
|
|
// calculate the width and height of the shape |
|
449
|
|
|
/** @var int $startRow */ |
|
450
|
|
|
[$startColumn, $startRow] = Coordinate::coordinateFromString($spContainer->getStartCoordinates()); |
|
451
|
|
|
/** @var int $endRow */ |
|
452
|
|
|
[$endColumn, $endRow] = Coordinate::coordinateFromString($spContainer->getEndCoordinates()); |
|
453
|
|
|
|
|
454
|
|
|
$startOffsetX = $spContainer->getStartOffsetX(); |
|
455
|
|
|
$startOffsetY = $spContainer->getStartOffsetY(); |
|
456
|
|
|
$endOffsetX = $spContainer->getEndOffsetX(); |
|
457
|
|
|
$endOffsetY = $spContainer->getEndOffsetY(); |
|
458
|
|
|
|
|
459
|
|
|
$width = SharedXls::getDistanceX($xls->phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX); |
|
460
|
|
|
$height = SharedXls::getDistanceY($xls->phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY); |
|
461
|
|
|
|
|
462
|
|
|
// calculate offsetX and offsetY of the shape |
|
463
|
|
|
$offsetX = (int) ($startOffsetX * SharedXls::sizeCol($xls->phpSheet, $startColumn) / 1024); |
|
464
|
|
|
$offsetY = (int) ($startOffsetY * SharedXls::sizeRow($xls->phpSheet, $startRow) / 256); |
|
465
|
|
|
|
|
466
|
|
|
switch ($obj['otObjType']) { |
|
467
|
|
|
case 0x19: |
|
468
|
|
|
// Note |
|
469
|
|
|
if (isset($xls->cellNotes[$obj['idObjID']])) { |
|
470
|
|
|
//$cellNote = $xls->cellNotes[$obj['idObjID']]; |
|
471
|
|
|
|
|
472
|
|
|
if (isset($xls->textObjects[$obj['idObjID']])) { |
|
473
|
|
|
$textObject = $xls->textObjects[$obj['idObjID']]; |
|
474
|
|
|
$xls->cellNotes[$obj['idObjID']]['objTextData'] = $textObject; |
|
475
|
|
|
} |
|
476
|
|
|
} |
|
477
|
|
|
|
|
478
|
|
|
break; |
|
479
|
|
|
case 0x08: |
|
480
|
|
|
// picture |
|
481
|
|
|
// get index to BSE entry (1-based) |
|
482
|
|
|
$BSEindex = $spContainer->getOPT(0x0104); |
|
483
|
|
|
|
|
484
|
|
|
// If there is no BSE Index, we will fail here and other fields are not read. |
|
485
|
|
|
// Fix by checking here. |
|
486
|
|
|
// TODO: Why is there no BSE Index? Is this a new Office Version? Password protected field? |
|
487
|
|
|
// More likely : a uncompatible picture |
|
488
|
|
|
if (!$BSEindex) { |
|
489
|
|
|
continue 2; |
|
490
|
|
|
} |
|
491
|
|
|
|
|
492
|
|
|
if ($escherWorkbook) { |
|
493
|
|
|
$BSECollection = method_exists($escherWorkbook, 'getDggContainer') ? $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection() : []; |
|
494
|
|
|
$BSE = $BSECollection[$BSEindex - 1]; |
|
495
|
|
|
$blipType = $BSE->getBlipType(); |
|
496
|
|
|
|
|
497
|
|
|
// need check because some blip types are not supported by Escher reader such as EMF |
|
498
|
|
|
if ($blip = $BSE->getBlip()) { |
|
499
|
|
|
$ih = imagecreatefromstring($blip->getData()); |
|
500
|
|
|
if ($ih !== false) { |
|
501
|
|
|
$drawing = new MemoryDrawing(); |
|
502
|
|
|
$drawing->setImageResource($ih); |
|
503
|
|
|
|
|
504
|
|
|
// width, height, offsetX, offsetY |
|
505
|
|
|
$drawing->setResizeProportional(false); |
|
506
|
|
|
$drawing->setWidth($width); |
|
507
|
|
|
$drawing->setHeight($height); |
|
508
|
|
|
$drawing->setOffsetX($offsetX); |
|
509
|
|
|
$drawing->setOffsetY($offsetY); |
|
510
|
|
|
|
|
511
|
|
|
switch ($blipType) { |
|
512
|
|
|
case BSE::BLIPTYPE_JPEG: |
|
513
|
|
|
$drawing->setRenderingFunction(MemoryDrawing::RENDERING_JPEG); |
|
514
|
|
|
$drawing->setMimeType(MemoryDrawing::MIMETYPE_JPEG); |
|
515
|
|
|
|
|
516
|
|
|
break; |
|
517
|
|
|
case BSE::BLIPTYPE_PNG: |
|
518
|
|
|
imagealphablending($ih, false); |
|
519
|
|
|
imagesavealpha($ih, true); |
|
520
|
|
|
$drawing->setRenderingFunction(MemoryDrawing::RENDERING_PNG); |
|
521
|
|
|
$drawing->setMimeType(MemoryDrawing::MIMETYPE_PNG); |
|
522
|
|
|
|
|
523
|
|
|
break; |
|
524
|
|
|
} |
|
525
|
|
|
|
|
526
|
|
|
$drawing->setWorksheet($xls->phpSheet); |
|
527
|
|
|
$drawing->setCoordinates($spContainer->getStartCoordinates()); |
|
528
|
|
|
} |
|
529
|
|
|
} |
|
530
|
|
|
} |
|
531
|
|
|
|
|
532
|
|
|
break; |
|
533
|
|
|
default: |
|
534
|
|
|
// other object type |
|
535
|
|
|
break; |
|
536
|
|
|
} |
|
537
|
|
|
} |
|
538
|
|
|
} |
|
539
|
|
|
|
|
540
|
|
|
// treat SHAREDFMLA records |
|
541
|
|
|
if ($xls->version == self::XLS_BIFF8) { |
|
542
|
|
|
foreach ($xls->sharedFormulaParts as $cell => $baseCell) { |
|
543
|
|
|
/** @var int $row */ |
|
544
|
|
|
[$column, $row] = Coordinate::coordinateFromString($cell); |
|
545
|
|
|
if (($xls->getReadFilter() !== null) && $xls->getReadFilter()->readCell($column, $row, $xls->phpSheet->getTitle())) { |
|
546
|
|
|
$formula = $xls->getFormulaFromStructure($xls->sharedFormulas[$baseCell], $cell); |
|
547
|
|
|
$xls->phpSheet->getCell($cell)->setValueExplicit('=' . $formula, DataType::TYPE_FORMULA); |
|
548
|
|
|
} |
|
549
|
|
|
} |
|
550
|
|
|
} |
|
551
|
|
|
|
|
552
|
|
|
if (!empty($xls->cellNotes)) { |
|
553
|
|
|
foreach ($xls->cellNotes as $note => $noteDetails) { |
|
554
|
|
|
if (!isset($noteDetails['objTextData'])) { |
|
555
|
|
|
if (isset($xls->textObjects[$note])) { |
|
556
|
|
|
$textObject = $xls->textObjects[$note]; |
|
557
|
|
|
$noteDetails['objTextData'] = $textObject; |
|
558
|
|
|
} else { |
|
559
|
|
|
$noteDetails['objTextData']['text'] = ''; |
|
560
|
|
|
} |
|
561
|
|
|
} |
|
562
|
|
|
$cellAddress = str_replace('$', '', $noteDetails['cellRef']); |
|
563
|
|
|
$xls->phpSheet->getComment($cellAddress)->setAuthor($noteDetails['author'])->setText($xls->parseRichText($noteDetails['objTextData']['text'])); |
|
564
|
|
|
} |
|
565
|
|
|
} |
|
566
|
|
|
if ($selectedCells !== '') { |
|
567
|
|
|
$xls->phpSheet->setSelectedCells($selectedCells); |
|
568
|
|
|
} |
|
569
|
|
|
} |
|
570
|
|
|
if ($xls->activeSheetSet === false) { |
|
571
|
|
|
$xls->spreadsheet->setActiveSheetIndex(0); |
|
572
|
|
|
} |
|
573
|
|
|
|
|
574
|
|
|
// add the named ranges (defined names) |
|
575
|
|
|
foreach ($xls->definedname as $definedName) { |
|
576
|
|
|
if ($definedName['isBuiltInName']) { |
|
577
|
|
|
switch ($definedName['name']) { |
|
578
|
|
|
case pack('C', 0x06): |
|
579
|
|
|
// print area |
|
580
|
|
|
// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2 |
|
581
|
|
|
$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? |
|
582
|
|
|
|
|
583
|
|
|
$extractedRanges = []; |
|
584
|
|
|
$sheetName = ''; |
|
585
|
|
|
/** @var non-empty-string $range */ |
|
586
|
|
|
foreach ($ranges as $range) { |
|
587
|
|
|
// $range should look like one of these |
|
588
|
|
|
// Foo!$C$7:$J$66 |
|
589
|
|
|
// Bar!$A$1:$IV$2 |
|
590
|
|
|
$explodes = Worksheet::extractSheetTitle($range, true); |
|
591
|
|
|
$sheetName = trim($explodes[0], "'"); |
|
592
|
|
|
if (!str_contains($explodes[1], ':')) { |
|
593
|
|
|
$explodes[1] = $explodes[1] . ':' . $explodes[1]; |
|
594
|
|
|
} |
|
595
|
|
|
$extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66 |
|
596
|
|
|
} |
|
597
|
|
|
if ($docSheet = $xls->spreadsheet->getSheetByName($sheetName)) { |
|
598
|
|
|
$docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2 |
|
599
|
|
|
} |
|
600
|
|
|
|
|
601
|
|
|
break; |
|
602
|
|
|
case pack('C', 0x07): |
|
603
|
|
|
// print titles (repeating rows) |
|
604
|
|
|
// Assuming BIFF8, there are 3 cases |
|
605
|
|
|
// 1. repeating rows |
|
606
|
|
|
// formula looks like this: Sheet!$A$1:$IV$2 |
|
607
|
|
|
// rows 1-2 repeat |
|
608
|
|
|
// 2. repeating columns |
|
609
|
|
|
// formula looks like this: Sheet!$A$1:$B$65536 |
|
610
|
|
|
// columns A-B repeat |
|
611
|
|
|
// 3. both repeating rows and repeating columns |
|
612
|
|
|
// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2 |
|
613
|
|
|
$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? |
|
614
|
|
|
foreach ($ranges as $range) { |
|
615
|
|
|
// $range should look like this one of these |
|
616
|
|
|
// Sheet!$A$1:$B$65536 |
|
617
|
|
|
// Sheet!$A$1:$IV$2 |
|
618
|
|
|
if (str_contains($range, '!')) { |
|
619
|
|
|
$explodes = Worksheet::extractSheetTitle($range, true); |
|
620
|
|
|
if ($docSheet = $xls->spreadsheet->getSheetByName($explodes[0])) { |
|
621
|
|
|
$extractedRange = $explodes[1]; |
|
622
|
|
|
$extractedRange = str_replace('$', '', $extractedRange); |
|
623
|
|
|
|
|
624
|
|
|
$coordinateStrings = explode(':', $extractedRange); |
|
625
|
|
|
if (count($coordinateStrings) == 2) { |
|
626
|
|
|
[$firstColumn, $firstRow] = Coordinate::coordinateFromString($coordinateStrings[0]); |
|
627
|
|
|
[$lastColumn, $lastRow] = Coordinate::coordinateFromString($coordinateStrings[1]); |
|
628
|
|
|
|
|
629
|
|
|
if ($firstColumn == 'A' && $lastColumn == 'IV') { |
|
630
|
|
|
// then we have repeating rows |
|
631
|
|
|
$docSheet->getPageSetup()->setRowsToRepeatAtTop([$firstRow, $lastRow]); |
|
632
|
|
|
} elseif ($firstRow == 1 && $lastRow == 65536) { |
|
633
|
|
|
// then we have repeating columns |
|
634
|
|
|
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft([$firstColumn, $lastColumn]); |
|
635
|
|
|
} |
|
636
|
|
|
} |
|
637
|
|
|
} |
|
638
|
|
|
} |
|
639
|
|
|
} |
|
640
|
|
|
|
|
641
|
|
|
break; |
|
642
|
|
|
} |
|
643
|
|
|
} else { |
|
644
|
|
|
// Extract range |
|
645
|
|
|
/** @var non-empty-string $formula */ |
|
646
|
|
|
$formula = $definedName['formula']; |
|
647
|
|
|
if (str_contains($formula, '!')) { |
|
648
|
|
|
$explodes = Worksheet::extractSheetTitle($formula, true); |
|
649
|
|
|
if ( |
|
650
|
|
|
($docSheet = $xls->spreadsheet->getSheetByName($explodes[0])) |
|
651
|
|
|
|| ($docSheet = $xls->spreadsheet->getSheetByName(trim($explodes[0], "'"))) |
|
652
|
|
|
) { |
|
653
|
|
|
$extractedRange = $explodes[1]; |
|
654
|
|
|
|
|
655
|
|
|
$localOnly = ($definedName['scope'] === 0) ? false : true; |
|
656
|
|
|
|
|
657
|
|
|
$scope = ($definedName['scope'] === 0) ? null : $xls->spreadsheet->getSheetByName($xls->sheets[$definedName['scope'] - 1]['name']); |
|
658
|
|
|
|
|
659
|
|
|
$xls->spreadsheet->addNamedRange(new NamedRange((string) $definedName['name'], $docSheet, $extractedRange, $localOnly, $scope)); |
|
660
|
|
|
} |
|
661
|
|
|
} |
|
662
|
|
|
// Named Value |
|
663
|
|
|
// TODO Provide support for named values |
|
664
|
|
|
} |
|
665
|
|
|
} |
|
666
|
|
|
$xls->data = ''; |
|
667
|
|
|
|
|
668
|
|
|
return $xls->spreadsheet; |
|
669
|
|
|
} |
|
670
|
|
|
} |
|
671
|
|
|
|
PHPOffice /
PhpSpreadsheet
