PHP实现读取Excel文件的记录(二) PHP实现读取Excel文件的记录(二

《PHP实现读取Excel文件的记录(一)》中有在PHP中读取Excel的例子，有些麻烦，因为必须要加载很多的文件。
应该有ODBC的读取方法，还没有试，今天的方法简单了很多，只需要加载两个文件即可，这两个文件（OLERead.php、reader.php）我找了好长时间才找到，放在后面。
试验成功的代码：

setOutputEncoding('utf-8'); //设置文本输出编码$excelData->read("20210930.xls"); //读取Excel文件echo '总行数：'.$excelData->sheets[0]['numRows']; //Excel行数echo '总列数：'.$excelData->sheets[0]['numCols']; //Excel列数 for ($i = 1; $i <= $excelData->sheets[0]['numRows']; $i++) {$strRowData=''; for ($j = 1; $j <= $excelData->sheets[0]['numCols']; $j++) {if ( isset( $excelData->sheets[0]['cells'][$i][$j] )){$strColData=https://www.it610.com/article/$excelData->sheets[0]['cells'][$i][$j]; $strRowData=https://www.it610.com/article/$strRowData.''.$strColData; }echo $strRowData.'
'; }}?>

注意：在输出的时候判断一下，即isset( $excelData->sheets[0]['cells'][$i][$j])，不然可能会报错。
能读出来做处理就好办了。
reader.php文件内容：（新建一个reader.php，将代码拷入即可）

* @licensehttp://www.php.net/license/3_0.txtPHP License 3.0* @versionCVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $* @linkhttp://pear.php.net/package/Spreadsheet_Excel_Reader* @seeOLE, Spreadsheet_Excel_Writer*/ //require_once 'PEAR.php'; require_once 'OLERead.php'; //require_once 'OLE.php'; define('SPREADSHEET_EXCEL_READER_BIFF8',0x600); define('SPREADSHEET_EXCEL_READER_BIFF7',0x500); define('SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS',0x5); define('SPREADSHEET_EXCEL_READER_WORKSHEET',0x10); define('SPREADSHEET_EXCEL_READER_TYPE_BOF',0x809); define('SPREADSHEET_EXCEL_READER_TYPE_EOF',0x0a); define('SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET',0x85); define('SPREADSHEET_EXCEL_READER_TYPE_DIMENSION',0x200); define('SPREADSHEET_EXCEL_READER_TYPE_ROW',0x208); define('SPREADSHEET_EXCEL_READER_TYPE_DBCELL',0xd7); define('SPREADSHEET_EXCEL_READER_TYPE_FILEPASS',0x2f); define('SPREADSHEET_EXCEL_READER_TYPE_NOTE',0x1c); define('SPREADSHEET_EXCEL_READER_TYPE_TXO',0x1b6); define('SPREADSHEET_EXCEL_READER_TYPE_RK',0x7e); define('SPREADSHEET_EXCEL_READER_TYPE_RK2',0x27e); define('SPREADSHEET_EXCEL_READER_TYPE_MULRK',0xbd); define('SPREADSHEET_EXCEL_READER_TYPE_MULBLANK',0xbe); define('SPREADSHEET_EXCEL_READER_TYPE_INDEX',0x20b); define('SPREADSHEET_EXCEL_READER_TYPE_SST',0xfc); define('SPREADSHEET_EXCEL_READER_TYPE_EXTSST',0xff); define('SPREADSHEET_EXCEL_READER_TYPE_CONTINUE',0x3c); define('SPREADSHEET_EXCEL_READER_TYPE_LABEL',0x204); define('SPREADSHEET_EXCEL_READER_TYPE_LABELSST',0xfd); define('SPREADSHEET_EXCEL_READER_TYPE_NUMBER',0x203); define('SPREADSHEET_EXCEL_READER_TYPE_NAME',0x18); define('SPREADSHEET_EXCEL_READER_TYPE_ARRAY',0x221); define('SPREADSHEET_EXCEL_READER_TYPE_STRING',0x207); define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA',0x406); define('SPREADSHEET_EXCEL_READER_TYPE_FORMULA2',0x6); define('SPREADSHEET_EXCEL_READER_TYPE_FORMAT',0x41e); define('SPREADSHEET_EXCEL_READER_TYPE_XF',0xe0); define('SPREADSHEET_EXCEL_READER_TYPE_BOOLERR',0x205); define('SPREADSHEET_EXCEL_READER_TYPE_UNKNOWN',0xffff); define('SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR', 0x22); define('SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS',0xE5); define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS' ,25569); define('SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904', 24107); define('SPREADSHEET_EXCEL_READER_MSINADAY',86400); //define('SPREADSHEET_EXCEL_READER_MSINADAY', 24 * 60 * 60); //define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT', "%.2f"); define('SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT',"%s"); /** Place includes, constant defines and $_GLOBAL settings here.* Make sure they have appropriate docblocks to avoid phpDocumentor* construing they are documented by the page-level docblock.*/ /*** A class for reading Microsoft Excel Spreadsheets.** Originally developed by Vadim Tkachenko under the name PHPExcelReader.* (http://sourceforge.net/projects/phpexcelreader)* Based on the Java version by Andy Khan (http://www.andykhan.com).Now* maintained by David Sanders.Reads only Biff 7 and Biff 8 formats.** @categorySpreadsheet* @packageSpreadsheet_Excel_Reader* @authorVadim Tkachenko * @copyright1997-2005 The PHP Group* @licensehttp://www.php.net/license/3_0.txtPHP License 3.0* @versionRelease: @package_version@* @linkhttp://pear.php.net/package/PackageName* @seeOLE, Spreadsheet_Excel_Writer*/class Spreadsheet_Excel_Reader{/*** Array of worksheets found** @var array* @access public*/var $boundsheets = array(); /*** Array of format records found* * @var array* @access public*/var $formatRecords = array(); /*** todo** @var array* @access public*/var $sst = array(); /*** Array of worksheets** The data is stored in 'cells' and the meta-data is stored in an array* called 'cellsInfo'** Example:** $sheets-->'cells'-->row --> column --> Interpreted value*-->'cellsInfo' --> row --> column --> 'type' - Can be 'date', 'number', or 'unknown'*--> 'raw' - The raw data that Excel stores for that data cell** @var array* @access public*/var $sheets = array(); /*** The data returned by OLE** @var string* @access public*/var $data; /*** OLE object for reading the file** @var OLE object* @access private*/var $_ole; /*** Default encoding** @var string* @access private*/var $_defaultEncoding; /*** Default number format** @var integer* @access private*/var $_defaultFormat = SPREADSHEET_EXCEL_READER_DEF_NUM_FORMAT; /*** todo* List of formats to use for each column** @var array* @access private*/var $_columnsFormat = array(); /*** todo** @var integer* @access private*/var $_rowoffset = 1; /*** todo** @var integer* @access private*/var $_coloffset = 1; /*** List of default date formats used by Excel** @var array* @access public*/var $dateFormats = array (0xe => "d/m/Y",0xf => "d-M-Y",0x10 => "d-M",0x11 => "M-Y",0x12 => "h:i a",0x13 => "h:i:s a",0x14 => "H:i",0x15 => "H:i:s",0x16 => "d/m/Y H:i",0x2d => "i:s",0x2e => "H:i:s",0x2f => "i:s.S"); /*** Default number formats used by Excel** @var array* @access public*/var $numberFormats = array(0x1 => "%1.0f",// "0"0x2 => "%1.2f",// "0.00",0x3 => "%1.0f",//"#,##0",0x4 => "%1.2f",//"#,##0.00",0x5 => "%1.0f",/*"$#,##0; ($#,##0)",*/0x6 => '$%1.0f',/*"$#,##0; ($#,##0)",*/0x7 => '$%1.2f',//"$#,##0.00; ($#,##0.00)",0x8 => '$%1.2f',//"$#,##0.00; ($#,##0.00)",0x9 => '%1.0f%%',// "0%"0xa => '%1.2f%%',// "0.00%"0xb => '%1.2f',// 0.00E00",0x25 => '%1.0f',// "#,##0; (#,##0)",0x26 => '%1.0f',//"#,##0; (#,##0)",0x27 => '%1.2f',//"#,##0.00; (#,##0.00)",0x28 => '%1.2f',//"#,##0.00; (#,##0.00)",0x29 => '%1.0f',//"#,##0; (#,##0)",0x2a => '$%1.0f',//"$#,##0; ($#,##0)",0x2b => '%1.2f',//"#,##0.00; (#,##0.00)",0x2c => '$%1.2f',//"$#,##0.00; ($#,##0.00)",0x30 => '%1.0f'); //"##0.0E0"; // }}}// {{{ Spreadsheet_Excel_Reader() /*** Constructor** Some basic initialisation*/ function Spreadsheet_Excel_Reader(){$this->_ole = new OLERead(); $this->setUTFEncoder('iconv'); } // }}}// {{{ setOutputEncoding() /*** Set the encoding method** @param string Encoding to use* @access public*/function setOutputEncoding($encoding){$this->_defaultEncoding = $encoding; } // }}}// {{{ setUTFEncoder() /***$encoder = 'iconv' or 'mb'*set iconv if you would like use 'iconv' for encode UTF-16LE to your encoding*set mb if you would like use 'mb_convert_encoding' for encode UTF-16LE to your encoding** @access public* @param string Encoding type to use.Either 'iconv' or 'mb'*/function setUTFEncoder($encoder = 'iconv'){$this->_encoderFunction = ''; if ($encoder == 'iconv') {$this->_encoderFunction = function_exists('iconv') ? 'iconv' : ''; } elseif ($encoder == 'mb') {$this->_encoderFunction = function_exists('mb_convert_encoding') ?'mb_convert_encoding' :''; }} // }}}// {{{ setRowColOffset() /*** todo** @access public* @param offset*/function setRowColOffset($iOffset){$this->_rowoffset = $iOffset; $this->_coloffset = $iOffset; } // }}}// {{{ setDefaultFormat() /*** Set the default number format** @access public* @param Default format*/function setDefaultFormat($sFormat){$this->_defaultFormat = $sFormat; } // }}}// {{{ setColumnFormat() /*** Force a column to use a certain format** @access public* @param integer Column number* @param string Format*/function setColumnFormat($column, $sFormat){$this->_columnsFormat[$column] = $sFormat; } // }}}// {{{ read() /*** Read the spreadsheet file using OLE, then parse** @access public* @param filename* @todo return a valid value*/function read($sFileName){/*require_once 'OLE.php'; $ole = new OLE(); $ole->read($sFileName); foreach ($ole->_list as $i => $pps) {if (($pps->Name == 'Workbook' || $pps->Name == 'Book') &&$pps->Size >= SMALL_BLOCK_THRESHOLD) {$this->data = https://www.it610.com/article/$ole->getData($i, 0, $ole->getDataLength($i)); } elseif ($pps->Name == 'Root Entry') {$this->data = https://www.it610.com/article/$ole->getData($i, 0, $ole->getDataLength($i)); }//var_dump(strlen($ole->getData($i, 0, $ole->getDataLength($i))), $pps->Name, md5($this->data), $ole->getDataLength($i)); }//exit; $this->_parse(); return sizeof($this->sheets) > 0; */ $res = $this->_ole->read($sFileName); // oops, something goes wrong (Darko Miljanovic)if($res === false) {// check error codeif($this->_ole->error == 1) {// bad filedie('The filename ' . $sFileName . ' is not readable'); }// check other error codes here (eg bad fileformat, etc...)} $this->data = https://www.it610.com/article/$this->_ole->getWorkBook(); /*$res = $this->_ole->read($sFileName); if ($this->isError($res)) {//var_dump($res); return $this->raiseError($res); }$total = $this->_ole->ppsTotal(); for ($i = 0; $i < $total; $i++) {if ($this->_ole->isFile($i)) {$type = unpack("v", $this->_ole->getData($i, 0, 2)); if ($type[''] == 0x0809){ // check if it's a BIFF stream$this->_index = $i; $this->data = https://www.it610.com/article/$this->_ole->getData($i, 0, $this->_ole->getDataLength($i)); break; }}}if ($this->_index === null) {return $this->raiseError("$file doesn't seem to be an Excel file"); }*/ //echo "data ="https://www.it610.com/article/.$this->data; //$this->readRecords(); $this->_parse(); } // }}}// {{{ _parse() /*** Parse a workbook** @access private* @return bool*/function _parse(){$pos = 0; $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8; $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8; $version = ord($this->data[$pos + 4]) | ord($this->data[$pos + 5])<<8; $substreamType = ord($this->data[$pos + 6]) | ord($this->data[$pos + 7])<<8; //echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n"; if (($version != SPREADSHEET_EXCEL_READER_BIFF8) &&($version != SPREADSHEET_EXCEL_READER_BIFF7)) {return false; } if ($substreamType != SPREADSHEET_EXCEL_READER_WORKBOOKGLOBALS){return false; } //print_r($rec); $pos += $length + 4; $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8; $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8; while ($code != SPREADSHEET_EXCEL_READER_TYPE_EOF) {switch ($code) {case SPREADSHEET_EXCEL_READER_TYPE_SST://echo "Type_SST\n"; $spos = $pos + 4; $limitpos = $spos + $length; $uniqueStrings = $this->_GetInt4d($this->data, $spos+4); $spos += 8; for ($i = 0; $i < $uniqueStrings; $i++) {// Read in the number of charactersif ($spos == $limitpos) {$opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; if ($opcode != 0x3c) {return -1; }$spos += 4; $limitpos = $spos + $conlength; }$numChars = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8); //echo "i = $i pos = $pos numChars = $numChars "; $spos += 2; $optionFlags = ord($this->data[$spos]); $spos++; $asciiEncoding = (($optionFlags & 0x01) == 0) ; $extendedString = ( ($optionFlags & 0x04) != 0); // See if string contains formatting information$richString = ( ($optionFlags & 0x08) != 0); if ($richString) {// Read in the crun$formattingRuns = ord($this->data[$spos]) | (ord($this->data[$spos+1]) << 8); $spos += 2; } if ($extendedString) {// Read in cchExtRst$extendedRunLength = $this->_GetInt4d($this->data, $spos); $spos += 4; } $len = ($asciiEncoding)? $numChars : $numChars*2; if ($spos + $len < $limitpos) {$retstr = substr($this->data, $spos, $len); $spos += $len; }else{// found countinue$retstr = substr($this->data, $spos, $limitpos - $spos); $bytesRead = $limitpos - $spos; $charsLeft = $numChars - (($asciiEncoding) ? $bytesRead : ($bytesRead / 2)); $spos = $limitpos; while ($charsLeft > 0){$opcode = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $conlength = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; if ($opcode != 0x3c) {return -1; }$spos += 4; $limitpos = $spos + $conlength; $option = ord($this->data[$spos]); $spos += 1; if ($asciiEncoding && ($option == 0)) {$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength); $retstr .= substr($this->data, $spos, $len); $charsLeft -= $len; $asciiEncoding = true; }elseif (!$asciiEncoding && ($option != 0)){$len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength); $retstr .= substr($this->data, $spos, $len); $charsLeft -= $len/2; $asciiEncoding = false; }elseif (!$asciiEncoding && ($option == 0)) {// Bummer - the string starts off as Unicode, but after the// continuation it is in straightforward ASCII encoding$len = min($charsLeft, $limitpos - $spos); // min($charsLeft, $conlength); for ($j = 0; $j < $len; $j++) {$retstr .= $this->data[$spos + $j].chr(0); }$charsLeft -= $len; $asciiEncoding = false; }else{$newstr = ''; for ($j = 0; $j < strlen($retstr); $j++) {$newstr = $retstr[$j].chr(0); }$retstr = $newstr; $len = min($charsLeft * 2, $limitpos - $spos); // min($charsLeft, $conlength); $retstr .= substr($this->data, $spos, $len); $charsLeft -= $len/2; $asciiEncoding = false; //echo "Izavrat\n"; }$spos += $len; }}$retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr); //echo "Str $i = $retstr\n"; if ($richString){$spos += 4 * $formattingRuns; } // For extended strings, skip over the extended string dataif ($extendedString) {$spos += $extendedRunLength; }//if ($retstr == 'Derby'){//echo "bb\n"; //}$this->sst[]=$retstr; }/*$continueRecords = array(); while ($this->getNextCode() == Type_CONTINUE) {$continueRecords[] = &$this->nextRecord(); }//echo " 1 Type_SST\n"; $this->shareStrings = new SSTRecord($r, $continueRecords); //print_r($this->shareStrings->strings); */// echo 'SST read: '.($time_end-$time_start)."\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_FILEPASS:return false; break; case SPREADSHEET_EXCEL_READER_TYPE_NAME://echo "Type_NAME\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_FORMAT:$indexCode = ord($this->data[$pos+4]) | ord($this->data[$pos+5]) << 8; if ($version == SPREADSHEET_EXCEL_READER_BIFF8) {$numchars = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8; if (ord($this->data[$pos+8]) == 0){$formatString = substr($this->data, $pos+9, $numchars); } else {$formatString = substr($this->data, $pos+9, $numchars*2); }} else {$numchars = ord($this->data[$pos+6]); $formatString = substr($this->data, $pos+7, $numchars*2); } $this->formatRecords[$indexCode] = $formatString; // echo "Type.FORMAT\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_XF://global $dateFormats, $numberFormats; $indexCode = ord($this->data[$pos+6]) | ord($this->data[$pos+7]) << 8; //echo "\nType.XF ".count($this->formatRecords['xfrecords'])." $indexCode "; if (array_key_exists($indexCode, $this->dateFormats)) {//echo "isdate ".$dateFormats[$indexCode]; $this->formatRecords['xfrecords'][] = array('type' => 'date','format' => $this->dateFormats[$indexCode]); }elseif (array_key_exists($indexCode, $this->numberFormats)) {//echo "isnumber ".$this->numberFormats[$indexCode]; $this->formatRecords['xfrecords'][] = array('type' => 'number','format' => $this->numberFormats[$indexCode]); }else{$isdate = FALSE; if ($indexCode > 0){if (isset($this->formatRecords[$indexCode]))$formatstr = $this->formatRecords[$indexCode]; //echo '.other.'; //echo "\ndate-time=$formatstr=\n"; if ($formatstr)if (preg_match("/[^hmsday\/\-:\s]/i", $formatstr) == 0) { // found day and time format$isdate = TRUE; $formatstr = str_replace('mm', 'i', $formatstr); $formatstr = str_replace('h', 'H', $formatstr); //echo "\ndate-time $formatstr \n"; }} if ($isdate){$this->formatRecords['xfrecords'][] = array('type' => 'date','format' => $formatstr,); }else{$this->formatRecords['xfrecords'][] = array('type' => 'other','format' => '','code' => $indexCode); }}//echo "\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_NINETEENFOUR://echo "Type.NINETEENFOUR\n"; $this->nineteenFour = (ord($this->data[$pos+4]) == 1); break; case SPREADSHEET_EXCEL_READER_TYPE_BOUNDSHEET://echo "Type.BOUNDSHEET\n"; $rec_offset = $this->_GetInt4d($this->data, $pos+4); $rec_typeFlag = ord($this->data[$pos+8]); $rec_visibilityFlag = ord($this->data[$pos+9]); $rec_length = ord($this->data[$pos+10]); if ($version == SPREADSHEET_EXCEL_READER_BIFF8){$chartype =ord($this->data[$pos+11]); if ($chartype == 0){$rec_name= substr($this->data, $pos+12, $rec_length); } else {$rec_name= $this->_encodeUTF16(substr($this->data, $pos+12, $rec_length*2)); }}elseif ($version == SPREADSHEET_EXCEL_READER_BIFF7){$rec_name= substr($this->data, $pos+11, $rec_length); }$this->boundsheets[] = array('name'=>$rec_name,'offset'=>$rec_offset); break; } //echo "Code = ".base_convert($r['code'],10,16)."\n"; $pos += $length + 4; $code = ord($this->data[$pos]) | ord($this->data[$pos+1])<<8; $length = ord($this->data[$pos+2]) | ord($this->data[$pos+3])<<8; //$r = &$this->nextRecord(); //echo "1 Code = ".base_convert($r['code'],10,16)."\n"; } foreach ($this->boundsheets as $key=>$val){$this->sn = $key; $this->_parsesheet($val['offset']); }return true; } /*** Parse a worksheet** @access private* @param todo* @todo fix return codes*/function _parsesheet($spos){$cont = true; // read BOF$code = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $version = ord($this->data[$spos + 4]) | ord($this->data[$spos + 5])<<8; $substreamType = ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8; if (($version != SPREADSHEET_EXCEL_READER_BIFF8) && ($version != SPREADSHEET_EXCEL_READER_BIFF7)) {return -1; } if ($substreamType != SPREADSHEET_EXCEL_READER_WORKSHEET){return -2; }//echo "Start parse code=".base_convert($code,10,16)." version=".base_convert($version,10,16)." substreamType=".base_convert($substreamType,10,16).""."\n"; $spos += $length + 4; //var_dump($this->formatRecords); //echo "code $code $length"; while($cont) {//echo "mem= ".memory_get_usage()."\n"; //$r = &$this->file->nextRecord(); $lowcode = ord($this->data[$spos]); if ($lowcode == SPREADSHEET_EXCEL_READER_TYPE_EOF) break; $code = $lowcode | ord($this->data[$spos+1])<<8; $length = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $spos += 4; $this->sheets[$this->sn]['maxrow'] = $this->_rowoffset - 1; $this->sheets[$this->sn]['maxcol'] = $this->_coloffset - 1; //echo "Code=".base_convert($code,10,16)." $code\n"; unset($this->rectype); $this->multiplier = 1; // need for format with %switch ($code) {case SPREADSHEET_EXCEL_READER_TYPE_DIMENSION://echo 'Type_DIMENSION '; if (!isset($this->numRows)) {if (($length == 10) ||($version == SPREADSHEET_EXCEL_READER_BIFF7)){$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+2]) | ord($this->data[$spos+3]) << 8; $this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+6]) | ord($this->data[$spos+7]) << 8; } else {$this->sheets[$this->sn]['numRows'] = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8; $this->sheets[$this->sn]['numCols'] = ord($this->data[$spos+10]) | ord($this->data[$spos+11]) << 8; }}//echo 'numRows '.$this->numRows.' '.$this->numCols."\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_MERGEDCELLS:$cellRanges = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; for ($i = 0; $i < $cellRanges; $i++) {$fr =ord($this->data[$spos + 8*$i + 2]) | ord($this->data[$spos + 8*$i + 3])<<8; $lr =ord($this->data[$spos + 8*$i + 4]) | ord($this->data[$spos + 8*$i + 5])<<8; $fc =ord($this->data[$spos + 8*$i + 6]) | ord($this->data[$spos + 8*$i + 7])<<8; $lc =ord($this->data[$spos + 8*$i + 8]) | ord($this->data[$spos + 8*$i + 9])<<8; //$this->sheets[$this->sn]['mergedCells'][] = array($fr + 1, $fc + 1, $lr + 1, $lc + 1); if ($lr - $fr > 0) {$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['rowspan'] = $lr - $fr + 1; }if ($lc - $fc > 0) {$this->sheets[$this->sn]['cellsInfo'][$fr+1][$fc+1]['colspan'] = $lc - $fc + 1; }}//echo "Merged Cells $cellRanges $lr $fr $lc $fc\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_RK:case SPREADSHEET_EXCEL_READER_TYPE_RK2://echo 'SPREADSHEET_EXCEL_READER_TYPE_RK'."\n"; $row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $rknum = $this->_GetInt4d($this->data, $spos + 6); $numValue = https://www.it610.com/article/$this->_GetIEEE754($rknum); //echo $numValue." "; if ($this->isDate($spos)) {list($string, $raw) = $this->createDate($numValue); }else{$raw = $numValue; if (isset($this->_columnsFormat[$column + 1])){$this->curformat = $this->_columnsFormat[$column + 1]; }$string = sprintf($this->curformat, $numValue * $this->multiplier); //$this->addcell(RKRecord($r)); }$this->addcell($row, $column, $string, $raw); //echo "Type_RK $row $column $string $raw {$this->curformat}\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_LABELSST:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column= ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $xfindex= ord($this->data[$spos+4]) | ord($this->data[$spos+5])<<8; $index= $this->_GetInt4d($this->data, $spos + 6); //var_dump($this->sst); $this->addcell($row, $column, $this->sst[$index]); //echo "LabelSST $row $column $string\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_MULRK:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $colFirst= ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $colLast= ord($this->data[$spos + $length - 2]) | ord($this->data[$spos + $length - 1])<<8; $columns= $colLast - $colFirst + 1; $tmppos = $spos+4; for ($i = 0; $i < $columns; $i++) {$numValue = https://www.it610.com/article/$this->_GetIEEE754($this->_GetInt4d($this->data, $tmppos + 2)); if ($this->isDate($tmppos-4)) {list($string, $raw) = $this->createDate($numValue); }else{$raw = $numValue; if (isset($this->_columnsFormat[$colFirst + $i + 1])){$this->curformat = $this->_columnsFormat[$colFirst + $i + 1]; }$string = sprintf($this->curformat, $numValue * $this->multiplier); }//$rec['rknumbers'][$i]['xfindex'] = ord($rec['data'][$pos]) | ord($rec['data'][$pos+1]) << 8; $tmppos += 6; $this->addcell($row, $colFirst + $i, $string, $raw); //echo "MULRK $row ".($colFirst + $i)." $string\n"; }//MulRKRecord($r); // Get the individual cell records from the multiple record//$num = ; break; case SPREADSHEET_EXCEL_READER_TYPE_NUMBER:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependentif ($this->isDate($spos)) {list($string, $raw) = $this->createDate($tmp['double']); //$this->addcell(DateRecord($r, 1)); }else{//$raw = $tmp['']; if (isset($this->_columnsFormat[$column + 1])){$this->curformat = $this->_columnsFormat[$column + 1]; }$raw = $this->createNumber($spos); $string = sprintf($this->curformat, $raw * $this->multiplier); //$this->addcell(NumberRecord($r)); }$this->addcell($row, $column, $string, $raw); //echo "Number $row $column $string\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_FORMULA:case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; if ((ord($this->data[$spos+6])==0) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {//String formula. Result follows in a STRING record//echo "FORMULA $row $column Formula with a string
\n"; } elseif ((ord($this->data[$spos+6])==1) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {//Boolean formula. Result is in +2; 0=false,1=true} elseif ((ord($this->data[$spos+6])==2) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {//Error formula. Error code is in +2; } elseif ((ord($this->data[$spos+6])==3) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {//Formula result is a null string.} else {// result is a number, so first 14 bytes are just like a _NUMBER record$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependentif ($this->isDate($spos)) {list($string, $raw) = $this->createDate($tmp['double']); //$this->addcell(DateRecord($r, 1)); }else{//$raw = $tmp['']; if (isset($this->_columnsFormat[$column + 1])){$this->curformat = $this->_columnsFormat[$column + 1]; }$raw = $this->createNumber($spos); $string = sprintf($this->curformat, $raw * $this->multiplier); //$this->addcell(NumberRecord($r)); }$this->addcell($row, $column, $string, $raw); //echo "Number $row $column $string\n"; }break; case SPREADSHEET_EXCEL_READER_TYPE_BOOLERR:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $string = ord($this->data[$spos+6]); $this->addcell($row, $column, $string); //echo 'Type_BOOLERR '."\n"; break; case SPREADSHEET_EXCEL_READER_TYPE_ROW:case SPREADSHEET_EXCEL_READER_TYPE_DBCELL:case SPREADSHEET_EXCEL_READER_TYPE_MULBLANK:break; case SPREADSHEET_EXCEL_READER_TYPE_LABEL:$row= ord($this->data[$spos]) | ord($this->data[$spos+1])<<8; $column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8; $this->addcell($row, $column, substr($this->data, $spos + 8, ord($this->data[$spos + 6]) | ord($this->data[$spos + 7])<<8)); // $this->addcell(LabelRecord($r)); break; case SPREADSHEET_EXCEL_READER_TYPE_EOF:$cont = false; break; default://echo ' unknown :'.base_convert($r['code'],10,16)."\n"; break; }$spos += $length; } if (!isset($this->sheets[$this->sn]['numRows']))$this->sheets[$this->sn]['numRows'] = $this->sheets[$this->sn]['maxrow']; if (!isset($this->sheets[$this->sn]['numCols']))$this->sheets[$this->sn]['numCols'] = $this->sheets[$this->sn]['maxcol']; } /*** Check whether the current record read is a date** @param todo* @return boolean True if date, false otherwise*/function isDate($spos){//$xfindex = GetInt2d(, 4); $xfindex = ord($this->data[$spos+4]) | ord($this->data[$spos+5]) << 8; //echo 'check is date '.$xfindex.' '.$this->formatRecords['xfrecords'][$xfindex]['type']."\n"; //var_dump($this->formatRecords['xfrecords'][$xfindex]); if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'date') {$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format']; $this->rectype = 'date'; return true; } else {if ($this->formatRecords['xfrecords'][$xfindex]['type'] == 'number') {$this->curformat = $this->formatRecords['xfrecords'][$xfindex]['format']; $this->rectype = 'number'; if (($xfindex == 0x9) || ($xfindex == 0xa)){$this->multiplier = 100; }}else{$this->curformat = $this->_defaultFormat; $this->rectype = 'unknown'; }return false; }} //}}}//{{{ createDate() /*** Convert the raw Excel date into a human readable format** Dates in Excel are stored as number of seconds from an epoch.On * Windows, the epoch is 30/12/1899 and on Mac it's 01/01/1904** @access private* @param integer The raw Excel value to convert* @return array First element is the converted date, the second element is number a unix timestamp*/ function createDate($numValue){if ($numValue > 1) {$utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS); $utcValue = https://www.it610.com/article/round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY); $string = date ($this->curformat, $utcValue); $raw = $utcValue; } else {$raw = $numValue; $hours = floor($numValue * 24); $mins = floor($numValue * 24 * 60) - $hours * 60; $secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60; $string = date ($this->curformat, mktime($hours, $mins, $secs)); } return array($string, $raw); } function createNumber($spos){$rknumhigh = $this->_GetInt4d($this->data, $spos + 10); $rknumlow = $this->_GetInt4d($this->data, $spos + 6); //for ($i=0; $i<8; $i++) { echo ord($this->data[$i+$spos+6]) . " "; } echo "
"; $sign = ($rknumhigh & 0x80000000) >> 31; $exp =($rknumhigh & 0x7ff00000) >> 20; $mantissa = (0x100000 | ($rknumhigh & 0x000fffff)); $mantissalow1 = ($rknumlow & 0x80000000) >> 31; $mantissalow2 = ($rknumlow & 0x7fffffff); $value = https://www.it610.com/article/$mantissa / pow( 2 , (20- ($exp - 1023))); if ($mantissalow1 != 0) $value += 1 / pow (2 , (21 - ($exp - 1023))); $value += $mantissalow2 / pow (2 , (52 - ($exp - 1023))); //echo"Sign = $sign, Exp = $exp, mantissahighx = $mantissa, mantissalow1 = $mantissalow1, mantissalow2 = $mantissalow2
\n"; if ($sign) {$value = https://www.it610.com/article/-1 * $value; }return$value; } function addcell($row, $col, $string, $raw =''){//echo "ADD cel $row-$col $string\n"; $this->sheets[$this->sn]['maxrow'] = max($this->sheets[$this->sn]['maxrow'], $row + $this->_rowoffset); $this->sheets[$this->sn]['maxcol'] = max($this->sheets[$this->sn]['maxcol'], $col + $this->_coloffset); $this->sheets[$this->sn]['cells'][$row + $this->_rowoffset][$col + $this->_coloffset] = $string; if ($raw)$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['raw'] = $raw; if (isset($this->rectype))$this->sheets[$this->sn]['cellsInfo'][$row + $this->_rowoffset][$col + $this->_coloffset]['type'] = $this->rectype; } function _GetIEEE754($rknum){if (($rknum & 0x02) != 0) {$value = https://www.it610.com/article/$rknum>> 2; } else {//mmp// first comment out the previously existing 7 lines of code here//$tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc))); ////$value = https://www.it610.com/article/$tmp['']; //if (array_key_exists(1, $tmp)) {//$value = $tmp[1]; //} else {//$value = https://www.it610.com/article/$tmp['']; //}// I got my info on IEEE754 encoding from// http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html// The RK format calls for using only the most significant 30 bits of the// 64 bit floating point value. The other 34 bits are assumed to be 0// So, we use the upper 30 bits of $rknum as follows...$sign = ($rknum & 0x80000000) >> 31; $exp = ($rknum & 0x7ff00000) >> 20; $mantissa = (0x100000 | ($rknum & 0x000ffffc)); $value = https://www.it610.com/article/$mantissa / pow( 2 , (20- ($exp - 1023))); if ($sign) {$value = -1 * $value; }//end of changes by mmp } if (($rknum & 0x01) != 0) {$value /= 100; }return $value; } function _encodeUTF16($string){$result = $string; if ($this->_defaultEncoding){switch ($this->_encoderFunction){case 'iconv' :$result = iconv('UTF-16LE', $this->_defaultEncoding, $string); break; case 'mb_convert_encoding' :$result = mb_convert_encoding($string, $this->_defaultEncoding, 'UTF-16LE' ); break; }}return $result; } function _GetInt4d($data, $pos){$value = https://www.it610.com/article/ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) << 24); if ($value>=4294967294){$value=https://www.it610.com/article/-2; }return $value; } } /* * Local variables: * tab-width: 4 * c-basic-offset: 4 * c-hanging-comment-ender-p: nil * End: */ ?>

OLERead.php文件内容：（新建OLERead.php，将代码内容拷入即可）

=4294967294){$value=https://www.it610.com/article/-2; }return $value; } class OLERead {var $data =''; function OLERead(){}function read($sFileName){// check if file exist and is readable (Darko Miljanovic)if(!is_readable($sFileName)) {$this->error = 1; return false; }$this->data = https://www.it610.com/article/@file_get_contents($sFileName); if (!$this->data) { $this->error = 1; return false; }//echo IDENTIFIER_OLE; //echo 'start'; if (substr($this->data, 0, 8) != IDENTIFIER_OLE) {$this->error = 1; return false; }$this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS); $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS); $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS); $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS); $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS); /*echo $this->numBigBlockDepotBlocks." "; echo $this->sbdStartBlock." "; echo $this->rootStartBlock." "; echo $this->extensionBlock." "; echo $this->numExtensionBlocks." "; *///echo "sbdStartBlock = $this->sbdStartBlock\n"; $bigBlockDepotBlocks = array(); $pos = BIG_BLOCK_DEPOT_BLOCKS_POS; // echo "pos = $pos"; $bbdBlocks = $this->numBigBlockDepotBlocks; if ($this->numExtensionBlocks != 0) {$bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4; }for ($i = 0; $i < $bbdBlocks; $i++) {$bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); $pos += 4; }for ($j = 0; $j < $this->numExtensionBlocks; $j++) {$pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE; $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1); for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) {$bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); $pos += 4; } $bbdBlocks += $blocksToRead; if ($bbdBlocks < $this->numBigBlockDepotBlocks) {$this->extensionBlock = GetInt4d($this->data, $pos); }} // var_dump($bigBlockDepotBlocks); // readBigBlockDepot$pos = 0; $index = 0; $this->bigBlockChain = array(); for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) {$pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE; //echo "pos = $pos"; for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) {$this->bigBlockChain[$index] = GetInt4d($this->data, $pos); $pos += 4 ; $index++; }} //var_dump($this->bigBlockChain); //echo '=====2'; // readSmallBlockDepot(); $pos = 0; $index = 0; $sbdBlock = $this->sbdStartBlock; $this->smallBlockChain = array(); while ($sbdBlock != -2) {$pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE; for ($j = 0; $j < BIG_BLOCK_SIZE / 4; $j++) {$this->smallBlockChain[$index] = GetInt4d($this->data, $pos); $pos += 4; $index++; }$sbdBlock = $this->bigBlockChain[$sbdBlock]; } // readData(rootStartBlock)$block = $this->rootStartBlock; $pos = 0; $this->entry = $this->__readData($block); /*while ($block != -2){$pos = ($block + 1) * BIG_BLOCK_SIZE; $this->entry = $this->entry.substr($this->data, $pos, BIG_BLOCK_SIZE); $block = $this->bigBlockChain[$block]; }*///echo '==='.$this->entry."==="; $this->__readPropertySets(); }function __readData($bl) {$block = $bl; $pos = 0; $datahttps://www.it610.com/article/= ''; while ($block != -2){$pos = ($block + 1) * BIG_BLOCK_SIZE; $data = https://www.it610.com/article/$data.substr($this->data, $pos, BIG_BLOCK_SIZE); //echo "pos = $pos data=https://www.it610.com/article/$data/n"; $block = $this->bigBlockChain[$block]; }return $data; }function __readPropertySets(){$offset = 0; //var_dump($this->entry); while ($offset < strlen($this->entry)) {$d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE); $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8); $type = ord($d[TYPE_POS]); //$maxBlock = strlen($d) / BIG_BLOCK_SIZE - 1; $startBlock = GetInt4d($d, START_BLOCK_POS); $size = GetInt4d($d, SIZE_POS); $name = ''; for ($i = 0; $i < $nameSize ; $i++) {$name .= $d[$i]; }$name = str_replace("\x00", "", $name); $this->props[] = array ('name' => $name, 'type' => $type,'startBlock' => $startBlock,'size' => $size); if (($name == "Workbook") || ($name == "Book")) {$this->wrkbook = count($this->props) - 1; } if ($name == "Root Entry") {$this->rootentry = count($this->props) - 1; }//echo "name ==$name=\n"; $offset += PROPERTY_STORAGE_BLOCK_SIZE; }}function getWorkBook(){if ($this->props[$this->wrkbook]['size'] < SMALL_BLOCK_THRESHOLD){//getSmallBlockStream(PropertyStorage ps) $rootdata = https://www.it610.com/article/$this->__readData($this->props[$this->rootentry]['startBlock']); $streamDatahttps://www.it610.com/article/= ''; $block = $this->props[$this->wrkbook]['startBlock']; //$count = 0; $pos = 0; while ($block != -2) {$pos = $block * SMALL_BLOCK_SIZE; $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE); $block = $this->smallBlockChain[$block]; }return $streamData; }else{$numBlocks = $this->props[$this->wrkbook]['size'] / BIG_BLOCK_SIZE; if ($this->props[$this->wrkbook]['size'] % BIG_BLOCK_SIZE != 0) {$numBlocks++; }if ($numBlocks == 0) return ''; //echo "numBlocks = $numBlocks\n"; //byte[] streamData = https://www.it610.com/article/new byte[numBlocks * BIG_BLOCK_SIZE]; //print_r($this->wrkbook); $streamDatahttps://www.it610.com/article/= ''; $block = $this->props[$this->wrkbook]['startBlock']; //$count = 0; $pos = 0; //echo "block = $block"; while ($block != -2) {$pos = ($block + 1) * BIG_BLOCK_SIZE; $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE); $block = $this->bigBlockChain[$block]; }//echo 'stream'.$streamData; return $streamData; }}}?>

【PHP实现读取Excel文件的记录(二)】以上就是PHP实现读取Excel文件的记录(二)的详细内容，更多关于PHP读取Excel记录的资料请关注脚本之家其它相关文章！