File: ExcelXMLParser.php

<?php
/*
* ============================================================================
*
* @name ExcelXMLParser.php
*
* @author Andrew A. Aculana
* @version 2.0
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
* @date 2006-07-03
*
* ============================================================================
*
* License:	GNU Lesser General Public License (LGPL)
*
* Copyright (c) 2004 LINK2SUPPORT INC.  All rights reserved.
*
* This file is part of the L2S Online Application Framework
*
* 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., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*
* ============================================================================
*/

/**
 * @package ExcelXMLParser
 * @subpackage ExcelXMLParser
 */
 
include_once 'ExcelXMLWorkbook.php';
include_once 'ExcelXMLConstants.php';
include_once 'ExcelXMLError.php';
include_once 'Utils.php';

class ExcelXMLParser
{
	/**
	 * Excel XML Parser Attributes
	 * @var Associative Array $data
	 * @var Workbook $Workbook
	 * @var Encoding $Encoding
	 * @var String $XMLFilename
	 */
	var $data;
	var $Workbook;
	var $Encoding;
	var $XMLFileName;
	
	function ExcelXMLParser()
	{
		/* xml data buffer */
		$this->data 		= array();
		/* xml filename */
		$this->XMLFileName 	= "";
		/* handle to workbook */
		$this->Workbook		= NULL;
		
	}
#-----------------------------------------------------------------------------#

	/**
	 * set the default encoding (for future implementation)
	 *
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyright 2006-07-03
	 * @param const int $Encoding
	 * @return null
	 */
	function setEncoding($Encoding = EXCELXML_ENCODING_UTF_8){
		$this->Encoding = $Encoding;
	}
#-----------------------------------------------------------------------------#

	/**
	 * get the default encoding
	 *
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyright 2006-07-03
	 * @return const int
	 */
	function getEncoding(){
		return $this->Encoding;
	}
#-----------------------------------------------------------------------------#

	/**
	 * convert XML to array
	 *
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyright 2006-07-03
	 * @param Associative Array $WorksheetNames
	 * @return Associative Array reference
	 */
	function &XMLToArray($WorksheetNames)
	{
		
		$XMLFileHandle 	 = NULL;
		$XMLParserHandle = NULL;
		
		/* make sure filename was supplied */
		if(trim($this->XMLFileName)==''){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: Filename not set");
			return $Error;
		}
		/* open xml file */
		if(!($XMLFileHandle=@fopen($this->XMLFileName,"r"))){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: Failed to open xml file");
			return $Error;			
		}else{
			/* tracked merge cells Accross horizontally*/
			$current_cell_merge_across = 0;
			
			/* create and initialize the xml parser */
			if(!($XMLParserHandle = xml_parser_create())){
				$Error = new ExcelXMLError();
				$Error->setErrorMessage("Error: Failed to create parser handle");
				return $Error;				
			}
			/* get the current file size */
			$FileSize = filesize($this->XMLFileName);
			if($FileSize){
				/* read xml file */
				$XMLString = @fread($XMLFileHandle,$FileSize);
				
				/* initialize parser options */
				xml_parser_set_option($XMLParserHandle, XML_OPTION_CASE_FOLDING, 0);
				xml_parser_set_option($XMLParserHandle, XML_OPTION_SKIP_WHITE, 1);
				/* convert xml string to xml array of elements */
				xml_parse_into_struct($XMLParserHandle, $XMLString, $XMLElements, $index);

				$CurrentWorksheet = "";
				$splice_start     = NULL;
				$splice_length	  = NULL;
				$splice_table = array();
				
				$TotalWorksheet   = 0;
				foreach($XMLElements as $key => $val){
					$value =& $XMLElements[$key];
					$elem  =& $XMLElements;
					if($value['tag'] == 'Worksheet' && $val['type'] == 'open'){
						$TotalWorksheet++;
						if(isset($value['attributes']['ss:Name'])){
							$CurrentWorksheet = $value['attributes']['ss:Name'];
						}elseif(isset($value['attributes']['Name'])){
							$CurrentWorksheet = $value['attributes']['Name'];
						}
						
						$splice_start = $key;
						
						foreach($index['Worksheet'] as $k => $v){
							if($v == $splice_start){
								$splice_length = ($index['Worksheet'][$k+1])-$splice_start;
								break;
							}
						}
						
						if(!in_array($CurrentWorksheet,$WorksheetNames) && count($WorksheetNames)){
							$TotalWorksheet--;
							$splice_table[] = array("START"=>$splice_start,"LENGTH"=>$splice_length);
						}
					}
					
				}
				
				if($TotalWorksheet <= 0){
					$Error = new ExcelXMLError();
					$Error->setErrorMessage("Error: A workbook must contain at least one visible worksheet.");
					return $Error;
				}
				
				
				if(count($WorksheetNames)){
					for($x = count($splice_table)-1;$x >= 0;$x--){
						array_splice($elem,$splice_table[$x]['START'],$splice_table[$x]['LENGTH']+1);
					}
				}
				
				/* free XML Parser Handle */
				xml_parser_free($XMLParserHandle);
				/* unset xml file content buffer */
				unset($XMLString);
				/* temporary stack to hold xml element */					
				$xmlStack 	= array(array());
				/* index to top of stack */
				$topOfStack = 0;
				/* xml array tree */
				$parent 	= array();
				/* current worksheet row */
				$row		= 0;
				$cell_row	= 0;
				$ctr 		= 0;
				$worksheetName = "";
				$processWorksheet = true;
				$tagi		= array();
				$celladdress= "";
				foreach($XMLElements as $vals){
					$val =& $vals;
					$type = $val['type'];
					if($type=='open' && $val['tag']=='Worksheet')
					{
						$worksheetName = $val['attributes']['ss:Name'];
						if(in_array($worksheetName,$WorksheetNames)){
							$processWorksheet = true;
						}else{
							$processWorksheet = false;
						}
					}
					
					if($type=='close' && $val['tag']=='Worksheet')
					{
						$worksheetName = "";
						$processWorksheet = true;
					}
					
					if(!$processWorksheet){
 					}
					
					/* check if this is an open or complete and a table element */
					if(($type=='open' || $type=='complete') && ($val['tag']=='Table')){
						/* reset row and cell to 0 
						   clear celladress string */
						$cell 			= 0;
						$row 			= 0;
						$cell_row 		= 0;
						$celladdress 	= "";
						$rowcol 		= "";
					}
					
					/* check if this is an open or complete and a cell element */
					if(($type=='open' || $type=='complete') && ($val['tag']=='Cell'))
					{
						if(isset($val['attributes']['ss:Index'])){
							$ssColIndex = (int) $val['attributes']['ss:Index'];
						}elseif(isset($val['attributes']['Index'])){
							$ssColIndex = (int) $val['attributes']['Index'];
						}else{
							$ssColIndex = 0;
						}
						
						if($ssColIndex > 0){
							$cell_col = $ssColIndex-1;
						}
						
						/* get number of cells merge accross */
						if(isset($val['attributes']['ss:MergeAcross'])){
							$ssMergeAccross = (int) $val['attributes']['ss:MergeAcross'];
						}elseif(isset($val['attributes']['MergeAcross'])){
							$ssMergeAccross = (int) $val['attributes']['MergeAcross'];
						}else{
							$ssMergeAccross = 0;
						}
					
						$celladdress 	= Utils::RowColToCell($cell_row-1,$cell_col);
						
						$rowcol		 	= array("Row"=>$row-1,"Col"=>$col,"A1"=>$celladdress);
						$cell_col 	   += $ssMergeAccross;
						$cell_col++;
						$col++;
					}
					
					/* check if this is an open or complete and a row element */
					if(($type=='open' || $type=='complete') && ($val['tag']=='Row'))
					{
						/* if index for this row is available set the current row to this index */
						if(isset($val['attributes']['ss:Index'])){
							$ssRowIndex = (int) $val['attributes']['ss:Index'];
						}elseif(isset($val['attributes']['Index'])){
							$ssRowIndex = (int) $val['attributes']['Index'];
						}else{
							$ssRowIndex = 0;
						}
						
						if($ssRowIndex > 0){
							$cell_row = $ssRowIndex;
						}else{
							$cell_row++;
						}
						
						$rowcol 		= "";
						$celladdress 	= "";
						$col			= 0;
						$cell_col		= 0;
						$row++;
					}
					
					/* check if this is an open or completed element*/
					if($type=='open' || $type=='complete')
					{
						
			 			$xmlStack[$topOfStack++] = $tagi;
			 			$tagi = array('tag' => $val['tag']);
			 			if(isset($val['attributes']))  $tagi['attrs'] = $val['attributes'];
			 			if(isset($val['value']))
			 			{
			   				$tagi['values'][] = $val['value'];
		   				}else{
			   				$tagi['children'] = array();
		   				}
	   					
					}
					/* check if this is a complete or closed element*/
					if($type=='complete' || $type=='close')
					{
			 			$tags[] = $oldtagi = $tagi; 
			 			$tagi = $xmlStack[--$topOfStack];
			 			$oldtag = $oldtagi['tag'];
			 			
			 			unset($oldtagi['tag']);
			 			$tagi['children'][$oldtag][] = $oldtagi;
			 			
			 			$lngth = count($tagi['children'][$oldtag]);
			 			
			 			if(trim($celladdress) != '' && $val['tag']=='Cell')
			 			{
				 			$tagi['children'][$oldtag][$lngth-1]['CellAddress'] = $rowcol;
			 			}
			 			$parent =& $tagi;
					}
					/* check if this data value */
					if($type=='cdata')
					{
			 			$tagi['values'][] = $val['value'];
					}
				}
				/* unset XML Element array */
				unset($XMLElements);
				/* unset element stack */
				unset($xmlStack);
			}
		}
		/* close current file handle */
		@fclose($XMLFileHandle);
		return $parent['children'];
	}
#-----------------------------------------------------------------------------#

	/**
	 *
	 * convert xml array to xml string
	 * @author Andrew A. Aculana
	 * @copyright 2006-07-03
	 * @param Associative Array $xml
	 * @return String
	 */
	function ArrayToXML($xml,$root = true)
	{
		/* xml buffer */
		$temp = "";
		/* iterate excel xml */
		foreach ($xml as $key => $value)
		{
			foreach($value as $kk => $xmlvalue)
			{
				if(isset($xmlvalue["children"]))
				{
					$attrs = "";
					if(isset($xmlvalue["attrs"]))
					{
						foreach($xmlvalue["attrs"] as $attrsKey => $attrsValue)
						{
							if(trim($attrs) == "")
							{
						   		$attrs .= " ".$attrsKey."=\"".htmlentities($attrsValue)."\"";
							}else{
								$attrs .= " ".$attrsKey."=\"".htmlentities($attrsValue)."\"";
							}	
						}
					}
					
					if(is_array($xmlvalue["children"]))
					{
					   $temp .= sprintf("<%s%s>", $key,$attrs);
					   $temp .= $this->ArrayToXML($xmlvalue["children"], false);
					   $temp .= sprintf("</%s>\r\n", $key);
					}
				}elseif(isset($xmlvalue["values"])){
					
					$attrs = "";
					if(isset($xmlvalue["attrs"]))
					{
						foreach($xmlvalue["attrs"] as $attrsKey => $attrsValue)
						{
							if(trim($attrs) == "")
							{
						   		$attrs .= " ".$attrsKey."=\"".htmlentities($attrsValue)."\"";
							}else{
								$attrs .= " ".$attrsKey."=\"".htmlentities($attrsValue)."\"";
							}	
						}
					}
					
					if(is_array($xmlvalue["values"]))
					{
					   $temp .= sprintf("<%s%s>", $key,$attrs);
					   $temp .= htmlentities($xmlvalue["values"][0]);
					   $temp .= sprintf("</%s>", $key);
					}
				}
			}
		}
		if ($root) {
		   $temp = "<?xml version=\"1.0\"?>\r\n".$temp;
		}
		return $temp;
	}
#-----------------------------------------------------------------------------#

	/**
	 * open the workbook
	 *
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyrights 2006-07-03
	 * @param String $XMLFilePath - path to xml file
	 * @param Array(String) $WorksheetName - an array of worksheet name
	 * @return bool
	 */
	function OpenWorkbook($XMLFilePath,$WorksheetNames = array())
	{
		if(trim($XMLFilePath)==""){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: Invalid or File Not found");
			return $Error;			
		}
		
		$this->XMLFileName =& $XMLFilePath;
		$this->data =& $this->XMLToArray($WorksheetNames);
		
		if(ExcelXMLError::isError($this->data)){
			return $this->data;
		}
		
		/* check if parsed some elements */
		if(!count($this->data)){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: No XML Element found");
			return $Error;
		}
		/* handle to Workbook Object */
		$this->Workbook = new ExcelXMLWorkbook(&$this->data,&$this->Encoding);
		return true;
	}
#-----------------------------------------------------------------------------#

	/**
	 * save the workbook and XML file
	 *
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyrights 2006-07-03
	 * @param String $FilePath - path to output file
	 * @return result
	 */
	function SaveWorkbook($FilePath = "",$Download = false){
		/* check if we have a data to save */
		if(count($this->data)){
			$xmldata = $this->ArrayToXML($this->data);
			if(trim($xmldata)==""){
				$Error = new ExcelXMLError();
				$Error->setErrorMessage("Error: Empty XML Data");
				return $Error;
			}
			$result = $this->writeXMLFile($FilePath,$xmldata);
			if($result && $Download){
				$FileInfo = pathinfo($FilePath);
				// fix for IE catching or PHP bug issue 
				header("Pragma: public"); 
				header("Expires: 0"); // set expiration time 
				header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
				// browser must download file from server instead of cache 
				// force download dialog 
				header("Content-Type: application/force-download"); 
				header("Content-Type: application/octet-stream"); 
				header("Content-type: application/x-msexcel");
				header("Content-Type: application/download"); 
				// use the Content-Disposition header to supply a recommended filename and  
				// force the browser to display the save dialog.  
				header("Content-Disposition: attachment; filename=".basename($FileInfo["basename"]).";"); 
				header("Content-Transfer-Encoding: binary"); 
				header("Content-Length: ".filesize($FilePath)); 
				@readfile("$FilePath");
				exit();
			}
			return $result;
		}
	}
#-----------------------------------------------------------------------------#

	/**
	 * save the xml back to file
	 *
	 * @access Private
	 * @author Andrew A. Aculana
	 * @version 2.0
	 * @copyrights 2006-07-03
	 * @param String $FilePath - path to output file
	 * @param String $xmlData - xml data
	 * @return Bool
	 */	
	function writeXMLFile($FilePath = "",$xmlData = "")
	{
		if (!$handle = @fopen($FilePath, 'w+')){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: Failed to create xml file");
			return $Error;
		}
		if (@fwrite($handle, $xmlData) === FALSE){
			$Error = new ExcelXMLError();
			$Error->setErrorMessage("Error: Failed to write xml file");
			return $Error;
		}
		@fclose($handle);
		return true;
	}
}
?>