PHP-ExcelReader,下载地址: http://sourceforge.net/projects/phpexcelreader
说明：
测试环境：MYSQL数据库采用utf8编码.导入EXCEL文档是xls格式,经过测试，xlsx 格式[excel 2007]也OK.
文中红色标注为需要注意的地方，请替换成你配置好的数据，如数据库配置等。运行http://localost/test.php实现导入。
以下是我贴出的详细代码，其中test.php为我写的测试文件，reader.php和oleread.inc文件是从上面提供的网址中下载的。
1. test.php
require_once
'
./includes/reader.php
'
;
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data -> setOutputEncoding( ' gbk ' );
// ”data.xls”是指要导入到mysql中的excel文件
$data -> read( ' date.xls ' );
@ $db = mysql_connect ( ' localhost ' , ' root ' , ' 1234 ' ) or
die ( " Could not connect to database. " ); // 连接数据库
mysql_query ( " set names 'gbk' " ); // 输出中文
mysql_select_db ( ' wenhuaedu ' ); // 选择数据库
error_reporting ( E_ALL ^ E_NOTICE );
for ( $i = 1 ; $i <= $data -> sheets[ 0 ][ ' numRows ' ]; $i ++ ) {
// 以下注释的for循环打印excel表数据
/*
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";
*/
// 以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！
$sql = " INSERT INTO test VALUES(' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 1 ] . " ',' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 2 ] . " ',' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 3 ] . " ') " ;
echo $sql . ' <br /> ' ;
$res = mysql_query ( $sql );
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data -> setOutputEncoding( ' gbk ' );
// ”data.xls”是指要导入到mysql中的excel文件
$data -> read( ' date.xls ' );
@ $db = mysql_connect ( ' localhost ' , ' root ' , ' 1234 ' ) or
die ( " Could not connect to database. " ); // 连接数据库
mysql_query ( " set names 'gbk' " ); // 输出中文
mysql_select_db ( ' wenhuaedu ' ); // 选择数据库
error_reporting ( E_ALL ^ E_NOTICE );
for ( $i = 1 ; $i <= $data -> sheets[ 0 ][ ' numRows ' ]; $i ++ ) {
// 以下注释的for循环打印excel表数据
/*
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";
*/
// 以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！
$sql = " INSERT INTO test VALUES(' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 1 ] . " ',' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 2 ] . " ',' " .
$data -> sheets[ 0 ][ ' cells ' ][ $i ][ 3 ] . " ') " ;
echo $sql . ' <br /> ' ;
$res = mysql_query ( $sql );
贴出其中包含的文件
OLERead.php
<?
php
define ( ' NUM_BIG_BLOCK_DEPOT_BLOCKS_POS ' , 0x2c );
define ( ' SMALL_BLOCK_DEPOT_BLOCK_POS ' , 0x3c );
define ( ' ROOT_START_BLOCK_POS ' , 0x30 );
define ( ' BIG_BLOCK_SIZE ' , 0x200 );
define ( ' SMALL_BLOCK_SIZE ' , 0x40 );
define ( ' EXTENSION_BLOCK_POS ' , 0x44 );
define ( ' NUM_EXTENSION_BLOCK_POS ' , 0x48 );
define ( ' PROPERTY_STORAGE_BLOCK_SIZE ' , 0x80 );
define ( ' BIG_BLOCK_DEPOT_BLOCKS_POS ' , 0x4c );
define ( ' SMALL_BLOCK_THRESHOLD ' , 0x1000 );
// property storage offsets
define ( ' SIZE_OF_NAME_POS ' , 0x40 );
define ( ' TYPE_POS ' , 0x42 );
define ( ' START_BLOCK_POS ' , 0x74 );
define ( ' SIZE_POS ' , 0x78 );
define ( ' IDENTIFIER_OLE ' , pack ( " CCCCCCCC " , 0xd0 , 0xcf , 0x11 , 0xe0 , 0xa1 , 0xb1 , 0x1a , 0xe1 ));
// echo 'ROOT_START_BLOCK_POS = '.ROOT_START_BLOCK_POS."\n";
//echo bin2hex($data[ROOT_START_BLOCK_POS])."\n";
//echo "a=";
//echo $data[ROOT_START_BLOCK_POS];
//function log
function GetInt4d( $data , $pos )
{
$value = ord ( $data [ $pos ]) | ( ord ( $data [ $pos + 1 ]) << 8 ) | ( ord ( $data [ $pos + 2 ]) << 16 ) | ( ord ( $data [ $pos + 3 ]) << 24 );
if ( $value >= 4294967294 )
{
$value =- 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 = @ 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 ;
$data = '' ;
while ( $block != - 2 ) {
$pos = ( $block + 1 ) * BIG_BLOCK_SIZE;
$data = $data . substr ( $this -> data , $pos , BIG_BLOCK_SIZE);
// echo "pos = $pos data=$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 = $this -> __readData( $this -> props[ $this -> rootentry][ ' startBlock ' ]);
$streamData = '' ;
$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 = new byte[numBlocks * BIG_BLOCK_SIZE];
//print_r($this->wrkbook);
$streamData = '' ;
$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 ;
}
}
}
?>
define ( ' NUM_BIG_BLOCK_DEPOT_BLOCKS_POS ' , 0x2c );
define ( ' SMALL_BLOCK_DEPOT_BLOCK_POS ' , 0x3c );
define ( ' ROOT_START_BLOCK_POS ' , 0x30 );
define ( ' BIG_BLOCK_SIZE ' , 0x200 );
define ( ' SMALL_BLOCK_SIZE ' , 0x40 );
define ( ' EXTENSION_BLOCK_POS ' , 0x44 );
define ( ' NUM_EXTENSION_BLOCK_POS ' , 0x48 );
define ( ' PROPERTY_STORAGE_BLOCK_SIZE ' , 0x80 );
define ( ' BIG_BLOCK_DEPOT_BLOCKS_POS ' , 0x4c );
define ( ' SMALL_BLOCK_THRESHOLD ' , 0x1000 );
// property storage offsets
define ( ' SIZE_OF_NAME_POS ' , 0x40 );
define ( ' TYPE_POS ' , 0x42 );
define ( ' START_BLOCK_POS ' , 0x74 );
define ( ' SIZE_POS ' , 0x78 );
define ( ' IDENTIFIER_OLE ' , pack ( " CCCCCCCC " , 0xd0 , 0xcf , 0x11 , 0xe0 , 0xa1 , 0xb1 , 0x1a , 0xe1 ));
// echo 'ROOT_START_BLOCK_POS = '.ROOT_START_BLOCK_POS."\n";
//echo bin2hex($data[ROOT_START_BLOCK_POS])."\n";
//echo "a=";
//echo $data[ROOT_START_BLOCK_POS];
//function log
function GetInt4d( $data , $pos )
{
$value = ord ( $data [ $pos ]) | ( ord ( $data [ $pos + 1 ]) << 8 ) | ( ord ( $data [ $pos + 2 ]) << 16 ) | ( ord ( $data [ $pos + 3 ]) << 24 );
if ( $value >= 4294967294 )
{
$value =- 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 = @ 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 ;
$data = '' ;
while ( $block != - 2 ) {
$pos = ( $block + 1 ) * BIG_BLOCK_SIZE;
$data = $data . substr ( $this -> data , $pos , BIG_BLOCK_SIZE);
// echo "pos = $pos data=$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 = $this -> __readData( $this -> props[ $this -> rootentry][ ' startBlock ' ]);
$streamData = '' ;
$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 = new byte[numBlocks * BIG_BLOCK_SIZE];
//print_r($this->wrkbook);
$streamData = '' ;
$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 ;
}
}
}
?>
还有一个
reader.php
<?
php
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
/* *
* 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.
*
* PHP versions 4 and 5
*
* LICENSE: This source file is subject to version 3.0 of the PHP license
* that is available through the world-wide-web at the following URI:
* http://www.php.net/license/3_0.txt. If you did not receive a copy of
* the PHP License and are unable to obtain it through the web, please
* send a note to license@php.net so we can mail you a copy immediately.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@apachephp.com>
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $
* @link http://pear.php.net/package/Spreadsheet_Excel_Reader
* @see OLE, 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.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@phpapache.com>
* @copyright 1997-2005 The PHP Group
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version Release: @package_version@
* @link http://pear.php.net/package/PackageName
* @see OLE, 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 = $ole->getData($i, 0, $ole->getDataLength($i));
} elseif ($pps->Name == 'Root Entry') {
$this->data = $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 code
if ( $this -> _ole -> error == 1 ) {
// bad file
die ( ' The filename ' . $sFileName . ' is not readable ' );
}
// check other error codes here (eg bad fileformat, etc
)
}
$this -> data = $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 = $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 =".$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 characters
if ( $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 data
if ( $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 = $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 = $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 dependent
if ( $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<br>\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 dependent
if ( $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 = 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 "<br>";
$sign = ( $rknumhigh & 0x80000000 ) >> 31 ;
$exp = ( $rknumhigh & 0x7ff00000 ) >> 20 ;
$mantissa = ( 0x100000 | ( $rknumhigh & 0x000fffff ));
$mantissalow1 = ( $rknumlow & 0x80000000 ) >> 31 ;
$mantissalow2 = ( $rknumlow & 0x7fffffff );
$value = $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<br>\n";
if ( $sign ) { $value = - 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 = $rknum >> 2 ;
} else {
// mmp
// first comment out the previously existing 7 lines of code here
// $tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc)));
// //$value = $tmp[''];
// if (array_key_exists(1, $tmp)) {
// $value = $tmp[1];
// } else {
// $value = $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 = $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 = ord ( $data [ $pos ]) | ( ord ( $data [ $pos + 1 ]) << 8 ) | ( ord ( $data [ $pos + 2 ]) << 16 ) | ( ord ( $data [ $pos + 3 ]) << 24 );
if ( $value >= 4294967294 )
{
$value =- 2 ;
}
return $value ;
}
}
/*
* Local variables:
* tab-width: 4
* c-basic-offset: 4
* c-hanging-comment-ender-p: nil
* End:
*/
?>
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
/* *
* 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.
*
* PHP versions 4 and 5
*
* LICENSE: This source file is subject to version 3.0 of the PHP license
* that is available through the world-wide-web at the following URI:
* http://www.php.net/license/3_0.txt. If you did not receive a copy of
* the PHP License and are unable to obtain it through the web, please
* send a note to license@php.net so we can mail you a copy immediately.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@apachephp.com>
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version CVS: $Id: reader.php 19 2007-03-13 12:42:41Z shangxiao $
* @link http://pear.php.net/package/Spreadsheet_Excel_Reader
* @see OLE, 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.
*
* @category Spreadsheet
* @package Spreadsheet_Excel_Reader
* @author Vadim Tkachenko <vt@phpapache.com>
* @copyright 1997-2005 The PHP Group
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version Release: @package_version@
* @link http://pear.php.net/package/PackageName
* @see OLE, 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 = $ole->getData($i, 0, $ole->getDataLength($i));
} elseif ($pps->Name == 'Root Entry') {
$this->data = $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 code
if ( $this -> _ole -> error == 1 ) {
// bad file
die ( ' The filename ' . $sFileName . ' is not readable ' );
}
// check other error codes here (eg bad fileformat, etc
)
}
$this -> data = $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 = $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 =".$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 characters
if ( $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 data
if ( $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 = $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 = $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 dependent
if ( $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<br>\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 dependent
if ( $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 = 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 "<br>";
$sign = ( $rknumhigh & 0x80000000 ) >> 31 ;
$exp = ( $rknumhigh & 0x7ff00000 ) >> 20 ;
$mantissa = ( 0x100000 | ( $rknumhigh & 0x000fffff ));
$mantissalow1 = ( $rknumlow & 0x80000000 ) >> 31 ;
$mantissalow2 = ( $rknumlow & 0x7fffffff );
$value = $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<br>\n";
if ( $sign ) { $value = - 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 = $rknum >> 2 ;
} else {
// mmp
// first comment out the previously existing 7 lines of code here
// $tmp = unpack("d", pack("VV", 0, ($rknum & 0xfffffffc)));
// //$value = $tmp[''];
// if (array_key_exists(1, $tmp)) {
// $value = $tmp[1];
// } else {
// $value = $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 = $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 = ord ( $data [ $pos ]) | ( ord ( $data [ $pos + 1 ]) << 8 ) | ( ord ( $data [ $pos + 2 ]) << 16 ) | ( ord ( $data [ $pos + 3 ]) << 24 );
if ( $value >= 4294967294 )
{
$value =- 2 ;
}
return $value ;
}
}
/*
* Local variables:
* tab-width: 4
* c-basic-offset: 4
* c-hanging-comment-ender-p: nil
* End:
*/
?>
转自http://skyline.yxtc.net/article_show.php?id=1255389392&cataid=18