excelparser.php

在线商店,可以在线购买,下定单,商品上架

<?php
require_once("debug.php");
require_once("exceldate.php");
require_once("excelfont.php");
require_once("dataprovider.php");

//------------------------------------------------------------------------
// ABC Excel Parser Pro (ExcelFileParser class)
//
// Version: 3.0
// PHP compatibility: 4.3.x
//
//------------------------------------------------------------------------


class ExcelFileParser {

	var $dp = null;
	
	var $max_blocks;
	var $max_sblocks;
	
	// Internal variables
	var $fat;
	var $sfat;
	
	// Removed: var $sbd;
	// Removed: var $syear;
	
	var $formats;
	var $xf;
	var $fonts;

    var $dbglog;


    function ExcelFileParser($logfile="",$level=ABC_NO_LOG) {
		$this->dbglog = &DebugOut::getWriterSingleton($logfile,"",$level);
        $this->dbglog->info("Logger started");
    }

	function populateFormat() {
	$this->dbglog->trace(" populateFormat() function call");

	$ret = array (
        	0=> "General",
        	1=> "0",
        	2=> "0.00",
        	3=> "#,##0",
        	4=> "#,##0.00",
        	5=> "($#,##0_);($#,##0)",
        	6=> "($#,##0_);[Red]($#,##0)",
        	7=> "($#,##0.00);($#,##0.00)",
        	8=> "($#,##0.00_);[Red]($#,##0.00)",
        	9=> "0%",
        	0xa=> "0.00%",
        	0xb=> "0.00E+00",
        	0xc=> "# ?/?",
        	0xd=> "# ??/??",
        	0xe=> "m/d/yy",
        	0xf=> "d-mmm-yy",
        	0x10=> "d-mmm",
        	0x11=> "mmm-yy",
        	0x12=> "h:mm AM/PM",
        	0x13=> "h:mm:ss AM/PM",
        	0x14=> "h:mm",
        	0x15=> "h:mm:ss",
        	0x16=> "m/d/yy h:mm",

        	// 0x17 - 0x24 reserved for international and undocumented
        	0x17=> "0x17",
        	0x18=> "0x18",
        	0x19=> "0x19",
        	0x1a=> "0x1a",
        	0x1b=> "0x1b",
	        0x1c=> "0x1c",
        	0x1d=> "0x1d",
	        0x1e=> "0x1e",
	        0x1f=> "0x1f",
	        0x20=> "0x20",
	        0x21=> "0x21",
	        0x22=> "0x22",
	        0x23=> "0x23",
	        0x24=> "0x24",

        	// 0x17 - 0x24 reserved for international and undocumented
        	0x25=> "(#,##0_);(#,##0)",
        	0x26=> "(#,##0_);[Red](#,##0)",
        	0x27=> "(#,##0.00_);(#,##0.00)",
        	0x28=> "(#,##0.00_);[Red](#,##0.00)",
        	0x29=> "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)",
        	0x2a=> "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)",
        	0x2b=> "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)",
        	0x2c=> "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)",
        	0x2d=> "mm:ss",
        	0x2e=> "[h]:mm:ss",
        	0x2f=> "mm:ss.0",
        	0x30=> "##0.0E+0",
	        0x31=> "@");

            $this->dbglog->dump($ret,"\$ret");
            $this->dbglog->trace("populateFormat() function return");

        	return $ret;

	}

	function xls2tstamp($date) {
	$date=$date>25568?$date:25569;
	/*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
   		$ofs=(70 * 365 + 17+2) * 86400;
  		 return ($date * 86400) - $ofs;
	}


	function getDateArray($date) {
	   return ExcelDateUtil::getDateArray($date);
	}

	function isDateFormat($val){
		$f_i=$this->xf['format'][$val];
		if(preg_match("/[m|d|y]/i",$this->format[$f_i])!=0){
		    if(strrpos($this->format[$f_i],'[')!=FALSE) {
		        $tmp = preg_replace("/(\[\/?)(\w+)([^\]]*\])/","'\\1'.''.'\\3'",$this->format[$f_i]);
		    	if(preg_match("/[m|d|y]/i",$tmp)!=0)
		    	   return TRUE;
		    	 else
		    	   return FALSE;
		    } else {
		        return TRUE;
		    }
		} else
		  return FALSE;
	}


	function getUnicodeString($str,$ofs){
	   $size=0;
	   $i_ofs=0;
/*	   if (ord($str[$ofs])==255) {
	     $size=ord($str[$ofs])+ 256*(ord($str[$ofs+1]));
	     $i_ofs=2;
	   } else {*/
	     $size=ord($str[$ofs]);
	     $i_ofs=1;
/*	   }*/
	   return substr($str,$ofs+$i_ofs+1,$size);

	}


	function getByteString($str,$ofs){
	   $size=0;
	   $i_ofs=0;
//	   if (ord($str[$ofs])==255) {
//	     $size=ord($str[$ofs])+ 256*(ord($str[$ofs+1]));
//	     $i_ofs=2;
//	   } else {
	     $size=ord($str[$ofs]);
	     $i_ofs=1;
//	   }
	   return substr($str,$ofs+$i_ofs+1,$size);
	}



	/*
	 * Get blocks chain
	 */
	function get_blocks_chain($start,$small_fat=false) {

		$this->dbglog->trace("get_blocks_chain(".var_export($start,true).",".var_export($small_fat,true).") function call ");
		$chain = array();

		$next_block = $start;
		if( !$small_fat ) {
			while(  ($next_block!=0xfffffffe) &&
				($next_block <= $this->max_blocks) &&
				($next_block < count($this->fat)) )
			{
				$chain[] = $next_block;
				$next_block = $this->fat[$next_block];
			}
		} else {
			while(  ($next_block!=0xfffffffe) &&
				($next_block <= $this->max_sblocks) &&
				($next_block < count($this->sfat)) )
			{
				$chain[] = $next_block;
				$next_block = $this->sfat[$next_block];
			}
		}
		
		if( $next_block != 0xfffffffe )
			return false;

		$this->dbglog->dump($chain,"\$chain");
		$this->dbglog->trace("get_blocks_chain() function return");
		return $chain;
	}

	/* Find stream by name
	 *
	 */

	function find_stream( $dir, $item_name,$item_num=0) {

        $this->dbglog->trace("find_stream(".var_export($dir,true).",".var_export($item_name,true).",".var_export($item_num,true).") function call ");

		$dt = $dir->getOrd( $item_num * 0x80 + 0x42 );
		$prev = $dir->getLong( $item_num * 0x80 + 0x44 );
		$next = $dir->getLong( $item_num * 0x80 + 0x48 );
		$dir_ = $dir->getLong( $item_num * 0x80 + 0x4c );

		$curr_name = '';
		if( ($dt==2) || ($dt==5) )
			for( $i=0;
			 $i < ( $dir->getOrd( $item_num * 0x80 + 0x40 ) +
			  256 * $dir->getOrd( $item_num * 0x80 + 0x41 ) )/2-1;
			 $i++ )
				$curr_name .= $dir->getByte( $item_num * 0x80 + $i * 2 );

		if( (($dt==2) || ($dt==5)) && (strcmp($curr_name,$item_name)==0) ){
		    $this->dbglog->trace("find_stream() function return with ".var_export($item_num,true));
			return $item_num;
		}

		if( $prev != 0xffffffff ) {
			$i = $this->find_stream( $dir, $item_name, $prev);
			if( $i>=0 ){
			    $this->dbglog->trace("find_stream() function return with ".var_export($i,true));
    			return $i;
   			 }
		}
		if( $next != 0xffffffff ) {
			$i = $this->find_stream( $dir, $item_name, $next);
			if( $i>=0 ){
			    $this->dbglog->trace("find_stream() function return with ".var_export($i,true));
			    return $i;
			}
		}
		if( $dir_ != 0xffffffff ) {
			$i = $this->find_stream( $dir, $item_name, $dir_ );
			if( $i>=0 ) {
			    $this->dbglog->trace("find_stream() function return with ".var_export($i,true));
			    return $i;
			}
		}
        $this->dbglog->trace("find_stream() function return with -1");
		return -1;
	}

	function rk_decode($rk) {

//	    $this->dbglog->trace("rk_decode(".var_export($rk,true).") function call");
		$res = array();
		if( $rk & 2 ) {
			// integer
			$val = ($rk & 0xfffffffc) >> 2;
			if( $rk & 1 ) $val = $val / 100;
			if (((float)$val) == floor((float)$val)){
			   $res['val'] = (int)$val;
			   $res['type'] = 1;
			} else {
			   $res['val'] = (float)$val;
			   $res['type'] = 2;
			}

		} else {
			// float
			$res['type'] = 2;
			$frk = $rk;

			$fexp =  (($frk & 0x7ff00000) >> 20) - 1023;
			$val = 1+(($frk & 0x000fffff) >> 2)/262144;

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

			if( $rk & 1 ) $val = $val / 100;
			if( $rk & 0x80000000 ) $val = -$val;

			$res['val'] = (float)$val;
		}
//		$this->dbglog->trace("rk_decode() function returns");
		return $res;
	}

	// Parse worksheet
	//-----------------

	function parse_worksheet($ws) {

        $this->dbglog->debug("parse_worksheet(DATA) function");
		if( strlen($ws) <= 0 ){
		    $this->dbglog->trace("parse_worksheet() function returns 7 (Data not Found)");
    	    return 7;
    	}
		if( strlen($ws) <  4 ){
		    $this->dbglog->trace("parse_worksheet() function returns 6 (File Corrupted)");
		    return 6;
		}

		// parse workbook header
		if( strlen($ws) < 256*ord($ws[3])+ord($ws[2]) ) return 6;

		if( ord($ws[0]) != 0x09 ) return 6;
		$vers = ord($ws[1]);
		if( ($vers!=0) && ($vers!=2) && ($vers!=4) && ($vers!=8) )
			return 8;

		if( $vers!=8 ) {
		 $biff_ver = ($ver+4)/2;
		} else {
		 if( strlen($ws) < 12 ) return 6;
		 switch( ord($ws[4])+256*ord($ws[5]) ) {
			case 0x0500:
				if( ord($ws[0x0a])+256*ord($ws[0x0b]) < 1994 ) {
					$biff_ver = 5;
				} else {
					switch(ord( $ws[8])+256*ord($ws[9]) ) {
					 case 2412:
					 case 3218:
					 case 3321:
/*dbg*/ 	            $this->dbglog->debug("Parsed BIFF version is 5");
						$biff_ver = 5;
						 break;
					 default:
					    $this->dbglog->debug("Parsed BIFF version is 7");
						$biff_ver = 7;
						break;
					}
				}
				break;
			case 0x0600:
/*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])) {

		  // Number
		  case 0x0203:
/*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