Source for file ExcelWriterXML_Sheet.php
Documentation is available at ExcelWriterXML_Sheet.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
* @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()
private $cells = array();
private $colWidth = array();
private $rowHeight = array();
private $mergeCells = array();
private $comments = array();
private $displayRightToLeft = false;
* Constructor for a new Sheet
* @param string $id The name of the sheet to be referenced within the
* Function to get the named value of the Sheet
* @return string Name of the Sheet
* 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){
* Returns any errors found in the sheet
* @return mixed Array of errors if they exist, otherwise false
* Converts a MySQL type datetime field to a value that can be used within
* 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"
$datetime = trim($datetime);
$pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
list ($date,$time) = explode(' ',$datetime);
return($date. 'T'. $time. '.000');
* 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"
* @return string Value in the Excel format "yyyy-mm-ddT00:00:00.000"
$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}/";
list ($date,$time) = explode(' ',$datetime);
return($date. 'T'. $time. '.000');
else if (preg_match($pattern2, $datetime, $matches)) {
return($date. 'T00:00:00.000');
* 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"
* @return string Value in the Excel format "1899-12-31Thh:ii:ss.000"
$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}/";
list ($date,$time) = explode(' ',$datetime);
return($date. 'T'. $time. '.000');
else if (preg_match($pattern2, $datetime, $matches)) {
return('1899-12-31T'. $time. '.000');
* Writes a formula to a cell
* 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.
* @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
public function writeFormula($dataType,$row,$column,$data,$style = null){
if ($dataType != 'String'
&& $dataType != 'DateTime'){
$this->addError(__FUNCTION__ ,'('. $row. ','. $column. ') DataType for formula was not valid "'. $dataType. '"');
$this->writeData('String',$row,$column,'',$style,$data);
* Writes a string to a cell
* @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
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
* @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
public function writeNumber($row,$column,$data,$style = null){
$this->writeData('String',$row,$column,$data,$style);
$this->addError(__FUNCTION__ ,'('. $row. ','. $column. ') Tried to write non-numeric data to type Number "'. $data. '"');
$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
* @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
$pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.000/";
$this->writeData('DateTime',$row,$column,$data,$style);
$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 (get_class($style) == 'ExcelWriterXML_Style'){
$styleID = $style->getID();
$this->addError(__FUNCTION__ ,'('. $row. ','. $column. ') StyleID supplied was an object, but not a style object "'. get_class($style). '"');
$this->cells[$row][$column] = $cell;
* Displays the sheet in Right to Left format
$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
$displayRightToLeft = ($this->displayRightToLeft) ? 'ss:RightToLeft="1"' : '';
$xml = '<Worksheet ss:Name="'. $this->id. '" '. $displayRightToLeft. '>'. "\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){
if (isset ($this->rowHeight[$row])){
$rowHeight = 'ss:AutoFitHeight="0" ss:Height="'. $this->rowHeight[$row]. '"';
$xml .= ' <Row ss:Index="'. $row. '" '. $rowHeight. ' >'. "\r";
foreach($rowData as $column => $cell){
if (!empty($cell['formula'])) $formula = 'ss:Formula="'. $cell['formula']. '"';
if (!empty($cell['style'])) $style = 'ss:StyleID="'. $cell['style']. '"';
if (empty($this->URLs[$row][$column])) $URL = '';
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 = '';
$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";
$xml .= ' <Cell '. $style. ' ss:Index="'. $column. '" '. $URL. ' '. $mergeCell. ' '. $formula. '>'. "\r";
$xml .= ' <Data ss:Type="'. $type. '">';
$xml .= ' </Table>'. "\r";
$xml .= '</Worksheet>'. "\r";
* Alias for function columnWidth()
* 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()
* 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. ')"');
$this->mergeCells[$row][$col] = array(
/* 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
function addComment( $row, $col,$comment,$author = 'SYSTEM'){
$this->comments[$row][$col] = array(
* 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
if (empty($host)) $this->addError('Database','HOSTNAME is empty');
if (empty($username)) $this->addError('Database','USERNAME is empty');
|