File Source for SYLK.php

PHPExcel_Reader

# Source for file SYLK.php

Documentation is available at SYLK.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
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. }
38. /**
39. * PHPExcel_Reader_SYLK
40. *
41. * @category PHPExcel
42. * @package PHPExcel_Reader
43. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
44. */
45. class PHPExcel_Reader_SYLK implements PHPExcel_Reader_IReader
46. {
47. /**
48. * Input encoding
49. *
50. * @var string
51. */
52. private $_inputEncoding = 'ANSI';
54. /**
55. * Sheet index to read
56. *
57. * @var int
58. */
59. private $_sheetIndex = 0;
61. /**
62. * Formats
63. *
64. * @var array
65. */
66. private $_formats = array();
68. /**
69. * Format Count
70. *
71. * @var int
72. */
73. private $_format = 0;
75. /**
76. * PHPExcel_Reader_IReadFilter instance
77. *
78. * @var PHPExcel_Reader_IReadFilter
79. */
80. private $_readFilter = null;
82. /**
83. * Create a new PHPExcel_Reader_SYLK
84. */
85. public function __construct() {
86. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
87. }
89. /**
90. * Can the current PHPExcel_Reader_IReader read the file?
91. *
92. * @param string $pFileName
93. * @return boolean
94. */
95. public function canRead($pFilename)
96. {
97. // Check if file exists
98. if (! file_exists($pFilename)) {
99. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
100. }
102. // Read sample data (first 2 KB will do)
103. $fh = fopen($pFilename, 'r');
104. $data = fread($fh, 2048);
105. fclose($fh);
107. // Count delimiters in file
108. $delimiterCount = substr_count($data, ';');
109. if ($delimiterCount < 1) {
110. return false;
111. }
113. // Analyze first line looking for ID; signature
114. $lines = explode("\n", $data);
115. if ( substr($lines[0],0,4) != 'ID;P') {
116. return false;
117. }
119. return true;
120. }
122. /**
123. * Loads PHPExcel from file
124. *
125. * @param string $pFilename
126. * @return PHPExcel
127. * @throws Exception
128. */
129. public function load($pFilename)
130. {
131. // Create new PHPExcel
132. $objPHPExcel = new PHPExcel();
134. // Load into this instance
135. return $this-> loadIntoExisting($pFilename, $objPHPExcel);
136. }
138. /**
139. * Read filter
140. *
141. * @return PHPExcel_Reader_IReadFilter
142. */
143. public function getReadFilter() {
144. return $this->_readFilter;
145. }
147. /**
148. * Set read filter
149. *
150. * @param PHPExcel_Reader_IReadFilter $pValue
151. */
152. public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) {
153. $this->_readFilter = $pValue;
154. return $this;
155. }
157. /**
158. * Set input encoding
159. *
160. * @param string $pValue Input encoding
161. */
162. public function setInputEncoding($pValue = 'ANSI')
163. {
164. $this->_inputEncoding = $pValue;
165. return $this;
166. }
168. /**
169. * Get input encoding
170. *
171. * @return string
172. */
173. public function getInputEncoding()
174. {
175. return $this->_inputEncoding;
176. }
178. /**
179. * Loads PHPExcel from file into PHPExcel instance
180. *
181. * @param string $pFilename
182. * @param PHPExcel $objPHPExcel
183. * @return PHPExcel
184. * @throws Exception
185. */
186. public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
187. {
188. // Check if file exists
189. if (! file_exists($pFilename)) {
190. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
191. }
193. // Create new PHPExcel
194. while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
195. $objPHPExcel->createSheet();
196. }
197. $objPHPExcel->setActiveSheetIndex( $this->_sheetIndex );
199. $fromFormats = array('\-', '\ ');
200. $toFormats = array('-', ' ');
202. // Open file
203. $fileHandle = fopen($pFilename, 'r');
204. if ($fileHandle === false) {
205. throw new Exception("Could not open file $pFilename for reading.");
206. }
208. // Loop through file
209. $rowData = array();
210. $column = $row = '';
212. // loop through one row (line) at a time in the file
213. while (($rowData = fgets($fileHandle)) !== FALSE) {
215. // convert SYLK encoded $rowData to UTF-8
216. $rowData = PHPExcel_Shared_String:: SYLKtoUTF8($rowData);
218. // explode each row at semicolons while taking into account that literal semicolon (;)
219. // is escaped like this (;;)
220. $rowData = explode("\t", str_replace('¤',';', str_replace(';',"\t", str_replace(';;','¤', rtrim($rowData)))));
222. $dataType = array_shift($rowData);
223. // Read shared styles
224. if ($dataType == 'P') {
225. $formatArray = array();
226. foreach($rowData as $rowDatum) {
227. switch($rowDatum{0}) {
228. case 'P' : $formatArray['numberformat']['code'] = str_replace($fromFormats,$toFormats, substr($rowDatum,1));
229. break;
230. case 'E' :
231. case 'F' : $formatArray['font']['name'] = substr($rowDatum,1);
232. break;
233. case 'L' : $formatArray['font']['size'] = substr($rowDatum,1);
234. break;
235. case 'S' : $styleSettings = substr($rowDatum,1);
236. for ($i=0;$i<strlen($styleSettings);++$i) {
237. switch ($styleSettings{$i}) {
238. case 'I' : $formatArray['font']['italic'] = true;
239. break;
240. case 'D' : $formatArray['font']['bold'] = true;
241. break;
242. case 'T' : $formatArray['borders']['top']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
243. break;
244. case 'B' : $formatArray['borders']['bottom']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
245. break;
246. case 'L' : $formatArray['borders']['left']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
247. break;
248. case 'R' : $formatArray['borders']['right']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
249. break;
250. }
251. }
252. break;
253. }
254. }
255. $this->_formats['P'.$this->_format++] = $formatArray;
256. // Read cell value data
257. } elseif ($dataType == 'C') {
258. $hasCalculatedValue = false;
259. $cellData = $cellDataFormula = '';
260. foreach($rowData as $rowDatum) {
261. switch($rowDatum{0}) {
262. case 'C' :
263. case 'X' : $column = substr($rowDatum,1);
264. break;
265. case 'R' :
266. case 'Y' : $row = substr($rowDatum,1);
267. break;
268. case 'K' : $cellData = substr($rowDatum,1);
269. break;
270. case 'E' : $cellDataFormula = '='.substr($rowDatum,1);
271. // Convert R1C1 style references to A1 style references (but only when not quoted)
272. $temp = explode('"',$cellDataFormula);
273. $key = false;
274. foreach($temp as &$value) {
275. // Only count/replace in alternate array entries
276. if ($key = !$key) {
277. preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
278. // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
279. // through the formula from left to right. Reversing means that we work right to left.through
280. // the formula
281. $cellReferences = array_reverse($cellReferences);
282. // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
283. // then modify the formula to use that new reference
284. foreach($cellReferences as $cellReference) {
285. $rowReference = $cellReference[2][0];
286. // Empty R reference is the current row
287. if ($rowReference == '') $rowReference = $row;
288. // Bracketed R references are relative to the current row
289. if ($rowReference{0} == '[') $rowReference = $row + trim($rowReference,'[]');
290. $columnReference = $cellReference[4][0];
291. // Empty C reference is the current column
292. if ($columnReference == '') $columnReference = $column;
293. // Bracketed C references are relative to the current column
294. if ($columnReference{0} == '[') $columnReference = $column + trim($columnReference,'[]');
295. $A1CellReference = PHPExcel_Cell:: stringFromColumnIndex($columnReference-1).$rowReference;
297. $value = substr_replace($value,$A1CellReference,$cellReference[0][1], strlen($cellReference[0][0]));
298. }
299. }
300. }
301. unset($value);
302. // Then rebuild the formula string
303. $cellDataFormula = implode('"',$temp);
304. $hasCalculatedValue = true;
305. break;
306. }
307. }
308. $columnLetter = PHPExcel_Cell:: stringFromColumnIndex($column-1);
309. $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
311. // Set cell value
312. $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
313. if ($hasCalculatedValue) {
314. $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
315. $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setCalculatedValue($cellData);
316. }
317. // Read cell formatting
318. } elseif ($dataType == 'F') {
319. $formatStyle = $columnWidth = $styleSettings = '';
320. $styleData = array();
321. foreach($rowData as $rowDatum) {
322. switch($rowDatum{0}) {
323. case 'C' :
324. case 'X' : $column = substr($rowDatum,1);
325. break;
326. case 'R' :
327. case 'Y' : $row = substr($rowDatum,1);
328. break;
329. case 'P' : $formatStyle = $rowDatum;
330. break;
331. case 'W' : list($startCol,$endCol,$columnWidth) = explode(' ', substr($rowDatum,1));
332. break;
333. case 'S' : $styleSettings = substr($rowDatum,1);
334. for ($i=0;$i<strlen($styleSettings);++$i) {
335. switch ($styleSettings{$i}) {
336. case 'I' : $styleData['font']['italic'] = true;
337. break;
338. case 'D' : $styleData['font']['bold'] = true;
339. break;
340. case 'T' : $styleData['borders']['top']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
341. break;
342. case 'B' : $styleData['borders']['bottom']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
343. break;
344. case 'L' : $styleData['borders']['left']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
345. break;
346. case 'R' : $styleData['borders']['right']['style'] = PHPExcel_Style_Border:: BORDER_THIN;
347. break;
348. }
349. }
350. break;
351. }
352. }
353. if (($formatStyle > '') && ($column > '') && ($row > '')) {
354. $columnLetter = PHPExcel_Cell:: stringFromColumnIndex($column-1);
355. $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($this->_formats[$formatStyle]);
356. }
357. if (( count($styleData) > 0) && ($column > '') && ($row > '')) {
358. $columnLetter = PHPExcel_Cell:: stringFromColumnIndex($column-1);
359. $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($styleData);
360. }
361. if ($columnWidth > '') {
362. if ($startCol == $endCol) {
363. $startCol = PHPExcel_Cell:: stringFromColumnIndex($startCol-1);
364. $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
365. } else {
366. $startCol = PHPExcel_Cell:: stringFromColumnIndex($startCol-1);
367. $endCol = PHPExcel_Cell:: stringFromColumnIndex($endCol-1);
368. $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
369. do {
370. $objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
371. } while ($startCol != $endCol);
372. }
373. }
374. } else {
375. foreach($rowData as $rowDatum) {
376. switch($rowDatum{0}) {
377. case 'C' :
378. case 'X' : $column = substr($rowDatum,1);
379. break;
380. case 'R' :
381. case 'Y' : $row = substr($rowDatum,1);
382. break;
383. }
384. }
385. }
386. }
388. // Close file
389. fclose($fileHandle);
391. // Return
392. return $objPHPExcel;
393. }
395. /**
396. * Get sheet index
397. *
398. * @return int
399. */
400. public function getSheetIndex() {
401. return $this->_sheetIndex;
402. }
404. /**
405. * Set sheet index
406. *
407. * @param int $pValue Sheet index
408. * @return PHPExcel_Reader_SYLK
409. */
410. public function setSheetIndex($pValue = 0) {
411. $this->_sheetIndex = $pValue;
412. return $this;
413. }
415. }

Documentation generated on Sun, 27 Feb 2011 16:34:20 -0800 by phpDocumentor 1.4.3