spreadsheetreader_xls.php (61222B)
1 <?php 2 /** 3 * Class for parsing XLS files 4 * 5 * @author Martins Pilsetnieks 6 */ 7 class SpreadsheetReader_XLS implements Iterator, Countable { 8 /** 9 * @var array Options array, pre-populated with the default values. 10 */ 11 private $Options = array( 12 ); 13 14 /** 15 * @var resource File handle 16 */ 17 private $Handle = false; 18 19 private $Index = 0; 20 21 private $Error = false; 22 23 /** 24 * @var array Sheet information 25 */ 26 private $Sheets = false; 27 private $SheetIndexes = array(); 28 29 /** 30 * @var int Current sheet index 31 */ 32 private $CurrentSheet = 0; 33 34 /** 35 * @var array Content of the current row 36 */ 37 private $CurrentRow = array(); 38 39 /** 40 * @var int Column count in the sheet 41 */ 42 private $ColumnCount = 0; 43 /** 44 * @var int Row count in the sheet 45 */ 46 private $RowCount = 0; 47 48 /** 49 * @var array Template to use for empty rows. Retrieved rows are merged 50 * with this so that empty cells are added, too 51 */ 52 private $EmptyRow = array(); 53 54 /** 55 * @param string Path to file 56 * @param array Options 57 */ 58 public function __construct($Filepath, array $Options = null) 59 { 60 if (!is_readable($Filepath)) 61 { 62 throw new Exception('SpreadsheetReader_XLS: File not readable ('.$Filepath.')'); 63 } 64 65 if (!class_exists('Spreadsheet_Excel_Reader')) 66 { 67 throw new Exception('SpreadsheetReader_XLS: Spreadsheet_Excel_Reader class not available'); 68 } 69 70 $this -> Handle = new Spreadsheet_Excel_Reader($Filepath, false, 'UTF-8'); 71 72 if (function_exists('mb_convert_encoding')) 73 { 74 $this -> Handle -> setUTFEncoder('mb'); 75 } 76 77 if (empty($this -> Handle -> sheets)) 78 { 79 $this -> Error = true; 80 return null; 81 } 82 83 $this -> ChangeSheet(0); 84 } 85 86 public function __destruct() 87 { 88 unset($this -> Handle); 89 } 90 91 /** 92 * Retrieves an array with information about sheets in the current file 93 * 94 * @return array List of sheets (key is sheet index, value is name) 95 */ 96 public function Sheets() 97 { 98 if ($this -> Sheets === false) 99 { 100 $this -> Sheets = array(); 101 $this -> SheetIndexes = array_keys($this -> Handle -> sheets); 102 103 foreach ($this -> SheetIndexes as $SheetIndex) 104 { 105 $this -> Sheets[] = $this -> Handle -> boundsheets[$SheetIndex]['name']; 106 } 107 } 108 return $this -> Sheets; 109 } 110 111 /** 112 * Changes the current sheet in the file to another 113 * 114 * @param int Sheet index 115 * 116 * @return bool True if sheet was successfully changed, false otherwise. 117 */ 118 public function ChangeSheet($Index) 119 { 120 $Index = (int)$Index; 121 $Sheets = $this -> Sheets(); 122 123 if (isset($this -> Sheets[$Index])) 124 { 125 $this -> rewind(); 126 $this -> CurrentSheet = $this -> SheetIndexes[$Index]; 127 128 $this -> ColumnCount = $this -> Handle -> sheets[$this -> CurrentSheet]['numCols']; 129 $this -> RowCount = $this -> Handle -> sheets[$this -> CurrentSheet]['numRows']; 130 131 // For the case when Spreadsheet_Excel_Reader doesn't have the row count set correctly. 132 if (!$this -> RowCount && count($this -> Handle -> sheets[$this -> CurrentSheet]['cells'])) 133 { 134 end($this -> Handle -> sheets[$this -> CurrentSheet]['cells']); 135 $this -> RowCount = (int)key($this -> Handle -> sheets[$this -> CurrentSheet]['cells']); 136 } 137 138 if ($this -> ColumnCount) 139 { 140 $this -> EmptyRow = array_fill(1, $this -> ColumnCount, ''); 141 } 142 else 143 { 144 $this -> EmptyRow = array(); 145 } 146 } 147 148 return false; 149 } 150 151 public function __get($Name) 152 { 153 switch ($Name) 154 { 155 case 'Error': 156 return $this -> Error; 157 break; 158 } 159 return null; 160 } 161 162 // !Iterator interface methods 163 /** 164 * Rewind the Iterator to the first element. 165 * Similar to the reset() function for arrays in PHP 166 */ 167 public function rewind() 168 { 169 $this -> Index = 0; 170 } 171 172 /** 173 * Return the current element. 174 * Similar to the current() function for arrays in PHP 175 * 176 * @return mixed current element from the collection 177 */ 178 public function current() 179 { 180 if ($this -> Index == 0) 181 { 182 $this -> next(); 183 } 184 185 return $this -> CurrentRow; 186 } 187 188 /** 189 * Move forward to next element. 190 * Similar to the next() function for arrays in PHP 191 */ 192 public function next() 193 { 194 // Internal counter is advanced here instead of the if statement 195 // because apparently it's fully possible that an empty row will not be 196 // present at all 197 $this -> Index++; 198 199 if ($this -> Error) 200 { 201 return array(); 202 } 203 elseif (isset($this -> Handle -> sheets[$this -> CurrentSheet]['cells'][$this -> Index])) 204 { 205 $this -> CurrentRow = $this -> Handle -> sheets[$this -> CurrentSheet]['cells'][$this -> Index]; 206 if (!$this -> CurrentRow) 207 { 208 return array(); 209 } 210 211 $this -> CurrentRow = $this -> CurrentRow + $this -> EmptyRow; 212 ksort($this -> CurrentRow); 213 214 $this -> CurrentRow = array_values($this -> CurrentRow); 215 return $this -> CurrentRow; 216 } 217 else 218 { 219 $this -> CurrentRow = $this -> EmptyRow; 220 return $this -> CurrentRow; 221 } 222 } 223 224 /** 225 * Return the identifying key of the current element. 226 * Similar to the key() function for arrays in PHP 227 * 228 * @return mixed either an integer or a string 229 */ 230 public function key() 231 { 232 return $this -> Index; 233 } 234 235 /** 236 * Check if there is a current element after calls to rewind() or next(). 237 * Used to check if we've iterated to the end of the collection 238 * 239 * @return boolean FALSE if there's nothing more to iterate over 240 */ 241 public function valid() 242 { 243 if ($this -> Error) 244 { 245 return false; 246 } 247 return ($this -> Index <= $this -> RowCount); 248 } 249 250 // !Countable interface method 251 /** 252 * Ostensibly should return the count of the contained items but this just returns the number 253 * of rows read so far. It's not really correct but at least coherent. 254 */ 255 public function count() 256 { 257 if ($this -> Error) 258 { 259 return 0; 260 } 261 262 return $this -> RowCount; 263 } 264 } 265 266 /** 267 * A class for reading Microsoft Excel (97/2003) Spreadsheets. 268 * 269 * Version 2.21 270 * 271 * Enhanced and maintained by Matt Kruse < http://mattkruse.com > 272 * Maintained at http://code.google.com/p/php-excel-reader/ 273 * 274 * Format parsing and MUCH more contributed by: 275 * Matt Roxburgh < http://www.roxburgh.me.uk > 276 * 277 * DOCUMENTATION 278 * ============= 279 * http://code.google.com/p/php-excel-reader/wiki/Documentation 280 * 281 * CHANGE LOG 282 * ========== 283 * http://code.google.com/p/php-excel-reader/wiki/ChangeHistory 284 * 285 * DISCUSSION/SUPPORT 286 * ================== 287 * http://groups.google.com/group/php-excel-reader-discuss/topics 288 * 289 * -------------------------------------------------------------------------- 290 * 291 * Originally developed by Vadim Tkachenko under the name PHPExcelReader. 292 * (http://sourceforge.net/projects/phpexcelreader) 293 * Based on the Java version by Andy Khan (http://www.andykhan.com). Now 294 * maintained by David Sanders. Reads only Biff 7 and Biff 8 formats. 295 * 296 * PHP versions 4 and 5 297 * 298 * LICENSE: This source file is subject to version 3.0 of the PHP license 299 * that is available through the world-wide-web at the following URI: 300 * http://www.php.net/license/3_0.txt. If you did not receive a copy of 301 * the PHP License and are unable to obtain it through the web, please 302 * send a note to license@php.net so we can mail you a copy immediately. 303 * 304 * @category Spreadsheet 305 * @package Spreadsheet_Excel_Reader 306 * @author Vadim Tkachenko <vt@apachephp.com> 307 * @license http://www.php.net/license/3_0.txt PHP License 3.0 308 * @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $ 309 * @link http://pear.php.net/package/Spreadsheet_Excel_Reader 310 * @see OLE, Spreadsheet_Excel_Writer 311 * -------------------------------------------------------------------------- 312 */ 313 314 define('NUM_BIG_BLOCK_DEPOT_BLOCKS_POS', 0x2c); 315 define('SMALL_BLOCK_DEPOT_BLOCK_POS', 0x3c); 316 define('ROOT_START_BLOCK_POS', 0x30); 317 define('BIG_BLOCK_SIZE', 0x200); 318 define('SMALL_BLOCK_SIZE', 0x40); 319 define('EXTENSION_BLOCK_POS', 0x44); 320 define('NUM_EXTENSION_BLOCK_POS', 0x48); 321 define('PROPERTY_STORAGE_BLOCK_SIZE', 0x80); 322 define('BIG_BLOCK_DEPOT_BLOCKS_POS', 0x4c); 323 define('SMALL_BLOCK_THRESHOLD', 0x1000); 324 // property storage offsets 325 define('SIZE_OF_NAME_POS', 0x40); 326 define('TYPE_POS', 0x42); 327 define('START_BLOCK_POS', 0x74); 328 define('SIZE_POS', 0x78); 329 define('IDENTIFIER_OLE', pack("CCCCCCCC",0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1)); 330 331 332 function GetInt4d($data, $pos) { 333 $value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24); 334 if ($value>=4294967294) { 335 $value=-2; 336 } 337 return $value; 338 } 339 340 // http://uk.php.net/manual/en/function.getdate.php 341 function gmgetdate($ts = null){ 342 $k = array('seconds','minutes','hours','mday','wday','mon','year','yday','weekday','month',0); 343 return(array_comb($k,explode(":",gmdate('s:i:G:j:w:n:Y:z:l:F:U',is_null($ts)?time():$ts)))); 344 } 345 346 // Added for PHP4 compatibility 347 function array_comb($array1, $array2) { 348 $out = array(); 349 foreach ($array1 as $key => $value) { 350 $out[$value] = $array2[$key]; 351 } 352 return $out; 353 } 354 355 function v($data,$pos) { 356 return ord($data[$pos]) | ord($data[$pos+1])<<8; 357 } 358 359 class OLERead { 360 var $data = ''; 361 function __construct(){ } 362 363 function read($sFileName){ 364 // check if file exist and is readable (Darko Miljanovic) 365 if(!is_readable($sFileName)) { 366 $this->error = 1; 367 return false; 368 } 369 $this->data = @file_get_contents($sFileName); 370 if (!$this->data) { 371 $this->error = 1; 372 return false; 373 } 374 if (substr($this->data, 0, 8) != IDENTIFIER_OLE) { 375 $this->error = 1; 376 return false; 377 } 378 $this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS); 379 $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS); 380 $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS); 381 $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS); 382 $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS); 383 384 $bigBlockDepotBlocks = array(); 385 $pos = BIG_BLOCK_DEPOT_BLOCKS_POS; 386 $bbdBlocks = $this->numBigBlockDepotBlocks; 387 if ($this->numExtensionBlocks != 0) { 388 $bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4; 389 } 390 391 for ($i = 0; $i < $bbdBlocks; $i++) { 392 $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); 393 $pos += 4; 394 } 395 396 397 for ($j = 0; $j < $this->numExtensionBlocks; $j++) { 398 $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE; 399 $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1); 400 401 for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) { 402 $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); 403 $pos += 4; 404 } 405 406 $bbdBlocks += $blocksToRead; 407 if ($bbdBlocks < $this->numBigBlockDepotBlocks) { 408 $this->extensionBlock = GetInt4d($this->data, $pos); 409 } 410 } 411 412 // readBigBlockDepot 413 $pos = 0; 414 $index = 0; 415 $this->bigBlockChain = array(); 416 417 for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) { 418 $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE; 419 //echo "pos = $pos"; 420 for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) { 421 $this->bigBlockChain[$index] = GetInt4d($this->data, $pos); 422 $pos += 4 ; 423 $index++; 424 } 425 } 426 427 // readSmallBlockDepot(); 428 $pos = 0; 429 $index = 0; 430 $sbdBlock = $this->sbdStartBlock; 431 $this->smallBlockChain = array(); 432 433 while ($sbdBlock != -2) { 434 $pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE; 435 for ($j = 0; $j < BIG_BLOCK_SIZE / 4; $j++) { 436 $this->smallBlockChain[$index] = GetInt4d($this->data, $pos); 437 $pos += 4; 438 $index++; 439 } 440 $sbdBlock = $this->bigBlockChain[$sbdBlock]; 441 } 442 443 444 // readData(rootStartBlock) 445 $block = $this->rootStartBlock; 446 $pos = 0; 447 $this->entry = $this->__readData($block); 448 $this->__readPropertySets(); 449 } 450 451 function __readData($bl) { 452 $block = $bl; 453 $pos = 0; 454 $data = ''; 455 while ($block != -2) { 456 $pos = ($block + 1) * BIG_BLOCK_SIZE; 457 $data = $data.substr($this->data, $pos, BIG_BLOCK_SIZE); 458 $block = $this->bigBlockChain[$block]; 459 } 460 return $data; 461 } 462 463 function __readPropertySets(){ 464 $offset = 0; 465 while ($offset < strlen($this->entry)) { 466 $d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE); 467 $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8); 468 $type = ord($d[TYPE_POS]); 469 $startBlock = GetInt4d($d, START_BLOCK_POS); 470 $size = GetInt4d($d, SIZE_POS); 471 $name = ''; 472 for ($i = 0; $i < $nameSize ; $i++) { 473 $name .= $d[$i]; 474 } 475 $name = str_replace("\x00", "", $name); 476 $this->props[] = array ( 477 'name' => $name, 478 'type' => $type, 479 'startBlock' => $startBlock, 480 'size' => $size); 481 if ((strtolower($name) == "workbook") || ( strtolower($name) == "book")) { 482 $this->wrkbook = count($this->props) - 1; 483 } 484 if ($name == "Root Entry") { 485 $this->rootentry = count($this->props) - 1; 486 } 487 $offset += PROPERTY_STORAGE_BLOCK_SIZE; 488 } 489 490 } 491 492 493 function getWorkBook(){ 494 if ($this->props[$this->wrkbook]['size'] < SMALL_BLOCK_THRESHOLD){ 495 $rootdata = $this->__readData($this->props[$this->rootentry]['startBlock']); 496 $streamData = ''; 497 $block = $this->props[$this->wrkbook]['startBlock']; 498 $pos = 0; 499 while ($block != -2) { 500 $pos = $block * SMALL_BLOCK_SIZE; 501 $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE); 502 $block = $this->smallBlockChain[$block]; 503 } 504 return $streamData; 505 }else{ 506 $numBlocks = $this->props[$this->wrkbook]['size'] / BIG_BLOCK_SIZE; 507 if ($this->props[$this->wrkbook]['size'] % BIG_BLOCK_SIZE != 0) { 508 $numBlocks++; 509 } 510 511 if ($numBlocks == 0) return ''; 512 $streamData = ''; 513 $block = $this->props[$this->wrkbook]['startBlock']; 514 $pos = 0; 515 while ($block != -2) { 516 $pos = ($block + 1) * BIG_BLOCK_SIZE; 517 $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE); 518 $block = $this->bigBlockChain[$block]; 519 } 520 return $streamData; 521 } 522 } 523 } 524 525 define('SPREADSHEET_EXCEL_READER_BIFF8', 0x600); 526 define('SPREADSHEET_EXCEL_READER_BIFF7', 0x500); 527 define('SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS', 0x5); 528 define('SPREADSHEET_EXCEL_READER_WORKSHEET', 0x10); 529 define('SPREADSHEET_EXCEL_READER_TYPE_BOF', 0x809); 530 define('SPREADSHEET_EXCEL_READER_TYPE_EOF', 0x0a); 531 define('SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET', 0x85); 532 define('SPREADSHEET_EXCEL_READER_TYPE_DIMENSION', 0x200); 533 define('SPREADSHEET_EXCEL_READER_TYPE_ROW', 0x208); 534 define('SPREADSHEET_EXCEL_READER_TYPE_DBCELL', 0xd7); 535 define('SPREADSHEET_EXCEL_READER_TYPE_FILEPASS', 0x2f); 536 define('SPREADSHEET_EXCEL_READER_TYPE_NOTE', 0x1c); 537 define('SPREADSHEET_EXCEL_READER_TYPE_TXO', 0x1b6); 538 define('SPREADSHEET_EXCEL_READER_TYPE_RK', 0x7e); 539 define('SPREADSHEET_EXCEL_READER_TYPE_RK2', 0x27e); 540 define('SPREADSHEET_EXCEL_READER_TYPE_MULRK', 0xbd); 541 define('SPREADSHEET_EXCEL_READER_TYPE_MULBLANK', 0xbe); 542 define('SPREADSHEET_EXCEL_READER_TYPE_INDEX', 0x20b); 543 define('SPREADSHEET_EXCEL_READER_TYPE_SST', 0xfc); 544 define('SPREADSHEET_EXCEL_READER_TYPE_EXTSST', 0xff); 545 define('SPREADSHEET_EXCEL_READER_TYPE_CONTINUE', 0x3c); 546 define('SPREADSHEET_EXCEL_READER_TYPE_LABEL', 0x204); 547 define('SPREADSHEET_EXCEL_READER_TYPE_LABELSST', 0xfd); 548 define('SPREADSHEET_EXCEL_READER_TYPE_NUMBER', 0x203); 549 define('SPREADSHEET_EXCEL_READER_TYPE_NAME', 0x18); 550 define('SPREADSHEET_EXCEL_READER_TYPE_ARRAY', 0x221); 551 define('SPREADSHEET_EXCEL_READER_TYPE_STRING', 0x207); 552 define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA', 0x406); 553 define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA2', 0x6); 554 define('SPREADSHEET_EXCEL_READER_TYPE_FORMAT', 0x41e); 555 define('SPREADSHEET_EXCEL_READER_TYPE_XF', 0xe0); 556 define('SPREADSHEET_EXCEL_READER_TYPE_BOOLERR', 0x205); 557 define('SPREADSHEET_EXCEL_READER_TYPE_FONT', 0x0031); 558 define('SPREADSHEET_EXCEL_READER_TYPE_PALETTE', 0x0092); 559 define('SPREADSHEET_EXCEL_READER_TYPE_UNKNOWN', 0xffff); 560 define('SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR', 0x22); 561 define('SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS', 0xE5); 562 define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS' , 25569); 563 define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904', 24107); 564 define('SPREADSHEET_EXCEL_READER_MSINADAY', 86400); 565 define('SPREADSHEET_EXCEL_READER_TYPE_HYPER', 0x01b8); 566 define('SPREADSHEET_EXCEL_READER_TYPE_COLINFO', 0x7d); 567 define('SPREADSHEET_EXCEL_READER_TYPE_DEFCOLWIDTH', 0x55); 568 define('SPREADSHEET_EXCEL_READER_TYPE_STANDARDWIDTH', 0x99); 569 define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%s"); 570 571 /* 572 * Main Class 573 */ 574 class Spreadsheet_Excel_Reader { 575 // MK: Added to make data retrieval easier 576 var $colnames = array(); 577 var $colindexes = array(); 578 var $standardColWidth = 0; 579 var $defaultColWidth = 0; 580 581 function myHex($d) { 582 if ($d < 16) return "0" . dechex($d); 583 return dechex($d); 584 } 585 586 function dumpHexData($data, $pos, $length) { 587 $info = ""; 588 for ($i = 0; $i <= $length; $i++) { 589 $info .= ($i==0?"":" ") . $this->myHex(ord($data[$pos + $i])) . (ord($data[$pos + $i])>31? "[" . $data[$pos + $i] . "]":''); 590 } 591 return $info; 592 } 593 594 function getCol($col) { 595 if (is_string($col)) { 596 $col = strtolower($col); 597 if (array_key_exists($col,$this->colnames)) { 598 $col = $this->colnames[$col]; 599 } 600 } 601 return $col; 602 } 603 604 // PUBLIC API FUNCTIONS 605 // -------------------- 606 607 function val($row,$col,$sheet=0) { 608 $col = $this->getCol($col); 609 if (array_key_exists($row,$this->sheets[$sheet]['cells']) && array_key_exists($col,$this->sheets[$sheet]['cells'][$row])) { 610 return $this->sheets[$sheet]['cells'][$row][$col]; 611 } 612 return ""; 613 } 614 function value($row,$col,$sheet=0) { 615 return $this->val($row,$col,$sheet); 616 } 617 function info($row,$col,$type='',$sheet=0) { 618 $col = $this->getCol($col); 619 if (array_key_exists('cellsInfo',$this->sheets[$sheet]) 620 && array_key_exists($row,$this->sheets[$sheet]['cellsInfo']) 621 && array_key_exists($col,$this->sheets[$sheet]['cellsInfo'][$row]) 622 && array_key_exists($type,$this->sheets[$sheet]['cellsInfo'][$row][$col])) { 623 return $this->sheets[$sheet]['cellsInfo'][$row][$col][$type]; 624 } 625 return ""; 626 } 627 function type($row,$col,$sheet=0) { 628 return $this->info($row,$col,'type',$sheet); 629 } 630 function raw($row,$col,$sheet=0) { 631 return $this->info($row,$col,'raw',$sheet); 632 } 633 function rowspan($row,$col,$sheet=0) { 634 $val = $this->info($row,$col,'rowspan',$sheet); 635 if ($val=="") { return 1; } 636 return $val; 637 } 638 function colspan($row,$col,$sheet=0) { 639 $val = $this->info($row,$col,'colspan',$sheet); 640 if ($val=="") { return 1; } 641 return $val; 642 } 643 function hyperlink($row,$col,$sheet=0) { 644 $link = $this->sheets[$sheet]['cellsInfo'][$row][$col]['hyperlink']; 645 if ($link) { 646 return $link['link']; 647 } 648 return ''; 649 } 650 function rowcount($sheet=0) { 651 return $this->sheets[$sheet]['numRows']; 652 } 653 function colcount($sheet=0) { 654 return $this->sheets[$sheet]['numCols']; 655 } 656 function colwidth($col,$sheet=0) { 657 // Col width is actually the width of the number 0. So we have to estimate and come close 658 return $this->colInfo[$sheet][$col]['width']/9142*200; 659 } 660 function colhidden($col,$sheet=0) { 661 return !!$this->colInfo[$sheet][$col]['hidden']; 662 } 663 function rowheight($row,$sheet=0) { 664 return $this->rowInfo[$sheet][$row]['height']; 665 } 666 function rowhidden($row,$sheet=0) { 667 return !!$this->rowInfo[$sheet][$row]['hidden']; 668 } 669 670 // GET THE CSS FOR FORMATTING 671 // ========================== 672 function style($row,$col,$sheet=0,$properties='') { 673 $css = ""; 674 $font=$this->font($row,$col,$sheet); 675 if ($font!="") { 676 $css .= "font-family:$font;"; 677 } 678 $align=$this->align($row,$col,$sheet); 679 if ($align!="") { 680 $css .= "text-align:$align;"; 681 } 682 $height=$this->height($row,$col,$sheet); 683 if ($height!="") { 684 $css .= "font-size:$height"."px;"; 685 } 686 $bgcolor=$this->bgColor($row,$col,$sheet); 687 if ($bgcolor!="") { 688 $bgcolor = $this->colors[$bgcolor]; 689 $css .= "background-color:$bgcolor;"; 690 } 691 $color=$this->color($row,$col,$sheet); 692 if ($color!="") { 693 $css .= "color:$color;"; 694 } 695 $bold=$this->bold($row,$col,$sheet); 696 if ($bold) { 697 $css .= "font-weight:bold;"; 698 } 699 $italic=$this->italic($row,$col,$sheet); 700 if ($italic) { 701 $css .= "font-style:italic;"; 702 } 703 $underline=$this->underline($row,$col,$sheet); 704 if ($underline) { 705 $css .= "text-decoration:underline;"; 706 } 707 // Borders 708 $bLeft = $this->borderLeft($row,$col,$sheet); 709 $bRight = $this->borderRight($row,$col,$sheet); 710 $bTop = $this->borderTop($row,$col,$sheet); 711 $bBottom = $this->borderBottom($row,$col,$sheet); 712 $bLeftCol = $this->borderLeftColor($row,$col,$sheet); 713 $bRightCol = $this->borderRightColor($row,$col,$sheet); 714 $bTopCol = $this->borderTopColor($row,$col,$sheet); 715 $bBottomCol = $this->borderBottomColor($row,$col,$sheet); 716 // Try to output the minimal required style 717 if ($bLeft!="" && $bLeft==$bRight && $bRight==$bTop && $bTop==$bBottom) { 718 $css .= "border:" . $this->lineStylesCss[$bLeft] .";"; 719 } 720 else { 721 if ($bLeft!="") { $css .= "border-left:" . $this->lineStylesCss[$bLeft] .";"; } 722 if ($bRight!="") { $css .= "border-right:" . $this->lineStylesCss[$bRight] .";"; } 723 if ($bTop!="") { $css .= "border-top:" . $this->lineStylesCss[$bTop] .";"; } 724 if ($bBottom!="") { $css .= "border-bottom:" . $this->lineStylesCss[$bBottom] .";"; } 725 } 726 // Only output border colors if there is an actual border specified 727 if ($bLeft!="" && $bLeftCol!="") { $css .= "border-left-color:" . $bLeftCol .";"; } 728 if ($bRight!="" && $bRightCol!="") { $css .= "border-right-color:" . $bRightCol .";"; } 729 if ($bTop!="" && $bTopCol!="") { $css .= "border-top-color:" . $bTopCol . ";"; } 730 if ($bBottom!="" && $bBottomCol!="") { $css .= "border-bottom-color:" . $bBottomCol .";"; } 731 732 return $css; 733 } 734 735 // FORMAT PROPERTIES 736 // ================= 737 function format($row,$col,$sheet=0) { 738 return $this->info($row,$col,'format',$sheet); 739 } 740 function formatIndex($row,$col,$sheet=0) { 741 return $this->info($row,$col,'formatIndex',$sheet); 742 } 743 function formatColor($row,$col,$sheet=0) { 744 return $this->info($row,$col,'formatColor',$sheet); 745 } 746 747 // CELL (XF) PROPERTIES 748 // ==================== 749 function xfRecord($row,$col,$sheet=0) { 750 $xfIndex = $this->info($row,$col,'xfIndex',$sheet); 751 if ($xfIndex!="") { 752 return $this->xfRecords[$xfIndex]; 753 } 754 return null; 755 } 756 function xfProperty($row,$col,$sheet,$prop) { 757 $xfRecord = $this->xfRecord($row,$col,$sheet); 758 if ($xfRecord!=null) { 759 return $xfRecord[$prop]; 760 } 761 return ""; 762 } 763 function align($row,$col,$sheet=0) { 764 return $this->xfProperty($row,$col,$sheet,'align'); 765 } 766 function bgColor($row,$col,$sheet=0) { 767 return $this->xfProperty($row,$col,$sheet,'bgColor'); 768 } 769 function borderLeft($row,$col,$sheet=0) { 770 return $this->xfProperty($row,$col,$sheet,'borderLeft'); 771 } 772 function borderRight($row,$col,$sheet=0) { 773 return $this->xfProperty($row,$col,$sheet,'borderRight'); 774 } 775 function borderTop($row,$col,$sheet=0) { 776 return $this->xfProperty($row,$col,$sheet,'borderTop'); 777 } 778 function borderBottom($row,$col,$sheet=0) { 779 return $this->xfProperty($row,$col,$sheet,'borderBottom'); 780 } 781 function borderLeftColor($row,$col,$sheet=0) { 782 return $this->colors[$this->xfProperty($row,$col,$sheet,'borderLeftColor')]; 783 } 784 function borderRightColor($row,$col,$sheet=0) { 785 return $this->colors[$this->xfProperty($row,$col,$sheet,'borderRightColor')]; 786 } 787 function borderTopColor($row,$col,$sheet=0) { 788 return $this->colors[$this->xfProperty($row,$col,$sheet,'borderTopColor')]; 789 } 790 function borderBottomColor($row,$col,$sheet=0) { 791 return $this->colors[$this->xfProperty($row,$col,$sheet,'borderBottomColor')]; 792 } 793 794 // FONT PROPERTIES 795 // =============== 796 function fontRecord($row,$col,$sheet=0) { 797 $xfRecord = $this->xfRecord($row,$col,$sheet); 798 if ($xfRecord!=null) { 799 $font = $xfRecord['fontIndex']; 800 if ($font!=null) { 801 return $this->fontRecords[$font]; 802 } 803 } 804 return null; 805 } 806 function fontProperty($row,$col,$sheet=0,$prop) { 807 $font = $this->fontRecord($row,$col,$sheet); 808 if ($font!=null) { 809 return $font[$prop]; 810 } 811 return false; 812 } 813 function fontIndex($row,$col,$sheet=0) { 814 return $this->xfProperty($row,$col,$sheet,'fontIndex'); 815 } 816 function color($row,$col,$sheet=0) { 817 $formatColor = $this->formatColor($row,$col,$sheet); 818 if ($formatColor!="") { 819 return $formatColor; 820 } 821 $ci = $this->fontProperty($row,$col,$sheet,'color'); 822 return $this->rawColor($ci); 823 } 824 function rawColor($ci) { 825 if (($ci <> 0x7FFF) && ($ci <> '')) { 826 return $this->colors[$ci]; 827 } 828 return ""; 829 } 830 function bold($row,$col,$sheet=0) { 831 return $this->fontProperty($row,$col,$sheet,'bold'); 832 } 833 function italic($row,$col,$sheet=0) { 834 return $this->fontProperty($row,$col,$sheet,'italic'); 835 } 836 function underline($row,$col,$sheet=0) { 837 return $this->fontProperty($row,$col,$sheet,'under'); 838 } 839 function height($row,$col,$sheet=0) { 840 return $this->fontProperty($row,$col,$sheet,'height'); 841 } 842 function font($row,$col,$sheet=0) { 843 return $this->fontProperty($row,$col,$sheet,'font'); 844 } 845 846 // DUMP AN HTML TABLE OF THE ENTIRE XLS DATA 847 // ========================================= 848 function dump($row_numbers=false,$col_letters=false,$sheet=0,$table_class='excel') { 849 $out = "<table class=\"$table_class\" cellspacing=0>"; 850 if ($col_letters) { 851 $out .= "<thead>\n\t<tr>"; 852 if ($row_numbers) { 853 $out .= "\n\t\t<th> </th>"; 854 } 855 for($i=1;$i<=$this->colcount($sheet);$i++) { 856 $style = "width:" . ($this->colwidth($i,$sheet)*1) . "px;"; 857 if ($this->colhidden($i,$sheet)) { 858 $style .= "display:none;"; 859 } 860 $out .= "\n\t\t<th style=\"$style\">" . strtoupper($this->colindexes[$i]) . "</th>"; 861 } 862 $out .= "</tr></thead>\n"; 863 } 864 865 $out .= "<tbody>\n"; 866 for($row=1;$row<=$this->rowcount($sheet);$row++) { 867 $rowheight = $this->rowheight($row,$sheet); 868 $style = "height:" . ($rowheight*(4/3)) . "px;"; 869 if ($this->rowhidden($row,$sheet)) { 870 $style .= "display:none;"; 871 } 872 $out .= "\n\t<tr style=\"$style\">"; 873 if ($row_numbers) { 874 $out .= "\n\t\t<th>$row</th>"; 875 } 876 for($col=1;$col<=$this->colcount($sheet);$col++) { 877 // Account for Rowspans/Colspans 878 $rowspan = $this->rowspan($row,$col,$sheet); 879 $colspan = $this->colspan($row,$col,$sheet); 880 for($i=0;$i<$rowspan;$i++) { 881 for($j=0;$j<$colspan;$j++) { 882 if ($i>0 || $j>0) { 883 $this->sheets[$sheet]['cellsInfo'][$row+$i][$col+$j]['dontprint']=1; 884 } 885 } 886 } 887 if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) { 888 $style = $this->style($row,$col,$sheet); 889 if ($this->colhidden($col,$sheet)) { 890 $style .= "display:none;"; 891 } 892 $out .= "\n\t\t<td style=\"$style\"" . ($colspan > 1?" colspan=$colspan":"") . ($rowspan > 1?" rowspan=$rowspan":"") . ">"; 893 $val = $this->val($row,$col,$sheet); 894 if ($val=='') { $val=" "; } 895 else { 896 $val = htmlentities($val); 897 $link = $this->hyperlink($row,$col,$sheet); 898 if ($link!='') { 899 $val = "<a href=\"$link\">$val</a>"; 900 } 901 } 902 $out .= "<nobr>".nl2br($val)."</nobr>"; 903 $out .= "</td>"; 904 } 905 } 906 $out .= "</tr>\n"; 907 } 908 $out .= "</tbody></table>"; 909 return $out; 910 } 911 912 // -------------- 913 // END PUBLIC API 914 915 916 var $boundsheets = array(); 917 var $formatRecords = array(); 918 var $fontRecords = array(); 919 var $xfRecords = array(); 920 var $colInfo = array(); 921 var $rowInfo = array(); 922 923 var $sst = array(); 924 var $sheets = array(); 925 926 var $data; 927 var $_ole; 928 var $_defaultEncoding = "UTF-8"; 929 var $_defaultFormat = SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT; 930 var $_columnsFormat = array(); 931 var $_rowoffset = 1; 932 var $_coloffset = 1; 933 934 /** 935 * List of default date formats used by Excel 936 */ 937 var $dateFormats = array ( 938 0xe => "m/d/Y", 939 0xf => "M-d-Y", 940 0x10 => "d-M", 941 0x11 => "M-Y", 942 0x12 => "h:i a", 943 0x13 => "h:i:s a", 944 0x14 => "H:i", 945 0x15 => "H:i:s", 946 0x16 => "d/m/Y H:i", 947 0x2d => "i:s", 948 0x2e => "H:i:s", 949 0x2f => "i:s.S" 950 ); 951 952 /** 953 * Default number formats used by Excel 954 */ 955 var $numberFormats = array( 956 0x1 => "0", 957 0x2 => "0.00", 958 0x3 => "#,##0", 959 0x4 => "#,##0.00", 960 0x5 => "\$#,##0;(\$#,##0)", 961 0x6 => "\$#,##0;[Red](\$#,##0)", 962 0x7 => "\$#,##0.00;(\$#,##0.00)", 963 0x8 => "\$#,##0.00;[Red](\$#,##0.00)", 964 0x9 => "0%", 965 0xa => "0.00%", 966 0xb => "0.00E+00", 967 0x25 => "#,##0;(#,##0)", 968 0x26 => "#,##0;[Red](#,##0)", 969 0x27 => "#,##0.00;(#,##0.00)", 970 0x28 => "#,##0.00;[Red](#,##0.00)", 971 0x29 => "#,##0;(#,##0)", // Not exactly 972 0x2a => "\$#,##0;(\$#,##0)", // Not exactly 973 0x2b => "#,##0.00;(#,##0.00)", // Not exactly 974 0x2c => "\$#,##0.00;(\$#,##0.00)", // Not exactly 975 0x30 => "##0.0E+0" 976 ); 977 978 var $colors = Array( 979 0x00 => "#000000", 980 0x01 => "#FFFFFF", 981 0x02 => "#FF0000", 982 0x03 => "#00FF00", 983 0x04 => "#0000FF", 984 0x05 => "#FFFF00", 985 0x06 => "#FF00FF", 986 0x07 => "#00FFFF", 987 0x08 => "#000000", 988 0x09 => "#FFFFFF", 989 0x0A => "#FF0000", 990 0x0B => "#00FF00", 991 0x0C => "#0000FF", 992 0x0D => "#FFFF00", 993 0x0E => "#FF00FF", 994 0x0F => "#00FFFF", 995 0x10 => "#800000", 996 0x11 => "#008000", 997 0x12 => "#000080", 998 0x13 => "#808000", 999 0x14 => "#800080", 1000 0x15 => "#008080", 1001 0x16 => "#C0C0C0", 1002 0x17 => "#808080", 1003 0x18 => "#9999FF", 1004 0x19 => "#993366", 1005 0x1A => "#FFFFCC", 1006 0x1B => "#CCFFFF", 1007 0x1C => "#660066", 1008 0x1D => "#FF8080", 1009 0x1E => "#0066CC", 1010 0x1F => "#CCCCFF", 1011 0x20 => "#000080", 1012 0x21 => "#FF00FF", 1013 0x22 => "#FFFF00", 1014 0x23 => "#00FFFF", 1015 0x24 => "#800080", 1016 0x25 => "#800000", 1017 0x26 => "#008080", 1018 0x27 => "#0000FF", 1019 0x28 => "#00CCFF", 1020 0x29 => "#CCFFFF", 1021 0x2A => "#CCFFCC", 1022 0x2B => "#FFFF99", 1023 0x2C => "#99CCFF", 1024 0x2D => "#FF99CC", 1025 0x2E => "#CC99FF", 1026 0x2F => "#FFCC99", 1027 0x30 => "#3366FF", 1028 0x31 => "#33CCCC", 1029 0x32 => "#99CC00", 1030 0x33 => "#FFCC00", 1031 0x34 => "#FF9900", 1032 0x35 => "#FF6600", 1033 0x36 => "#666699", 1034 0x37 => "#969696", 1035 0x38 => "#003366", 1036 0x39 => "#339966", 1037 0x3A => "#003300", 1038 0x3B => "#333300", 1039 0x3C => "#993300", 1040 0x3D => "#993366", 1041 0x3E => "#333399", 1042 0x3F => "#333333", 1043 0x40 => "#000000", 1044 0x41 => "#FFFFFF", 1045 1046 0x43 => "#000000", 1047 0x4D => "#000000", 1048 0x4E => "#FFFFFF", 1049 0x4F => "#000000", 1050 0x50 => "#FFFFFF", 1051 0x51 => "#000000", 1052 1053 0x7FFF => "#000000" 1054 ); 1055 1056 var $lineStyles = array( 1057 0x00 => "", 1058 0x01 => "Thin", 1059 0x02 => "Medium", 1060 0x03 => "Dashed", 1061 0x04 => "Dotted", 1062 0x05 => "Thick", 1063 0x06 => "Double", 1064 0x07 => "Hair", 1065 0x08 => "Medium dashed", 1066 0x09 => "Thin dash-dotted", 1067 0x0A => "Medium dash-dotted", 1068 0x0B => "Thin dash-dot-dotted", 1069 0x0C => "Medium dash-dot-dotted", 1070 0x0D => "Slanted medium dash-dotted" 1071 ); 1072 1073 var $lineStylesCss = array( 1074 "Thin" => "1px solid", 1075 "Medium" => "2px solid", 1076 "Dashed" => "1px dashed", 1077 "Dotted" => "1px dotted", 1078 "Thick" => "3px solid", 1079 "Double" => "double", 1080 "Hair" => "1px solid", 1081 "Medium dashed" => "2px dashed", 1082 "Thin dash-dotted" => "1px dashed", 1083 "Medium dash-dotted" => "2px dashed", 1084 "Thin dash-dot-dotted" => "1px dashed", 1085 "Medium dash-dot-dotted" => "2px dashed", 1086 "Slanted medium dash-dotte" => "2px dashed" 1087 ); 1088 1089 function read16bitstring($data, $start) { 1090 $len = 0; 1091 while (ord($data[$start + $len]) + ord($data[$start + $len + 1]) > 0) $len++; 1092 return substr($data, $start, $len); 1093 } 1094 1095 // ADDED by Matt Kruse for better formatting 1096 function _format_value($format,$num,$f) { 1097 // 49==TEXT format 1098 // http://code.google.com/p/php-excel-reader/issues/detail?id=7 1099 if ( (!$f && $format=="%s") || ($f==49) || ($format=="GENERAL") ) { 1100 return array('string'=>$num, 'formatColor'=>null); 1101 } 1102 1103 // Custom pattern can be POSITIVE;NEGATIVE;ZERO 1104 // The "text" option as 4th parameter is not handled 1105 $parts = explode(";",$format); 1106 $pattern = $parts[0]; 1107 // Negative pattern 1108 if (count($parts)>2 && $num==0) { 1109 $pattern = $parts[2]; 1110 } 1111 // Zero pattern 1112 if (count($parts)>1 && $num<0) { 1113 $pattern = $parts[1]; 1114 $num = abs($num); 1115 } 1116 1117 $color = ""; 1118 $matches = array(); 1119 $color_regex = "/^\[(BLACK|BLUE|CYAN|GREEN|MAGENTA|RED|WHITE|YELLOW)\]/i"; 1120 if (preg_match($color_regex,$pattern,$matches)) { 1121 $color = strtolower($matches[1]); 1122 $pattern = preg_replace($color_regex,"",$pattern); 1123 } 1124 1125 // In Excel formats, "_" is used to add spacing, which we can't do in HTML 1126 $pattern = preg_replace("/_./","",$pattern); 1127 1128 // Some non-number characters are escaped with \, which we don't need 1129 $pattern = preg_replace("/\\\/","",$pattern); 1130 1131 // Some non-number strings are quoted, so we'll get rid of the quotes 1132 $pattern = preg_replace("/\"/","",$pattern); 1133 1134 // TEMPORARY - Convert # to 0 1135 $pattern = preg_replace("/\#/","0",$pattern); 1136 1137 // Find out if we need comma formatting 1138 $has_commas = preg_match("/,/",$pattern); 1139 if ($has_commas) { 1140 $pattern = preg_replace("/,/","",$pattern); 1141 } 1142 1143 // Handle Percentages 1144 if (preg_match("/\d(\%)([^\%]|$)/",$pattern,$matches)) { 1145 $num = $num * 100; 1146 $pattern = preg_replace("/(\d)(\%)([^\%]|$)/","$1%$3",$pattern); 1147 } 1148 1149 // Handle the number itself 1150 $number_regex = "/(\d+)(\.?)(\d*)/"; 1151 if (preg_match($number_regex,$pattern,$matches)) { 1152 $left = $matches[1]; 1153 $dec = $matches[2]; 1154 $right = $matches[3]; 1155 if ($has_commas) { 1156 $formatted = number_format($num,strlen($right)); 1157 } 1158 else { 1159 $sprintf_pattern = "%1.".strlen($right)."f"; 1160 $formatted = sprintf($sprintf_pattern, $num); 1161 } 1162 $pattern = preg_replace($number_regex, $formatted, $pattern); 1163 } 1164 1165 return array( 1166 'string'=>$pattern, 1167 'formatColor'=>$color 1168 ); 1169 } 1170 1171 /** 1172 * Constructor 1173 * 1174 * Some basic initialisation 1175 */ 1176 function __construct($file='',$store_extended_info=true,$outputEncoding='') { 1177 $this->_ole = new OLERead(); 1178 $this->setUTFEncoder('iconv'); 1179 if ($outputEncoding != '') { 1180 $this->setOutputEncoding($outputEncoding); 1181 } 1182 for ($i=1; $i<245; $i++) { 1183 $name = strtolower(( (($i-1)/26>=1)?chr(($i-1)/26+64):'') . chr(($i-1)%26+65)); 1184 $this->colnames[$name] = $i; 1185 $this->colindexes[$i] = $name; 1186 } 1187 $this->store_extended_info = $store_extended_info; 1188 if ($file!="") { 1189 $this->read($file); 1190 } 1191 } 1192 1193 /** 1194 * Set the encoding method 1195 */ 1196 function setOutputEncoding($encoding) { 1197 $this->_defaultEncoding = $encoding; 1198 } 1199 1200 /** 1201 * $encoder = 'iconv' or 'mb' 1202 * set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding 1203 * set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding 1204 */ 1205 function setUTFEncoder($encoder = 'iconv') { 1206 $this->_encoderFunction = ''; 1207 if ($encoder == 'iconv') { 1208 $this->_encoderFunction = function_exists('iconv') ? 'iconv' : ''; 1209 } elseif ($encoder == 'mb') { 1210 $this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : ''; 1211 } 1212 } 1213 1214 function setRowColOffset($iOffset) { 1215 $this->_rowoffset = $iOffset; 1216 $this->_coloffset = $iOffset; 1217 } 1218 1219 /** 1220 * Set the default number format 1221 */ 1222 function setDefaultFormat($sFormat) { 1223 $this->_defaultFormat = $sFormat; 1224 } 1225 1226 /** 1227 * Force a column to use a certain format 1228 */ 1229 function setColumnFormat($column, $sFormat) { 1230 $this->_columnsFormat[$column] = $sFormat; 1231 } 1232 1233 /** 1234 * Read the spreadsheet file using OLE, then parse 1235 */ 1236 function read($sFileName) { 1237 $res = $this->_ole->read($sFileName); 1238 1239 // oops, something goes wrong (Darko Miljanovic) 1240 if($res === false) { 1241 // check error code 1242 if($this->_ole->error == 1) { 1243 // bad file 1244 die('The filename ' . $sFileName . ' is not readable'); 1245 } 1246 // check other error codes here (eg bad fileformat, etc...) 1247 } 1248 $this->data = $this->_ole->getWorkBook(); 1249 $this->_parse(); 1250 } 1251 1252 /** 1253 * Parse a workbook 1254 * 1255 * @access private 1256 * @return bool 1257 */ 1258 function _parse() { 1259 $pos = 0; 1260 $data = $this->data; 1261 1262 $code = v($data,$pos); 1263 $length = v($data,$pos+2); 1264 $version = v($data,$pos+4); 1265 $substreamType = v($data,$pos+6); 1266 1267 $this->version = $version; 1268 1269 if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && 1270 ($version != SPREADSHEET_EXCEL_READER_BIFF7)) { 1271 return false; 1272 } 1273 1274 if ($substreamType != SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS){ 1275 return false; 1276 } 1277 1278 $pos += $length + 4; 1279 1280 $code = v($data,$pos); 1281 $length = v($data,$pos+2); 1282 1283 while ($code != SPREADSHEET_EXCEL_READER_TYPE_EOF) { 1284 switch ($code) { 1285 case SPREADSHEET_EXCEL_READER_TYPE_SST: 1286 $spos = $pos + 4; 1287 $limitpos = $spos + $length; 1288 $uniqueStrings = $this->_GetInt4d($data, $spos+4); 1289 $spos += 8; 1290 for ($i = 0; $i < $uniqueStrings; $i++) { 1291 // Read in the number of characters 1292 if ($spos == $limitpos) { 1293 $opcode = v($data,$spos); 1294 $conlength = v($data,$spos+2); 1295 if ($opcode != 0x3c) { 1296 return -1; 1297 } 1298 $spos += 4; 1299 $limitpos = $spos + $conlength; 1300 } 1301 $numChars = ord($data[$spos]) | (ord($data[$spos+1]) << 8); 1302 $spos += 2; 1303 $optionFlags = ord($data[$spos]); 1304 $spos++; 1305 $asciiEncoding = (($optionFlags & 0x01) == 0) ; 1306 $extendedString = ( ($optionFlags & 0x04) != 0); 1307 1308 // See if string contains formatting information 1309 $richString = ( ($optionFlags & 0x08) != 0); 1310 1311 if ($richString) { 1312 // Read in the crun 1313 $formattingRuns = v($data,$spos); 1314 $spos += 2; 1315 } 1316 1317 if ($extendedString) { 1318 // Read in cchExtRst 1319 $extendedRunLength = $this->_GetInt4d($data, $spos); 1320 $spos += 4; 1321 } 1322 1323 $len = ($asciiEncoding)? $numChars : $numChars*2; 1324 if ($spos + $len < $limitpos) { 1325 $retstr = substr($data, $spos, $len); 1326 $spos += $len; 1327 } 1328 else{ 1329 // found countinue 1330 $retstr = substr($data, $spos, $limitpos - $spos); 1331 $bytesRead = $limitpos - $spos; 1332 $charsLeft = $numChars - (($asciiEncoding) ? $bytesRead : ($bytesRead / 2)); 1333 $spos = $limitpos; 1334 1335 while ($charsLeft > 0){ 1336 $opcode = v($data,$spos); 1337 $conlength = v($data,$spos+2); 1338 if ($opcode != 0x3c) { 1339 return -1; 1340 } 1341 $spos += 4; 1342 $limitpos = $spos + $conlength; 1343 $option = ord($data[$spos]); 1344 $spos += 1; 1345 if ($asciiEncoding && ($option == 0)) { 1346 $len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength); 1347 $retstr .= substr($data, $spos, $len); 1348 $charsLeft -= $len; 1349 $asciiEncoding = true; 1350 } 1351 elseif (!$asciiEncoding && ($option != 0)) { 1352 $len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength); 1353 $retstr .= substr($data, $spos, $len); 1354 $charsLeft -= $len/2; 1355 $asciiEncoding = false; 1356 } 1357 elseif (!$asciiEncoding && ($option == 0)) { 1358 // Bummer - the string starts off as Unicode, but after the 1359 // continuation it is in straightforward ASCII encoding 1360 $len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength); 1361 for ($j = 0; $j < $len; $j++) { 1362 $retstr .= $data[$spos + $j].chr(0); 1363 } 1364 $charsLeft -= $len; 1365 $asciiEncoding = false; 1366 } 1367 else{ 1368 $newstr = ''; 1369 for ($j = 0; $j < strlen($retstr); $j++) { 1370 $newstr = $retstr[$j].chr(0); 1371 } 1372 $retstr = $newstr; 1373 $len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength); 1374 $retstr .= substr($data, $spos, $len); 1375 $charsLeft -= $len/2; 1376 $asciiEncoding = false; 1377 } 1378 $spos += $len; 1379 } 1380 } 1381 $retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr); 1382 1383 if ($richString){ 1384 $spos += 4 * $formattingRuns; 1385 } 1386 1387 // For extended strings, skip over the extended string data 1388 if ($extendedString) { 1389 $spos += $extendedRunLength; 1390 } 1391 $this->sst[]=$retstr; 1392 } 1393 break; 1394 case SPREADSHEET_EXCEL_READER_TYPE_FILEPASS: 1395 return false; 1396 break; 1397 case SPREADSHEET_EXCEL_READER_TYPE_NAME: 1398 break; 1399 case SPREADSHEET_EXCEL_READER_TYPE_FORMAT: 1400 $indexCode = v($data,$pos+4); 1401 if ($version == SPREADSHEET_EXCEL_READER_BIFF8) { 1402 $numchars = v($data,$pos+6); 1403 if (ord($data[$pos+8]) == 0){ 1404 $formatString = substr($data, $pos+9, $numchars); 1405 } else { 1406 $formatString = substr($data, $pos+9, $numchars*2); 1407 } 1408 } else { 1409 $numchars = ord($data[$pos+6]); 1410 $formatString = substr($data, $pos+7, $numchars*2); 1411 } 1412 $this->formatRecords[$indexCode] = $formatString; 1413 break; 1414 case SPREADSHEET_EXCEL_READER_TYPE_FONT: 1415 $height = v($data,$pos+4); 1416 $option = v($data,$pos+6); 1417 $color = v($data,$pos+8); 1418 $weight = v($data,$pos+10); 1419 $under = ord($data[$pos+14]); 1420 $font = ""; 1421 // Font name 1422 $numchars = ord($data[$pos+18]); 1423 if ((ord($data[$pos+19]) & 1) == 0){ 1424 $font = substr($data, $pos+20, $numchars); 1425 } else { 1426 $font = substr($data, $pos+20, $numchars*2); 1427 $font = $this->_encodeUTF16($font); 1428 } 1429 $this->fontRecords[] = array( 1430 'height' => $height / 20, 1431 'italic' => !!($option & 2), 1432 'color' => $color, 1433 'under' => !($under==0), 1434 'bold' => ($weight==700), 1435 'font' => $font, 1436 'raw' => $this->dumpHexData($data, $pos+3, $length) 1437 ); 1438 break; 1439 1440 case SPREADSHEET_EXCEL_READER_TYPE_PALETTE: 1441 $colors = ord($data[$pos+4]) | ord($data[$pos+5]) << 8; 1442 for ($coli = 0; $coli < $colors; $coli++) { 1443 $colOff = $pos + 2 + ($coli * 4); 1444 $colr = ord($data[$colOff]); 1445 $colg = ord($data[$colOff+1]); 1446 $colb = ord($data[$colOff+2]); 1447 $this->colors[0x07 + $coli] = '#' . $this->myhex($colr) . $this->myhex($colg) . $this->myhex($colb); 1448 } 1449 break; 1450 1451 case SPREADSHEET_EXCEL_READER_TYPE_XF: 1452 $fontIndexCode = (ord($data[$pos+4]) | ord($data[$pos+5]) << 8) - 1; 1453 $fontIndexCode = max(0,$fontIndexCode); 1454 $indexCode = ord($data[$pos+6]) | ord($data[$pos+7]) << 8; 1455 $alignbit = ord($data[$pos+10]) & 3; 1456 $bgi = (ord($data[$pos+22]) | ord($data[$pos+23]) << 8) & 0x3FFF; 1457 $bgcolor = ($bgi & 0x7F); 1458 // $bgcolor = ($bgi & 0x3f80) >> 7; 1459 $align = ""; 1460 if ($alignbit==3) { $align="right"; } 1461 if ($alignbit==2) { $align="center"; } 1462 1463 $fillPattern = (ord($data[$pos+21]) & 0xFC) >> 2; 1464 if ($fillPattern == 0) { 1465 $bgcolor = ""; 1466 } 1467 1468 $xf = array(); 1469 $xf['formatIndex'] = $indexCode; 1470 $xf['align'] = $align; 1471 $xf['fontIndex'] = $fontIndexCode; 1472 $xf['bgColor'] = $bgcolor; 1473 $xf['fillPattern'] = $fillPattern; 1474 1475 $border = ord($data[$pos+14]) | (ord($data[$pos+15]) << 8) | (ord($data[$pos+16]) << 16) | (ord($data[$pos+17]) << 24); 1476 $xf['borderLeft'] = $this->lineStyles[($border & 0xF)]; 1477 $xf['borderRight'] = $this->lineStyles[($border & 0xF0) >> 4]; 1478 $xf['borderTop'] = $this->lineStyles[($border & 0xF00) >> 8]; 1479 $xf['borderBottom'] = $this->lineStyles[($border & 0xF000) >> 12]; 1480 1481 $xf['borderLeftColor'] = ($border & 0x7F0000) >> 16; 1482 $xf['borderRightColor'] = ($border & 0x3F800000) >> 23; 1483 $border = (ord($data[$pos+18]) | ord($data[$pos+19]) << 8); 1484 1485 $xf['borderTopColor'] = ($border & 0x7F); 1486 $xf['borderBottomColor'] = ($border & 0x3F80) >> 7; 1487 1488 if (array_key_exists($indexCode, $this->dateFormats)) { 1489 $xf['type'] = 'date'; 1490 $xf['format'] = $this->dateFormats[$indexCode]; 1491 if ($align=='') { $xf['align'] = 'right'; } 1492 }elseif (array_key_exists($indexCode, $this->numberFormats)) { 1493 $xf['type'] = 'number'; 1494 $xf['format'] = $this->numberFormats[$indexCode]; 1495 if ($align=='') { $xf['align'] = 'right'; } 1496 }else{ 1497 $isdate = FALSE; 1498 $formatstr = ''; 1499 if ($indexCode > 0){ 1500 if (isset($this->formatRecords[$indexCode])) 1501 $formatstr = $this->formatRecords[$indexCode]; 1502 if ($formatstr!="") { 1503 $tmp = preg_replace("/\;.*/","",$formatstr); 1504 $tmp = preg_replace("/^\[[^\]]*\]/","",$tmp); 1505 if (preg_match("/[^hmsday\/\-:\s\\\,AMP]/i", $tmp) == 0) { // found day and time format 1506 $isdate = TRUE; 1507 $formatstr = $tmp; 1508 $formatstr = str_replace(array('AM/PM','mmmm','mmm'), array('a','F','M'), $formatstr); 1509 // m/mm are used for both minutes and months - oh SNAP! 1510 // This mess tries to fix for that. 1511 // 'm' == minutes only if following h/hh or preceding s/ss 1512 $formatstr = preg_replace("/(h:?)mm?/","$1i", $formatstr); 1513 $formatstr = preg_replace("/mm?(:?s)/","i$1", $formatstr); 1514 // A single 'm' = n in PHP 1515 $formatstr = preg_replace("/(^|[^m])m([^m]|$)/", '$1n$2', $formatstr); 1516 $formatstr = preg_replace("/(^|[^m])m([^m]|$)/", '$1n$2', $formatstr); 1517 // else it's months 1518 $formatstr = str_replace('mm', 'm', $formatstr); 1519 // Convert single 'd' to 'j' 1520 $formatstr = preg_replace("/(^|[^d])d([^d]|$)/", '$1j$2', $formatstr); 1521 $formatstr = str_replace(array('dddd','ddd','dd','yyyy','yy','hh','h'), array('l','D','d','Y','y','H','g'), $formatstr); 1522 $formatstr = preg_replace("/ss?/", 's', $formatstr); 1523 } 1524 } 1525 } 1526 if ($isdate){ 1527 $xf['type'] = 'date'; 1528 $xf['format'] = $formatstr; 1529 if ($align=='') { $xf['align'] = 'right'; } 1530 }else{ 1531 // If the format string has a 0 or # in it, we'll assume it's a number 1532 if (preg_match("/[0#]/", $formatstr)) { 1533 $xf['type'] = 'number'; 1534 if ($align=='') { $xf['align']='right'; } 1535 } 1536 else { 1537 $xf['type'] = 'other'; 1538 } 1539 $xf['format'] = $formatstr; 1540 $xf['code'] = $indexCode; 1541 } 1542 } 1543 $this->xfRecords[] = $xf; 1544 break; 1545 case SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR: 1546 $this->nineteenFour = (ord($data[$pos+4]) == 1); 1547 break; 1548 case SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET: 1549 $rec_offset = $this->_GetInt4d($data, $pos+4); 1550 $rec_typeFlag = ord($data[$pos+8]); 1551 $rec_visibilityFlag = ord($data[$pos+9]); 1552 $rec_length = ord($data[$pos+10]); 1553 1554 if ($version == SPREADSHEET_EXCEL_READER_BIFF8){ 1555 $chartype = ord($data[$pos+11]); 1556 if ($chartype == 0){ 1557 $rec_name = substr($data, $pos+12, $rec_length); 1558 } else { 1559 $rec_name = $this->_encodeUTF16(substr($data, $pos+12, $rec_length*2)); 1560 } 1561 }elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){ 1562 $rec_name = substr($data, $pos+11, $rec_length); 1563 } 1564 $this->boundsheets[] = array('name'=>$rec_name,'offset'=>$rec_offset); 1565 break; 1566 1567 } 1568 1569 $pos += $length + 4; 1570 $code = ord($data[$pos]) | ord($data[$pos+1])<<8; 1571 $length = ord($data[$pos+2]) | ord($data[$pos+3])<<8; 1572 } 1573 1574 foreach ($this->boundsheets as $key=>$val){ 1575 $this->sn = $key; 1576 $this->_parsesheet($val['offset']); 1577 } 1578 return true; 1579 } 1580 1581 /** 1582 * Parse a worksheet 1583 */ 1584 function _parsesheet($spos) { 1585 $cont = true; 1586 $data = $this->data; 1587 // read BOF 1588 $code = ord($data[$spos]) | ord($data[$spos+1])<<8; 1589 $length = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1590 1591 $version = ord($data[$spos + 4]) | ord($data[$spos + 5])<<8; 1592 $substreamType = ord($data[$spos + 6]) | ord($data[$spos + 7])<<8; 1593 1594 if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && ($version != SPREADSHEET_EXCEL_READER_BIFF7)) { 1595 return -1; 1596 } 1597 1598 if ($substreamType != SPREADSHEET_EXCEL_READER_WORKSHEET){ 1599 return -2; 1600 } 1601 $spos += $length + 4; 1602 while($cont) { 1603 $lowcode = ord($data[$spos]); 1604 if ($lowcode == SPREADSHEET_EXCEL_READER_TYPE_EOF) break; 1605 $code = $lowcode | ord($data[$spos+1])<<8; 1606 $length = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1607 $spos += 4; 1608 $this->sheets[$this->sn]['maxrow'] = $this->_rowoffset - 1; 1609 $this->sheets[$this->sn]['maxcol'] = $this->_coloffset - 1; 1610 unset($this->rectype); 1611 switch ($code) { 1612 case SPREADSHEET_EXCEL_READER_TYPE_DIMENSION: 1613 if (!isset($this->numRows)) { 1614 if (($length == 10) || ($version == SPREADSHEET_EXCEL_READER_BIFF7)){ 1615 $this->sheets[$this->sn]['numRows'] = ord($data[$spos+2]) | ord($data[$spos+3]) << 8; 1616 $this->sheets[$this->sn]['numCols'] = ord($data[$spos+6]) | ord($data[$spos+7]) << 8; 1617 } else { 1618 $this->sheets[$this->sn]['numRows'] = ord($data[$spos+4]) | ord($data[$spos+5]) << 8; 1619 $this->sheets[$this->sn]['numCols'] = ord($data[$spos+10]) | ord($data[$spos+11]) << 8; 1620 } 1621 } 1622 break; 1623 case SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS: 1624 $cellRanges = ord($data[$spos]) | ord($data[$spos+1])<<8; 1625 for ($i = 0; $i < $cellRanges; $i++) { 1626 $fr = ord($data[$spos + 8*$i + 2]) | ord($data[$spos + 8*$i + 3])<<8; 1627 $lr = ord($data[$spos + 8*$i + 4]) | ord($data[$spos + 8*$i + 5])<<8; 1628 $fc = ord($data[$spos + 8*$i + 6]) | ord($data[$spos + 8*$i + 7])<<8; 1629 $lc = ord($data[$spos + 8*$i + 8]) | ord($data[$spos + 8*$i + 9])<<8; 1630 if ($lr - $fr > 0) { 1631 $this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['rowspan'] = $lr - $fr + 1; 1632 } 1633 if ($lc - $fc > 0) { 1634 $this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['colspan'] = $lc - $fc + 1; 1635 } 1636 } 1637 break; 1638 case SPREADSHEET_EXCEL_READER_TYPE_RK: 1639 case SPREADSHEET_EXCEL_READER_TYPE_RK2: 1640 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1641 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1642 $rknum = $this->_GetInt4d($data, $spos + 6); 1643 $numValue = $this->_GetIEEE754($rknum); 1644 $info = $this->_getCellDetails($spos,$numValue,$column); 1645 $this->addcell($row, $column, $info['string'],$info); 1646 break; 1647 case SPREADSHEET_EXCEL_READER_TYPE_LABELSST: 1648 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1649 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1650 $xfindex = ord($data[$spos+4]) | ord($data[$spos+5])<<8; 1651 $index = $this->_GetInt4d($data, $spos + 6); 1652 $this->addcell($row, $column, $this->sst[$index], array('xfIndex'=>$xfindex) ); 1653 break; 1654 case SPREADSHEET_EXCEL_READER_TYPE_MULRK: 1655 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1656 $colFirst = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1657 $colLast = ord($data[$spos + $length - 2]) | ord($data[$spos + $length - 1])<<8; 1658 $columns = $colLast - $colFirst + 1; 1659 $tmppos = $spos+4; 1660 for ($i = 0; $i < $columns; $i++) { 1661 $numValue = $this->_GetIEEE754($this->_GetInt4d($data, $tmppos + 2)); 1662 $info = $this->_getCellDetails($tmppos-4,$numValue,$colFirst + $i + 1); 1663 $tmppos += 6; 1664 $this->addcell($row, $colFirst + $i, $info['string'], $info); 1665 } 1666 break; 1667 case SPREADSHEET_EXCEL_READER_TYPE_NUMBER: 1668 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1669 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1670 $tmp = unpack("ddouble", substr($data, $spos + 6, 8)); // It machine machine dependent 1671 if ($this->isDate($spos)) { 1672 $numValue = $tmp['double']; 1673 } 1674 else { 1675 $numValue = $this->createNumber($spos); 1676 } 1677 $info = $this->_getCellDetails($spos,$numValue,$column); 1678 $this->addcell($row, $column, $info['string'], $info); 1679 break; 1680 1681 case SPREADSHEET_EXCEL_READER_TYPE_FORMULA: 1682 case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2: 1683 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1684 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1685 if ((ord($data[$spos+6])==0) && (ord($data[$spos+12])==255) && (ord($data[$spos+13])==255)) { 1686 //String formula. Result follows in a STRING record 1687 // This row/col are stored to be referenced in that record 1688 // http://code.google.com/p/php-excel-reader/issues/detail?id=4 1689 $previousRow = $row; 1690 $previousCol = $column; 1691 } elseif ((ord($data[$spos+6])==1) && (ord($data[$spos+12])==255) && (ord($data[$spos+13])==255)) { 1692 //Boolean formula. Result is in +2; 0=false,1=true 1693 // http://code.google.com/p/php-excel-reader/issues/detail?id=4 1694 if (ord($this->data[$spos+8])==1) { 1695 $this->addcell($row, $column, "TRUE"); 1696 } else { 1697 $this->addcell($row, $column, "FALSE"); 1698 } 1699 } elseif ((ord($data[$spos+6])==2) && (ord($data[$spos+12])==255) && (ord($data[$spos+13])==255)) { 1700 //Error formula. Error code is in +2; 1701 } elseif ((ord($data[$spos+6])==3) && (ord($data[$spos+12])==255) && (ord($data[$spos+13])==255)) { 1702 //Formula result is a null string. 1703 $this->addcell($row, $column, ''); 1704 } else { 1705 // result is a number, so first 14 bytes are just like a _NUMBER record 1706 $tmp = unpack("ddouble", substr($data, $spos + 6, 8)); // It machine machine dependent 1707 if ($this->isDate($spos)) { 1708 $numValue = $tmp['double']; 1709 } 1710 else { 1711 $numValue = $this->createNumber($spos); 1712 } 1713 $info = $this->_getCellDetails($spos,$numValue,$column); 1714 $this->addcell($row, $column, $info['string'], $info); 1715 } 1716 break; 1717 case SPREADSHEET_EXCEL_READER_TYPE_BOOLERR: 1718 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1719 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1720 $string = ord($data[$spos+6]); 1721 $this->addcell($row, $column, $string); 1722 break; 1723 case SPREADSHEET_EXCEL_READER_TYPE_STRING: 1724 // http://code.google.com/p/php-excel-reader/issues/detail?id=4 1725 if ($version == SPREADSHEET_EXCEL_READER_BIFF8){ 1726 // Unicode 16 string, like an SST record 1727 $xpos = $spos; 1728 $numChars =ord($data[$xpos]) | (ord($data[$xpos+1]) << 8); 1729 $xpos += 2; 1730 $optionFlags =ord($data[$xpos]); 1731 $xpos++; 1732 $asciiEncoding = (($optionFlags &0x01) == 0) ; 1733 $extendedString = (($optionFlags & 0x04) != 0); 1734 // See if string contains formatting information 1735 $richString = (($optionFlags & 0x08) != 0); 1736 if ($richString) { 1737 // Read in the crun 1738 $formattingRuns =ord($data[$xpos]) | (ord($data[$xpos+1]) << 8); 1739 $xpos += 2; 1740 } 1741 if ($extendedString) { 1742 // Read in cchExtRst 1743 $extendedRunLength =$this->_GetInt4d($this->data, $xpos); 1744 $xpos += 4; 1745 } 1746 $len = ($asciiEncoding)?$numChars : $numChars*2; 1747 $retstr =substr($data, $xpos, $len); 1748 $xpos += $len; 1749 $retstr = ($asciiEncoding)? $retstr : $this->_encodeUTF16($retstr); 1750 } 1751 elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){ 1752 // Simple byte string 1753 $xpos = $spos; 1754 $numChars =ord($data[$xpos]) | (ord($data[$xpos+1]) << 8); 1755 $xpos += 2; 1756 $retstr =substr($data, $xpos, $numChars); 1757 } 1758 $this->addcell($previousRow, $previousCol, $retstr); 1759 break; 1760 case SPREADSHEET_EXCEL_READER_TYPE_ROW: 1761 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1762 $rowInfo = ord($data[$spos + 6]) | ((ord($data[$spos+7]) << 8) & 0x7FFF); 1763 if (($rowInfo & 0x8000) > 0) { 1764 $rowHeight = -1; 1765 } else { 1766 $rowHeight = $rowInfo & 0x7FFF; 1767 } 1768 $rowHidden = (ord($data[$spos + 12]) & 0x20) >> 5; 1769 $this->rowInfo[$this->sn][$row+1] = Array('height' => $rowHeight / 20, 'hidden'=>$rowHidden ); 1770 break; 1771 case SPREADSHEET_EXCEL_READER_TYPE_DBCELL: 1772 break; 1773 case SPREADSHEET_EXCEL_READER_TYPE_MULBLANK: 1774 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1775 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1776 $cols = ($length / 2) - 3; 1777 for ($c = 0; $c < $cols; $c++) { 1778 $xfindex = ord($data[$spos + 4 + ($c * 2)]) | ord($data[$spos + 5 + ($c * 2)])<<8; 1779 $this->addcell($row, $column + $c, "", array('xfIndex'=>$xfindex)); 1780 } 1781 break; 1782 case SPREADSHEET_EXCEL_READER_TYPE_LABEL: 1783 $row = ord($data[$spos]) | ord($data[$spos+1])<<8; 1784 $column = ord($data[$spos+2]) | ord($data[$spos+3])<<8; 1785 $this->addcell($row, $column, substr($data, $spos + 8, ord($data[$spos + 6]) | ord($data[$spos + 7])<<8)); 1786 break; 1787 case SPREADSHEET_EXCEL_READER_TYPE_EOF: 1788 $cont = false; 1789 break; 1790 case SPREADSHEET_EXCEL_READER_TYPE_HYPER: 1791 // Only handle hyperlinks to a URL 1792 $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; 1793 $row2 = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; 1794 $column = ord($this->data[$spos+4]) | ord($this->data[$spos+5])<<8; 1795 $column2 = ord($this->data[$spos+6]) | ord($this->data[$spos+7])<<8; 1796 $linkdata = Array(); 1797 $flags = ord($this->data[$spos + 28]); 1798 $udesc = ""; 1799 $ulink = ""; 1800 $uloc = 32; 1801 $linkdata['flags'] = $flags; 1802 if (($flags & 1) > 0 ) { // is a type we understand 1803 // is there a description ? 1804 if (($flags & 0x14) == 0x14 ) { // has a description 1805 $uloc += 4; 1806 $descLen = ord($this->data[$spos + 32]) | ord($this->data[$spos + 33]) << 8; 1807 $udesc = substr($this->data, $spos + $uloc, $descLen * 2); 1808 $uloc += 2 * $descLen; 1809 } 1810 $ulink = $this->read16bitstring($this->data, $spos + $uloc + 20); 1811 if ($udesc == "") { 1812 $udesc = $ulink; 1813 } 1814 } 1815 $linkdata['desc'] = $udesc; 1816 $linkdata['link'] = $this->_encodeUTF16($ulink); 1817 for ($r=$row; $r<=$row2; $r++) { 1818 for ($c=$column; $c<=$column2; $c++) { 1819 $this->sheets[$this->sn]['cellsInfo'][$r+1][$c+1]['hyperlink'] = $linkdata; 1820 } 1821 } 1822 break; 1823 case SPREADSHEET_EXCEL_READER_TYPE_DEFCOLWIDTH: 1824 $this->defaultColWidth = ord($data[$spos+4]) | ord($data[$spos+5]) << 8; 1825 break; 1826 case SPREADSHEET_EXCEL_READER_TYPE_STANDARDWIDTH: 1827 $this->standardColWidth = ord($data[$spos+4]) | ord($data[$spos+5]) << 8; 1828 break; 1829 case SPREADSHEET_EXCEL_READER_TYPE_COLINFO: 1830 $colfrom = ord($data[$spos+0]) | ord($data[$spos+1]) << 8; 1831 $colto = ord($data[$spos+2]) | ord($data[$spos+3]) << 8; 1832 $cw = ord($data[$spos+4]) | ord($data[$spos+5]) << 8; 1833 $cxf = ord($data[$spos+6]) | ord($data[$spos+7]) << 8; 1834 $co = ord($data[$spos+8]); 1835 for ($coli = $colfrom; $coli <= $colto; $coli++) { 1836 $this->colInfo[$this->sn][$coli+1] = Array('width' => $cw, 'xf' => $cxf, 'hidden' => ($co & 0x01), 'collapsed' => ($co & 0x1000) >> 12); 1837 } 1838 break; 1839 1840 default: 1841 break; 1842 } 1843 $spos += $length; 1844 } 1845 1846 if (!isset($this->sheets[$this->sn]['numRows'])) 1847 $this->sheets[$this->sn]['numRows'] = $this->sheets[$this->sn]['maxrow']; 1848 if (!isset($this->sheets[$this->sn]['numCols'])) 1849 $this->sheets[$this->sn]['numCols'] = $this->sheets[$this->sn]['maxcol']; 1850 } 1851 1852 function isDate($spos) { 1853 $xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8; 1854 return ($this->xfRecords[$xfindex]['type'] == 'date'); 1855 } 1856 1857 // Get the details for a particular cell 1858 function _getCellDetails($spos,$numValue,$column) { 1859 $xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8; 1860 $xfrecord = $this->xfRecords[$xfindex]; 1861 $type = $xfrecord['type']; 1862 1863 $format = $xfrecord['format']; 1864 $formatIndex = $xfrecord['formatIndex']; 1865 $fontIndex = $xfrecord['fontIndex']; 1866 $formatColor = ""; 1867 $rectype = ''; 1868 $string = ''; 1869 $raw = ''; 1870 1871 if (isset($this->_columnsFormat[$column + 1])){ 1872 $format = $this->_columnsFormat[$column + 1]; 1873 } 1874 1875 if ($type == 'date') { 1876 // See http://groups.google.com/group/php-excel-reader-discuss/browse_frm/thread/9c3f9790d12d8e10/f2045c2369ac79de 1877 $rectype = 'date'; 1878 // Convert numeric value into a date 1879 $utcDays = floor($numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS)); 1880 $utcValue = ($utcDays) * SPREADSHEET_EXCEL_READER_MSINADAY; 1881 $dateinfo = gmgetdate($utcValue); 1882 1883 $raw = $numValue; 1884 $fractionalDay = $numValue - floor($numValue) + .0000001; // The .0000001 is to fix for php/excel fractional diffs 1885 1886 $totalseconds = floor(SPREADSHEET_EXCEL_READER_MSINADAY * $fractionalDay); 1887 $secs = $totalseconds % 60; 1888 $totalseconds -= $secs; 1889 $hours = floor($totalseconds / (60 * 60)); 1890 $mins = floor($totalseconds / 60) % 60; 1891 $string = date ($format, mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"])); 1892 } else if ($type == 'number') { 1893 $rectype = 'number'; 1894 $formatted = $this->_format_value($format, $numValue, $formatIndex); 1895 $string = $formatted['string']; 1896 $formatColor = $formatted['formatColor']; 1897 $raw = $numValue; 1898 } else{ 1899 if ($format=="") { 1900 $format = $this->_defaultFormat; 1901 } 1902 $rectype = 'unknown'; 1903 $formatted = $this->_format_value($format, $numValue, $formatIndex); 1904 $string = $formatted['string']; 1905 $formatColor = $formatted['formatColor']; 1906 $raw = $numValue; 1907 } 1908 1909 return array( 1910 'string'=>$string, 1911 'raw'=>$raw, 1912 'rectype'=>$rectype, 1913 'format'=>$format, 1914 'formatIndex'=>$formatIndex, 1915 'fontIndex'=>$fontIndex, 1916 'formatColor'=>$formatColor, 1917 'xfIndex'=>$xfindex 1918 ); 1919 1920 } 1921 1922 1923 function createNumber($spos) { 1924 $rknumhigh = $this->_GetInt4d($this->data, $spos + 10); 1925 $rknumlow = $this->_GetInt4d($this->data, $spos + 6); 1926 $sign = ($rknumhigh & 0x80000000) >> 31; 1927 $exp = ($rknumhigh & 0x7ff00000) >> 20; 1928 $mantissa = (0x100000 | ($rknumhigh & 0x000fffff)); 1929 $mantissalow1 = ($rknumlow & 0x80000000) >> 31; 1930 $mantissalow2 = ($rknumlow & 0x7fffffff); 1931 $value = $mantissa / pow( 2 , (20- ($exp - 1023))); 1932 if ($mantissalow1 != 0) $value += 1 / pow (2 , (21 - ($exp - 1023))); 1933 $value += $mantissalow2 / pow (2 , (52 - ($exp - 1023))); 1934 if ($sign) {$value = -1 * $value;} 1935 return $value; 1936 } 1937 1938 function addcell($row, $col, $string, $info=null) { 1939 $this->sheets[$this->sn]['maxrow'] = max($this->sheets[$this->sn]['maxrow'], $row + $this->_rowoffset); 1940 $this->sheets[$this->sn]['maxcol'] = max($this->sheets[$this->sn]['maxcol'], $col + $this->_coloffset); 1941 $this->sheets[$this->sn]['cells'][$row + $this->_rowoffset][$col + $this->_coloffset] = $string; 1942 if ($this->store_extended_info && $info) { 1943 foreach ($info as $key=>$val) { 1944 $this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset][$key] = $val; 1945 } 1946 } 1947 } 1948 1949 1950 function _GetIEEE754($rknum) { 1951 if (($rknum & 0x02) != 0) { 1952 $value = $rknum >> 2; 1953 } else { 1954 //mmp 1955 // I got my info on IEEE754 encoding from 1956 // http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html 1957 // The RK format calls for using only the most significant 30 bits of the 1958 // 64 bit floating point value. The other 34 bits are assumed to be 0 1959 // So, we use the upper 30 bits of $rknum as follows... 1960 $sign = ($rknum & 0x80000000) >> 31; 1961 $exp = ($rknum & 0x7ff00000) >> 20; 1962 $mantissa = (0x100000 | ($rknum & 0x000ffffc)); 1963 $value = $mantissa / pow( 2 , (20- ($exp - 1023))); 1964 if ($sign) { 1965 $value = -1 * $value; 1966 } 1967 //end of changes by mmp 1968 } 1969 if (($rknum & 0x01) != 0) { 1970 $value /= 100; 1971 } 1972 return $value; 1973 } 1974 1975 function _encodeUTF16($string) { 1976 $result = $string; 1977 if ($this->_defaultEncoding){ 1978 switch ($this->_encoderFunction){ 1979 case 'iconv' : $result = iconv('UTF-16LE', $this->_defaultEncoding, $string); 1980 break; 1981 case 'mb_convert_encoding' : $result = mb_convert_encoding($string, $this->_defaultEncoding, 'UTF-16LE' ); 1982 break; 1983 } 1984 } 1985 return $result; 1986 } 1987 1988 function _GetInt4d($data, $pos) { 1989 $value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24); 1990 if ($value>=4294967294) { 1991 $value=-2; 1992 } 1993 return $value; 1994 } 1995 } 1996 1997 ?>