excelparser.php

Excel->MySQL 解决UNIX下使用Excel数据的困难。

/*DBG*/		    $this->dbglog->debug("Parsed BIFF version is 8");
				$biff_ver = 8;
				break;
			default:
				return 8;
		 }
		}

		if( $biff_ver < 5 ) {
/*DBG*/  $this->dbglog->debug("parse_worksheet() function found ($biff_ver < 5) return 8");
		  return 8;
		}
		$ptr = 0;
		$data = array('biff_version' => $biff_ver );

		while( (ord($ws[$ptr])!=0x0a) && ($ptr<strlen($ws)) ) {
					 
		 switch (ord($ws[$ptr])+256*ord($ws[$ptr+1])) {
			
		 
		  // Formula
		  // Number
		  
		  case 0x0203:
		  case 0x0006:
		  case 0x0206:
		  case 0x0406:
		  
/*DBG*/     $this->dbglog->trace("found NUMBER");

			if( ($biff_ver < 3) ){
/*DBG*/         $this->dbglog->trace("$biff_ver < 3 break;");
			    break;
			}
			if( (ord($ws[$ptr+2])+256*ord($ws[$ptr+3])) < 14 ){
/*DBG*/         $this->dbglog->debug("parse_worksheet() return 6");
				return 6;
			}

			$row = ord($ws[$ptr+4])+256*ord($ws[$ptr+5]);
			$col = ord($ws[$ptr+6])+256*ord($ws[$ptr+7]);
			$num_lo = ExcelParserUtil::str2long(substr($ws,$ptr+10,4));
			$num_hi = ExcelParserUtil::str2long(substr($ws,$ptr+14,4));
			$xf_i = ord($ws[$ptr+8])+256*ord($ws[$ptr+9]);

			if($this->isDateFormat($xf_i)){
				$data['cell'][$row][$col]['type'] = 3;
			} else {
				$data['cell'][$row][$col]['type'] = 2;
			}

			$fonti = $this->xf['font'][$xf_i];
		    	$data['cell'][$row][$fc+$i]['font'] = $fonti;

			$fexp = (($num_hi & 0x7ff00000) >> 20) - 1023;
			$val = 1+(($num_hi & 0x000fffff)+$num_lo/4294967296)/1048576;

			if( $fexp > 0 ) {
				for( $i=0; $i<$fexp; $i++ )
					$val *= 2;
			} else {
				for( $i=0; $i<abs($fexp); $i++ )
					$val /= 2;
			}
			if( $num_hi & 0x80000000 ) $val = -$val;

			$data['cell'][$row][$col]['data'] = (float)$val;

			if( !isset($data['max_row']) ||
			    ($data['max_row'] < $row) )
				$data['max_row'] = $row;

			if( !isset($data['max_col']) ||
			    ($data['max_col'] < $col) )
				$data['max_col'] = $col;

			break;

		  // RK
		  case 0x027e:
/*DBG*/  $this->dbglog->trace("found RK");
			if( ($biff_ver < 3) ) break;
			if( (ord($ws[$ptr+2])+256*ord($ws[$ptr+3])) < 0x0a )
				return 6;
			$row  = ord($ws[$ptr+4])+256*ord($ws[$ptr+5]);
			$col  = ord($ws[$ptr+6])+256*ord($ws[$ptr+7]);
			$xf_i = ord($ws[$ptr+8])+256*ord($ws[$ptr+9]);
			$val  = $this->rk_decode(
					ExcelParserUtil::str2long(substr($ws,$ptr+10,4))
				);

			if($this->isDateFormat($xf_i)==TRUE){
				$data['cell'][$row][$col]['type'] = 3;
			} else {
				$data['cell'][$row][$col]['type'] = $val['type'];
			}
			$fonti = $this->xf['font'][$xf_i];

		    $data['cell'][$row][$col]['font'] = $fonti;
			$data['cell'][$row][$col]['data'] = $val['val'];


			if( !isset($data['max_row']) ||
			    ($data['max_row'] < $row) )
				$data['max_row'] = $row;

			if( !isset($data['max_col']) ||
			    ($data['max_col'] < $col) )
				$data['max_col'] = $col;

			break;

		  // MULRK
		  case 0x00bd:
/*DBG*/  $this->dbglog->trace("found  MULL RK");
			if( ($biff_ver < 5) ) break;
			$sz = ord($ws[$ptr+2])+256*ord($ws[$ptr+3]);
			if( $sz < 6 ) return 6;

			$row = ord($ws[$ptr+4])+256*ord($ws[$ptr+5]);
			$fc = ord($ws[$ptr+6])+256*ord($ws[$ptr+7]);
			$lc = ord($ws[$ptr+$sz+2])+256*ord($ws[$ptr+$sz+3]);

			for( $i=0; $i<=$lc-$fc; $i++) {
			 $val = $this->rk_decode(
				ExcelParserUtil::str2long(substr($ws,$ptr+10+$i*6,4))
				);

			   $xf_i=ord($ws[$ptr+8+$i*6])+256*ord($ws[($ptr+9+$i*6)]);
			   if($this->isDateFormat($xf_i)==TRUE) {
			   	$data['cell'][$row][$fc+$i]['type'] = 3;
			   } else {
			   	$data['cell'][$row][$fc+$i]['type'] = $val['type'];
			   }
			   $fonti = $this->xf['font'][$xf_i];
		       $data['cell'][$row][$fc+$i]['font'] = $fonti;
			   $data['cell'][$row][$fc+$i]['data'] = $val['val'];
			}

			if( !isset($data['max_row']) ||
			    ($data['max_row'] < $row) )
				$data['max_row'] = $row;

			if( !isset($data['max_col']) ||
			    ($data['max_col'] < $lc) )
				$data['max_col'] = $lc;

			break;

		  // LABEL
		  case 0x0204:
/*DBG*/  $this->dbglog->trace("found LABEL");
			if( ($biff_ver < 3) ){
			    break;
			}
			if( (ord($ws[$ptr+2])+256*ord($ws[$ptr+3])) < 8 ){
				return 6;
			}
			$row = ord($ws[$ptr+4])+256*ord($ws[$ptr+5]);
			$col = ord($ws[$ptr+6])+256*ord($ws[$ptr+7]);
			$xf = ord($ws[$ptr+8])+256*ord($ws[$ptr+9]);
			$fonti = $this->xf['font'][$xf];
			$font =  $this->fonts[$fonti];


			$str_len = ord($ws[$ptr+10])+256*ord($ws[$ptr+11]);

			if( $ptr+12+$str_len > strlen($ws) )
				return 6;
			$this->sst['unicode'][] = false;
			$this->sst['data'][] = substr($ws,$ptr+12,$str_len);
			$data['cell'][$row][$col]['type'] = 0;
			$sst_ind = count($this->sst['data'])-1;
			$data['cell'][$row][$col]['data'] = $sst_ind;
			$data['cell'][$row][$col]['font'] = $fonti;

/*			echo str_replace("\n","<br>\n", ExcelFont::toString($font,$fonti));
		    echo "FontRecord for sting ".$this->sst['data'][$sst_ind]."<br>";*/

			if( !isset($data['max_row']) ||
			    ($data['max_row'] < $row) )
				$data['max_row'] = $row;

			if( !isset($data['max_col']) ||
			    ($data['max_col'] < $col) )
				$data['max_col'] = $col;



			break;

		  // LABELSST
		  case 0x00fd:
			if( $biff_ver < 8 ) break;
			if( (ord($ws[$ptr+2])+256*ord($ws[$ptr+3])) < 0x0a )
				return 6;
			$row = ord($ws[$ptr+4])+256*ord($ws[$ptr+5]);
			$col = ord($ws[$ptr+6])+256*ord($ws[$ptr+7]);
			$xf = ord($ws[$ptr+8])+256*ord($ws[$ptr+9]);
			$fonti = $this->xf['font'][$xf];
			$font = &$this->fonts[$fonti];

			$data['cell'][$row][$col]['type'] = 0;
			$sst_ind = ExcelParserUtil::str2long(substr($ws,$ptr+10,4));
			$data['cell'][$row][$col]['data'] = $sst_ind;
			$data['cell'][$row][$col]['font'] = $fonti;

/*            echo "FontRecord for sting at $row,$col<br>";
			echo str_replace("\n","<br>\n", ExcelFont::toString($font,$fonti));*/

			if( !isset($data['max_row']) ||
			    ($data['max_row'] < $row) )
				$data['max_row'] = $row;

			if( !isset($data['max_col']) ||
			    ($data['max_col'] < $col) )
				$data['max_col'] = $col;

			break;

		  // unknown, unsupported or unused opcode
		  default:
			break;
		 }

		$ptr += 4+256*ord($ws[$ptr+3])+ord($ws[$ptr+2]);
		}
		//$this->dbglog->debug("parse_worksheet() function returns ".var_export($data,true));

		return $data;

	}

	// Parse workbook
	//----------------

	function parse_workbook( $f_header, $dp ) {

/*DBG*/ $this->dbglog->debug("parse_workbook() function");

		$root_entry_block = $f_header->getLong(0x30);
		$num_fat_blocks = $f_header->getLong(0x2c);

/*TRC*/ $this->dbglog->trace("Header parsed");

		$this->fat = array();
		for( $i = 0; $i < $num_fat_blocks; $i++ ){
/*TRC*/		$this->dbglog->trace("FOR LOOP iteration i =".$i);

			$fat_block = $f_header->getLong( 0x4c + 4 * $i );			
			$fatbuf = $dp->get( $fat_block * 0x200, 0x200 );
			$fat = new DataProvider( $fatbuf, DP_STRING_SOURCE );

			if( $fat->getSize() < 0x200 ){
/*DBG*/    		$this->dbglog->debug("parse_workbook() function found (strlen($fat) < 0x200) returns 6");
				return 6;
			}
			
			for( $j=0; $j<0x80; $j++ )
				$this->fat[] = $fat->getLong( $j * 4 );
					
			$fat->close();
			unset( $fat_block, $fatbuf, $fat );			
		}
		
/*DBG*/ $this->dbglog->dump( $this->fat, "\$fat" );
		
		if( count($this->fat) < $num_fat_blocks ) {
/*DBG*/    	$this->dbglog->debug("parse_workbook() function found (count($this->fat) < $num_fat_blocks) returns 6");
			return 6;
		}
		
		$chain = $this->get_blocks_chain($root_entry_block);
		$dir = new DataProvider( $dp->ReadFromFat( $chain ), DP_STRING_SOURCE );
		unset( $chain );

		$this->sfat = array();
		$small_block = $f_header->getLong( 0x3c );
		if( $small_block != 0xfeffffff ) {
			
			$root_entry_index = $this->find_stream( $dir, 'Root Entry');
			
			// Deleted for support MAC files
			
			//if( $root_entry_index < 0 ) {
/*DBG*/    		//$this->dbglog->debug("parse_workbook() function dont found Root Entry returns 6");
		    	//return 6;
		 	//}
		 	
		 	$sdc_start_block = $dir->getLong( $root_entry_index * 0x80 + 0x74 );
		 	$small_data_chain = $this->get_blocks_chain($sdc_start_block);
		 	$this->max_sblocks = count($small_data_chain) * 8;
		 	
		 	$schain = $this->get_blocks_chain($small_block);		 	
		 	for( $i = 0; $i < count( $schain ); $i++ ) {
		 		
				$sfatbuf = $dp->get( $schain[$i] * 0x200, 0x200 );
				$sfat = new DataProvider( $sfatbuf, DP_STRING_SOURCE );
				
				//$this->dbglog->dump( strlen($sfatbuf), "strlen(\$sftabuf)");
				//$this->dbglog->dump( $sfat, "\$sfat");
				
		  		if( $sfat->getSize() < 0x200 ) {
/*DBG*/    	 		$this->dbglog->debug("parse_workbook() function found (strlen($sfat) < 0x200)  returns 6");
		     		return 6;
 	      		}
 	      		
		  		for( $j=0; $j<0x80; $j++ )
		   			$this->sfat[] = $sfat->getLong( $j * 4 );
		   		
		   		$sfat->close();
		   		unset( $sfatbuf, $sfat );
		 	}
		 	unset( $schain );

		 	$sfcbuf = $dp->ReadFromFat( $small_data_chain );
		  	$sdp = new DataProvider( $sfcbuf, DP_STRING_SOURCE );
		  	unset( $sfcbuf, $small_data_chain );
		}

		$workbook_index = $this->find_stream( $dir, 'Workbook' );
		if( $workbook_index<0 ) {
			$workbook_index = $this->find_stream( $dir, 'Book' );
			if( $workbook_index<0 ){
/*DBG*/    	    $this->dbglog->debug("parse_workbook() function workbook index not found returns 7");
				return 7;
			}
		}

		$workbook_start_block = $dir->getLong( $workbook_index * 0x80 + 0x74 );
		$workbook_length = $dir->getLong( $workbook_index * 0x80 + 0x78 );
		$wb = '';

		if( $workbook_length > 0 ) {
			if( $workbook_length >= 0x1000 ) {
				$chain = $this->get_blocks_chain($workbook_start_block);
				$wb = $dp->ReadFromFat( $chain );
		 	} else {
				$chain = $this->get_blocks_chain($workbook_start_block,true);
				$wb = $sdp->ReadFromFat( $chain, 0x40 );
				unset( $sdp );
		 	}
			$wb = substr($wb,0,$workbook_length);
			if( strlen($wb) != $workbook_length )
				return 6;
			unset( $chain );
		}
		
		// Unset fat arrays
		unset( $this->fat, $this->sfat );

		if( strlen($wb) <= 0 ) {
/*DBG*/    $this->dbglog->debug("parse_workbook() function workbook found (strlen($wb) <= 0) returns 7");
		   return 7;
		}
		if( strlen($wb) <  4 ) {
/*DBG*/    $this->dbglog->debug("parse_workbook() function workbook found (strlen($wb) < 4) returns 6");
		    return 6;
		}
			
		// parse workbook header
		if( strlen($wb) < 256*ord($wb[3])+ord($wb[2]) ){
/*DBG*/ 	$this->dbglog->debug("parse_workbook() function workbook found (strlen($wb) < 256*ord($wb[3])+ord($wb[2])) < 4) returns 6");
			return 6;
		}