-
Notifications
You must be signed in to change notification settings - Fork 0
/
SpreadsheetReader.php
350 lines (318 loc) · 8.58 KB
/
SpreadsheetReader.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
<?php
/**
* Main class for spreadsheet reading
*
* @version 0.5.10
* @author Martins Pilsetnieks
*/
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind(): void
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current(): mixed
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next(): void
{
if ($this -> Handle)
{
$this -> Index++;
$this -> Handle -> next();
}
//return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key(): mixed
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid(): bool
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count(): int
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position): void
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
if (!isset($this ->Handle[$position])) {
throw new OutOfBoundsException("invalid seek position ($position)");
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
//return null;
}
}
?>