3 * File contains the class files for ExcelWriterXML_Sheet
4 * @package ExcelWriterXML
8 * Class for generating sheets within the Excel document
9 * @link http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
10 * @author Robert F Greer
12 * @package ExcelWriterXML
13 * @uses ExcelWriterXML_Style::alignHorizontal()
14 * @uses ExcelWriterXML_Style::alignRotate()
15 * @uses ExcelWriterXML_Style::alignShrinktofit()
16 * @uses ExcelWriterXML_Style::alignVertical()
17 * @uses ExcelWriterXML_Style::alignVerticaltext()
18 * @uses ExcelWriterXML_Style::alignWraptext()
19 * @uses ExcelWriterXML_Style::bgColor()
20 * @uses ExcelWriterXML_Style::bgPattern()
21 * @uses ExcelWriterXML_Style::bgPatternColor()
22 * @uses ExcelWriterXML_Style::border()
23 * @uses ExcelWriterXML_Style::checkColor()
24 * @uses ExcelWriterXML_Style::fontBold()
25 * @uses ExcelWriterXML_Style::fontColor()
26 * @uses ExcelWriterXML_Style::fontFamily()
27 * @uses ExcelWriterXML_Style::fontItalic()
28 * @uses ExcelWriterXML_Style::fontName()
29 * @uses ExcelWriterXML_Style::fontOutline()
30 * @uses ExcelWriterXML_Style::fontShadow()
31 * @uses ExcelWriterXML_Style::fontStrikethrough()
32 * @uses ExcelWriterXML_Style::fontUnderline()
33 * @uses ExcelWriterXML_Style::getErrors()
34 * @uses ExcelWriterXML_Style::getID()
35 * @uses ExcelWriterXML_Style::getStyleXML()
36 * @uses ExcelWriterXML_Style::name()
37 * @uses ExcelWriterXML_Style::numberFormat()
38 * @uses ExcelWriterXML_Style::numberFormatDate()
39 * @uses ExcelWriterXML_Style::numberFormatDatetime()
40 * @uses ExcelWriterXML_Style::numberFormatTime()
42 class ExcelWriterXML_Sheet
47 var $colWidth = array();
48 var $rowHeight = array();
50 var $mergeCells = array();
51 var $comments = array();
52 var $formatErrors = array();
53 var $displayRightToLeft = false;
61 * Constructor for a new Sheet
62 * @param string $id The name of the sheet to be referenced within the
65 function ExcelWriterXML_Sheet($id)
71 * Function to get the named value of the Sheet
72 * @return string Name of the Sheet
80 * Adds a format error. When the document is generated if there are any
81 * errors they will be listed on a seperate sheet.
82 * @param string $function The name of the function that was called
83 * @param string $message Details of the error
85 function _addError($function, $message)
89 'function' => $function,
90 'message' => $message,
92 $this->formatErrors[] = $tmp;
96 * Returns any errors found in the sheet
97 * @return mixed Array of errors if they exist, otherwise false
101 return($this->formatErrors);
105 * Converts a MySQL type datetime field to a value that can be used within
107 * If the passed value is not valid then the passed string is sent back.
108 * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
109 * @return string Value in the Excel format "yyyy-mm-ddThh:ii:ss.000"
111 function convertMysqlDatetime($datetime)
113 $datetime = trim($datetime);
114 $pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
115 if (preg_match($pattern, $datetime, $matches))
117 $datetime = $matches[0];
118 list($date,$time) = explode(' ',$datetime);
119 return($date.'T'.$time.'.000');
128 * Converts a MySQL type date field to a value that can be used within Excel
129 * If the passed value is not valid then the passed string is sent back.
130 * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
132 * @return string Value in the Excel format "yyyy-mm-ddT00:00:00.000"
134 function convertMysqlDate($datetime)
136 $datetime = trim($datetime);
137 $pattern1 = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
138 $pattern2 = "/[0-9]{4}-[0-9]{2}-[0-9]{2}/";
139 if (preg_match($pattern1, $datetime, $matches))
141 $datetime = $matches[0];
142 list($date,$time) = explode(' ',$datetime);
143 return($date.'T'.$time.'.000');
145 elseif (preg_match($pattern2, $datetime, $matches))
148 return($date.'T00:00:00.000');
157 * Converts a MySQL type time field to a value that can be used within Excel
158 * If the passed value is not valid then the passed string is sent back.
159 * @param string $datetime Value must in in the format "yyyy-mm-dd hh:ii:ss"
161 * @return string Value in the Excel format "1899-12-31Thh:ii:ss.000"
163 function convertMysqlTime($datetime)
165 $datetime = trim($datetime);
166 $pattern1 = "/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}/";
167 $pattern2 = "/[0-9]{2}:[0-9]{2}:[0-9]{2}/";
168 if (preg_match($pattern1, $datetime, $matches))
170 $datetime = $matches[0];
171 list($date,$time) = explode(' ',$datetime);
172 return($date.'T'.$time.'.000');
174 elseif (preg_match($pattern2, $datetime, $matches))
177 return('1899-12-31T'.$time.'.000');
187 * Writes a formula to a cell
189 * Specifies the formula stored in this cell. All formulas are persisted in
190 * R1C1 notation because they are significantly easier to parse and generate
191 * than A1-style formulas. The formula is calculated upon reload unless
192 * calculation is set to manual. Recalculation of the formula overrides the
193 * value in this cell's Value attribute.
194 * @see writeFormula()
195 * @param string $dataType Type of data that the formula should generate,
196 * "String" "Number" "DateTime"
197 * @param integer $row Row, based upon a "1" based array
198 * @param integer $column Column, based upon a "1" based array
199 * @param string $data Formula data to be written to a cell
200 * @param mixed $style Named style, or style reference to be applied to the
203 function writeFormula($dataType,$row,$column,$data,$style = null)
205 if ($dataType != 'String'
206 && $dataType != 'Number'
207 && $dataType != 'DateTime')
209 $this->_addError(__FUNCTION__,'('.$row.','.$column.') DataType for formula was not valid "'.$dataType.'"');
210 $halign = 'Automatic';
213 $this->_writeData('String',$row,$column,'',$style,$data);
217 * Writes a string to a cell
219 * @param integer $row Row, based upon a "1" based array
220 * @param integer $column Column, based upon a "1" based array
221 * @param string $data String data to be written to a cell
222 * @param mixed $style Named style, or style reference to be applied to the
225 function writeString($row,$column,$data,$style = null)
227 $this->_writeData('String',$row,$column,$data,$style);
231 * Writes a number to a cell.
232 * If the data is not numeric then the function will write the data as a
235 * @param integer $row Row, based upon a "1" based array
236 * @param integer $column Column, based upon a "1" based array
237 * @param mixed $data Number data to be written to a cell
238 * @param mixed $style Named style, or style reference to be applied to the
241 function writeNumber($row,$column,$data,$style = null)
243 if (!is_numeric($data))
245 $this->_writeData('String',$row,$column,$data,$style);
246 $this->_addError(__FUNCTION__,'('.$row.','.$column.') Tried to write non-numeric data to type Number "'.$data.'"');
250 $this->_writeData('Number',$row,$column,$data,$style);
255 * Writes a Date/Time to a cell.
256 * If data is not valid the function will write the passed value as a
259 * @param integer $row Row, based upon a "1" based array
260 * @param integer $column Column, based upon a "1" based array
261 * @param string $data Date or Time data to be written to a cell. This must
262 * be in the format "yyyy-mm-ddThh:ii:ss.000" for Excel to recognize it.
263 * @param mixed $style Named style, or style reference to be applied to the
266 function writeDateTime($row,$column,$data,$style = null)
268 $pattern = "/[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.000/";
269 if (preg_match($pattern, $data, $matches))
272 $this->_writeData('DateTime',$row,$column,$data,$style);
276 $this->_writeData('String',$row,$column,$data,$style);
277 $this->_addError(__FUNCTION__,'('.$row.','.$column.') Tried to write invalid datetime data to type DateTime "'.$data.'"');
280 function _writeData($type,$row,$column,$data,$style = null,$formula = null)
284 /* if (gettype($style) == 'object')
286 if (get_class($style) == 'ExcelWriterXML_Style')
288 $styleID = $style->getID();
292 $this->_addError(__FUNCTION__,'('.$row.','.$column.') StyleID supplied was an object, but not a style object "'.get_class($style).'"');
310 'formula' => $formula,
312 $this->cells[$row][$column] = $cell;
316 * Displays the sheet in Right to Left format
318 function displayRightToLeft()
320 $this->displayRightToLeft = true;
324 * Called by the ExcelWriterXML class to get the XML data for this object
325 * @return string Contains only the XML data for the sheet
327 function getSheetXML()
330 $displayRightToLeft = ($this->displayRightToLeft) ? 'ss:RightToLeft="1"' : '';
332 $xml = '<Worksheet ss:Name="'.$this->id.'" '.$displayRightToLeft.'>'."\r";
333 $xml .= ' <Table>'."\r";
334 foreach($this->colWidth as $colIndex => $colWidth)
336 $xml .= ' <Column ss:Index="'.$colIndex.'" ss:AutoFitWidth="0" ss:Width="'.$colWidth.'"/>'."\r";
338 foreach($this->cells as $row => $rowData)
341 if (isset($this->rowHeight[$row]))
343 $rowHeight = 'ss:AutoFitHeight="0" ss:Height="'.$this->rowHeight[$row].'"';
349 $xml .= ' <Row ss:Index="'.$row.'" '.$rowHeight.' >'."\r";
350 foreach($rowData as $column => $cell)
352 if (!empty($cell['formula']))
353 $formula = 'ss:Formula="'.$cell['formula'].'"';
356 if (!empty($cell['style']))
357 $style = 'ss:StyleID="'.$cell['style'].'"';
360 if (empty($this->URLs[$row][$column]))
363 $URL = 'ss:HRef="'.htmlspecialchars($this->URLs[$row][$column]).'"';
364 if (empty($this->mergeCells[$row][$column]))
367 $mergeCell = 'ss:MergeAcross="'.$this->mergeCells[$row][$column]['width'].'" ss:MergeDown="'.$this->mergeCells[$row][$column]['height'].'"';
368 if (empty($this->comments[$row][$column]))
372 $comment = ' <Comment ss:Author="'.$this->comments[$row][$column]['author'].'">'."\r";
373 $comment .= ' <ss:Data xmlns="http://www.w3.org/TR/REC-html40">'."\r";
374 $comment .= ' <B><Font html:Face="Tahoma" x:CharSet="1" html:Size="8" html:Color="#000000">'.htmlspecialchars($this->comments[$row][$column]['author']).':</Font></B>'."\r";
375 $comment .= ' <Font html:Face="Tahoma" x:CharSet="1" html:Size="8" html:Color="#000000">'.htmlspecialchars($this->comments[$row][$column]['comment']).'</Font>'."\r";
376 $comment .= ' </ss:Data>'."\r";
377 $comment .= ' </Comment>'."\r";
379 $type = $cell['type'];
380 $data = $cell['data'];
382 $xml .= ' <Cell '.$style.' ss:Index="'.$column.'" '.$URL.' '.$mergeCell.' '.$formula.'>'."\r";
383 $xml .= ' <Data ss:Type="'.$type.'">';
384 $xml .= htmlspecialchars($data);
385 $xml .= '</Data>'."\r";
387 $xml .= ' </Cell>'."\r";
389 $xml .= ' </Row>'."\r";
391 $xml .= ' </Table>'."\r";
392 $xml .= '</Worksheet>'."\r";
397 * Alias for function columnWidth()
399 function cellWidth( $row, $col,$width = 48) { $this->columnWidth($col,$width); }
402 * Sets the width of a cell.
403 * Sets the width of the column that the cell resides in.
404 * Cell width of zero effectively hides the column
405 * @param integer $row Row, based upon a "1" based array
406 * @param integer $col Column, based upon a "1" based array
407 * @param mixed $width Width of the cell/column, default is 48
409 function columnWidth( $col,$width = 48) { $this->colWidth[$col] = $width; }
412 * Alias for function rowHeight()
414 function cellHeight( $row, $col,$height = 12.5) { $this->rowHeight($row,$height); }
417 * Sets the height of a cell.
418 * Sets the height of the column that the cell resides in.
419 * Cell height of zero effectively hides the row
420 * @param integer $row Row, based upon a "1" based array
421 * @param integer $col Column, based upon a "1" based array
422 * @param mixed $height Height of the cell/column, default is 12.5
424 function rowHeight( $row,$height = 12.5) { $this->rowHeight[$row] = $height; }
427 * Makes the target cell a link to a URL
428 * @param integer $row Row, based upon a "1" based array
429 * @param integer $col Column, based upon a "1" based array
430 * @param string $URL The URL that the link should point to
432 function addURL( $row, $col,$URL) { $this->URLs[$row][$col] = $URL; }
435 * Merges 2 or more cells.
436 * The function acts like a bounding box, with the row and column defining
437 * the upper left corner, and the width and height extending the box.
438 * If width or height are zero (or ommitted) then the function does nothing.
439 * @param integer $row Row, based upon a "1" based array
440 * @param integer $col Column, based upon a "1" based array
441 * @param integer $width Number of cells to the right to merge with
442 * @param integer $height Number of cells down to merge with
444 function cellMerge($row,$col, $width = 0, $height = 0)
446 if ($width < 0 || $height < 0)
448 $this->_addError(__FUNCTION__,'('.$row.','.$col.') Tried to merge cells with width/height < 0 "(w='.$width.',h='.$height.')"');
452 $this->mergeCells[$row][$col] = array(
456 /* I don't think this code is necessary
457 if (!isset($cells[$row][$col]))
459 $this->writeString($row,$col,'');
465 * Adds a comment to a cell
466 * @param integer $row Row, based upon a "1" based array
467 * @param integer $col Column, based upon a "1" based array
468 * @param string $comment The comment to be displayed on the cell
469 * @param string $author The comment will show a bold header displaying the
472 function addComment( $row, $col,$comment,$author = 'SYSTEM')
474 $this->comments[$row][$col] = array(
475 'comment' => $comment,