visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Reader / Slk.php

Slk.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.4.7, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Slk.php

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