rsvp / Excel / reader.php

reader.php in RSVP and Event Management 0.5.0, at Excel/reader.php

1,086 lines 47.9 KB

Raw Download Zip

1	<?php
2	/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
3
4	/**
5	* A class for reading Microsoft Excel Spreadsheets.
6	*
7	* Originally developed by Vadim Tkachenko under the name PHPExcelReader.
8	* (http://sourceforge.net/projects/phpexcelreader)
9	* Based on the Java version by Andy Khan (http://www.andykhan.com). Now
10	* maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
11	*
12	* PHP versions 4 and 5
13	*
14	* LICENSE: This source file is subject to version 3.0 of the PHP license
15	* that is available through the world-wide-web at the following URI:
16	* http://www.php.net/license/3_0.txt. If you did not receive a copy of
17	* the PHP License and are unable to obtain it through the web, please
18	* send a note to license@php.net so we can mail you a copy immediately.
19	*
20	* @category Spreadsheet
21	* @package Spreadsheet_Excel_Reader
22	* @author Vadim Tkachenko <vt@apachephp.com>
23	* @license http://www.php.net/license/3_0.txt PHP License 3.0
24	* @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $
25	* @link http://pear.php.net/package/Spreadsheet_Excel_Reader
26	* @see OLE, Spreadsheet_Excel_Writer
27	*/
28
29
30	//require_once 'PEAR.php';
31	//require_once 'Spreadsheet/Excel/Reader/OLERead.php';
32	//require_once 'OLE.php';
33	require_once "oleread.inc";
34
35	define('SPREADSHEET_EXCEL_READER_BIFF8', 0x600);
36	define('SPREADSHEET_EXCEL_READER_BIFF7', 0x500);
37	define('SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS', 0x5);
38	define('SPREADSHEET_EXCEL_READER_WORKSHEET', 0x10);
39
40	define('SPREADSHEET_EXCEL_READER_TYPE_BOF', 0x809);
41	define('SPREADSHEET_EXCEL_READER_TYPE_EOF', 0x0a);
42	define('SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET', 0x85);
43	define('SPREADSHEET_EXCEL_READER_TYPE_DIMENSION', 0x200);
44	define('SPREADSHEET_EXCEL_READER_TYPE_ROW', 0x208);
45	define('SPREADSHEET_EXCEL_READER_TYPE_DBCELL', 0xd7);
46	define('SPREADSHEET_EXCEL_READER_TYPE_FILEPASS', 0x2f);
47	define('SPREADSHEET_EXCEL_READER_TYPE_NOTE', 0x1c);
48	define('SPREADSHEET_EXCEL_READER_TYPE_TXO', 0x1b6);
49	define('SPREADSHEET_EXCEL_READER_TYPE_RK', 0x7e);
50	define('SPREADSHEET_EXCEL_READER_TYPE_RK2', 0x27e);
51	define('SPREADSHEET_EXCEL_READER_TYPE_MULRK', 0xbd);
52	define('SPREADSHEET_EXCEL_READER_TYPE_MULBLANK', 0xbe);
53	define('SPREADSHEET_EXCEL_READER_TYPE_INDEX', 0x20b);
54	define('SPREADSHEET_EXCEL_READER_TYPE_SST', 0xfc);
55	define('SPREADSHEET_EXCEL_READER_TYPE_EXTSST', 0xff);
56	define('SPREADSHEET_EXCEL_READER_TYPE_CONTINUE', 0x3c);
57	define('SPREADSHEET_EXCEL_READER_TYPE_LABEL', 0x204);
58	define('SPREADSHEET_EXCEL_READER_TYPE_LABELSST', 0xfd);
59	define('SPREADSHEET_EXCEL_READER_TYPE_NUMBER', 0x203);
60	define('SPREADSHEET_EXCEL_READER_TYPE_NAME', 0x18);
61	define('SPREADSHEET_EXCEL_READER_TYPE_ARRAY', 0x221);
62	define('SPREADSHEET_EXCEL_READER_TYPE_STRING', 0x207);
63	define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA', 0x406);
64	define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA2', 0x6);
65	define('SPREADSHEET_EXCEL_READER_TYPE_FORMAT', 0x41e);
66	define('SPREADSHEET_EXCEL_READER_TYPE_XF', 0xe0);
67	define('SPREADSHEET_EXCEL_READER_TYPE_BOOLERR', 0x205);
68	define('SPREADSHEET_EXCEL_READER_TYPE_UNKNOWN', 0xffff);
69	define('SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR', 0x22);
70	define('SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS', 0xE5);
71
72	define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS' , 25569);
73	define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904', 24107);
74	define('SPREADSHEET_EXCEL_READER_MSINADAY', 86400);
75	//define('SPREADSHEET_EXCEL_READER_MSINADAY', 24 * 60 * 60);
76
77	//define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%.2f");
78	define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%s");
79
80
81	/*
82	* Place includes, constant defines and $_GLOBAL settings here.
83	* Make sure they have appropriate docblocks to avoid phpDocumentor
84	* construing they are documented by the page-level docblock.
85	*/
86
87	/**
88	* A class for reading Microsoft Excel Spreadsheets.
89	*
90	* Originally developed by Vadim Tkachenko under the name PHPExcelReader.
91	* (http://sourceforge.net/projects/phpexcelreader)
92	* Based on the Java version by Andy Khan (http://www.andykhan.com). Now
93	* maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
94	*
95	* @category Spreadsheet
96	* @package Spreadsheet_Excel_Reader
97	* @author Vadim Tkachenko <vt@phpapache.com>
98	* @copyright 1997-2005 The PHP Group
99	* @license http://www.php.net/license/3_0.txt PHP License 3.0
100	* @version Release: @package_version@
101	* @link http://pear.php.net/package/PackageName
102	* @see OLE, Spreadsheet_Excel_Writer
103	*/
104	class Spreadsheet_Excel_Reader
105	{
106	/**
107	* Array of worksheets found
108	*
109	* @var array
110	* @access public
111	*/
112	var $boundsheets = array();
113
114	/**
115	* Array of format records found
116	*
117	* @var array
118	* @access public
119	*/
120	var $formatRecords = array();
121
122	/**
123	* todo
124	*
125	* @var array
126	* @access public
127	*/
128	var $sst = array();
129
130	/**
131	* Array of worksheets
132	*
133	* The data is stored in 'cells' and the meta-data is stored in an array
134	* called 'cellsInfo'
135	*
136	* Example:
137	*
138	* $sheets --> 'cells' --> row --> column --> Interpreted value
139	* --> 'cellsInfo' --> row --> column --> 'type' - Can be 'date', 'number', or 'unknown'
140	* --> 'raw' - The raw data that Excel stores for that data cell
141	*
142	* @var array
143	* @access public
144	*/
145	var $sheets = array();
146
147	/**
148	* The data returned by OLE
149	*
150	* @var string
151	* @access public
152	*/
153	var $data;
154
155	/**
156	* OLE object for reading the file
157	*
158	* @var OLE object
159	* @access private
160	*/
161	var $_ole;
162
163	/**
164	* Default encoding
165	*
166	* @var string
167	* @access private
168	*/
169	var $_defaultEncoding;
170
171	/**
172	* Default number format
173	*
174	* @var integer
175	* @access private
176	*/
177	var $_defaultFormat = SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT;
178
179	/**
180	* todo
181	* List of formats to use for each column
182	*
183	* @var array
184	* @access private
185	*/
186	var $_columnsFormat = array();
187
188	/**
189	* todo
190	*
191	* @var integer
192	* @access private
193	*/
194	var $_rowoffset = 1;
195
196	/**
197	* todo
198	*
199	* @var integer
200	* @access private
201	*/
202	var $_coloffset = 1;
203
204	/**
205	* List of default date formats used by Excel
206	*
207	* @var array
208	* @access public
209	*/
210	var $dateFormats = array (
211	0xe => "d/m/Y",
212	0xf => "d-M-Y",
213	0x10 => "d-M",
214	0x11 => "M-Y",
215	0x12 => "h:i a",
216	0x13 => "h:i:s a",
217	0x14 => "H:i",
218	0x15 => "H:i:s",
219	0x16 => "d/m/Y H:i",
220	0x2d => "i:s",
221	0x2e => "H:i:s",
222	0x2f => "i:s.S");
223
224	/**
225	* Default number formats used by Excel
226	*
227	* @var array
228	* @access public
229	*/
230	var $numberFormats = array(
231	0x1 => "%1.0f", // "0"
232	0x2 => "%1.2f", // "0.00",
233	0x3 => "%1.0f", //"#,##0",
234	0x4 => "%1.2f", //"#,##0.00",
235	0x5 => "%1.0f", /"$#,##0;($#,##0)",/
236	0x6 => '$%1.0f', /"$#,##0;($#,##0)",/
237	0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
238	0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
239	0x9 => '%1.0f%%', // "0%"
240	0xa => '%1.2f%%', // "0.00%"
241	0xb => '%1.2f', // 0.00E00",
242	0x25 => '%1.0f', // "#,##0;(#,##0)",
243	0x26 => '%1.0f', //"#,##0;(#,##0)",
244	0x27 => '%1.2f', //"#,##0.00;(#,##0.00)",
245	0x28 => '%1.2f', //"#,##0.00;(#,##0.00)",
246	0x29 => '%1.0f', //"#,##0;(#,##0)",
247	0x2a => '$%1.0f', //"$#,##0;($#,##0)",
248	0x2b => '%1.2f', //"#,##0.00;(#,##0.00)",
249	0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)",
250	0x30 => '%1.0f'); //"##0.0E0";
251
252	// }}}
253	// {{{ Spreadsheet_Excel_Reader()
254
255	/**
256	* Constructor
257	*
258	* Some basic initialisation
259	*/
260	function Spreadsheet_Excel_Reader()
261	{
262	$this->_ole =& new OLERead();
263	$this->setUTFEncoder('iconv');
264	}
265
266	// }}}
267	// {{{ setOutputEncoding()
268
269	/**
270	* Set the encoding method
271	*
272	* @param string Encoding to use
273	* @access public
274	*/
275	function setOutputEncoding($encoding)
276	{
277	$this->_defaultEncoding = $encoding;
278	}
279
280	// }}}
281	// {{{ setUTFEncoder()
282
283	/**
284	* $encoder = 'iconv' or 'mb'
285	* set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding
286	* set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding
287	*
288	* @access public
289	* @param string Encoding type to use. Either 'iconv' or 'mb'
290	*/
291	function setUTFEncoder($encoder = 'iconv')
292	{
293	$this->_encoderFunction = '';
294
295	if ($encoder == 'iconv') {
296	$this->_encoderFunction = function_exists('iconv') ? 'iconv' : '';
297	} elseif ($encoder == 'mb') {
298	$this->_encoderFunction = function_exists('mb_convert_encoding') ?
299	'mb_convert_encoding' :
300	'';
301	}
302	}
303
304	// }}}
305	// {{{ setRowColOffset()
306
307	/**
308	* todo
309	*
310	* @access public
311	* @param offset
312	*/
313	function setRowColOffset($iOffset)
314	{
315	$this->_rowoffset = $iOffset;
316	$this->_coloffset = $iOffset;
317	}
318
319	// }}}
320	// {{{ setDefaultFormat()
321
322	/**
323	* Set the default number format
324	*
325	* @access public
326	* @param Default format
327	*/
328	function setDefaultFormat($sFormat)
329	{
330	$this->_defaultFormat = $sFormat;
331	}
332
333	// }}}
334	// {{{ setColumnFormat()
335
336	/**
337	* Force a column to use a certain format
338	*
339	* @access public
340	* @param integer Column number
341	* @param string Format
342	*/
343	function setColumnFormat($column, $sFormat)
344	{
345	$this->_columnsFormat[$column] = $sFormat;
346	}
347
348
349	// }}}
350	// {{{ read()
351
352	/**
353	* Read the spreadsheet file using OLE, then parse
354	*
355	* @access public
356	* @param filename
357	* @todo return a valid value
358	*/
359	function read($sFileName)
360	{
361	/*
362	require_once 'OLE.php';
363	$ole = new OLE();
364	$ole->read($sFileName);
365
366	foreach ($ole->_list as $i => $pps) {
367	if (($pps->Name == 'Workbook' \|\| $pps->Name == 'Book') &&
368	$pps->Size >= SMALL_BLOCK_THRESHOLD) {
369
370	$this->data = $ole->getData($i, 0, $ole->getDataLength($i));
371	} elseif ($pps->Name == 'Root Entry') {
372	$this->data = $ole->getData($i, 0, $ole->getDataLength($i));
373	}
374	//var_dump(strlen($ole->getData($i, 0, $ole->getDataLength($i))), $pps->Name, md5($this->data), $ole->getDataLength($i));
375	}
376	//exit;
377	$this->_parse();
378
379	return sizeof($this->sheets) > 0;
380	*/
381
382	$res = $this->_ole->read($sFileName);
383
384	// oops, something goes wrong (Darko Miljanovic)
385	if($res === false) {
386	// check error code
387	if($this->_ole->error == 1) {
388	// bad file
389	die('The filename ' . $sFileName . ' is not readable');
390	}
391	// check other error codes here (eg bad fileformat, etc...)
392	}
393
394	$this->data = $this->_ole->getWorkBook();
395
396
397	/*
398	$res = $this->_ole->read($sFileName);
399
400	if ($this->isError($res)) {
401	// var_dump($res);
402	return $this->raiseError($res);
403	}
404
405	$total = $this->_ole->ppsTotal();
406	for ($i = 0; $i < $total; $i++) {
407	if ($this->_ole->isFile($i)) {
408	$type = unpack("v", $this->_ole->getData($i, 0, 2));
409	if ($type[''] == 0x0809) { // check if it's a BIFF stream
410	$this->_index = $i;
411	$this->data = $this->_ole->getData($i, 0, $this->_ole->getDataLength($i));
412	break;
413	}
414	}
415	}
416
417	if ($this->_index === null) {
418	return $this->raiseError("$file doesn't seem to be an Excel file");
419	}
420
421	*/
422
423	//echo "data =".$this->data;
424	//$this->readRecords();
425	$this->_parse();
426	}
427
428
429	// }}}
430	// {{{ _parse()
431
432	/**
433	* Parse a workbook
434	*
435	* @access private
436	* @return bool
437	*/
438	function _parse()
439	{
440	$pos = 0;
441
442	$code = ord($this->data[$pos]) \| ord($this->data[$pos+1])<<8;
443	$length = ord($this->data[$pos+2]) \| ord($this->data[$pos+3])<<8;
444
445	$version = ord($this->data[$pos + 4]) \| ord($this->data[$pos + 5])<<8;
446	$substreamType = ord($this->data[$pos + 6]) \| ord($this->data[$pos + 7])<<8;
447	//echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";
448
449	if (($version != SPREADSHEET_EXCEL_READER_BIFF8) &&
450	($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
451	return false;
452	}
453
454	if ($substreamType != SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS){
455	return false;
456	}
457
458	//print_r($rec);
459	$pos += $length + 4;
460
461	$code = ord($this->data[$pos]) \| ord($this->data[$pos+1])<<8;
462	$length = ord($this->data[$pos+2]) \| ord($this->data[$pos+3])<<8;
463
464	while ($code != SPREADSHEET_EXCEL_READER_TYPE_EOF) {
465	switch ($code) {
466	case SPREADSHEET_EXCEL_READER_TYPE_SST:
467	//echo "Type_SST\n";
468	$spos = $pos + 4;
469	$limitpos = $spos + $length;
470	$uniqueStrings = $this->_GetInt4d($this->data, $spos+4);
471	$spos += 8;
472	for ($i = 0; $i < $uniqueStrings; $i++) {
473	// Read in the number of characters
474	if ($spos == $limitpos) {
475	$opcode = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
476	$conlength = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
477	if ($opcode != 0x3c) {
478	return -1;
479	}
480	$spos += 4;
481	$limitpos = $spos + $conlength;
482	}
483	$numChars = ord($this->data[$spos]) \| (ord($this->data[$spos+1]) << 8);
484	//echo "i = $i pos = $pos numChars = $numChars ";
485	$spos += 2;
486	$optionFlags = ord($this->data[$spos]);
487	$spos++;
488	$asciiEncoding = (($optionFlags & 0x01) == 0) ;
489	$extendedString = ( ($optionFlags & 0x04) != 0);
490
491	// See if string contains formatting information
492	$richString = ( ($optionFlags & 0x08) != 0);
493
494	if ($richString) {
495	// Read in the crun
496	$formattingRuns = ord($this->data[$spos]) \| (ord($this->data[$spos+1]) << 8);
497	$spos += 2;
498	}
499
500	if ($extendedString) {
501	// Read in cchExtRst
502	$extendedRunLength = $this->_GetInt4d($this->data, $spos);
503	$spos += 4;
504	}
505
506	$len = ($asciiEncoding)? $numChars : $numChars*2;
507	if ($spos + $len < $limitpos) {
508	$retstr = substr($this->data, $spos, $len);
509	$spos += $len;
510	}else{
511	// found countinue
512	$retstr = substr($this->data, $spos, $limitpos - $spos);
513	$bytesRead = $limitpos - $spos;
514	$charsLeft = $numChars - (($asciiEncoding) ? $bytesRead : ($bytesRead / 2));
515	$spos = $limitpos;
516
517	while ($charsLeft > 0){
518	$opcode = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
519	$conlength = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
520	if ($opcode != 0x3c) {
521	return -1;
522	}
523	$spos += 4;
524	$limitpos = $spos + $conlength;
525	$option = ord($this->data[$spos]);
526	$spos += 1;
527	if ($asciiEncoding && ($option == 0)) {
528	$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);
529	$retstr .= substr($this->data, $spos, $len);
530	$charsLeft -= $len;
531	$asciiEncoding = true;
532	}elseif (!$asciiEncoding && ($option != 0)){
533	$len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);
534	$retstr .= substr($this->data, $spos, $len);
535	$charsLeft -= $len/2;
536	$asciiEncoding = false;
537	}elseif (!$asciiEncoding && ($option == 0)) {
538	// Bummer - the string starts off as Unicode, but after the
539	// continuation it is in straightforward ASCII encoding
540	$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength);
541	for ($j = 0; $j < $len; $j++) {
542	$retstr .= $this->data[$spos + $j].chr(0);
543	}
544	$charsLeft -= $len;
545	$asciiEncoding = false;
546	}else{
547	$newstr = '';
548	for ($j = 0; $j < strlen($retstr); $j++) {
549	$newstr = $retstr[$j].chr(0);
550	}
551	$retstr = $newstr;
552	$len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength);
553	$retstr .= substr($this->data, $spos, $len);
554	$charsLeft -= $len/2;
555	$asciiEncoding = false;
556	//echo "Izavrat\n";
557	}
558	$spos += $len;
559
560	}
561	}
562	$retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr);
563	// echo "Str $i = $retstr\n";
564	if ($richString){
565	$spos += 4 * $formattingRuns;
566	}
567
568	// For extended strings, skip over the extended string data
569	if ($extendedString) {
570	$spos += $extendedRunLength;
571	}
572	//if ($retstr == 'Derby'){
573	// echo "bb\n";
574	//}
575	$this->sst[]=$retstr;
576	}
577	/*$continueRecords = array();
578	while ($this->getNextCode() == Type_CONTINUE) {
579	$continueRecords[] = &$this->nextRecord();
580	}
581	//echo " 1 Type_SST\n";
582	$this->shareStrings = new SSTRecord($r, $continueRecords);
583	//print_r($this->shareStrings->strings);
584	*/
585	// echo 'SST read: '.($time_end-$time_start)."\n";
586	break;
587
588	case SPREADSHEET_EXCEL_READER_TYPE_FILEPASS:
589	return false;
590	break;
591	case SPREADSHEET_EXCEL_READER_TYPE_NAME:
592	//echo "Type_NAME\n";
593	break;
594	case SPREADSHEET_EXCEL_READER_TYPE_FORMAT:
595	$indexCode = ord($this->data[$pos+4]) \| ord($this->data[$pos+5]) << 8;
596
597	if ($version == SPREADSHEET_EXCEL_READER_BIFF8) {
598	$numchars = ord($this->data[$pos+6]) \| ord($this->data[$pos+7]) << 8;
599	if (ord($this->data[$pos+8]) == 0){
600	$formatString = substr($this->data, $pos+9, $numchars);
601	} else {
602	$formatString = substr($this->data, $pos+9, $numchars*2);
603	}
604	} else {
605	$numchars = ord($this->data[$pos+6]);
606	$formatString = substr($this->data, $pos+7, $numchars*2);
607	}
608
609	$this->formatRecords[$indexCode] = $formatString;
610	// echo "Type.FORMAT\n";
611	break;
612	case SPREADSHEET_EXCEL_READER_TYPE_XF:
613	//global $dateFormats, $numberFormats;
614	$indexCode = ord($this->data[$pos+6]) \| ord($this->data[$pos+7]) << 8;
615	//echo "\nType.XF ".count($this->formatRecords['xfrecords'])." $indexCode ";
616	if (array_key_exists($indexCode, $this->dateFormats)) {
617	//echo "isdate ".$dateFormats[$indexCode];
618	$this->formatRecords['xfrecords'][] = array(
619	'type' => 'date',
620	'format' => $this->dateFormats[$indexCode]
621	);
622	}elseif (array_key_exists($indexCode, $this->numberFormats)) {
623	//echo "isnumber ".$this->numberFormats[$indexCode];
624	$this->formatRecords['xfrecords'][] = array(
625	'type' => 'number',
626	'format' => $this->numberFormats[$indexCode]
627	);
628	}else{
629	$isdate = FALSE;
630	if ($indexCode > 0){
631	if (isset($this->formatRecords[$indexCode]))
632	$formatstr = $this->formatRecords[$indexCode];
633	//echo '.other.';
634	//echo "\ndate-time=$formatstr=\n";
635	if ($formatstr)
636	if (preg_match("/[^hmsday\/\-:\s]/i", $formatstr) == 0) { // found day and time format
637	$isdate = TRUE;
638	$formatstr = str_replace('mm', 'i', $formatstr);
639	$formatstr = str_replace('h', 'H', $formatstr);
640	//echo "\ndate-time $formatstr \n";
641	}
642	}
643
644	if ($isdate){
645	$this->formatRecords['xfrecords'][] = array(
646	'type' => 'date',
647	'format' => $formatstr,
648	);
649	}else{
650	$this->formatRecords['xfrecords'][] = array(
651	'type' => 'other',
652	'format' => '',
653	'code' => $indexCode
654	);
655	}
656	}
657	//echo "\n";
658	break;
659	case SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR:
660	//echo "Type.NINETEENFOUR\n";
661	$this->nineteenFour = (ord($this->data[$pos+4]) == 1);
662	break;
663	case SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET:
664	//echo "Type.BOUNDSHEET\n";
665	$rec_offset = $this->_GetInt4d($this->data, $pos+4);
666	$rec_typeFlag = ord($this->data[$pos+8]);
667	$rec_visibilityFlag = ord($this->data[$pos+9]);
668	$rec_length = ord($this->data[$pos+10]);
669
670	if ($version == SPREADSHEET_EXCEL_READER_BIFF8){
671	$chartype = ord($this->data[$pos+11]);
672	if ($chartype == 0){
673	$rec_name = substr($this->data, $pos+12, $rec_length);
674	} else {
675	$rec_name = $this->_encodeUTF16(substr($this->data, $pos+12, $rec_length*2));
676	}
677	}elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){
678	$rec_name = substr($this->data, $pos+11, $rec_length);
679	}
680	$this->boundsheets[] = array('name'=>$rec_name,
681	'offset'=>$rec_offset);
682
683	break;
684
685	}
686
687	//echo "Code = ".base_convert($r['code'],10,16)."\n";
688	$pos += $length + 4;
689	$code = ord($this->data[$pos]) \| ord($this->data[$pos+1])<<8;
690	$length = ord($this->data[$pos+2]) \| ord($this->data[$pos+3])<<8;
691
692	//$r = &$this->nextRecord();
693	//echo "1 Code = ".base_convert($r['code'],10,16)."\n";
694	}
695
696	foreach ($this->boundsheets as $key=>$val){
697	$this->sn = $key;
698	$this->_parsesheet($val['offset']);
699	}
700	return true;
701
702	}
703
704	/**
705	* Parse a worksheet
706	*
707	* @access private
708	* @param todo
709	* @todo fix return codes
710	*/
711	function _parsesheet($spos)
712	{
713	$cont = true;
714	// read BOF
715	$code = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
716	$length = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
717
718	$version = ord($this->data[$spos + 4]) \| ord($this->data[$spos + 5])<<8;
719	$substreamType = ord($this->data[$spos + 6]) \| ord($this->data[$spos + 7])<<8;
720
721	if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && ($version != SPREADSHEET_EXCEL_READER_BIFF7)) {
722	return -1;
723	}
724
725	if ($substreamType != SPREADSHEET_EXCEL_READER_WORKSHEET){
726	return -2;
727	}
728	//echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n";
729	$spos += $length + 4;
730	//var_dump($this->formatRecords);
731	//echo "code $code $length";
732	while($cont) {
733	//echo "mem= ".memory_get_usage()."\n";
734	// $r = &$this->file->nextRecord();
735	$lowcode = ord($this->data[$spos]);
736	if ($lowcode == SPREADSHEET_EXCEL_READER_TYPE_EOF) break;
737	$code = $lowcode \| ord($this->data[$spos+1])<<8;
738	$length = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
739	$spos += 4;
740	$this->sheets[$this->sn]['maxrow'] = $this->_rowoffset - 1;
741	$this->sheets[$this->sn]['maxcol'] = $this->_coloffset - 1;
742	//echo "Code=".base_convert($code,10,16)." $code\n";
743	unset($this->rectype);
744	$this->multiplier = 1; // need for format with %
745	switch ($code) {
746	case SPREADSHEET_EXCEL_READER_TYPE_DIMENSION:
747	//echo 'Type_DIMENSION ';
748	if (!isset($this->numRows)) {
749	if (($length == 10) \|\| ($version == SPREADSHEET_EXCEL_READER_BIFF7)){
750	$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+2]) \| ord($this->data[$spos+3]) << 8;
751	$this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+6]) \| ord($this->data[$spos+7]) << 8;
752	} else {
753	$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+4]) \| ord($this->data[$spos+5]) << 8;
754	$this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+10]) \| ord($this->data[$spos+11]) << 8;
755	}
756	}
757	//echo 'numRows '.$this->numRows.' '.$this->numCols."\n";
758	break;
759	case SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS:
760	$cellRanges = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
761	for ($i = 0; $i < $cellRanges; $i++) {
762	$fr = ord($this->data[$spos + 8$i + 2]) \| ord($this->data[$spos + 8$i + 3])<<8;
763	$lr = ord($this->data[$spos + 8$i + 4]) \| ord($this->data[$spos + 8$i + 5])<<8;
764	$fc = ord($this->data[$spos + 8$i + 6]) \| ord($this->data[$spos + 8$i + 7])<<8;
765	$lc = ord($this->data[$spos + 8$i + 8]) \| ord($this->data[$spos + 8$i + 9])<<8;
766	//$this->sheets[$this->sn]['mergedCells'][] = array($fr + 1, $fc + 1, $lr + 1, $lc + 1);
767	if ($lr - $fr > 0) {
768	$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['rowspan'] = $lr - $fr + 1;
769	}
770	if ($lc - $fc > 0) {
771	$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['colspan'] = $lc - $fc + 1;
772	}
773	}
774	//echo "Merged Cells $cellRanges $lr $fr $lc $fc\n";
775	break;
776	case SPREADSHEET_EXCEL_READER_TYPE_RK:
777	case SPREADSHEET_EXCEL_READER_TYPE_RK2:
778	//echo 'SPREADSHEET_EXCEL_READER_TYPE_RK'."\n";
779	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
780	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
781	$rknum = $this->_GetInt4d($this->data, $spos + 6);
782	$numValue = $this->_GetIEEE754($rknum);
783	//echo $numValue." ";
784	if ($this->isDate($spos)) {
785	list($string, $raw) = $this->createDate($numValue);
786	}else{
787	$raw = $numValue;
788	if (isset($this->_columnsFormat[$column + 1])){
789	$this->curformat = $this->_columnsFormat[$column + 1];
790	}
791	$string = sprintf($this->curformat, $numValue * $this->multiplier);
792	//$this->addcell(RKRecord($r));
793	}
794	$this->addcell($row, $column, $string, $raw);
795	//echo "Type_RK $row $column $string $raw {$this->curformat}\n";
796	break;
797	case SPREADSHEET_EXCEL_READER_TYPE_LABELSST:
798	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
799	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
800	$xfindex = ord($this->data[$spos+4]) \| ord($this->data[$spos+5])<<8;
801	$index = $this->_GetInt4d($this->data, $spos + 6);
802	//var_dump($this->sst);
803	$this->addcell($row, $column, $this->sst[$index]);
804	//echo "LabelSST $row $column $string\n";
805	break;
806	case SPREADSHEET_EXCEL_READER_TYPE_MULRK:
807	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
808	$colFirst = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
809	$colLast = ord($this->data[$spos + $length - 2]) \| ord($this->data[$spos + $length - 1])<<8;
810	$columns = $colLast - $colFirst + 1;
811	$tmppos = $spos+4;
812	for ($i = 0; $i < $columns; $i++) {
813	$numValue = $this->_GetIEEE754($this->_GetInt4d($this->data, $tmppos + 2));
814	if ($this->isDate($tmppos-4)) {
815	list($string, $raw) = $this->createDate($numValue);
816	}else{
817	$raw = $numValue;
818	if (isset($this->_columnsFormat[$colFirst + $i + 1])){
819	$this->curformat = $this->_columnsFormat[$colFirst + $i + 1];
820	}
821	$string = sprintf($this->curformat, $numValue * $this->multiplier);
822	}
823	//$rec['rknumbers'][$i]['xfindex'] = ord($rec['data'][$pos]) \| ord($rec['data'][$pos+1]) << 8;
824	$tmppos += 6;
825	$this->addcell($row, $colFirst + $i, $string, $raw);
826	//echo "MULRK $row ".($colFirst + $i)." $string\n";
827	}
828	//MulRKRecord($r);
829	// Get the individual cell records from the multiple record
830	//$num = ;
831
832	break;
833	case SPREADSHEET_EXCEL_READER_TYPE_NUMBER:
834	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
835	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
836	$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
837	if ($this->isDate($spos)) {
838	list($string, $raw) = $this->createDate($tmp['double']);
839	// $this->addcell(DateRecord($r, 1));
840	}else{
841	//$raw = $tmp[''];
842	if (isset($this->_columnsFormat[$column + 1])){
843	$this->curformat = $this->_columnsFormat[$column + 1];
844	}
845	$raw = $this->createNumber($spos);
846	$string = sprintf($this->curformat, $raw * $this->multiplier);
847
848	// $this->addcell(NumberRecord($r));
849	}
850	$this->addcell($row, $column, $string, $raw);
851	//echo "Number $row $column $string\n";
852	break;
853	case SPREADSHEET_EXCEL_READER_TYPE_FORMULA:
854	case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2:
855	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
856	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
857	if ((ord($this->data[$spos+6])==0) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
858	//String formula. Result follows in a STRING record
859	//echo "FORMULA $row $column Formula with a string<br>\n";
860	} elseif ((ord($this->data[$spos+6])==1) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
861	//Boolean formula. Result is in +2; 0=false,1=true
862	} elseif ((ord($this->data[$spos+6])==2) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
863	//Error formula. Error code is in +2;
864	} elseif ((ord($this->data[$spos+6])==3) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
865	//Formula result is a null string.
866	} else {
867	// result is a number, so first 14 bytes are just like a _NUMBER record
868	$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
869	if ($this->isDate($spos)) {
870	list($string, $raw) = $this->createDate($tmp['double']);
871	// $this->addcell(DateRecord($r, 1));
872	}else{
873	//$raw = $tmp[''];
874	if (isset($this->_columnsFormat[$column + 1])){
875	$this->curformat = $this->_columnsFormat[$column + 1];
876	}
877	$raw = $this->createNumber($spos);
878	$string = sprintf($this->curformat, $raw * $this->multiplier);
879
880	// $this->addcell(NumberRecord($r));
881	}
882	$this->addcell($row, $column, $string, $raw);
883	//echo "Number $row $column $string\n";
884	}
885	break;
886	case SPREADSHEET_EXCEL_READER_TYPE_BOOLERR:
887	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
888	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
889	$string = ord($this->data[$spos+6]);
890	$this->addcell($row, $column, $string);
891	//echo 'Type_BOOLERR '."\n";
892	break;
893	case SPREADSHEET_EXCEL_READER_TYPE_ROW:
894	case SPREADSHEET_EXCEL_READER_TYPE_DBCELL:
895	case SPREADSHEET_EXCEL_READER_TYPE_MULBLANK:
896	break;
897	case SPREADSHEET_EXCEL_READER_TYPE_LABEL:
898	$row = ord($this->data[$spos]) \| ord($this->data[$spos+1])<<8;
899	$column = ord($this->data[$spos+2]) \| ord($this->data[$spos+3])<<8;
900	$this->addcell($row, $column, substr($this->data, $spos + 8, ord($this->data[$spos + 6]) \| ord($this->data[$spos + 7])<<8));
901
902	// $this->addcell(LabelRecord($r));
903	break;
904
905	case SPREADSHEET_EXCEL_READER_TYPE_EOF:
906	$cont = false;
907	break;
908	default:
909	//echo ' unknown :'.base_convert($r['code'],10,16)."\n";
910	break;
911
912	}
913	$spos += $length;
914	}
915
916	if (!isset($this->sheets[$this->sn]['numRows']))
917	$this->sheets[$this->sn]['numRows'] = $this->sheets[$this->sn]['maxrow'];
918	if (!isset($this->sheets[$this->sn]['numCols']))
919	$this->sheets[$this->sn]['numCols'] = $this->sheets[$this->sn]['maxcol'];
920
921	}
922
923	/**
924	* Check whether the current record read is a date
925	*
926	* @param todo
927	* @return boolean True if date, false otherwise
928	*/
929	function isDate($spos)
930	{
931	//$xfindex = GetInt2d(, 4);
932	$xfindex = ord($this->data[$spos+4]) \| ord($this->data[$spos+5]) << 8;
933	//echo 'check is date '.$xfindex.' '.$this->formatRecords['xfrecords'][$xfindex]['type']."\n";
934	//var_dump($this->formatRecords['xfrecords'][$xfindex]);
935	if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'date') {
936	$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
937	$this->rectype = 'date';
938	return true;
939	} else {
940	if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'number') {
941	$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format'];
942	$this->rectype = 'number';
943	if (($xfindex == 0x9) \|\| ($xfindex == 0xa)){
944	$this->multiplier = 100;
945	}
946	}else{
947	$this->curformat = $this->_defaultFormat;
948	$this->rectype = 'unknown';
949	}
950	return false;
951	}
952	}
953
954	//}}}
955	//{{{ createDate()
956
957	/**
958	* Convert the raw Excel date into a human readable format
959	*
960	* Dates in Excel are stored as number of seconds from an epoch. On
961	* Windows, the epoch is 30/12/1899 and on Mac it's 01/01/1904
962	*
963	* @access private
964	* @param integer The raw Excel value to convert
965	* @return array First element is the converted date, the second element is number a unix timestamp
966	*/
967	function createDate($numValue)
968	{
969	if ($numValue > 1) {
970	$utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
971	$utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
972	$string = date ($this->curformat, $utcValue);
973	$raw = $utcValue;
974	} else {
975	$raw = $numValue;
976	$hours = floor($numValue * 24);
977	$mins = floor($numValue * 24 * 60) - $hours * 60;
978	$secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
979	$string = date ($this->curformat, mktime($hours, $mins, $secs));
980	}
981
982	return array($string, $raw);
983	}
984
985	function createNumber($spos)
986	{
987	$rknumhigh = $this->_GetInt4d($this->data, $spos + 10);
988	$rknumlow = $this->_GetInt4d($this->data, $spos + 6);
989	//for ($i=0; $i<8; $i++) { echo ord($this->data[$i+$spos+6]) . " "; } echo "<br>";
990	$sign = ($rknumhigh & 0x80000000) >> 31;
991	$exp = ($rknumhigh & 0x7ff00000) >> 20;
992	$mantissa = (0x100000 \| ($rknumhigh & 0x000fffff));
993	$mantissalow1 = ($rknumlow & 0x80000000) >> 31;
994	$mantissalow2 = ($rknumlow & 0x7fffffff);
995	$value = $mantissa / pow( 2 , (20- ($exp - 1023)));
996	if ($mantissalow1 != 0) $value += 1 / pow (2 , (21 - ($exp - 1023)));
997	$value += $mantissalow2 / pow (2 , (52 - ($exp - 1023)));
998	//echo "Sign = $sign, Exp = $exp, mantissahighx = $mantissa, mantissalow1 = $mantissalow1, mantissalow2 = $mantissalow2<br>\n";
999	if ($sign) {$value = -1 * $value;}
1000	return $value;
1001	}
1002
1003	function addcell($row, $col, $string, $raw = '')
1004	{
1005	//echo "ADD cel $row-$col $string\n";
1006	$this->sheets[$this->sn]['maxrow'] = max($this->sheets[$this->sn]['maxrow'], $row + $this->_rowoffset);
1007	$this->sheets[$this->sn]['maxcol'] = max($this->sheets[$this->sn]['maxcol'], $col + $this->_coloffset);
1008	$this->sheets[$this->sn]['cells'][$row + $this->_rowoffset][$col + $this->_coloffset] = $string;
1009	if ($raw)
1010	$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['raw'] = $raw;
1011	if (isset($this->rectype))
1012	$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['type'] = $this->rectype;
1013
1014	}
1015
1016
1017	function _GetIEEE754($rknum)
1018	{
1019	if (($rknum & 0x02) != 0) {
1020	$value = $rknum >> 2;
1021	} else {
1022	//mmp
1023	// first comment out the previously existing 7 lines of code here
1024	// $tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc)));
1025	// //$value = $tmp[''];
1026	// if (array_key_exists(1, $tmp)) {
1027	// $value = $tmp[1];
1028	// } else {
1029	// $value = $tmp[''];
1030	// }
1031	// I got my info on IEEE754 encoding from
1032	// http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
1033	// The RK format calls for using only the most significant 30 bits of the
1034	// 64 bit floating point value. The other 34 bits are assumed to be 0
1035	// So, we use the upper 30 bits of $rknum as follows...
1036	$sign = ($rknum & 0x80000000) >> 31;
1037	$exp = ($rknum & 0x7ff00000) >> 20;
1038	$mantissa = (0x100000 \| ($rknum & 0x000ffffc));
1039	$value = $mantissa / pow( 2 , (20- ($exp - 1023)));
1040	if ($sign) {$value = -1 * $value;}
1041	//end of changes by mmp
1042
1043	}
1044
1045	if (($rknum & 0x01) != 0) {
1046	$value /= 100;
1047	}
1048	return $value;
1049	}
1050
1051	function _encodeUTF16($string)
1052	{
1053	$result = $string;
1054	if ($this->_defaultEncoding){
1055	switch ($this->_encoderFunction){
1056	case 'iconv' : $result = iconv('UTF-16LE', $this->_defaultEncoding, $string);
1057	break;
1058	case 'mb_convert_encoding' : $result = mb_convert_encoding($string, $this->_defaultEncoding, 'UTF-16LE' );
1059	break;
1060	}
1061	}
1062	return $result;
1063	}
1064
1065	function _GetInt4d($data, $pos)
1066	{
1067	$value = ord($data[$pos]) \| (ord($data[$pos+1]) << 8) \| (ord($data[$pos+2]) << 16) \| (ord($data[$pos+3]) << 24);
1068	if ($value>=4294967294)
1069	{
1070	$value=-2;
1071	}
1072	return $value;
1073	}
1074
1075	}
1076
1077	/*
1078	* Local variables:
1079	* tab-width: 4
1080	* c-basic-offset: 4
1081	* c-hanging-comment-ender-p: nil
1082	* End:
1083	*/
1084
1085	?>
1086