1, readexcel.system.php
Copy Code code as follows:
<?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.3ppt.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's available through the World-wide-web at the following URI:
* Http://www.php.net/license/3_0.txt. If you are not receive a copy of did
* The PHP License and are unable to obtain it through the web,
* 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 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 ';
Oleread.php's Content start========================================================================================== =============
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->sbdstartblockn";
$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= $datan";
$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 = $NUMBLOCKSN";
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;
}
}
}
Oleread.php's Content end============================================================================================ ===========
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.
* 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.3ppt.com). Now
* Maintained by David Sanders. Reads only Biff 7 and Biff 8 formats.
*
* @category spreadsheet
* @package Spreadsheet_excel_reader
* @author Vadim Tkachenko <vt@shoujidy.com>
* @copyright 2010-2011 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 readexcelsystem{
/**
* 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 are 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; ($#,# #0.00)",
0x8 => ' $%1.2f ',//"$#,# #0; ($#,# #0.00)",
0x9 => '%1.0f%% ',//"0%"
0xa => '%1.2f%% ',//"0%"
0xb => '%1.2f ',//0.00E00 ",
0x25 => '%1.0f ',//"#,# #0;(#,# #0)",
0x26 => '%1.0f ',//"#,# #0;(#,# #0)",
0x27 => '%1.2f ',//"#,# #0; (#,# #0.00)",
0x28 => '%1.2f ',//"#,# #0; (#,# #0.00)",
0x29 => '%1.0f ',//"#,# #0;(#,# #0)",
0x2a => ' $%1.0f ',//"$#,# #0;($#,# #0)",
0x2b => '%1.2f ',//"#,# #0; (#,# #0.00)",
0x2c => ' $%1.2f ',//"$#,# #0; ($#,# #0.00)",
0x30 => '%1.0f '); "# #0.0E0";
// }}}
{{{Spreadsheet_excel_reader ()
/**
* Constructor
*
* Some Basic Initialisation
*/
function __construct ()
{
$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 the ' iconv ' for encode utf-16le to your encoding
* Set MB If you are would like "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 is 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_SSTN";
$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);
The contains formatting information with the IF string
$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 "Izavratn";
}
$spos + + $len;
}
}
$retstr = ($asciiEncoding)? $RETSTR: $this->_encodeutf16 ($RETSTR);
echo "Str $i = $retstrn";
if ($richString) {
$spos + + 4 * $formattingRuns;
}
For extended strings, skip over the extended string data
if ($extendedString) {
$spos + + $extendedRunLength;
}
if ($retstr = = ' Derby ') {
echo "BBN";
//}
$this->sst[]= $retstr;
}
/* $continueRecords = array ();
while ($this->getnextcode () = = Type_continue) {
$continueRecords [] = & $this->nextrecord ();
}
echo "1 type_sstn";
$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_namen";
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.formatn";
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.nineteenfourn";
$this->nineteenfour = (ord ($this->data[$pos +4]) = = 1);
Break
Case Spreadsheet_excel_reader_type_boundsheet:
echo "TYPE.BOUNDSHEETN";
$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). "$coden";
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 $FCN";
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 $stringn";
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). " $STRINGN ";
}
Mulrkrecord ($R);
Get the individual cell records to 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 $stringn";
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 was 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 is a null string.
} else {
Result was a number, so the 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 $stringn";
}
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 a 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 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 * *)-$hours * 60;
$secs = Floor ($numValue * spreadsheet_excel_reader_msinaday)-$hours * 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));
$mantissalow 1 = ($rknumlow & 0x80000000) >> 31;
$mantissalow 2 = ($rknumlow & 0x7fffffff);
$value = $mantissa/pow (2, 20-($exp-1023));
if ($mantissalow 1!= 0) $value + + = 1/pow (2, ($exp-1023));
$value + + $mantissalow 2/pow (2 ($exp-1023));
echo "Sign = $sign, Exp = $exp, Mantissahighx = $mantissa, Mantissalow1 = $mantissalow 1, mantissalow2 = $mantissalow 2< ; Br>n ";
if ($sign) {$value =-1 * $value;}
return $value;
}
function Addcell ($row, $col, $string, $raw = ")
{
echo "ADD cel $row-$col $stringn";
$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
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 bits of the
The bit floating point value. The other bits are assumed to be 0
So, we use the upper 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:
*/
?>
2. Calling methods
Copy Code code as follows:
<?php
$data = new Readexcelsystem ();
$data->setoutputencoding (' utf-8 ');
$data->read ($location);
Print_r ($data->sheets[0][' cells ']);
?>
$data->sheets[0][' cells ']
Is the data in the Excle file we're going to get.
