Subversion Repositories eFlore/Applications.cel

Rev

Rev 2388 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 PHPExcel
6
 *
7
 * This library is free software; you can redistribute it and/or
8
 * modify it under the terms of the GNU Lesser General Public
9
 * License as published by the Free Software Foundation; either
10
 * version 2.1 of the License, or (at your option) any later version.
11
 *
12
 * This library is distributed in the hope that it will be useful,
13
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15
 * Lesser General Public License for more details.
16
 *
17
 * You should have received a copy of the GNU Lesser General Public
18
 * License along with this library; if not, write to the Free Software
19
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
20
 *
21
 * @category   PHPExcel
22
 * @package    PHPExcel_Reader
23
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version    ##VERSION##, ##DATE##
26
 */
27
 
28
 
29
/** PHPExcel root directory */
30
if (!defined('PHPEXCEL_ROOT')) {
31
	/**
32
	 * @ignore
33
	 */
34
	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35
	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36
}
37
 
38
/**
39
 * PHPExcel_Reader_SYLK
40
 *
41
 * @category   PHPExcel
42
 * @package    PHPExcel_Reader
43
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
44
 */
45
class PHPExcel_Reader_SYLK extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
46
{
47
	/**
48
	 * Input encoding
49
	 *
50
	 * @var string
51
	 */
52
	private $_inputEncoding	= 'ANSI';
53
 
54
	/**
55
	 * Sheet index to read
56
	 *
57
	 * @var int
58
	 */
59
	private $_sheetIndex 	= 0;
60
 
61
	/**
62
	 * Formats
63
	 *
64
	 * @var array
65
	 */
66
	private $_formats = array();
67
 
68
	/**
69
	 * Format Count
70
	 *
71
	 * @var int
72
	 */
73
	private $_format = 0;
74
 
75
	/**
76
	 * Create a new PHPExcel_Reader_SYLK
77
	 */
78
	public function __construct() {
79
		$this->_readFilter 	= new PHPExcel_Reader_DefaultReadFilter();
80
	}
81
 
82
	/**
83
	 * Validate that the current file is a SYLK file
84
	 *
85
	 * @return boolean
86
	 */
87
	protected function _isValidFormat()
88
	{
89
		// Read sample data (first 2 KB will do)
90
		$data = fread($this->_fileHandle, 2048);
91
 
92
		// Count delimiters in file
93
		$delimiterCount = substr_count($data, ';');
94
		if ($delimiterCount < 1) {
95
			return FALSE;
96
		}
97
 
98
		// Analyze first line looking for ID; signature
99
		$lines = explode("\n", $data);
100
		if (substr($lines[0],0,4) != 'ID;P') {
101
			return FALSE;
102
		}
103
 
104
		return TRUE;
105
	}
106
 
107
	/**
108
	 * Set input encoding
109
	 *
110
	 * @param string $pValue Input encoding
111
	 */
112
	public function setInputEncoding($pValue = 'ANSI')
113
	{
114
		$this->_inputEncoding = $pValue;
115
		return $this;
116
	}
117
 
118
	/**
119
	 * Get input encoding
120
	 *
121
	 * @return string
122
	 */
123
	public function getInputEncoding()
124
	{
125
		return $this->_inputEncoding;
126
	}
127
 
128
	/**
129
	 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
130
	 *
131
	 * @param   string     $pFilename
132
	 * @throws   PHPExcel_Reader_Exception
133
	 */
134
	public function listWorksheetInfo($pFilename)
135
	{
136
		// Open file
137
		$this->_openFile($pFilename);
138
		if (!$this->_isValidFormat()) {
139
			fclose ($this->_fileHandle);
140
			throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
141
		}
142
		$fileHandle = $this->_fileHandle;
143
		rewind($fileHandle);
144
 
145
		$worksheetInfo = array();
146
		$worksheetInfo[0]['worksheetName'] = 'Worksheet';
147
		$worksheetInfo[0]['lastColumnLetter'] = 'A';
148
		$worksheetInfo[0]['lastColumnIndex'] = 0;
149
		$worksheetInfo[0]['totalRows'] = 0;
150
		$worksheetInfo[0]['totalColumns'] = 0;
151
 
152
		// Loop through file
153
		$rowData = array();
154
 
155
		// loop through one row (line) at a time in the file
156
		$rowIndex = 0;
157
		while (($rowData = fgets($fileHandle)) !== FALSE) {
158
			$columnIndex = 0;
159
 
160
			// convert SYLK encoded $rowData to UTF-8
161
			$rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
162
 
163
			// explode each row at semicolons while taking into account that literal semicolon (;)
164
			// is escaped like this (;;)
165
			$rowData = explode("\t",str_replace('¤',';',str_replace(';',"\t",str_replace(';;','¤',rtrim($rowData)))));
166
 
167
			$dataType = array_shift($rowData);
168
			if ($dataType == 'C') {
169
				//  Read cell value data
170
				foreach($rowData as $rowDatum) {
171
					switch($rowDatum{0}) {
172
						case 'C' :
173
						case 'X' :
174
							$columnIndex = substr($rowDatum,1) - 1;
175
							break;
176
						case 'R' :
177
						case 'Y' :
178
							$rowIndex = substr($rowDatum,1);
179
							break;
180
					}
181
 
182
					$worksheetInfo[0]['totalRows'] = max($worksheetInfo[0]['totalRows'], $rowIndex);
183
					$worksheetInfo[0]['lastColumnIndex'] = max($worksheetInfo[0]['lastColumnIndex'], $columnIndex);
184
				}
185
			}
186
		}
187
 
188
		$worksheetInfo[0]['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex']);
189
		$worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1;
190
 
191
		// Close file
192
		fclose($fileHandle);
193
 
194
		return $worksheetInfo;
195
	}
196
 
197
	/**
198
	 * Loads PHPExcel from file
199
	 *
200
	 * @param 	string 		$pFilename
201
	 * @return 	PHPExcel
202
	 * @throws 	PHPExcel_Reader_Exception
203
	 */
204
	public function load($pFilename)
205
	{
206
		// Create new PHPExcel
207
		$objPHPExcel = new PHPExcel();
208
 
209
		// Load into this instance
210
		return $this->loadIntoExisting($pFilename, $objPHPExcel);
211
	}
212
 
213
	/**
214
	 * Loads PHPExcel from file into PHPExcel instance
215
	 *
216
	 * @param 	string 		$pFilename
217
	 * @param	PHPExcel	$objPHPExcel
218
	 * @return 	PHPExcel
219
	 * @throws 	PHPExcel_Reader_Exception
220
	 */
221
	public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
222
	{
223
		// Open file
224
		$this->_openFile($pFilename);
225
		if (!$this->_isValidFormat()) {
226
			fclose ($this->_fileHandle);
227
			throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
228
		}
229
		$fileHandle = $this->_fileHandle;
230
		rewind($fileHandle);
231
 
232
		// Create new PHPExcel
233
		while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
234
			$objPHPExcel->createSheet();
235
		}
236
		$objPHPExcel->setActiveSheetIndex( $this->_sheetIndex );
237
 
238
		$fromFormats	= array('\-',	'\ ');
239
		$toFormats		= array('-',	' ');
240
 
241
		// Loop through file
242
		$rowData = array();
243
		$column = $row = '';
244
 
245
		// loop through one row (line) at a time in the file
246
		while (($rowData = fgets($fileHandle)) !== FALSE) {
247
 
248
			// convert SYLK encoded $rowData to UTF-8
249
			$rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
250
 
251
			// explode each row at semicolons while taking into account that literal semicolon (;)
252
			// is escaped like this (;;)
253
			$rowData = explode("\t",str_replace('¤',';',str_replace(';',"\t",str_replace(';;','¤',rtrim($rowData)))));
254
 
255
			$dataType = array_shift($rowData);
256
			//	Read shared styles
257
			if ($dataType == 'P') {
258
				$formatArray = array();
259
				foreach($rowData as $rowDatum) {
260
					switch($rowDatum{0}) {
261
						case 'P' :	$formatArray['numberformat']['code'] = str_replace($fromFormats,$toFormats,substr($rowDatum,1));
262
									break;
263
						case 'E' :
264
						case 'F' :	$formatArray['font']['name'] = substr($rowDatum,1);
265
									break;
266
						case 'L' :	$formatArray['font']['size'] = substr($rowDatum,1);
267
									break;
268
						case 'S' :	$styleSettings = substr($rowDatum,1);
269
									for ($i=0;$i<strlen($styleSettings);++$i) {
270
										switch ($styleSettings{$i}) {
271
											case 'I' :	$formatArray['font']['italic'] = true;
272
														break;
273
											case 'D' :	$formatArray['font']['bold'] = true;
274
														break;
275
											case 'T' :	$formatArray['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
276
														break;
277
											case 'B' :	$formatArray['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
278
														break;
279
											case 'L' :	$formatArray['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
280
														break;
281
											case 'R' :	$formatArray['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
282
														break;
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' :	$column = substr($rowDatum,1);
297
									break;
298
						case 'R' :
299
						case 'Y' :	$row = substr($rowDatum,1);
300
									break;
301
						case 'K' :	$cellData = substr($rowDatum,1);
302
									break;
303
						case 'E' :	$cellDataFormula = '='.substr($rowDatum,1);
304
									//	Convert R1C1 style references to A1 style references (but only when not quoted)
305
									$temp = explode('"',$cellDataFormula);
306
									$key = false;
307
									foreach($temp as &$value) {
308
										//	Only count/replace in alternate array entries
309
										if ($key = !$key) {
310
											preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
311
											//	Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
312
											//		through the formula from left to right. Reversing means that we work right to left.through
313
											//		the formula
314
											$cellReferences = array_reverse($cellReferences);
315
											//	Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
316
											//		then modify the formula to use that new reference
317
											foreach($cellReferences as $cellReference) {
318
												$rowReference = $cellReference[2][0];
319
												//	Empty R reference is the current row
320
												if ($rowReference == '') $rowReference = $row;
321
												//	Bracketed R references are relative to the current row
322
												if ($rowReference{0} == '[') $rowReference = $row + trim($rowReference,'[]');
323
												$columnReference = $cellReference[4][0];
324
												//	Empty C reference is the current column
325
												if ($columnReference == '') $columnReference = $column;
326
												//	Bracketed C references are relative to the current column
327
												if ($columnReference{0} == '[') $columnReference = $column + trim($columnReference,'[]');
328
												$A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference;
329
 
330
												$value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
331
											}
332
										}
333
									}
334
									unset($value);
335
									//	Then rebuild the formula string
336
									$cellDataFormula = implode('"',$temp);
337
									$hasCalculatedValue = true;
338
									break;
339
					}
340
				}
341
				$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
342
				$cellData = PHPExcel_Calculation::_unwrapResult($cellData);
343
 
344
				// Set cell value
345
				$objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
346
				if ($hasCalculatedValue) {
347
					$cellData = PHPExcel_Calculation::_unwrapResult($cellData);
348
					$objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setCalculatedValue($cellData);
349
				}
350
			//	Read cell formatting
351
			} elseif ($dataType == 'F') {
352
				$formatStyle = $columnWidth = $styleSettings = '';
353
				$styleData = array();
354
				foreach($rowData as $rowDatum) {
355
					switch($rowDatum{0}) {
356
						case 'C' :
357
						case 'X' :	$column = substr($rowDatum,1);
358
									break;
359
						case 'R' :
360
						case 'Y' :	$row = substr($rowDatum,1);
361
									break;
362
						case 'P' :	$formatStyle = $rowDatum;
363
									break;
364
						case 'W' :	list($startCol,$endCol,$columnWidth) = explode(' ',substr($rowDatum,1));
365
									break;
366
						case 'S' :	$styleSettings = substr($rowDatum,1);
367
									for ($i=0;$i<strlen($styleSettings);++$i) {
368
										switch ($styleSettings{$i}) {
369
											case 'I' :	$styleData['font']['italic'] = true;
370
														break;
371
											case 'D' :	$styleData['font']['bold'] = true;
372
														break;
373
											case 'T' :	$styleData['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
374
														break;
375
											case 'B' :	$styleData['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
376
														break;
377
											case 'L' :	$styleData['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
378
														break;
379
											case 'R' :	$styleData['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
380
														break;
381
										}
382
									}
383
									break;
384
					}
385
				}
386
				if (($formatStyle > '') && ($column > '') && ($row > '')) {
387
					$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
388
					if (isset($this->_formats[$formatStyle])) {
389
						$objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($this->_formats[$formatStyle]);
390
					}
391
				}
392
				if ((!empty($styleData)) && ($column > '') && ($row > '')) {
393
					$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
394
					$objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($styleData);
395
				}
396
				if ($columnWidth > '') {
397
					if ($startCol == $endCol) {
398
						$startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
399
						$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
400
					} else {
401
						$startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
402
						$endCol = PHPExcel_Cell::stringFromColumnIndex($endCol-1);
403
						$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
404
						do {
405
							$objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
406
						} while ($startCol != $endCol);
407
					}
408
				}
409
			} else {
410
				foreach($rowData as $rowDatum) {
411
					switch($rowDatum{0}) {
412
						case 'C' :
413
						case 'X' :	$column = substr($rowDatum,1);
414
									break;
415
						case 'R' :
416
						case 'Y' :	$row = substr($rowDatum,1);
417
									break;
418
					}
419
				}
420
			}
421
		}
422
 
423
		// Close file
424
		fclose($fileHandle);
425
 
426
		// Return
427
		return $objPHPExcel;
428
	}
429
 
430
	/**
431
	 * Get sheet index
432
	 *
433
	 * @return int
434
	 */
435
	public function getSheetIndex() {
436
		return $this->_sheetIndex;
437
	}
438
 
439
	/**
440
	 * Set sheet index
441
	 *
442
	 * @param	int		$pValue		Sheet index
443
	 * @return PHPExcel_Reader_SYLK
444
	 */
445
	public function setSheetIndex($pValue = 0) {
446
		$this->_sheetIndex = $pValue;
447
		return $this;
448
	}
449
 
450
}