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

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

565 lines 14.1 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Reader;
4
5	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
7	use PhpOffice\PhpSpreadsheet\Spreadsheet;
8
9	class Csv extends BaseReader
10	{
11	/**
12	* Input encoding.
13	*
14	* @var string
15	*/
16	private $inputEncoding = 'UTF-8';
17
18	/**
19	* Delimiter.
20	*
21	* @var string
22	*/
23	private $delimiter;
24
25	/**
26	* Enclosure.
27	*
28	* @var string
29	*/
30	private $enclosure = '"';
31
32	/**
33	* Sheet index to read.
34	*
35	* @var int
36	*/
37	private $sheetIndex = 0;
38
39	/**
40	* Load rows contiguously.
41	*
42	* @var bool
43	*/
44	private $contiguous = false;
45
46	/**
47	* Row counter for loading rows contiguously.
48	*
49	* @var int
50	*/
51	private $contiguousRow = -1;
52
53	/**
54	* The character that can escape the enclosure.
55	*
56	* @var string
57	*/
58	private $escapeCharacter = '\\';
59
60	/**
61	* Create a new CSV Reader instance.
62	*/
63	public function __construct()
64	{
65	parent::__construct();
66	}
67
68	/**
69	* Set input encoding.
70	*
71	* @param string $pValue Input encoding, eg: 'UTF-8'
72	*
73	* @return Csv
74	*/
75	public function setInputEncoding($pValue)
76	{
77	$this->inputEncoding = $pValue;
78
79	return $this;
80	}
81
82	/**
83	* Get input encoding.
84	*
85	* @return string
86	*/
87	public function getInputEncoding()
88	{
89	return $this->inputEncoding;
90	}
91
92	/**
93	* Move filepointer past any BOM marker.
94	*/
95	protected function skipBOM()
96	{
97	rewind($this->fileHandle);
98
99	switch ($this->inputEncoding) {
100	case 'UTF-8':
101	fgets($this->fileHandle, 4) == "\xEF\xBB\xBF" ?
102	fseek($this->fileHandle, 3) : fseek($this->fileHandle, 0);
103
104	break;
105	case 'UTF-16LE':
106	fgets($this->fileHandle, 3) == "\xFF\xFE" ?
107	fseek($this->fileHandle, 2) : fseek($this->fileHandle, 0);
108
109	break;
110	case 'UTF-16BE':
111	fgets($this->fileHandle, 3) == "\xFE\xFF" ?
112	fseek($this->fileHandle, 2) : fseek($this->fileHandle, 0);
113
114	break;
115	case 'UTF-32LE':
116	fgets($this->fileHandle, 5) == "\xFF\xFE\x00\x00" ?
117	fseek($this->fileHandle, 4) : fseek($this->fileHandle, 0);
118
119	break;
120	case 'UTF-32BE':
121	fgets($this->fileHandle, 5) == "\x00\x00\xFE\xFF" ?
122	fseek($this->fileHandle, 4) : fseek($this->fileHandle, 0);
123
124	break;
125	default:
126	break;
127	}
128	}
129
130	/**
131	* Identify any separator that is explicitly set in the file.
132	*/
133	protected function checkSeparator()
134	{
135	$line = fgets($this->fileHandle);
136	if ($line === false) {
137	return;
138	}
139
140	if ((strlen(trim($line, "\r\n")) == 5) && (stripos($line, 'sep=') === 0)) {
141	$this->delimiter = substr($line, 4, 1);
142
143	return;
144	}
145
146	$this->skipBOM();
147	}
148
149	/**
150	* Infer the separator if it isn't explicitly set in the file or specified by the user.
151	*/
152	protected function inferSeparator()
153	{
154	if ($this->delimiter !== null) {
155	return;
156	}
157
158	$potentialDelimiters = [',', ';', "\t", '\|', ':', ' ', '~'];
159	$counts = [];
160	foreach ($potentialDelimiters as $delimiter) {
161	$counts[$delimiter] = [];
162	}
163
164	// Count how many times each of the potential delimiters appears in each line
165	$numberLines = 0;
166	while (($line = $this->getNextLine()) !== false && (++$numberLines < 1000)) {
167	$countLine = [];
168	for ($i = strlen($line) - 1; $i >= 0; --$i) {
169	$char = $line[$i];
170	if (isset($counts[$char])) {
171	if (!isset($countLine[$char])) {
172	$countLine[$char] = 0;
173	}
174	++$countLine[$char];
175	}
176	}
177	foreach ($potentialDelimiters as $delimiter) {
178	$counts[$delimiter][] = isset($countLine[$delimiter])
179	? $countLine[$delimiter]
180	: 0;
181	}
182	}
183
184	// If number of lines is 0, nothing to infer : fall back to the default
185	if ($numberLines === 0) {
186	$this->delimiter = reset($potentialDelimiters);
187	$this->skipBOM();
188
189	return;
190	}
191
192	// Calculate the mean square deviations for each delimiter (ignoring delimiters that haven't been found consistently)
193	$meanSquareDeviations = [];
194	$middleIdx = floor(($numberLines - 1) / 2);
195
196	foreach ($potentialDelimiters as $delimiter) {
197	$series = $counts[$delimiter];
198	sort($series);
199
200	$median = ($numberLines % 2)
201	? $series[$middleIdx]
202	: ($series[$middleIdx] + $series[$middleIdx + 1]) / 2;
203
204	if ($median === 0) {
205	continue;
206	}
207
208	$meanSquareDeviations[$delimiter] = array_reduce(
209	$series,
210	function ($sum, $value) use ($median) {
211	return $sum + pow($value - $median, 2);
212	}
213	) / count($series);
214	}
215
216	// ... and pick the delimiter with the smallest mean square deviation (in case of ties, the order in potentialDelimiters is respected)
217	$min = INF;
218	foreach ($potentialDelimiters as $delimiter) {
219	if (!isset($meanSquareDeviations[$delimiter])) {
220	continue;
221	}
222
223	if ($meanSquareDeviations[$delimiter] < $min) {
224	$min = $meanSquareDeviations[$delimiter];
225	$this->delimiter = $delimiter;
226	}
227	}
228
229	// If no delimiter could be detected, fall back to the default
230	if ($this->delimiter === null) {
231	$this->delimiter = reset($potentialDelimiters);
232	}
233
234	$this->skipBOM();
235	}
236
237	/**
238	* Get the next full line from the file.
239	*
240	* @param string $line
241	*
242	* @return bool\|string
243	*/
244	private function getNextLine($line = '')
245	{
246	// Get the next line in the file
247	$newLine = fgets($this->fileHandle);
248
249	// Return false if there is no next line
250	if ($newLine === false) {
251	return false;
252	}
253
254	// Add the new line to the line passed in
255	$line = $line . $newLine;
256
257	// Drop everything that is enclosed to avoid counting false positives in enclosures
258	$enclosure = '(?<!' . preg_quote($this->escapeCharacter, '/') . ')'
259	. preg_quote($this->enclosure, '/');
260	$line = preg_replace('/(' . $enclosure . '.*' . $enclosure . ')/Us', '', $line);
261
262	// See if we have any enclosures left in the line
263	// if we still have an enclosure then we need to read the next line as well
264	if (preg_match('/(' . $enclosure . ')/', $line) > 0) {
265	$line = $this->getNextLine($line);
266	}
267
268	return $line;
269	}
270
271	/**
272	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
273	*
274	* @param string $pFilename
275	*
276	* @throws Exception
277	*
278	* @return array
279	*/
280	public function listWorksheetInfo($pFilename)
281	{
282	// Open file
283	if (!$this->canRead($pFilename)) {
284	throw new Exception($pFilename . ' is an Invalid Spreadsheet file.');
285	}
286	$this->openFile($pFilename);
287	$fileHandle = $this->fileHandle;
288
289	// Skip BOM, if any
290	$this->skipBOM();
291	$this->checkSeparator();
292	$this->inferSeparator();
293
294	$worksheetInfo = [];
295	$worksheetInfo[0]['worksheetName'] = 'Worksheet';
296	$worksheetInfo[0]['lastColumnLetter'] = 'A';
297	$worksheetInfo[0]['lastColumnIndex'] = 0;
298	$worksheetInfo[0]['totalRows'] = 0;
299	$worksheetInfo[0]['totalColumns'] = 0;
300
301	// Loop through each line of the file in turn
302	while (($rowData = fgetcsv($fileHandle, 0, $this->delimiter, $this->enclosure, $this->escapeCharacter)) !== false) {
303	++$worksheetInfo[0]['totalRows'];
304	$worksheetInfo[0]['lastColumnIndex'] = max($worksheetInfo[0]['lastColumnIndex'], count($rowData) - 1);
305	}
306
307	$worksheetInfo[0]['lastColumnLetter'] = Coordinate::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex'] + 1);
308	$worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1;
309
310	// Close file
311	fclose($fileHandle);
312
313	return $worksheetInfo;
314	}
315
316	/**
317	* Loads Spreadsheet from file.
318	*
319	* @param string $pFilename
320	*
321	* @throws Exception
322	*
323	* @return Spreadsheet
324	*/
325	public function load($pFilename)
326	{
327	// Create new Spreadsheet
328	$spreadsheet = new Spreadsheet();
329
330	// Load into this instance
331	return $this->loadIntoExisting($pFilename, $spreadsheet);
332	}
333
334	/**
335	* Loads PhpSpreadsheet from file into PhpSpreadsheet instance.
336	*
337	* @param string $pFilename
338	* @param Spreadsheet $spreadsheet
339	*
340	* @throws Exception
341	*
342	* @return Spreadsheet
343	*/
344	public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet)
345	{
346	$lineEnding = ini_get('auto_detect_line_endings');
347	ini_set('auto_detect_line_endings', true);
348
349	// Open file
350	if (!$this->canRead($pFilename)) {
351	throw new Exception($pFilename . ' is an Invalid Spreadsheet file.');
352	}
353	$this->openFile($pFilename);
354	$fileHandle = $this->fileHandle;
355
356	// Skip BOM, if any
357	$this->skipBOM();
358	$this->checkSeparator();
359	$this->inferSeparator();
360
361	// Create new PhpSpreadsheet object
362	while ($spreadsheet->getSheetCount() <= $this->sheetIndex) {
363	$spreadsheet->createSheet();
364	}
365	$sheet = $spreadsheet->setActiveSheetIndex($this->sheetIndex);
366
367	// Set our starting row based on whether we're in contiguous mode or not
368	$currentRow = 1;
369	if ($this->contiguous) {
370	$currentRow = ($this->contiguousRow == -1) ? $sheet->getHighestRow() : $this->contiguousRow;
371	}
372
373	// Loop through each line of the file in turn
374	while (($rowData = fgetcsv($fileHandle, 0, $this->delimiter, $this->enclosure, $this->escapeCharacter)) !== false) {
375	$columnLetter = 'A';
376	foreach ($rowData as $rowDatum) {
377	if ($rowDatum != '' && $this->readFilter->readCell($columnLetter, $currentRow)) {
378	// Convert encoding if necessary
379	if ($this->inputEncoding !== 'UTF-8') {
380	$rowDatum = StringHelper::convertEncoding($rowDatum, 'UTF-8', $this->inputEncoding);
381	}
382
383	// Set cell value
384	$sheet->getCell($columnLetter . $currentRow)->setValue($rowDatum);
385	}
386	++$columnLetter;
387	}
388	++$currentRow;
389	}
390
391	// Close file
392	fclose($fileHandle);
393
394	if ($this->contiguous) {
395	$this->contiguousRow = $currentRow;
396	}
397
398	ini_set('auto_detect_line_endings', $lineEnding);
399
400	// Return
401	return $spreadsheet;
402	}
403
404	/**
405	* Get delimiter.
406	*
407	* @return string
408	*/
409	public function getDelimiter()
410	{
411	return $this->delimiter;
412	}
413
414	/**
415	* Set delimiter.
416	*
417	* @param string $delimiter Delimiter, eg: ','
418	*
419	* @return CSV
420	*/
421	public function setDelimiter($delimiter)
422	{
423	$this->delimiter = $delimiter;
424
425	return $this;
426	}
427
428	/**
429	* Get enclosure.
430	*
431	* @return string
432	*/
433	public function getEnclosure()
434	{
435	return $this->enclosure;
436	}
437
438	/**
439	* Set enclosure.
440	*
441	* @param string $enclosure Enclosure, defaults to "
442	*
443	* @return CSV
444	*/
445	public function setEnclosure($enclosure)
446	{
447	if ($enclosure == '') {
448	$enclosure = '"';
449	}
450	$this->enclosure = $enclosure;
451
452	return $this;
453	}
454
455	/**
456	* Get sheet index.
457	*
458	* @return int
459	*/
460	public function getSheetIndex()
461	{
462	return $this->sheetIndex;
463	}
464
465	/**
466	* Set sheet index.
467	*
468	* @param int $pValue Sheet index
469	*
470	* @return CSV
471	*/
472	public function setSheetIndex($pValue)
473	{
474	$this->sheetIndex = $pValue;
475
476	return $this;
477	}
478
479	/**
480	* Set Contiguous.
481	*
482	* @param bool $contiguous
483	*
484	* @return Csv
485	*/
486	public function setContiguous($contiguous)
487	{
488	$this->contiguous = (bool) $contiguous;
489	if (!$contiguous) {
490	$this->contiguousRow = -1;
491	}
492
493	return $this;
494	}
495
496	/**
497	* Get Contiguous.
498	*
499	* @return bool
500	*/
501	public function getContiguous()
502	{
503	return $this->contiguous;
504	}
505
506	/**
507	* Set escape backslashes.
508	*
509	* @param string $escapeCharacter
510	*
511	* @return $this
512	*/
513	public function setEscapeCharacter($escapeCharacter)
514	{
515	$this->escapeCharacter = $escapeCharacter;
516
517	return $this;
518	}
519
520	/**
521	* Get escape backslashes.
522	*
523	* @return string
524	*/
525	public function getEscapeCharacter()
526	{
527	return $this->escapeCharacter;
528	}
529
530	/**
531	* Can the current IReader read the file?
532	*
533	* @param string $pFilename
534	*
535	* @return bool
536	*/
537	public function canRead($pFilename)
538	{
539	// Check if file exists
540	try {
541	$this->openFile($pFilename);
542	} catch (Exception $e) {
543	return false;
544	}
545
546	fclose($this->fileHandle);
547
548	// Trust file extension if any
549	$extension = strtolower(pathinfo($pFilename, PATHINFO_EXTENSION));
550	if (in_array($extension, ['csv', 'tsv'])) {
551	return true;
552	}
553
554	// Attempt to guess mimetype
555	$type = mime_content_type($pFilename);
556	$supportedTypes = [
557	'text/csv',
558	'text/plain',
559	'inode/x-empty',
560	];
561
562	return in_array($type, $supportedTypes, true);
563	}
564	}
565