Gecko [ nrupathungauniversityblr.ac.in ]

Name	Size	Permission
Documentation	[ DIR ]	drwxr-xr-x
ExcelWriterXML.php	16.29 KB	-rwxr-xr-x
ExcelWriterXML_Sheet.php	15.83 KB	-rwxr-xr-x
ExcelWriterXML_Style.php	23.71 KB	-rwxr-xr-x
example.php	562 B	-rwxr-xr-x
example2.php	2.95 KB	-rwxr-xr-x
example3_db.php	980 B	-rwxr-xr-x

Code Editor : ExcelWriterXML_Sheet.php

<?php
/**
 * File contains the class files for ExcelWriterXML_Sheet
 * @package ExcelWriterXML
 */

/**
 * Class for generating sheets within the Excel document
 * @link http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
 * @author Robert F Greer
 * @version 1.0
 * @package ExcelWriterXML
 * @uses ExcelWriterXML_Style::alignHorizontal()
 * @uses ExcelWriterXML_Style::alignRotate()
 * @uses ExcelWriterXML_Style::alignShrinktofit()
 * @uses ExcelWriterXML_Style::alignVertical()
 * @uses ExcelWriterXML_Style::alignVerticaltext()
 * @uses ExcelWriterXML_Style::alignWraptext()
 * @uses ExcelWriterXML_Style::bgColor()
 * @uses ExcelWriterXML_Style::bgPattern()
 * @uses ExcelWriterXML_Style::bgPatternColor()
 * @uses ExcelWriterXML_Style::border()
 * @uses ExcelWriterXML_Style::checkColor()
 * @uses ExcelWriterXML_Style::fontBold()
 * @uses ExcelWriterXML_Style::fontColor()
 * @uses ExcelWriterXML_Style::fontFamily()
 * @uses ExcelWriterXML_Style::fontItalic()
 * @uses ExcelWriterXML_Style::fontName()
 * @uses ExcelWriterXML_Style::fontOutline()
 * @uses ExcelWriterXML_Style::fontShadow()
 * @uses ExcelWriterXML_Style::fontStrikethrough()
 * @uses ExcelWriterXML_Style::fontUnderline()
 * @uses ExcelWriterXML_Style::getErrors()
 * @uses ExcelWriterXML_Style::getID()
 * @uses ExcelWriterXML_Style::getStyleXML()
 * @uses ExcelWriterXML_Style::name()
 * @uses ExcelWriterXML_Style::numberFormat()
 * @uses ExcelWriterXML_Style::numberFormatDate()
 * @uses ExcelWriterXML_Style::numberFormatDatetime()
 * @uses ExcelWriterXML_Style::numberFormatTime()
 */
class ExcelWriterXML_Sheet {
	// Private Variables
	private $id;
	private $cells = array();
	private $colWidth = array();
	private $rowHeight = array();
	private $URLs = array();
	private $mergeCells = array();
	private $comments = array();
	public  $formatErrors = array();
	private $displayRightToLeft = false;
	/////////////////////
	
	// Public Variables
	/////////////////////
	
	// Constructor
	/**
     * Constructor for a new Sheet
     * @param string $id The name of the sheet to be referenced within the
     * spreadsheet
     */
	function ExcelWriterXML_Sheet($id){
		$this->id = $id;
	}
	
	/**
	 * Function to get the named value of the Sheet
	 * @return string Name of the Sheet
	 */
	function getID(){
		return $this->id;
	}
	
	/**
	 * Adds a format error.  When the document is generated if there are any
	 * errors they will be listed on a seperate sheet.
@param string $function The name of the function that was called
	 * @param string $message Details of the error
	 */
	public function addError($function, $message){
		$tmp = array(
			'sheet'		=> $this->id,
			'function'	=> $function,
			'message'	=> $message,
		);
		$this->formatErrors[] = $tmp;
	}
	
	/**
	 * Returns any errors found in the sheet
	 * @return mixed Array of errors if they exist, otherwise false
	 */
	public function getErrors(){
		return($this->formatErrors);
	}

/**
     * Converts a MySQL type datetime field to a value that can be used within
     * Excel.
     * If the passed value is not valid then the passed string is sent back.
     * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
     * @return string Value in the Excel format "yyyy-mm-ddThh:ii:ss.000"
     */
	public function convertMysqlDatetime($datetime){
		$datetime = trim($datetime);
		$pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
		if (preg_match($pattern, $datetime, $matches)) {
			$datetime = $matches[0];
			list($date,$time) = explode(' ',$datetime);
			return($date.'T'.$time.'.000');
		}
		else{
			return($datetime);
		}
	}
	
	/**
     * Converts a MySQL type date field to a value that can be used within Excel
     * If the passed value is not valid then the passed string is sent back.
     * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
     * or "yyyy-mm-dd"
     * @return string Value in the Excel format "yyyy-mm-ddT00:00:00.000"
     */
	public function convertMysqlDate($datetime){
		$datetime = trim($datetime);
		$pattern1 = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
		$pattern2 = "/[0-9]{4}-[0-9]{2}-[0-9]{2}/";
		if (preg_match($pattern1, $datetime, $matches)) {
			$datetime = $matches[0];
			list($date,$time) = explode(' ',$datetime);
			return($date.'T'.$time.'.000');
		}
		else if (preg_match($pattern2, $datetime, $matches)) {
			$date = $matches[0];
			return($date.'T00:00:00.000');
		}
		else{
			return($datetime);
		}
	}
	
	/**
     * Converts a MySQL type time field to a value that can be used within Excel
     * If the passed value is not valid then the passed string is sent back.
     * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
     * or "hh:ii:ss"
     * @return string Value in the Excel format "1899-12-31Thh:ii:ss.000"
     */
	public function convertMysqlTime($datetime){
		$datetime = trim($datetime);
		$pattern1 = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
		$pattern2 = "/[0-9]{2}:[0-9]{2}:[0-9]{2}/";
		if (preg_match($pattern1, $datetime, $matches)) {
			$datetime = $matches[0];
			list($date,$time) = explode(' ',$datetime);
			return($date.'T'.$time.'.000');
		}
		else if (preg_match($pattern2, $datetime, $matches)) {
			$time = $matches[0];
			return('1899-12-31T'.$time.'.000');
		}
		else{
			return($datetime);
		}
	}

/**
     * Writes a formula to a cell
     * From MS
     * Specifies the formula stored in this cell. All formulas are persisted in
     * R1C1 notation because they are significantly easier to parse and generate
     * than A1-style formulas. The formula is calculated upon reload unless
     * calculation is set to manual. Recalculation of the formula overrides the
     * value in this cell's Value attribute.
     * @see writeFormula()
     * @param string $dataType Type of data that the formula should generate,
     * "String" "Number" "DateTime"
     * @param integer $row Row, based upon a "1" based array
     * @param integer $column Column, based upon a "1" based array
     * @param string $data Formula data to be written to a cell
     * @param mixed $style Named style, or style reference to be applied to the
     * cell
     */
	public function writeFormula($dataType,$row,$column,$data,$style = null){
		if ($dataType != 'String'
			&& $dataType != 'Number'
			&& $dataType != 'DateTime'){
			$this->addError(__FUNCTION__,'('.$row.','.$column.') DataType for formula was not valid "'.$dataType.'"');
			$halign = 'Automatic';
		}

$this->writeData('String',$row,$column,'',$style,$data);
	}

/**
     * Writes a string to a cell
     * @see writeData()
     * @param integer $row Row, based upon a "1" based array
     * @param integer $column Column, based upon a "1" based array
     * @param string $data String data to be written to a cell
     * @param mixed $style Named style, or style reference to be applied to the
     * cell
     */
	public function writeString($row,$column,$data,$style = null){
		$this->writeData('String',$row,$column,$data,$style);
	}
	
	/**
     * Writes a number to a cell.
     * If the data is not numeric then the function will write the data as a
     * string.
     * @see writeData()
     * @param integer $row Row, based upon a "1" based array
     * @param integer $column Column, based upon a "1" based array
     * @param mixed $data Number data to be written to a cell
     * @param mixed $style Named style, or style reference to be applied to the
     * cell
     */
	public function writeNumber($row,$column,$data,$style = null){
		if (!is_numeric($data)){
			$this->writeData('String',$row,$column,$data,$style);
			$this->addError(__FUNCTION__,'('.$row.','.$column.') Tried to write non-numeric data to type Number "'.$data.'"');
		}
		else{
			$this->writeData('Number',$row,$column,$data,$style);
		}
	}
	
	/**
     * Writes a Date/Time to a cell.
     * If data is not valid the function will write the passed value as a
     * string.
     * @see writeData()
     * @param integer $row Row, based upon a "1" based array
     * @param integer $column Column, based upon a "1" based array
     * @param string $data Date or Time data to be written to a cell.  This must
     * be in the format "yyyy-mm-ddThh:ii:ss.000" for Excel to recognize it.
     * @param mixed $style Named style, or style reference to be applied to the
     * cell
     */
	public function writeDateTime($row,$column,$data,$style = null){
		$pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.000/";
		if (preg_match($pattern, $data, $matches)) {
			$data = $matches[0];
			$this->writeData('DateTime',$row,$column,$data,$style);
		}
		else{
			$this->writeData('String',$row,$column,$data,$style);
			$this->addError(__FUNCTION__,'('.$row.','.$column.') Tried to write invalid datetime data to type DateTime "'.$data.'"');
		}
	}
	private function writeData($type,$row,$column,$data,$style = null,$formula = null){
		if ($style != null){
			if (gettype($style) == 'object'){
				if (get_class($style) == 'ExcelWriterXML_Style'){
					$styleID = $style->getID();
				}
				else{
					$this->addError(__FUNCTION__,'('.$row.','.$column.') StyleID supplied was an object, but not a style object "'.get_class($style).'"');
					$styleID = null;
				}
			}
			else{
				$styleID = $style;
			}
		}
		else{
			$styleID = null;
		}
		
		$cell = array(
			'type'		=> $type,
			'style'		=> $styleID,
			'data'		=> $data,
			'formula'	=> $formula,
		);
		$this->cells[$row][$column] = $cell;
	}
	
	/**
	 * Displays the sheet in Right to Left format
	 */
	public function displayRightToLeft(){
		$this->displayRightToLeft = true;
	}

/**
     * Called by the ExcelWriterXML class to get the XML data for this object
     * @return string Contains only the XML data for the sheet
     */
	public function getSheetXML(){
		ksort($this->cells);
		
		$displayRightToLeft = ($this->displayRightToLeft) ? 'ss:RightToLeft="1"' : '';
		
		$xml = '<Worksheet ss:Name="'.$this->id.'" '.$displayRightToLeft.'>'."\r";
		$xml .= '	<Table>'."\r";
		foreach($this->colWidth as $colIndex => $colWidth){
			  $xml .= '		<Column ss:Index="'.$colIndex.'" ss:AutoFitWidth="0" ss:Width="'.$colWidth.'"/>'."\r";
		}
		foreach($this->cells as $row => $rowData){
			ksort($rowData);
			if (isset($this->rowHeight[$row])){
				$rowHeight = 'ss:AutoFitHeight="0" ss:Height="'.$this->rowHeight[$row].'"';
			}
			else{$rowHeight = '';}
			$xml .= '		<Row ss:Index="'.$row.'" '.$rowHeight.' >'."\r";
			foreach($rowData as $column => $cell){
				if (!empty($cell['formula'])) $formula = 'ss:Formula="'.$cell['formula'].'"';
				else $formula = '';
				if (!empty($cell['style'])) $style = 'ss:StyleID="'.$cell['style'].'"';
				else $style = '';
				if (empty($this->URLs[$row][$column])) $URL = '';
				else $URL = 'ss:HRef="'.htmlspecialchars($this->URLs[$row][$column]).'"';
				if (empty($this->mergeCells[$row][$column])) $mergeCell = '';
				else $mergeCell = 'ss:MergeAcross="'.$this->mergeCells[$row][$column]['width'].'" ss:MergeDown="'.$this->mergeCells[$row][$column]['height'].'"';
				if (empty($this->comments[$row][$column])) $comment = '';
				else{
					$comment = '					<Comment ss:Author="'.$this->comments[$row][$column]['author'].'">'."\r";
					$comment .= '					<ss:Data xmlns="http://www.w3.org/TR/REC-html40">'."\r";
					$comment .= '					<B><Font html:Face="Tahoma" x:CharSet="1" html:Size="8" html:Color="#000000">'.htmlspecialchars($this->comments[$row][$column]['author']).':</Font></B>'."\r";
					$comment .= '					<Font html:Face="Tahoma" x:CharSet="1" html:Size="8" html:Color="#000000">'.htmlspecialchars($this->comments[$row][$column]['comment']).'</Font>'."\r";
					$comment .= '					</ss:Data>'."\r";
					$comment .= '					</Comment>'."\r";
				}
				$type = $cell['type'];
				$data = $cell['data'];
				$data = htmlspecialchars($data);
				$data = str_replace("\r\n",'&#10;',$data);
				$data = str_replace("\n",'&#10;',$data);
				
				$xml .= '			<Cell '.$style.' ss:Index="'.$column.'" '.$URL.' '.$mergeCell.' '.$formula.'>'."\r";
				$xml .= '				<Data ss:Type="'.$type.'">';
				$xml .= $data;
				$xml .= '</Data>'."\r";
				$xml .= str_replace('&amp;#10;','&#10;',$comment);
				$xml .= '			</Cell>'."\r";
			}
			$xml .= '		</Row>'."\r";
		}
		$xml .= '	</Table>'."\r";
		$xml .= '</Worksheet>'."\r";
		return($xml);
	}

/**
     * Alias for function columnWidth()
     */
	function cellWidth( $row, $col,$width = 48){$this->columnWidth($col,$width);}

/**
     * Sets the width of a cell.
     * Sets  the width of the column that the cell resides in.
     * Cell width of zero effectively hides the column
     * @param integer $col Column, based upon a "1" based array
     * @param mixed $width Width of the cell/column, default is 48
     */
	function columnWidth( $col,$width = 48){$this->colWidth[$col] = $width;}

/**
     * Alias for function rowHeight()
     */
	function cellHeight( $row, $col,$height = 12.5){$this->rowHeight($row,$height);}

/**
     * Sets the height of a cell.
     * Sets  the height of the column that the cell resides in.
     * Cell height of zero effectively hides the row
     * @param integer $row Row, based upon a "1" based array
     * @param integer $col Column, based upon a "1" based array
     * @param mixed $height Height of the cell/column, default is 12.5
     */
	function rowHeight( $row,$height = 12.5){$this->rowHeight[$row] = $height;}

/**
     * Makes the target cell a link to a URL
     * @param integer $row Row, based upon a "1" based array
     * @param integer $col Column, based upon a "1" based array
     * @param string $URL The URL that the link should point to
     */
	function addURL( $row, $col,$URL){$this->URLs[$row][$col] = $URL;}
	
	/**
     * Merges 2 or more cells.
     * The function acts like a bounding box, with the row and column defining
     * the upper left corner, and the width and height extending the box.
     * If width or height are zero (or ommitted) then the function does nothing.
     * @param integer $row Row, based upon a "1" based array
     * @param integer $col Column, based upon a "1" based array
     * @param integer $width Number of cells to the right to merge with
     * @param integer $height Number of cells down to merge with
     */
	function cellMerge($row,$col, $width = 0, $height = 0){
		if ($width < 0 || $height < 0){
			$this->addError(__FUNCTION__,'('.$row.','.$col.') Tried to merge cells with width/height < 0 "(w='.$width.',h='.$height.')"');
			return;
		}
		
		$this->mergeCells[$row][$col] = array(
			'width'		=> $width,
			'height'	=> $height,
		);
		/* I don't think this code is necessary
		if (!isset($cells[$row][$col])){
			$this->writeString($row,$col,'');
		}
		*/
	}
	
	/**
     * Adds a comment to a cell
     * @param integer $row Row, based upon a "1" based array
     * @param integer $col Column, based upon a "1" based array
     * @param string $comment The comment to be displayed on the cell
     * @param string $author The comment will show a bold header displaying the
     * author
     */
	function addComment( $row, $col,$comment,$author = 'SYSTEM'){
		$this->comments[$row][$col] = array(
			'comment'	=> $comment,
			'author'	=> $author,
		);
	}
	
	/**
     * Outputs a MYSQL table or list of tables to an Excel doc
     * @param string $host MySQL host to connect to
     * @param string $username Username to connect with
     * @param string $password Password to connect with
     * @param string $db Database to use
     * @param mixed $table If string, out specific table.  If array, each table will have it's own sheet
     */
	public function mysqlQueryToTable($host,$username,$password,$query){
		EWXcheckDriverDB('mysql');
		if (empty($host))		$this->addError('Database','HOSTNAME is empty');
		if (empty($username))	$this->addError('Database','USERNAME is empty');
		if (count($this->formatErrors) > 0){return false;}
		
		$link = mysql_connect($host,$username,$password);
		if (!$link) $this->addError('Database','UNABLE to connect to '.$host.'('.mysql_error().')');
		if (count($this->formatErrors) > 0){return false;}
		
		EWXmysqlGenerateByQuery($this,$link,$query);
	}
}
?>

${this.title}

Code Editor : ExcelWriterXML_Sheet.php