用php处理excel文件

2008年8月15日 0条评论 843次阅读 0人点赞

//———————————————————————这一段是我自己写的演示代码

<?php
require “excel_class.php”;

$data = array(
     “good” => array(

          array(“中国”,”美国”),
          array(“100″,”200”),
     )

);

//Read_Excel_File(“Book1.xls”,$return);
//print_r($return);
Create_Excel_File(“收入.xls”,$data[‘good’]);

//print_r($data);

//——————————————————————–

读取Excel文件
function Read_Excel_File($ExcelFile,$Result)

$ExcelFile Excel文件名
$Result 返回的结果
函数返回值正常返回0，否则返回错误信息

返回的值数组
$result[sheet名][行][列] 的值为相应Excel Cell的值

建立Excel文件
function Create_Excel_File($ExcelFile,$Data)

$ExcelFile Excel文件名
$Data Excel表格数据
请把函数写在PHP脚本的开头

例1：
<?
require “excel_class.php”;

Read_Excel_File(“Book1.xls”,$return);

for ($i=0;$i<count($return[Sheet1]);$i++)
{
for ($j=0;$j<count($return[Sheet1][$i]);$j++)
{
echo $return[Sheet1][$i][$j].”|”;
}
echo “ ”;
}
?>

例2：
<?
require “excel_class.php”;

Read_Excel_File(“Book1.xls”,$return);
Create_Excel_File(“ddd.xls”,$return[Sheet1]);
?>

下面这个是具体的类文件，这个是我自己修改过后的，原版的会把数字当成字符处理

<?
define(‘ABC_CRITICAL’, 0);
define(‘ABC_ERROR’, 1);
define(‘ABC_ALERT’, 2);
define(‘ABC_WARNING’, 3);
define(‘ABC_NOTICE’, 4);
define(‘ABC_INFO’, 5);
define(‘ABC_DEBUG’, 6);
define(‘ABC_TRACE’, 7);
define(‘ABC_VAR_DUMP’, 8);
define(‘ABC_NO_LOG’, -1);
$php_version = split( “\.”, phpversion() );
if( $php_version[0] == 4 && $php_version[1] <= 1 ) {
 if( !function_exists(‘var_export’) ) {
 function var_export( $exp, $ret ) {
ob_start();
var_dump( $exp );
$result = ob_get_contents();
ob_end_clean();
return $result;
}}}function print_bt()
{
print “<code>\n”;
$cs = debug_backtrace();
for( $i = 1; $i < count($cs) ; $i++ )
{
$item = $cs[ $i ];
for( $j = 0; $j < count($item[‘args’]); $j++ )
if( is_string($item[‘args’][$j]) )
$item[‘args’][$j] = “\”” . $item[‘args’][$j] . “\””;
$args = join(“,”, $item[‘args’] );
if( isset( $item[‘class’] ) )
$str = sprintf(“%s(%d): %s%s%s(%s)”,
$item[‘file’],
$item[‘line’],
$item[‘class’],
$item[‘type’],
$item[‘function’],
$args );
else
$str = sprintf(“%s(%d): %s(%s)”,
$item[‘file’],
$item[‘line’],
$item[‘function’],
$args );
echo $str . “ \n”;
}print “</code>\n”;
}function _die( $str )
{
print “Script died with reason: $str \n”;
print_bt();
exit();
}class DebugOut
{
var $priorities = array(ABC_CRITICAL => ‘critical’,
 ABC_ERROR => ‘error’,
 ABC_ALERT => ‘alert’,
 ABC_WARNING => ‘warning’,
 ABC_NOTICE => ‘notice’,
 ABC_INFO => ‘info’,
 ABC_DEBUG => ‘debug’,
 ABC_TRACE => ‘trace’,
 ABC_VAR_DUMP => ‘dump’
 );
var $_ready = false;
var $_currentPriority = ABC_DEBUG;
var $_consumers = array();
var $_filename;
var $_fp;
var $_logger_name;

/*
* return 1900 Date as integer TIMESTAMP.
* for UNIX date must be
*
*/
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($xls_date){
 $ret = array();
 // leap year bug
 if ($xls_date == 60) {
 $ret[‘day’] = 29;
 $ret[‘month’] = 2;
 $ret[‘year’] = 1900;
 return $ret;
 } else if ($xls_date < 60) {
 // 29-02-1900 bug
 $xls_date++;
 } // Modified Julian to DMY calculation with an addition of 2415019
 $l = $xls_date + 68569 + 2415019;
 $n = (int)(( 4 * $l ) / 146097);
 $l = $l – (int)(( 146097 * $n + 3 ) / 4);
 $i = (int)(( 4000 * ( $l + 1 ) ) / 1461001);
 $l = $l – (int)(( 1461 * $i ) / 4) + 31;
 $j = (int)(( 80 * $l ) / 2447);
 $ret[‘day’] = $l – (int)(( 2447 * $j ) / 80);
 $l = (int)($j / 11);
 $ret[‘month’] = $j + 2 – ( 12 * $l );
 $ret[‘year’] = 100 * ( $n – 49 ) + $i + $l;
 return $ret;
}function isInternalDateFormat($format) {
 $retval =false;
 switch(format) {
 // Internal Date Formats as described on page 427 in
 // Microsoft Excel Dev’s Kit…
 case 0x0e:
 case 0x0f:
 case 0x10:
 case 0x11:
 case 0x12:
 case 0x13:
 case 0x14:
 case 0x15:
 case 0x16:
 case 0x2d:
 case 0x2e:
 case 0x2f:
 // Additional internal date formats found by inspection
 // Using Excel v.X 10.1.0 (Mac)
 case 0xa4:
 case 0xa5:
 case 0xa6:
 case 0xa7:
 case 0xa8:
 case 0xa9:
 case 0xaa:
 case 0xab:
 case 0xac:
 case 0xad:
 $retval = true; break;
 default: $retval = false; break;
 } return $retval;
}}define(‘EXCEL_FONT_RID’,0x31);
define(‘XF_SCRIPT_NONE’,0);
define(‘XF_SCRIPT_SUPERSCRIPT’,1);
define(‘XF_SCRIPT_SUBSCRIPT’,2);
define(‘XF_UNDERLINE_NONE’,0x0);
define(‘XF_UNDERLINE_SINGLE’,0x1);
define(‘XF_UNDERLINE_DOUBLE’,0x2);
define(‘XF_UNDERLINE_SINGLE_ACCOUNTING’,0x3);
define(‘XF_UNDERLINE_DOUBLE_ACCOUNTING’,0x4);
define(‘XF_STYLE_ITALIC’, 0x2);
define(‘XF_STYLE_STRIKEOUT’, 0x8);
define(‘XF_BOLDNESS_REGULAR’,0x190);
define(‘XF_BOLDNESS_BOLD’,0x2BC);

class ExcelFont {

$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”,” \n”, ExcelFont::toString($font,$fonti));
 echo “FontRecord for sting “.$this->sst[‘data’][$sst_ind].” ”;*/
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 ”;
echo str_replace(“\n”,” \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]);
}/*DEBUG*/ $this->dbglog->debug(“parse_worksheet() function returns “.var_export($data,true)); /*DEBUG*/
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’);
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;
}if( ord($wb[0]) != 0x09 ){
/*DBG*/ $this->dbglog->debug(“parse_workbook() function workbook found (ord($wb[0]) != 0x09) returns 6”);
return 6;
}$vers = ord($wb[1]);
if( ($vers!=0) && ($vers!=2) && ($vers!=4) && ($vers!=8) ){
return 8;
 }if( $vers!=8 )
$biff_ver = ($ver+4)/2;
else {
if( strlen($wb) < 12 ) return 6;
switch( ord($wb[4])+256*ord($wb[5]) )
{
case 0x0500:
if( ord($wb[0x0a])+256*ord($wb[0x0b]) < 1994 )
$biff_ver = 5;
else {
switch(ord( $wb[8])+256*ord($wb[9]) ) {
case 2412:
case 3218:
case 3321:
$biff_ver = 5;
break;
default:
$biff_ver = 7;
break;
}}break;
case 0x0600:
$biff_ver = 8;
break;
default:
return 8;
}}if( $biff_ver < 5 ) return 8;
$ptr = 0;
$this->worksheet[‘offset’] = array();
$this->worksheet[‘options’] = array();
$this->worksheet[‘unicode’] = array();
$this->worksheet[‘name’] = array();
$this->worksheet[‘data’] = array();
$this->format = $this->populateFormat();
$this->fonts = array();
$this->fonts[0] = ExcelFont::basicFontRecord();
$this->xf = array();
$this->xf[‘format’] = array();
$this->xf[‘font’] = array();
$this->xf[‘type_prot’] = array();
$this->xf[‘alignment’] = array();
$this->xf[‘decoration’] = array();
$xf_cnt=0;
$this->sst[‘unicode’] = array();
$this->sst[‘data’] = array();
$opcode = 0;
$sst_defined = false;
$wblen = strlen($wb);
while( (ord($wb[$ptr])!=0x0a) && ($ptr<$wblen) )
{
$oc = ord($wb[$ptr])+256*ord($wb[$ptr+1]);
if( $oc != 0x3c )
$opcode = $oc;
switch ($opcode)
{
case 0x0085:
$ofs = ExcelParserUtil::str2long(substr($wb,$ptr+4,4));
$this->worksheet[‘offset’][] = $ofs;
$this->worksheet[‘options’][] = ord($wb[$ptr+8])+256*ord($wb[$ptr+9]);
if( $biff_ver==8 ) {
$len = ord($wb[$ptr+10]);
if( (ord($wb[$ptr+11]) & 1) > 0 ) {
$this->worksheet[‘unicode’][] = true;
$len = $len*2;
} else {
$this->worksheet[‘unicode’][] = false;
} $this->worksheet[‘name’][] = substr($wb,$ptr+12,$len);
} else {
$this->worksheet[‘unicode’][] = false;
$len = ord($wb[$ptr+10]);
$this->worksheet[‘name’][] = substr($wb,$ptr+11,$len);
}$pws = $this->parse_worksheet(substr($wb,$ofs));
if( is_array($pws) )
$this->worksheet[‘data’][] = $pws;
else
return $pws;
break;
// Format
case 0x041e:
 $fidx = ord($wb[$ptr+4])+256*ord($wb[$ptr+5]);
if($fidx<0x31 ||$fidx==0x31 )
break;
elseif($biff_ver>7)
 $this->format[$fidx] = $this->getUnicodeString($wb,$ptr+6);
 break;
// FONT 0x31
 case EXCEL_FONT_RID:
 $rec = ExcelFont::getFontRecord($wb,$ptr+4);
 $this->fonts[count($this->fonts)] = $rec;
/*echo str_replace(“\n”,” \n”,ExcelFont::toString($rec,count($this->fonts)-1));
echo “FontRecord ” */;
 break;
// XF
case 0x00e0:
$this->xf[‘font’][$xf_cnt] = ord($wb[$ptr+4])+256*ord($wb[$ptr+5]);
$this->xf[‘format’][$xf_cnt] = ord($wb[$ptr+6])+256*ord($wb[$ptr+7]);
$this->xf[‘type’][$xf_cnt] = “1”;
$this->xf[‘bitmask’][$xf_cnt] = “1”;
$xf_cnt++;
 break;
// SST
case 0x00fc:
if( $biff_ver < 8 ) break;
$sbuflen = ord($wb[$ptr+2]) + 256*ord($wb[$ptr+3]);
if( $oc != 0x3c ) {
if( $sst_defined ) return 6;
$snum = ExcelParserUtil::str2long(substr($wb,$ptr+8,4));
$sptr = $ptr+12;
$sst_defined = true;
} else {
if( $rslen > $slen ) {
$sptr = $ptr+4;
$rslen -= $slen;
$slen = $rslen;
if( (ord($wb[$sptr]) & 1) > 0 ) {
if( $char_bytes == 1 ) {
$sstr = ”;
for( $i=0; $i<strlen($str); $i++ )
$sstr .= $str[$i].chr(0);
$str = $sstr;
$char_bytes=2;
} $schar_bytes = 2;
} else {
$schar_bytes = 1;
}if( $sptr+$slen*$schar_bytes > $ptr+4+$sbuflen )
$slen = ($ptr+$sbuflen-$sptr+3)/$schar_bytes;
$sstr = substr($wb,$sptr+1,$slen*$schar_bytes);
if( ($char_bytes == 2) && ($schar_bytes == 1) )
{
$sstr2 = ”;
for( $i=0; $i<strlen($sstr); $i++ )
$sstr2 .= $sstr[$i].chr(0);
$sstr = $sstr2;
}$str .= $sstr;
$sptr += $slen*$schar_bytes+1+4*$rt+$fesz;
if( $slen < $rslen ) {
if( ($sptr >= strlen($wb)) ||
 ($sptr < $ptr+4+$sbuflen) ||
 (ord($wb[$sptr]) != 0x3c) )
{
 return 6;
}break;
} else {
if( $char_bytes == 2 ) {
$this->sst[‘unicode’][] = true;
} else {
$this->sst[‘unicode’][] = false;
}$this->sst[‘data’][] = $str;
$snum–;
} } else {
$sptr = $ptr+4;
if( $sptr > $ptr ) $sptr += 4*$rt+$fesz;
}}while( ($sptr < $ptr+4+$sbuflen) &&
($sptr < strlen($wb)) &&
($snum > 0) )
{
$rslen = ord($wb[$sptr])+256*ord($wb[$sptr+1]);
$slen = $rslen;
if( (ord($wb[$sptr+2]) & 1) > 0 ) {
$char_bytes = 2;
} else {
$char_bytes = 1;
} $rt = 0;
$fesz = 0;
switch (ord($wb[$sptr+2]) & 0x0c) {
// Rich-Text with Far-East
case 0x0c:
$rt = ord($wb[$sptr+3])+256*(ord($wb[$sptr+4]));
$fesz = ExcelParserUtil::str2long(substr($wb,$sptr+5,4));
if( $sptr+9+$slen*$char_bytes > $ptr+4+$sbuflen )
$slen = ($ptr+$sbuflen-$sptr-5)/$char_bytes;
$str = substr($wb,$sptr+9,$slen*$char_bytes);
$sptr += $slen*$char_bytes+9;
break;
// Rich-Text
case 8:
$rt = ord($wb[$sptr+3])+256*(ord($wb[$sptr+4]));
if( $sptr+5+$slen*$char_bytes > $ptr+4+$sbuflen )
$slen = ($ptr+$sbuflen-$sptr-1)/$char_bytes;
$str = substr($wb,$sptr+5,$slen*$char_bytes);
$sptr += $slen*$char_bytes+5;
break;
// Far-East
case 4:
$fesz = ExcelParserUtil::str2long(substr($wb,$sptr+3,4));
if( $sptr+7+$slen*$char_bytes > $ptr+4+$sbuflen )
$slen = ($ptr+$sbuflen-$sptr-3)/$char_bytes;
$str = substr($wb,$sptr+7,$slen*$char_bytes);
$sptr += $slen*$char_bytes+7;
break;
// Compressed or uncompressed unicode
case 0:
if( $sptr+3+$slen*$char_bytes > $ptr+4+$sbuflen )
$slen = ($ptr+$sbuflen-$sptr+1)/$char_bytes;
$str = substr($wb,$sptr+3,$slen*$char_bytes);
$sptr += $slen*$char_bytes+3;
break;
} if( $slen < $rslen ) {
if( ($sptr >= strlen($wb)) ||
 ($sptr < $ptr+4+$sbuflen) ||
 (ord($wb[$sptr]) != 0x3c) ) return 6;
} else {
if( $char_bytes == 2 ) {
$this->sst[‘unicode’][] = true;
} else {
$this->sst[‘unicode’][] = false;
}$sptr += 4*$rt+$fesz;
$this->sst[‘data’][] = $str;
$snum–;
}} // switch
break;
} // switch
// !!! Optimization:
// $this->wsb[] = substr($wb,$ptr,4+256*ord($wb[$ptr+3])+ord($wb[$ptr+2]));
$ptr += 4+256*ord($wb[$ptr+3])+ord($wb[$ptr+2]);
} // while
// !!! Optimization:
// $this->workbook = $wb;
$this->biff_version = $biff_ver;
/*DBG*/ $this->dbglog->debug(“parse_workbook() function returns 0”);
return 0;
}// ParseFromString & ParseFromFile
//———————————
//
// IN:
//string contents – File contents
//string filename – File name of an existing Excel file.
//
// OUT:
//0 – success
//1 – can’t open file
//2 – file too small to be an Excel file
//3 – error reading header
//4 – error reading file
//5 – This is not an Excel file or file stored in < Excel 5.0
//6 – file corrupted
//7 – data not found
//8 – Unsupported file version
function ParseFromString( $contents )
{
$this->dbglog->info(“ParseFromString() enter.”);
$this->dp = new DataProvider( $contents, DP_STRING_SOURCE );
return $this->InitParser();
}function ParseFromFile( $filename )
{
$this->dbglog->info(“ParseFromFile() enter.”);
$this->dp = new DataProvider( $filename, DP_FILE_SOURCE );
return $this->InitParser();
}function InitParser()
{
$this->dbglog->info(“InitParser() enter.”);
if( !$this->dp->isValid() )
{
$this->dbglog->error(“InitParser() Failed to open file.”);
$this->dbglog->error(“InitParser() function returns 1”);
return 1;
}if( $this->dp->getSize() <= 0x200 )
{
$this->dbglog->error(“InitParser() File too small to be an Excel file.”);
$this->dbglog->error(“InitParser() function returns 2”);
return 2;
}$this->max_blocks = $this->dp->getBlocks();
// read file header
$hdrbuf = $this->dp->get( 0, 0x200 );
if( strlen( $hdrbuf ) < 0x200 )
{
$this->dbglog->error(“InitParser() Error reading header.”);
$this->dbglog->error(“InitParser() function returns 3”);
return 3;
}// check file header
$header_sig = array(0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1);
for( $i = 0; $i < count($header_sig); $i++ )
if( $header_sig[$i] != ord( $hdrbuf[$i] ) ){
/*DBG*/ $this->dbglog->error(“InitParser() function founds invalid header”);
/*DBG*/ $this->dbglog->error(“InitParser() function returns 5”);
return 5;
 }$f_header = new DataProvider( $hdrbuf, DP_STRING_SOURCE );
unset( $hdrbuf, $header_sig, $i );
$this->dp->_baseOfs = 0x200;
$rc = $this->parse_workbook( $f_header, $this->dp );
unset( $f_header );
unset( $this->dp, $this->max_blocks, $this->max_sblocks );
return $rc;
}}function uc2html($str) {
$ret = ”;
for( $i=0; $i<strlen($str)/2; $i++ ) {
$charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);
$ret .= ‘&#’.$charcode;
}return $ret;
}

//————————读取Excel文件
function Read_Excel_File($ExcelFile,&$result) {

$exc = new ExcelFileParser(“”, ABC_NO_LOG );
$res=$exc->ParseFromFile($ExcelFile);$result=null;

switch ($res) {
case 0: break;
case 1: $err=”无法打开文件”; break;
case 2: $err=”文件太小，可能不是Excel文件”; break;
case 3: $err=”文件头读取错误”; break;
case 4: $err=”读取文件时出错”; break;
case 5: $err=”这不是一个Excel文件或者是Excel5.0以前版本文件”; break;
case 6: $err=”文件损坏”; break;
case 7: $err=”在文件中没有发现Excel数据”; break;
case 8: $err=”不支持的文件版本”; break;
default:
$err=”未知错误”; break;
}

for( $ws_num=0; $ws_num<count($exc->worksheet[‘name’]); $ws_num++ )
{
$Sheetname=$exc->worksheet[‘name’][$ws_num];
$ws = $exc->worksheet[‘data’][$ws_num];
for( $j=0; $j<=$ws[‘max_row’]; $j++ ) {
 for( $i=0; $i<=$ws[‘max_col’]; $i++ ) {

 $data = $ws[‘cell’][$j][$i];

 switch ($data[‘type’]) {
 // string
 case 0:
 $ind = $data[‘data’];
 if( $exc->sst[‘unicode’][$ind] ) {
 $s = uc2html($exc->sst[‘data’][$ind]);
 } else
 $s = $exc->sst[‘data’][$ind];
 if( strlen(trim($s))==0 )
 $V=””;
 else
 $V=$s;
 break;
 // integer number
 case 1:
 $V=(int)($data[‘data’]);
 break;
 // float number
 case 2:
 $V=(float)($data[‘data’]);
 break;
 // date
 case 3:
 $ret = $exc->getDateArray($data[‘data’]);
 $V=$ret[‘year’].”-“.$ret[‘month’].”-“.$ret[‘day’].” “.$ret[‘hour’];
 break;
 default:
 break;
 }
 $result[$Sheetname][$j][$i]=$V;
 }
}
}
if ($err==”) {return 0;} else {return $err;}
}

//————————建立Excel文件
function Create_Excel_File($ExcelFile,$Data) {

header (‘Content-type: application/x-msexcel’);
header (“Content-Disposition: attachment; filename=$ExcelFile” );

xlsBOF();
for ($i=0;$i<count($Data[0]);$i++)
{
for ($j=0;$j<count($Data);$j++)
{
 $v=$Data[$j][$i];

 if (is_numeric($v))
 xlsWriteNumber($j,$i,$v);
 else
 xlsWriteLabel($j,$i,$v);
}
}
xlsEOF();
}
?>

比格高

用php处理excel文件

发表评论