wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Reader / Slk.php

Slk.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Slk.php

593 lines 19.6 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Reader;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7	use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
8	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9	use PhpOffice\PhpSpreadsheet\Spreadsheet;
10	use PhpOffice\PhpSpreadsheet\Style\Border;
11	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
12
13	class Slk extends BaseReader
14	{
15	/**
16	* Input encoding.
17	*
18	* @var string
19	*/
20	private $inputEncoding = 'ANSI';
21
22	/**
23	* Sheet index to read.
24	*
25	* @var int
26	*/
27	private $sheetIndex = 0;
28
29	/**
30	* Formats.
31	*
32	* @var array
33	*/
34	private $formats = [];
35
36	/**
37	* Format Count.
38	*
39	* @var int
40	*/
41	private $format = 0;
42
43	/**
44	* Fonts.
45	*
46	* @var array
47	*/
48	private $fonts = [];
49
50	/**
51	* Font Count.
52	*
53	* @var int
54	*/
55	private $fontcount = 0;
56
57	/**
58	* Create a new SYLK Reader instance.
59	*/
60	public function __construct()
61	{
62	parent::__construct();
63	}
64
65	/**
66	* Validate that the current file is a SYLK file.
67	*/
68	public function canRead(string $filename): bool
69	{
70	try {
71	$this->openFile($filename);
72	} catch (ReaderException $e) {
73	return false;
74	}
75
76	// Read sample data (first 2 KB will do)
77	$data = (string) fread($this->fileHandle, 2048);
78
79	// Count delimiters in file
80	$delimiterCount = substr_count($data, ';');
81	$hasDelimiter = $delimiterCount > 0;
82
83	// Analyze first line looking for ID; signature
84	$lines = explode("\n", $data);
85	$hasId = substr($lines[0], 0, 4) === 'ID;P';
86
87	fclose($this->fileHandle);
88
89	return $hasDelimiter && $hasId;
90	}
91
92	private function canReadOrBust(string $filename): void
93	{
94	if (!$this->canRead($filename)) {
95	throw new ReaderException($filename . ' is an Invalid SYLK file.');
96	}
97	$this->openFile($filename);
98	}
99
100	/**
101	* Set input encoding.
102	*
103	* @deprecated no use is made of this property
104	*
105	* @param string $inputEncoding Input encoding, eg: 'ANSI'
106	*
107	* @return $this
108	*
109	* @codeCoverageIgnore
110	*/
111	public function setInputEncoding($inputEncoding)
112	{
113	$this->inputEncoding = $inputEncoding;
114
115	return $this;
116	}
117
118	/**
119	* Get input encoding.
120	*
121	* @deprecated no use is made of this property
122	*
123	* @return string
124	*
125	* @codeCoverageIgnore
126	*/
127	public function getInputEncoding()
128	{
129	return $this->inputEncoding;
130	}
131
132	/**
133	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
134	*
135	* @param string $filename
136	*
137	* @return array
138	*/
139	public function listWorksheetInfo($filename)
140	{
141	// Open file
142	$this->canReadOrBust($filename);
143	$fileHandle = $this->fileHandle;
144	rewind($fileHandle);
145
146	$worksheetInfo = [];
147	$worksheetInfo[0]['worksheetName'] = basename($filename, '.slk');
148
149	// loop through one row (line) at a time in the file
150	$rowIndex = 0;
151	$columnIndex = 0;
152	while (($rowData = fgets($fileHandle)) !== false) {
153	$columnIndex = 0;
154
155	// convert SYLK encoded $rowData to UTF-8
156	$rowData = StringHelper::SYLKtoUTF8($rowData);
157
158	// explode each row at semicolons while taking into account that literal semicolon (;)
159	// is escaped like this (;;)
160	$rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
161
162	$dataType = array_shift($rowData);
163	if ($dataType == 'B') {
164	foreach ($rowData as $rowDatum) {
165	switch ($rowDatum[0]) {
166	case 'X':
167	$columnIndex = (int) substr($rowDatum, 1) - 1;
168
169	break;
170	case 'Y':
171	$rowIndex = substr($rowDatum, 1);
172
173	break;
174	}
175	}
176
177	break;
178	}
179	}
180
181	$worksheetInfo[0]['lastColumnIndex'] = $columnIndex;
182	$worksheetInfo[0]['totalRows'] = $rowIndex;
183	$worksheetInfo[0]['lastColumnLetter'] = Coordinate::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex'] + 1);
184	$worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1;
185
186	// Close file
187	fclose($fileHandle);
188
189	return $worksheetInfo;
190	}
191
192	/**
193	* Loads PhpSpreadsheet from file.
194	*/
195	protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
196	{
197	// Create new Spreadsheet
198	$spreadsheet = new Spreadsheet();
199
200	// Load into this instance
201	return $this->loadIntoExisting($filename, $spreadsheet);
202	}
203
204	private const COLOR_ARRAY = [
205	'FF00FFFF', // 0 - cyan
206	'FF000000', // 1 - black
207	'FFFFFFFF', // 2 - white
208	'FFFF0000', // 3 - red
209	'FF00FF00', // 4 - green
210	'FF0000FF', // 5 - blue
211	'FFFFFF00', // 6 - yellow
212	'FFFF00FF', // 7 - magenta
213	];
214
215	private const FONT_STYLE_MAPPINGS = [
216	'B' => 'bold',
217	'I' => 'italic',
218	'U' => 'underline',
219	];
220
221	private function processFormula(string $rowDatum, bool &$hasCalculatedValue, string &$cellDataFormula, string $row, string $column): void
222	{
223	$cellDataFormula = '=' . substr($rowDatum, 1);
224	// Convert R1C1 style references to A1 style references (but only when not quoted)
225	$temp = explode('"', $cellDataFormula);
226	$key = false;
227	foreach ($temp as &$value) {
228	// Only count/replace in alternate array entries
229	$key = $key === false;
230	if ($key) {
231	preg_match_all('/(R(\[?-?\d\]?))(C(\[?-?\d\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
232	// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
233	// through the formula from left to right. Reversing means that we work right to left.through
234	// the formula
235	$cellReferences = array_reverse($cellReferences);
236	// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
237	// then modify the formula to use that new reference
238	foreach ($cellReferences as $cellReference) {
239	$rowReference = $cellReference[2][0];
240	// Empty R reference is the current row
241	if ($rowReference == '') {
242	$rowReference = $row;
243	}
244	// Bracketed R references are relative to the current row
245	if ($rowReference[0] == '[') {
246	$rowReference = (int) $row + (int) trim($rowReference, '[]');
247	}
248	$columnReference = $cellReference[4][0];
249	// Empty C reference is the current column
250	if ($columnReference == '') {
251	$columnReference = $column;
252	}
253	// Bracketed C references are relative to the current column
254	if ($columnReference[0] == '[') {
255	$columnReference = (int) $column + (int) trim($columnReference, '[]');
256	}
257	$A1CellReference = Coordinate::stringFromColumnIndex((int) $columnReference) . $rowReference;
258
259	$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
260	}
261	}
262	}
263	unset($value);
264	// Then rebuild the formula string
265	$cellDataFormula = implode('"', $temp);
266	$hasCalculatedValue = true;
267	}
268
269	private function processCRecord(array $rowData, Spreadsheet &$spreadsheet, string &$row, string &$column): void
270	{
271	// Read cell value data
272	$hasCalculatedValue = false;
273	$cellDataFormula = $cellData = '';
274	foreach ($rowData as $rowDatum) {
275	switch ($rowDatum[0]) {
276	case 'C':
277	case 'X':
278	$column = substr($rowDatum, 1);
279
280	break;
281	case 'R':
282	case 'Y':
283	$row = substr($rowDatum, 1);
284
285	break;
286	case 'K':
287	$cellData = substr($rowDatum, 1);
288
289	break;
290	case 'E':
291	$this->processFormula($rowDatum, $hasCalculatedValue, $cellDataFormula, $row, $column);
292
293	break;
294	case 'A':
295	$comment = substr($rowDatum, 1);
296	$columnLetter = Coordinate::stringFromColumnIndex((int) $column);
297	$spreadsheet->getActiveSheet()
298	->getComment("$columnLetter$row")
299	->getText()
300	->createText($comment);
301
302	break;
303	}
304	}
305	$columnLetter = Coordinate::stringFromColumnIndex((int) $column);
306	$cellData = Calculation::unwrapResult($cellData);
307
308	// Set cell value
309	$this->processCFinal($spreadsheet, $hasCalculatedValue, $cellDataFormula, $cellData, "$columnLetter$row");
310	}
311
312	private function processCFinal(Spreadsheet &$spreadsheet, bool $hasCalculatedValue, string $cellDataFormula, string $cellData, string $coordinate): void
313	{
314	// Set cell value
315	$spreadsheet->getActiveSheet()->getCell($coordinate)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
316	if ($hasCalculatedValue) {
317	$cellData = Calculation::unwrapResult($cellData);
318	$spreadsheet->getActiveSheet()->getCell($coordinate)->setCalculatedValue($cellData);
319	}
320	}
321
322	private function processFRecord(array $rowData, Spreadsheet &$spreadsheet, string &$row, string &$column): void
323	{
324	// Read cell formatting
325	$formatStyle = $columnWidth = '';
326	$startCol = $endCol = '';
327	$fontStyle = '';
328	$styleData = [];
329	foreach ($rowData as $rowDatum) {
330	switch ($rowDatum[0]) {
331	case 'C':
332	case 'X':
333	$column = substr($rowDatum, 1);
334
335	break;
336	case 'R':
337	case 'Y':
338	$row = substr($rowDatum, 1);
339
340	break;
341	case 'P':
342	$formatStyle = $rowDatum;
343
344	break;
345	case 'W':
346	[$startCol, $endCol, $columnWidth] = explode(' ', substr($rowDatum, 1));
347
348	break;
349	case 'S':
350	$this->styleSettings($rowDatum, $styleData, $fontStyle);
351
352	break;
353	}
354	}
355	$this->addFormats($spreadsheet, $formatStyle, $row, $column);
356	$this->addFonts($spreadsheet, $fontStyle, $row, $column);
357	$this->addStyle($spreadsheet, $styleData, $row, $column);
358	$this->addWidth($spreadsheet, $columnWidth, $startCol, $endCol);
359	}
360
361	private const STYLE_SETTINGS_FONT = ['D' => 'bold', 'I' => 'italic'];
362
363	private const STYLE_SETTINGS_BORDER = [
364	'B' => 'bottom',
365	'L' => 'left',
366	'R' => 'right',
367	'T' => 'top',
368	];
369
370	private function styleSettings(string $rowDatum, array &$styleData, string &$fontStyle): void
371	{
372	$styleSettings = substr($rowDatum, 1);
373	$iMax = strlen($styleSettings);
374	for ($i = 0; $i < $iMax; ++$i) {
375	$char = $styleSettings[$i];
376	if (array_key_exists($char, self::STYLE_SETTINGS_FONT)) {
377	$styleData['font'][self::STYLE_SETTINGS_FONT[$char]] = true;
378	} elseif (array_key_exists($char, self::STYLE_SETTINGS_BORDER)) {
379	$styleData['borders'][self::STYLE_SETTINGS_BORDER[$char]]['borderStyle'] = Border::BORDER_THIN;
380	} elseif ($char == 'S') {
381	$styleData['fill']['fillType'] = \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_PATTERN_GRAY125;
382	} elseif ($char == 'M') {
383	if (preg_match('/M([1-9]\d*)/', $styleSettings, $matches)) {
384	$fontStyle = $matches[1];
385	}
386	}
387	}
388	}
389
390	private function addFormats(Spreadsheet &$spreadsheet, string $formatStyle, string $row, string $column): void
391	{
392	if ($formatStyle && $column > '' && $row > '') {
393	$columnLetter = Coordinate::stringFromColumnIndex((int) $column);
394	if (isset($this->formats[$formatStyle])) {
395	$spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->formats[$formatStyle]);
396	}
397	}
398	}
399
400	private function addFonts(Spreadsheet &$spreadsheet, string $fontStyle, string $row, string $column): void
401	{
402	if ($fontStyle && $column > '' && $row > '') {
403	$columnLetter = Coordinate::stringFromColumnIndex((int) $column);
404	if (isset($this->fonts[$fontStyle])) {
405	$spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->fonts[$fontStyle]);
406	}
407	}
408	}
409
410	private function addStyle(Spreadsheet &$spreadsheet, array $styleData, string $row, string $column): void
411	{
412	if ((!empty($styleData)) && $column > '' && $row > '') {
413	$columnLetter = Coordinate::stringFromColumnIndex((int) $column);
414	$spreadsheet->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
415	}
416	}
417
418	private function addWidth(Spreadsheet $spreadsheet, string $columnWidth, string $startCol, string $endCol): void
419	{
420	if ($columnWidth > '') {
421	if ($startCol == $endCol) {
422	$startCol = Coordinate::stringFromColumnIndex((int) $startCol);
423	$spreadsheet->getActiveSheet()->getColumnDimension($startCol)->setWidth((float) $columnWidth);
424	} else {
425	$startCol = Coordinate::stringFromColumnIndex((int) $startCol);
426	$endCol = Coordinate::stringFromColumnIndex((int) $endCol);
427	$spreadsheet->getActiveSheet()->getColumnDimension($startCol)->setWidth((float) $columnWidth);
428	do {
429	$spreadsheet->getActiveSheet()->getColumnDimension(++$startCol)->setWidth((float) $columnWidth);
430	} while ($startCol !== $endCol);
431	}
432	}
433	}
434
435	private function processPRecord(array $rowData, Spreadsheet &$spreadsheet): void
436	{
437	// Read shared styles
438	$formatArray = [];
439	$fromFormats = ['\-', '\ '];
440	$toFormats = ['-', ' '];
441	foreach ($rowData as $rowDatum) {
442	switch ($rowDatum[0]) {
443	case 'P':
444	$formatArray['numberFormat']['formatCode'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
445
446	break;
447	case 'E':
448	case 'F':
449	$formatArray['font']['name'] = substr($rowDatum, 1);
450
451	break;
452	case 'M':
453	$formatArray['font']['size'] = ((float) substr($rowDatum, 1)) / 20;
454
455	break;
456	case 'L':
457	$this->processPColors($rowDatum, $formatArray);
458
459	break;
460	case 'S':
461	$this->processPFontStyles($rowDatum, $formatArray);
462
463	break;
464	}
465	}
466	$this->processPFinal($spreadsheet, $formatArray);
467	}
468
469	private function processPColors(string $rowDatum, array &$formatArray): void
470	{
471	if (preg_match('/L([1-9]\d*)/', $rowDatum, $matches)) {
472	$fontColor = $matches[1] % 8;
473	$formatArray['font']['color']['argb'] = self::COLOR_ARRAY[$fontColor];
474	}
475	}
476
477	private function processPFontStyles(string $rowDatum, array &$formatArray): void
478	{
479	$styleSettings = substr($rowDatum, 1);
480	$iMax = strlen($styleSettings);
481	for ($i = 0; $i < $iMax; ++$i) {
482	if (array_key_exists($styleSettings[$i], self::FONT_STYLE_MAPPINGS)) {
483	$formatArray['font'][self::FONT_STYLE_MAPPINGS[$styleSettings[$i]]] = true;
484	}
485	}
486	}
487
488	private function processPFinal(Spreadsheet &$spreadsheet, array $formatArray): void
489	{
490	if (array_key_exists('numberFormat', $formatArray)) {
491	$this->formats['P' . $this->format] = $formatArray;
492	++$this->format;
493	} elseif (array_key_exists('font', $formatArray)) {
494	++$this->fontcount;
495	$this->fonts[$this->fontcount] = $formatArray;
496	if ($this->fontcount === 1) {
497	$spreadsheet->getDefaultStyle()->applyFromArray($formatArray);
498	}
499	}
500	}
501
502	/**
503	* Loads PhpSpreadsheet from file into PhpSpreadsheet instance.
504	*
505	* @param string $filename
506	*
507	* @return Spreadsheet
508	*/
509	public function loadIntoExisting($filename, Spreadsheet $spreadsheet)
510	{
511	// Open file
512	$this->canReadOrBust($filename);
513	$fileHandle = $this->fileHandle;
514	rewind($fileHandle);
515
516	// Create new Worksheets
517	while ($spreadsheet->getSheetCount() <= $this->sheetIndex) {
518	$spreadsheet->createSheet();
519	}
520	$spreadsheet->setActiveSheetIndex($this->sheetIndex);
521	$spreadsheet->getActiveSheet()->setTitle(substr(basename($filename, '.slk'), 0, Worksheet::SHEET_TITLE_MAXIMUM_LENGTH));
522
523	// Loop through file
524	$column = $row = '';
525
526	// loop through one row (line) at a time in the file
527	while (($rowDataTxt = fgets($fileHandle)) !== false) {
528	// convert SYLK encoded $rowData to UTF-8
529	$rowDataTxt = StringHelper::SYLKtoUTF8($rowDataTxt);
530
531	// explode each row at semicolons while taking into account that literal semicolon (;)
532	// is escaped like this (;;)
533	$rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowDataTxt)))));
534
535	$dataType = array_shift($rowData);
536	if ($dataType == 'P') {
537	// Read shared styles
538	$this->processPRecord($rowData, $spreadsheet);
539	} elseif ($dataType == 'C') {
540	// Read cell value data
541	$this->processCRecord($rowData, $spreadsheet, $row, $column);
542	} elseif ($dataType == 'F') {
543	// Read cell formatting
544	$this->processFRecord($rowData, $spreadsheet, $row, $column);
545	} else {
546	$this->columnRowFromRowData($rowData, $column, $row);
547	}
548	}
549
550	// Close file
551	fclose($fileHandle);
552
553	// Return
554	return $spreadsheet;
555	}
556
557	private function columnRowFromRowData(array $rowData, string &$column, string &$row): void
558	{
559	foreach ($rowData as $rowDatum) {
560	$char0 = $rowDatum[0];
561	if ($char0 === 'X' \|\| $char0 == 'C') {
562	$column = substr($rowDatum, 1);
563	} elseif ($char0 === 'Y' \|\| $char0 == 'R') {
564	$row = substr($rowDatum, 1);
565	}
566	}
567	}
568
569	/**
570	* Get sheet index.
571	*
572	* @return int
573	*/
574	public function getSheetIndex()
575	{
576	return $this->sheetIndex;
577	}
578
579	/**
580	* Set sheet index.
581	*
582	* @param int $sheetIndex Sheet index
583	*
584	* @return $this
585	*/
586	public function setSheetIndex($sheetIndex)
587	{
588	$this->sheetIndex = $sheetIndex;
589
590	return $this;
591	}
592	}
593