Subversion Repositories eFlore/Applications.cel

<?php

/**

 * PHPExcel

 *

 * Copyright (c) 2006 - 2013 PHPExcel

 *

 * This library is free software; you can redistribute it and/or

 * modify it under the terms of the GNU Lesser General Public

 * License as published by the Free Software Foundation; either

 * version 2.1 of the License, or (at your option) any later version.

 *

 * This library is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

 * Lesser General Public License for more details.

 *

 * You should have received a copy of the GNU Lesser General Public

 * License along with this library; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

 *

 * @category   PHPExcel

 * @package    PHPExcel_Reader_Excel5

 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)

 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL

 * @version    ##VERSION##, ##DATE##

 */

// Original file header of ParseXL (used as the base for this class):

// --------------------------------------------------------------------------------

// Adapted from Excel_Spreadsheet_Reader developed by users bizon153,

// trex005, and mmp11 (SourceForge.net)

// http://sourceforge.net/projects/phpexcelreader/

// Primary changes made by canyoncasa (dvc) for ParseXL 1.00 ...

//	 Modelled moreso after Perl Excel Parse/Write modules

//	 Added Parse_Excel_Spreadsheet object

//		 Reads a whole worksheet or tab as row,column array or as

//		 associated hash of indexed rows and named column fields

//	 Added variables for worksheet (tab) indexes and names

//	 Added an object call for loading individual woorksheets

//	 Changed default indexing defaults to 0 based arrays

//	 Fixed date/time and percent formats

//	 Includes patches found at SourceForge...

//		 unicode patch by nobody

//		 unpack("d") machine depedency patch by matchy

//		 boundsheet utf16 patch by bjaenichen

//	 Renamed functions for shorter names

//	 General code cleanup and rigor, including <80 column width

//	 Included a testcase Excel file and PHP example calls

//	 Code works for PHP 5.x

// Primary changes made by canyoncasa (dvc) for ParseXL 1.10 ...

// http://sourceforge.net/tracker/index.php?func=detail&aid=1466964&group_id=99160&atid=623334

//	 Decoding of formula conditions, results, and tokens.

//	 Support for user-defined named cells added as an array "namedcells"

//		 Patch code for user-defined named cells supports single cells only.

//		 NOTE: this patch only works for BIFF8 as BIFF5-7 use a different

//		 external sheet reference structure

/** PHPExcel root directory */

if (!defined('PHPEXCEL_ROOT')) {

	/**

	 * @ignore

	 */

	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');

	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');

}

/**

 * PHPExcel_Reader_Excel5

 *

 * This class uses {@link http://sourceforge.net/projects/phpexcelreader/parseXL}

 *

 * @category	PHPExcel

 * @package		PHPExcel_Reader_Excel5

 * @copyright	Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)

 */

class PHPExcel_Reader_Excel5 extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader

{

	// ParseXL definitions

	const XLS_BIFF8						= 0x0600;

	const XLS_BIFF7						= 0x0500;

	const XLS_WorkbookGlobals			= 0x0005;

	const XLS_Worksheet					= 0x0010;

	// record identifiers

	const XLS_Type_FORMULA				= 0x0006;

	const XLS_Type_EOF					= 0x000a;

	const XLS_Type_PROTECT				= 0x0012;

	const XLS_Type_OBJECTPROTECT		= 0x0063;

	const XLS_Type_SCENPROTECT			= 0x00dd;

	const XLS_Type_PASSWORD				= 0x0013;

	const XLS_Type_HEADER				= 0x0014;

	const XLS_Type_FOOTER				= 0x0015;

	const XLS_Type_EXTERNSHEET			= 0x0017;

	const XLS_Type_DEFINEDNAME			= 0x0018;

	const XLS_Type_VERTICALPAGEBREAKS	= 0x001a;

	const XLS_Type_HORIZONTALPAGEBREAKS	= 0x001b;

	const XLS_Type_NOTE					= 0x001c;

	const XLS_Type_SELECTION			= 0x001d;

	const XLS_Type_DATEMODE				= 0x0022;

	const XLS_Type_EXTERNNAME			= 0x0023;

	const XLS_Type_LEFTMARGIN			= 0x0026;

	const XLS_Type_RIGHTMARGIN			= 0x0027;

	const XLS_Type_TOPMARGIN			= 0x0028;

	const XLS_Type_BOTTOMMARGIN			= 0x0029;

	const XLS_Type_PRINTGRIDLINES		= 0x002b;

	const XLS_Type_FILEPASS				= 0x002f;

	const XLS_Type_FONT					= 0x0031;

	const XLS_Type_CONTINUE				= 0x003c;

	const XLS_Type_PANE					= 0x0041;

	const XLS_Type_CODEPAGE				= 0x0042;

	const XLS_Type_DEFCOLWIDTH 			= 0x0055;

	const XLS_Type_OBJ					= 0x005d;

	const XLS_Type_COLINFO				= 0x007d;

	const XLS_Type_IMDATA				= 0x007f;

	const XLS_Type_SHEETPR				= 0x0081;

	const XLS_Type_HCENTER				= 0x0083;

	const XLS_Type_VCENTER				= 0x0084;

	const XLS_Type_SHEET				= 0x0085;

	const XLS_Type_PALETTE				= 0x0092;

	const XLS_Type_SCL					= 0x00a0;

	const XLS_Type_PAGESETUP			= 0x00a1;

	const XLS_Type_MULRK				= 0x00bd;

	const XLS_Type_MULBLANK				= 0x00be;

	const XLS_Type_DBCELL				= 0x00d7;

	const XLS_Type_XF					= 0x00e0;

	const XLS_Type_MERGEDCELLS			= 0x00e5;

	const XLS_Type_MSODRAWINGGROUP		= 0x00eb;

	const XLS_Type_MSODRAWING			= 0x00ec;

	const XLS_Type_SST					= 0x00fc;

	const XLS_Type_LABELSST				= 0x00fd;

	const XLS_Type_EXTSST				= 0x00ff;

	const XLS_Type_EXTERNALBOOK			= 0x01ae;

	const XLS_Type_DATAVALIDATIONS		= 0x01b2;

	const XLS_Type_TXO					= 0x01b6;

	const XLS_Type_HYPERLINK			= 0x01b8;

	const XLS_Type_DATAVALIDATION		= 0x01be;

	const XLS_Type_DIMENSION			= 0x0200;

	const XLS_Type_BLANK				= 0x0201;

	const XLS_Type_NUMBER				= 0x0203;

	const XLS_Type_LABEL				= 0x0204;

	const XLS_Type_BOOLERR				= 0x0205;

	const XLS_Type_STRING				= 0x0207;

	const XLS_Type_ROW					= 0x0208;

	const XLS_Type_INDEX				= 0x020b;

	const XLS_Type_ARRAY				= 0x0221;

	const XLS_Type_DEFAULTROWHEIGHT 	= 0x0225;

	const XLS_Type_WINDOW2				= 0x023e;

	const XLS_Type_RK					= 0x027e;

	const XLS_Type_STYLE				= 0x0293;

	const XLS_Type_FORMAT				= 0x041e;

	const XLS_Type_SHAREDFMLA			= 0x04bc;

	const XLS_Type_BOF					= 0x0809;

	const XLS_Type_SHEETPROTECTION		= 0x0867;

	const XLS_Type_RANGEPROTECTION		= 0x0868;

	const XLS_Type_SHEETLAYOUT			= 0x0862;

	const XLS_Type_XFEXT				= 0x087d;

	const XLS_Type_PAGELAYOUTVIEW		= 0x088b;

	const XLS_Type_UNKNOWN				= 0xffff;

	/**

	 * Summary Information stream data.

	 *

	 * @var string

	 */

	private $_summaryInformation;

	/**

	 * Extended Summary Information stream data.

	 *

	 * @var string

	 */

	private $_documentSummaryInformation;

	/**

	 * User-Defined Properties stream data.

	 *

	 * @var string

	 */

	private $_userDefinedProperties;

	/**

	 * Workbook stream data. (Includes workbook globals substream as well as sheet substreams)

	 *

	 * @var string

	 */

	private $_data;

	/**

	 * Size in bytes of $this->_data

	 *

	 * @var int

	 */

	private $_dataSize;

	/**

	 * Current position in stream

	 *

	 * @var integer

	 */

	private $_pos;

	/**

	 * Workbook to be returned by the reader.

	 *

	 * @var PHPExcel

	 */

	private $_phpExcel;

	/**

	 * Worksheet that is currently being built by the reader.

	 *

	 * @var PHPExcel_Worksheet

	 */

	private $_phpSheet;

	/**

	 * BIFF version

	 *

	 * @var int

	 */

	private $_version;

	/**

	 * Codepage set in the Excel file being read. Only important for BIFF5 (Excel 5.0 - Excel 95)

	 * For BIFF8 (Excel 97 - Excel 2003) this will always have the value 'UTF-16LE'

	 *

	 * @var string

	 */

	private $_codepage;

	/**

	 * Shared formats

	 *

	 * @var array

	 */

	private $_formats;

	/**

	 * Shared fonts

	 *

	 * @var array

	 */

	private $_objFonts;

	/**

	 * Color palette

	 *

	 * @var array

	 */

	private $_palette;

	/**

	 * Worksheets

	 *

	 * @var array

	 */

	private $_sheets;

	/**

	 * External books

	 *

	 * @var array

	 */

	private $_externalBooks;

	/**

	 * REF structures. Only applies to BIFF8.

	 *

	 * @var array

	 */

	private $_ref;

	/**

	 * External names

	 *

	 * @var array

	 */

	private $_externalNames;

	/**

	 * Defined names

	 *

	 * @var array

	 */

	private $_definedname;

	/**

	 * Shared strings. Only applies to BIFF8.

	 *

	 * @var array

	 */

	private $_sst;

	/**

	 * Panes are frozen? (in sheet currently being read). See WINDOW2 record.

	 *

	 * @var boolean

	 */

	private $_frozen;

	/**

	 * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.

	 *

	 * @var boolean

	 */

	private $_isFitToPages;

	/**

	 * Objects. One OBJ record contributes with one entry.

	 *

	 * @var array

	 */

	private $_objs;

	/**

	 * Text Objects. One TXO record corresponds with one entry.

	 *

	 * @var array

	 */

	private $_textObjects;

	/**

	 * Cell Annotations (BIFF8)

	 *

	 * @var array

	 */

	private $_cellNotes;

	/**

	 * The combined MSODRAWINGGROUP data

	 *

	 * @var string

	 */

	private $_drawingGroupData;

	/**

	 * The combined MSODRAWING data (per sheet)

	 *

	 * @var string

	 */

	private $_drawingData;

	/**

	 * Keep track of XF index

	 *

	 * @var int

	 */

	private $_xfIndex;

	/**

	 * Mapping of XF index (that is a cell XF) to final index in cellXf collection

	 *

	 * @var array

	 */

	private $_mapCellXfIndex;

	/**

	 * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection

	 *

	 * @var array

	 */

	private $_mapCellStyleXfIndex;

	/**

	 * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.

	 *

	 * @var array

	 */

	private $_sharedFormulas;

	/**

	 * The shared formula parts in a sheet. One FORMULA record contributes with one value if it

	 * refers to a shared formula.

	 *

	 * @var array

	 */

	private $_sharedFormulaParts;

	/**

	 * Create a new PHPExcel_Reader_Excel5 instance

	 */

	public function __construct() {

		$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();

	}

	/**

	 * Can the current PHPExcel_Reader_IReader read the file?

	 *

	 * @param 	string 		$pFilename

	 * @return 	boolean

	 * @throws PHPExcel_Reader_Exception

	 */

	public function canRead($pFilename)

	{

		// Check if file exists

		if (!file_exists($pFilename)) {

			throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");

		}

		try {

			// Use ParseXL for the hard work.

			$ole = new PHPExcel_Shared_OLERead();

			// get excel data

			$res = $ole->read($pFilename);

			return true;

		} catch (PHPExcel_Exception $e) {

			return false;

		}

	}

	/**

	 * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object

	 *

	 * @param 	string 		$pFilename

	 * @throws 	PHPExcel_Reader_Exception

	 */

	public function listWorksheetNames($pFilename)

	{

		// Check if file exists

		if (!file_exists($pFilename)) {

			throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");

		}

		$worksheetNames = array();

		// Read the OLE file

		$this->_loadOLE($pFilename);

		// total byte size of Excel data (workbook global substream + sheet substreams)

		$this->_dataSize = strlen($this->_data);

		$this->_pos		= 0;

		$this->_sheets	= array();

		// Parse Workbook Global Substream

		while ($this->_pos < $this->_dataSize) {

			$code = self::_GetInt2d($this->_data, $this->_pos);

			switch ($code) {

				case self::XLS_Type_BOF:	$this->_readBof();		break;

				case self::XLS_Type_SHEET:	$this->_readSheet();	break;

				case self::XLS_Type_EOF:	$this->_readDefault();	break 2;

				default:					$this->_readDefault();	break;

			}

		}

		foreach ($this->_sheets as $sheet) {

			if ($sheet['sheetType'] != 0x00) {

				// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module

				continue;

			}

			$worksheetNames[] = $sheet['name'];

		}

		return $worksheetNames;

	}

	/**

	 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)

	 *

	 * @param   string     $pFilename

	 * @throws   PHPExcel_Reader_Exception

	 */

	public function listWorksheetInfo($pFilename)

	{

		// Check if file exists

		if (!file_exists($pFilename)) {

			throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");

		}

		$worksheetInfo = array();

		// Read the OLE file

		$this->_loadOLE($pFilename);

		// total byte size of Excel data (workbook global substream + sheet substreams)

		$this->_dataSize = strlen($this->_data);

		// initialize

		$this->_pos    = 0;

		$this->_sheets = array();

		// Parse Workbook Global Substream

		while ($this->_pos < $this->_dataSize) {

			$code = self::_GetInt2d($this->_data, $this->_pos);

			switch ($code) {

				case self::XLS_Type_BOF:        $this->_readBof();        break;

				case self::XLS_Type_SHEET:      $this->_readSheet();      break;

				case self::XLS_Type_EOF:        $this->_readDefault();    break 2;

				default:                        $this->_readDefault();    break;

			}

		}

		// Parse the individual sheets

		foreach ($this->_sheets as $sheet) {

			if ($sheet['sheetType'] != 0x00) {

				// 0x00: Worksheet

				// 0x02: Chart

				// 0x06: Visual Basic module

				continue;

			}

			$tmpInfo = array();

			$tmpInfo['worksheetName'] = $sheet['name'];

			$tmpInfo['lastColumnLetter'] = 'A';

			$tmpInfo['lastColumnIndex'] = 0;

			$tmpInfo['totalRows'] = 0;

			$tmpInfo['totalColumns'] = 0;

			$this->_pos = $sheet['offset'];

			while ($this->_pos <= $this->_dataSize - 4) {

				$code = self::_GetInt2d($this->_data, $this->_pos);

				switch ($code) {

					case self::XLS_Type_RK:

					case self::XLS_Type_LABELSST:

					case self::XLS_Type_NUMBER:

					case self::XLS_Type_FORMULA:

					case self::XLS_Type_BOOLERR:

					case self::XLS_Type_LABEL:

						$length = self::_GetInt2d($this->_data, $this->_pos + 2);

						$recordData = substr($this->_data, $this->_pos + 4, $length);

						// move stream pointer to next record

						$this->_pos += 4 + $length;

						$rowIndex = self::_GetInt2d($recordData, 0) + 1;

						$columnIndex = self::_GetInt2d($recordData, 2);

						$tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex);

						$tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex);

						break;

					case self::XLS_Type_BOF:      $this->_readBof();          break;

					case self::XLS_Type_EOF:      $this->_readDefault();      break 2;

					default:                      $this->_readDefault();      break;

				}

			}

			$tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);

			$tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;

			$worksheetInfo[] = $tmpInfo;

		}

		return $worksheetInfo;

	}

	/**

	 * Loads PHPExcel from file

	 *

	 * @param 	string 		$pFilename

	 * @return 	PHPExcel

	 * @throws 	PHPExcel_Reader_Exception

	 */

	public function load($pFilename)

	{

		// Read the OLE file

		$this->_loadOLE($pFilename);

		// Initialisations

		$this->_phpExcel = new PHPExcel;

		$this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet

		if (!$this->_readDataOnly) {

			$this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style

			$this->_phpExcel->removeCellXfByIndex(0); // remove the default style

		}

		// Read the summary information stream (containing meta data)

		$this->_readSummaryInformation();

		// Read the Additional document summary information stream (containing application-specific meta data)

		$this->_readDocumentSummaryInformation();

		// total byte size of Excel data (workbook global substream + sheet substreams)

		$this->_dataSize = strlen($this->_data);

		// initialize

		$this->_pos					= 0;

		$this->_codepage			= 'CP1252';

		$this->_formats				= array();

		$this->_objFonts			= array();

		$this->_palette				= array();

		$this->_sheets				= array();

		$this->_externalBooks		= array();

		$this->_ref					= array();

		$this->_definedname			= array();

		$this->_sst					= array();

		$this->_drawingGroupData	= '';

		$this->_xfIndex				= '';

		$this->_mapCellXfIndex		= array();

		$this->_mapCellStyleXfIndex	= array();

		// Parse Workbook Global Substream

		while ($this->_pos < $this->_dataSize) {

			$code = self::_GetInt2d($this->_data, $this->_pos);

			switch ($code) {

				case self::XLS_Type_BOF:			$this->_readBof();				break;

				case self::XLS_Type_FILEPASS:		$this->_readFilepass();			break;

				case self::XLS_Type_CODEPAGE:		$this->_readCodepage();			break;

				case self::XLS_Type_DATEMODE:		$this->_readDateMode();			break;

				case self::XLS_Type_FONT:			$this->_readFont();				break;

				case self::XLS_Type_FORMAT:			$this->_readFormat();			break;

				case self::XLS_Type_XF:				$this->_readXf();				break;

				case self::XLS_Type_XFEXT:			$this->_readXfExt();			break;

				case self::XLS_Type_STYLE:			$this->_readStyle();			break;

				case self::XLS_Type_PALETTE:		$this->_readPalette();			break;

				case self::XLS_Type_SHEET:			$this->_readSheet();			break;

				case self::XLS_Type_EXTERNALBOOK:	$this->_readExternalBook();		break;

				case self::XLS_Type_EXTERNNAME:		$this->_readExternName();		break;

				case self::XLS_Type_EXTERNSHEET:	$this->_readExternSheet();		break;

				case self::XLS_Type_DEFINEDNAME:	$this->_readDefinedName();		break;

				case self::XLS_Type_MSODRAWINGGROUP:	$this->_readMsoDrawingGroup();	break;

				case self::XLS_Type_SST:			$this->_readSst();				break;

				case self::XLS_Type_EOF:			$this->_readDefault();			break 2;

				default:							$this->_readDefault();			break;

			}

		}

		// Resolve indexed colors for font, fill, and border colors

		// Cannot be resolved already in XF record, because PALETTE record comes afterwards

		if (!$this->_readDataOnly) {

			foreach ($this->_objFonts as $objFont) {

				if (isset($objFont->colorIndex)) {

					$color = self::_readColor($objFont->colorIndex,$this->_palette,$this->_version);

					$objFont->getColor()->setRGB($color['rgb']);

				}

			}

			foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {

				// fill start and end color

				$fill = $objStyle->getFill();

				if (isset($fill->startcolorIndex)) {

					$startColor = self::_readColor($fill->startcolorIndex,$this->_palette,$this->_version);

					$fill->getStartColor()->setRGB($startColor['rgb']);

				}

				if (isset($fill->endcolorIndex)) {

					$endColor = self::_readColor($fill->endcolorIndex,$this->_palette,$this->_version);

					$fill->getEndColor()->setRGB($endColor['rgb']);

				}

				// border colors

				$top      = $objStyle->getBorders()->getTop();

				$right    = $objStyle->getBorders()->getRight();

				$bottom   = $objStyle->getBorders()->getBottom();

				$left     = $objStyle->getBorders()->getLeft();

				$diagonal = $objStyle->getBorders()->getDiagonal();

				if (isset($top->colorIndex)) {

					$borderTopColor = self::_readColor($top->colorIndex,$this->_palette,$this->_version);

					$top->getColor()->setRGB($borderTopColor['rgb']);

				}

				if (isset($right->colorIndex)) {

					$borderRightColor = self::_readColor($right->colorIndex,$this->_palette,$this->_version);

					$right->getColor()->setRGB($borderRightColor['rgb']);

				}

				if (isset($bottom->colorIndex)) {

					$borderBottomColor = self::_readColor($bottom->colorIndex,$this->_palette,$this->_version);

					$bottom->getColor()->setRGB($borderBottomColor['rgb']);

				}

				if (isset($left->colorIndex)) {

					$borderLeftColor = self::_readColor($left->colorIndex,$this->_palette,$this->_version);

					$left->getColor()->setRGB($borderLeftColor['rgb']);

				}

				if (isset($diagonal->colorIndex)) {

					$borderDiagonalColor = self::_readColor($diagonal->colorIndex,$this->_palette,$this->_version);

					$diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);

				}

			}

		}

		// treat MSODRAWINGGROUP records, workbook-level Escher

		if (!$this->_readDataOnly && $this->_drawingGroupData) {

			$escherWorkbook = new PHPExcel_Shared_Escher();

			$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);

			$escherWorkbook = $reader->load($this->_drawingGroupData);

			// debug Escher stream

			//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());

			//$debug->load($this->_drawingGroupData);

		}

		// Parse the individual sheets

		foreach ($this->_sheets as $sheet) {

			if ($sheet['sheetType'] != 0x00) {

				// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module

				continue;

			}

			// check if sheet should be skipped

			if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {

				continue;

			}

			// add sheet to PHPExcel object

			$this->_phpSheet = $this->_phpExcel->createSheet();

			//	Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula

			//		cells... during the load, all formulae should be correct, and we're simply bringing the worksheet

			//		name in line with the formula, not the reverse

			$this->_phpSheet->setTitle($sheet['name'],false);

			$this->_phpSheet->setSheetState($sheet['sheetState']);

			$this->_pos = $sheet['offset'];

			// Initialize isFitToPages. May change after reading SHEETPR record.

			$this->_isFitToPages = false;

			// Initialize drawingData

			$this->_drawingData = '';

			// Initialize objs

			$this->_objs = array();

			// Initialize shared formula parts

			$this->_sharedFormulaParts = array();

			// Initialize shared formulas

			$this->_sharedFormulas = array();

			// Initialize text objs

			$this->_textObjects = array();

			// Initialize cell annotations

			$this->_cellNotes = array();

			$this->textObjRef = -1;

			while ($this->_pos <= $this->_dataSize - 4) {

				$code = self::_GetInt2d($this->_data, $this->_pos);

				switch ($code) {

					case self::XLS_Type_BOF:					$this->_readBof();						break;

					case self::XLS_Type_PRINTGRIDLINES:			$this->_readPrintGridlines();			break;

					case self::XLS_Type_DEFAULTROWHEIGHT:		$this->_readDefaultRowHeight();			break;

					case self::XLS_Type_SHEETPR:				$this->_readSheetPr();					break;

					case self::XLS_Type_HORIZONTALPAGEBREAKS:	$this->_readHorizontalPageBreaks();		break;

					case self::XLS_Type_VERTICALPAGEBREAKS:		$this->_readVerticalPageBreaks();		break;

					case self::XLS_Type_HEADER:					$this->_readHeader();					break;

					case self::XLS_Type_FOOTER:					$this->_readFooter();					break;

					case self::XLS_Type_HCENTER:				$this->_readHcenter();					break;

					case self::XLS_Type_VCENTER:				$this->_readVcenter();					break;

					case self::XLS_Type_LEFTMARGIN:				$this->_readLeftMargin();				break;

					case self::XLS_Type_RIGHTMARGIN:			$this->_readRightMargin();				break;

					case self::XLS_Type_TOPMARGIN:				$this->_readTopMargin();				break;

					case self::XLS_Type_BOTTOMMARGIN:			$this->_readBottomMargin();				break;

					case self::XLS_Type_PAGESETUP:				$this->_readPageSetup();				break;

					case self::XLS_Type_PROTECT:				$this->_readProtect();					break;

					case self::XLS_Type_SCENPROTECT:			$this->_readScenProtect();				break;

					case self::XLS_Type_OBJECTPROTECT:			$this->_readObjectProtect();			break;

					case self::XLS_Type_PASSWORD:				$this->_readPassword();					break;

					case self::XLS_Type_DEFCOLWIDTH:			$this->_readDefColWidth();				break;

					case self::XLS_Type_COLINFO:				$this->_readColInfo();					break;

					case self::XLS_Type_DIMENSION:				$this->_readDefault();					break;

					case self::XLS_Type_ROW:					$this->_readRow();						break;

					case self::XLS_Type_DBCELL:					$this->_readDefault();					break;

					case self::XLS_Type_RK:						$this->_readRk();						break;

					case self::XLS_Type_LABELSST:				$this->_readLabelSst();					break;

					case self::XLS_Type_MULRK:					$this->_readMulRk();					break;

					case self::XLS_Type_NUMBER:					$this->_readNumber();					break;

					case self::XLS_Type_FORMULA:				$this->_readFormula();					break;

					case self::XLS_Type_SHAREDFMLA:				$this->_readSharedFmla();				break;

					case self::XLS_Type_BOOLERR:				$this->_readBoolErr();					break;

					case self::XLS_Type_MULBLANK:				$this->_readMulBlank();					break;

					case self::XLS_Type_LABEL:					$this->_readLabel();					break;

					case self::XLS_Type_BLANK:					$this->_readBlank();					break;

					case self::XLS_Type_MSODRAWING:				$this->_readMsoDrawing();				break;

					case self::XLS_Type_OBJ:					$this->_readObj();						break;

					case self::XLS_Type_WINDOW2:				$this->_readWindow2();					break;

					case self::XLS_Type_PAGELAYOUTVIEW:	$this->_readPageLayoutView();					break;

					case self::XLS_Type_SCL:					$this->_readScl();						break;

					case self::XLS_Type_PANE:					$this->_readPane();						break;

					case self::XLS_Type_SELECTION:				$this->_readSelection();				break;

					case self::XLS_Type_MERGEDCELLS:			$this->_readMergedCells();				break;

					case self::XLS_Type_HYPERLINK:				$this->_readHyperLink();				break;

					case self::XLS_Type_DATAVALIDATIONS:		$this->_readDataValidations();			break;

					case self::XLS_Type_DATAVALIDATION:			$this->_readDataValidation();			break;

					case self::XLS_Type_SHEETLAYOUT:			$this->_readSheetLayout();				break;

					case self::XLS_Type_SHEETPROTECTION:		$this->_readSheetProtection();			break;

					case self::XLS_Type_RANGEPROTECTION:		$this->_readRangeProtection();			break;

					case self::XLS_Type_NOTE:					$this->_readNote();						break;

					//case self::XLS_Type_IMDATA:				$this->_readImData();					break;

					case self::XLS_Type_TXO:					$this->_readTextObject();				break;

					case self::XLS_Type_CONTINUE:				$this->_readContinue();					break;

					case self::XLS_Type_EOF:					$this->_readDefault();					break 2;

					default:									$this->_readDefault();					break;

				}

			}

			// treat MSODRAWING records, sheet-level Escher

			if (!$this->_readDataOnly && $this->_drawingData) {

				$escherWorksheet = new PHPExcel_Shared_Escher();

				$reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);

				$escherWorksheet = $reader->load($this->_drawingData);

				// debug Escher stream

				//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());

				//$debug->load($this->_drawingData);

				// get all spContainers in one long array, so they can be mapped to OBJ records

				$allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();

			}

			// treat OBJ records

			foreach ($this->_objs as $n => $obj) {

//				echo '<hr /><b>Object</b> reference is ',$n,'<br />';

//				var_dump($obj);

//				echo '<br />';

				// the first shape container never has a corresponding OBJ record, hence $n + 1

				if (isset($allSpContainers[$n + 1]) && is_object($allSpContainers[$n + 1])) {

					$spContainer = $allSpContainers[$n + 1];

					// we skip all spContainers that are a part of a group shape since we cannot yet handle those

					if ($spContainer->getNestingLevel() > 1) {

						continue;

					}

					// calculate the width and height of the shape

					list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());

					list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());

					$startOffsetX = $spContainer->getStartOffsetX();

					$startOffsetY = $spContainer->getStartOffsetY();

					$endOffsetX = $spContainer->getEndOffsetX();

					$endOffsetY = $spContainer->getEndOffsetY();

					$width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);

					$height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);

					// calculate offsetX and offsetY of the shape

					$offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;

					$offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;

					switch ($obj['otObjType']) {

						case 0x19:

							// Note

//							echo 'Cell Annotation Object<br />';

//							echo 'Object ID is ',$obj['idObjID'],'<br />';

//

							if (isset($this->_cellNotes[$obj['idObjID']])) {

								$cellNote = $this->_cellNotes[$obj['idObjID']];

								if (isset($this->_textObjects[$obj['idObjID']])) {

									$textObject = $this->_textObjects[$obj['idObjID']];

									$this->_cellNotes[$obj['idObjID']]['objTextData'] = $textObject;

								}

							}

							break;

						case 0x08:

//							echo 'Picture Object<br />';

							// picture

							// get index to BSE entry (1-based)

							$BSEindex = $spContainer->getOPT(0x0104);

							$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();

							$BSE = $BSECollection[$BSEindex - 1];

							$blipType = $BSE->getBlipType();

							// need check because some blip types are not supported by Escher reader such as EMF

							if ($blip = $BSE->getBlip()) {

								$ih = imagecreatefromstring($blip->getData());

								$drawing = new PHPExcel_Worksheet_MemoryDrawing();

								$drawing->setImageResource($ih);

								// width, height, offsetX, offsetY

								$drawing->setResizeProportional(false);

								$drawing->setWidth($width);

								$drawing->setHeight($height);

								$drawing->setOffsetX($offsetX);

								$drawing->setOffsetY($offsetY);

								switch ($blipType) {

									case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:

										$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);

										$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);

										break;

									case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:

										$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);

										$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);

										break;

								}

								$drawing->setWorksheet($this->_phpSheet);

								$drawing->setCoordinates($spContainer->getStartCoordinates());

							}

							break;

						default:

							// other object type

							break;

					}

				}

			}

			// treat SHAREDFMLA records

			if ($this->_version == self::XLS_BIFF8) {

				foreach ($this->_sharedFormulaParts as $cell => $baseCell) {

					list($column, $row) = PHPExcel_Cell::coordinateFromString($cell);

					if (($this->getReadFilter() !== NULL) && $this->getReadFilter()->readCell($column, $row, $this->_phpSheet->getTitle()) ) {

						$formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);

						$this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);

					}

				}

			}

			if (!empty($this->_cellNotes)) {

				foreach($this->_cellNotes as $note => $noteDetails) {

					if (!isset($noteDetails['objTextData'])) {

						if (isset($this->_textObjects[$note])) {

							$textObject = $this->_textObjects[$note];

							$noteDetails['objTextData'] = $textObject;

						} else {

							$noteDetails['objTextData']['text'] = '';

						}

					}

//					echo '<b>Cell annotation ',$note,'</b><br />';

//					var_dump($noteDetails);

//					echo '<br />';

					$cellAddress = str_replace('$','',$noteDetails['cellRef']);

					$this->_phpSheet->getComment( $cellAddress )

													->setAuthor( $noteDetails['author'] )

													->setText($this->_parseRichText($noteDetails['objTextData']['text']) );

				}

			}

		}

		// add the named ranges (defined names)

		foreach ($this->_definedname as $definedName) {

			if ($definedName['isBuiltInName']) {

				switch ($definedName['name']) {

				case pack('C', 0x06):

					// print area

					//	in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2

					$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?

					$extractedRanges = array();

					foreach ($ranges as $range) {

						// $range should look like one of these

						//		Foo!$C$7:$J$66

						//		Bar!$A$1:$IV$2

						$explodes = explode('!', $range);	// FIXME: what if sheetname contains exclamation mark?

						$sheetName = trim($explodes[0], "'");

						if (count($explodes) == 2) {

							if (strpos($explodes[1], ':') === FALSE) {

								$explodes[1] = $explodes[1] . ':' . $explodes[1];

							}

							$extractedRanges[] = str_replace('$', '', $explodes[1]); // C7:J66

						}

					}

					if ($docSheet = $this->_phpExcel->getSheetByName($sheetName)) {

						$docSheet->getPageSetup()->setPrintArea(implode(',', $extractedRanges)); // C7:J66,A1:IV2

					}

					break;

				case pack('C', 0x07):

					// print titles (repeating rows)

					// Assuming BIFF8, there are 3 cases

					// 1. repeating rows

					//		formula looks like this: Sheet!$A$1:$IV$2

					//		rows 1-2 repeat

					// 2. repeating columns

					//		formula looks like this: Sheet!$A$1:$B$65536

					//		columns A-B repeat

					// 3. both repeating rows and repeating columns

					//		formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2

					$ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma?

					foreach ($ranges as $range) {

						// $range should look like this one of these

						//		Sheet!$A$1:$B$65536

						//		Sheet!$A$1:$IV$2

						$explodes = explode('!', $range);