3 * File contains the class files for ExcelWriterXML_Style
4 * @package ExcelWriterXML
8 * Style class for generating Excel styles
9 * @link http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
10 * @author Robert F Greer
12 * @package ExcelWriterXML
14 class ExcelWriterXML_Style
21 var $useAlignment = false;
23 var $useBorder = false;
24 var $useInterior = false;
31 var $verticaltext = 0;
35 var $fontColor = 'Automatic';
48 var $borderTop = array();
49 var $borderBottom = array();
50 var $borderLeft = array();
51 var $borderRight = array();
52 var $borderDL = array();
53 var $borderDR = array();
59 var $interiorPatternColor;
67 var $formatErrors = array();
68 var $namedColorsIE = array (
69 'aliceblue' => '#F0F8FF',
70 'antiquewhite' => '#FAEBD7',
72 'aquamarine' => '#7FFFD4',
75 'bisque' => '#FFE4C4',
77 'blanchedalmond' => '#FFEBCD',
79 'blueviolet' => '#8A2BE2',
81 'burlywood' => '#DEB887',
82 'cadetblue' => '#5F9EA0',
83 'chartreuse' => '#7FFF00',
84 'chocolate' => '#D2691E',
86 'cornflowerblue' => '#6495ED',
87 'cornsilk' => '#FFF8DC',
88 'crimson' => '#DC143C',
90 'darkblue' => '#00008B',
91 'darkcyan' => '#008B8B',
92 'darkgoldenrod' => '#B8860B',
93 'darkgray' => '#A9A9A9',
94 'darkgreen' => '#006400',
95 'darkkhaki' => '#BDB76B',
96 'darkmagenta' => '#8B008B',
97 'darkolivegreen' => '#556B2F',
98 'darkorange' => '#FF8C00',
99 'darkorchid' => '#9932CC',
100 'darkred' => '#8B0000',
101 'darksalmon' => '#E9967A',
102 'darkseagreen' => '#8FBC8F',
103 'darkslateblue' => '#483D8B',
104 'darkslategray' => '#2F4F4F',
105 'darkturquoise' => '#00CED1',
106 'darkviolet' => '#9400D3',
107 'deeppink' => '#FF1493',
108 'deepskyblue' => '#00BFFF',
109 'dimgray' => '#696969',
110 'dodgerblue' => '#1E90FF',
111 'firebrick' => '#B22222',
112 'floralwhite' => '#FFFAF0',
113 'forestgreen' => '#228B22',
114 'fuchsia' => '#FF00FF',
115 'gainsboro' => '#DCDCDC',
116 'ghostwhite' => '#F8F8FF',
118 'goldenrod' => '#DAA520',
120 'green' => '#008000',
121 'greenyellow' => '#ADFF2F',
122 'honeydew' => '#F0FFF0',
123 'hotpink' => '#FF69B4',
124 'indianred' => '#CD5C5C',
125 'indigo' => '#4B0082',
126 'ivory' => '#FFFFF0',
127 'khaki' => '#F0E68C',
128 'lavender' => '#E6E6FA',
129 'lavenderblush' => '#FFF0F5',
130 'lawngreen' => '#7CFC00',
131 'lemonchiffon' => '#FFFACD',
132 'lightblue' => '#ADD8E6',
133 'lightcoral' => '#F08080',
134 'lightcyan' => '#E0FFFF',
135 'lightgoldenrodyellow' => '#FAFAD2',
136 'lightgreen' => '#90EE90',
137 'lightgrey' => '#D3D3D3',
138 'lightpink' => '#FFB6C1',
139 'lightsalmon' => '#FFA07A',
140 'lightseagreen' => '#20B2AA',
141 'lightskyblue' => '#87CEFA',
142 'lightslategray' => '#778899',
143 'lightsteelblue' => '#B0C4DE',
144 'lightyellow' => '#FFFFE0',
146 'limegreen' => '#32CD32',
147 'linen' => '#FAF0E6',
148 'magenta' => '#FF00FF',
149 'maroon' => '#800000',
150 'mediumaquamarine' => '#66CDAA',
151 'mediumblue' => '#0000CD',
152 'mediumorchid' => '#BA55D3',
153 'mediumpurple' => '#9370DB',
154 'mediumseagreen' => '#3CB371',
155 'mediumslateblue' => '#7B68EE',
156 'mediumspringgreen' => '#00FA9A',
157 'mediumturquoise' => '#48D1CC',
158 'mediumvioletred' => '#C71585',
159 'midnightblue' => '#191970',
160 'mintcream' => '#F5FFFA',
161 'mistyrose' => '#FFE4E1',
162 'moccasin' => '#FFE4B5',
163 'navajowhite' => '#FFDEAD',
165 'oldlace' => '#FDF5E6',
166 'olive' => '#808000',
167 'olivedrab' => '#6B8E23',
168 'orange' => '#FFA500',
169 'orangered' => '#FF4500',
170 'orchid' => '#DA70D6',
171 'palegoldenrod' => '#EEE8AA',
172 'palegreen' => '#98FB98',
173 'paleturquoise' => '#AFEEEE',
174 'palevioletred' => '#DB7093',
175 'papayawhip' => '#FFEFD5',
176 'peachpuff' => '#FFDAB9',
180 'powderblue' => '#B0E0E6',
181 'purple' => '#800080',
183 'rosybrown' => '#BC8F8F',
184 'royalblue' => '#4169E1',
185 'saddlebrown' => '#8B4513',
186 'salmon' => '#FA8072',
187 'sandybrown' => '#F4A460',
188 'seagreen' => '#2E8B57',
189 'seashell' => '#FFF5EE',
190 'sienna' => '#A0522D',
191 'silver' => '#C0C0C0',
192 'skyblue' => '#87CEEB',
193 'slateblue' => '#6A5ACD',
194 'slategray' => '#708090',
196 'springgreen' => '#00FF7F',
197 'steelblue' => '#4682B4',
200 'thistle' => '#D8BFD8',
201 'tomato' => '#FF6347',
202 'turquoise' => '#40E0D0',
203 'violet' => '#EE82EE',
204 'wheat' => '#F5DEB3',
205 'white' => '#FFFFFF',
206 'whitesmoke' => '#F5F5F5',
207 'yellow' => '#FFFF00',
208 'yellowgreen' => '#9ACD32',
210 /////////////////////
213 /////////////////////
218 * Constructor for a style
219 * @param string $id The named style referenced by Excel. This is called by
220 * ExcelWriterXML object when adding a style
222 function ExcelWriterXML_Style($id)
226 /////////////////////
229 * Returns the named style for this style
230 * @return string $id The id for this style
238 * Retrieves the XML string data for a style.
239 * Called by ExcelWriterXML object
240 * @return string Returns the formatted XML data <style>...</style>
242 function getStyleXML()
266 $interiorPattern = '';
267 $interiorPatternColor = '';
271 //if (empty($this->id)) //***
272 // throw new exception;
273 if (!empty($this->name)){$name = 'ss:Name="'.$this->name.'"';}
276 if ($this->useAlignment)
278 if (!empty($this->valign)) {$valign = 'ss:Vertical="'.$this->valign.'"';}
279 if (!empty($this->halign)) {$halign = 'ss:Horizontal="'.$this->halign.'"';}
280 if (!empty($this->rotate)) {$rotate = 'ss:Rotate="'.$this->rotate.'"';}
281 if (!empty($this->shinktofit)) {$shrinktofit = 'ss:ShrinkToFit="1"';}
282 if (!empty($this->verticaltext)) {$verticaltext = 'ss:VerticalText="1"';}
283 if (!empty($this->wraptext)) {$wraptext = 'ss:WrapText="1"';}
289 if (!empty($this->fontColor)) {$fontColor = 'ss:Color="'.$this->fontColor.'"';}
290 if (!empty($this->bold)) {$bold = 'ss:Bold="1"';}
291 if (!empty($this->italic)) {$italic = 'ss:Italic="1"';}
292 if (!empty($this->strikethrough)) {$strikethrough = 'ss:StrikeThrough="'.$this->strikethrough.'"';}
293 if (!empty($this->underline)) {$underline = 'ss:Underline="'.$this->underline.'"';}
294 if (!empty($this->outline)) {$outline = 'ss:Outline="1"';}
295 if (!empty($this->shadow)) {$shadow = 'ss:Shadow="1"';}
296 if (!empty($this->fontName)) {$fontName = 'ss:FontName="'.$this->fontName.'"';}
297 if (!empty($this->fontFamily)) {$fontFamily = 'x:Family="'.$this->fontFamily.'"';}
298 if (!empty($this->fontSize)) {$fontSize = 'ss:Size="'.$this->fontSize.'"';}
301 if ($this->useBorder)
303 $borders = ' <Borders>'."\r";
305 'Top' => $this->borderTop,
306 'Bottom' => $this->borderBottom,
307 'Left' => $this->borderLeft,
308 'Right' => $this->borderRight,
309 'DiagonalLeft' => $this->borderDL,
310 'DiagonalRight' => $this->borderDR,
313 foreach($positions as $position => $pData)
317 $bLinestyle = isset($pData['LineStyle'])
318 ? 'ss:LineStyle="'.$pData['LineStyle'].'"'
320 $bColor = isset($pData['Color'])
321 ? 'ss:Color="'.$pData['Color'].'"'
323 $bWeight = isset($pData['Weight'])
324 ? 'ss:Weight="'.$pData['Weight'].'"'
326 $borders .= ' <Border ss:Position="'.$position.'" '.$bLinestyle.' '.$bColor.' '.$bWeight.'/>'."\r";
328 $borders .= ' </Borders>'."\r";
331 if ($this->useInterior)
333 if (!empty($this->interiorColor)) {$interiorColor = 'ss:Color="'.$this->interiorColor.'"';}
334 if (!empty($this->interiorPattern)) {$interiorPattern = 'ss:Pattern="'.$this->interiorPattern.'"';}
335 if (!empty($this->interiorPatternColor)) {$interiorPatternColor = 'ss:PatternColor="'.$this->interiorPatternColor.'"';}
336 $interior = ' <Interior '.$interiorColor.' '.$interiorPattern.' '.$interiorPatternColor.'/>'."\r";
339 if (!empty($this->numberFormat))
341 $numberFormat = ' <NumberFormat ss:Format="'.$this->numberFormat.'"/>'."\r";
344 $numberFormat = ' <NumberFormat/>'."\r";
346 $xml = ' <Style ss:ID="'.$this->id.'" '.$name.'>'."\r";
347 if ($this->useAlignment)
348 $xml .= ' <Alignment '.$valign.' '.$halign.' '.$rotate.' '.$shrinktofit.' '.$wraptext.' '.$verticaltext.'/>'."\r";
349 if ($this->useBorder)
352 $xml .= ' <Font '.$fontSize.' '.$fontColor.' '.$bold.' '.$italic.' '.$strikethrough.' '.$underline.' '.$shadow.' '.$outline.' '.$fontName.' '.$fontFamily.'/>'."\r";
353 if ($this->useInterior)
355 $xml .= $numberFormat;
356 $xml .= ' <Protection/>'."\r";
357 $xml .= ' </Style>'."\r";
362 * Checks whether a color is valid for the spreadsheet
363 * @param string $color Named color from MS or web color in HEX format (e.g.
365 * @return mixed Either the valid color in HEX format or false if the color
368 function checkColor($color)
370 $pattern = "/[0-9a-f]{6}/";
371 if (preg_match($pattern, $color, $matches))
373 $color = '#'.$matches[0];
376 elseif (isset($this->namedColorsIE[strtolower($color)]))
378 $color = $this->namedColorsIE[strtolower($color)];
383 $this->_addError(__FUNCTION__,'Supplied color was not valid "'.$color.'"');
389 * Adds a format error. When the document is generated if there are any
390 * errors they will be listed on a seperate sheet.
391 * @param string $namedStyle The style in which the error occurred
392 * @param string $function The name of the function that was called
393 * @param string $message Details of the error
395 function _addError($function, $message)
398 'style' => $this->id,
399 'function' => $function,
400 'message' => $message,
402 $this->formatErrors[] = $tmp;
406 * Returns any errors found in the sheet
407 * @return mixed Array of errors if they exist, otherwise false
411 return($this->formatErrors);
418 * Changes the name of the named style
419 * @param string $name The named style referenced by Excel.
421 function name($name) {$this->name = $name; }
422 ////////////////////////
426 * Changes the vertical alignment setting for the style
427 * @param string $valign The value for the vertical alignment.
428 * Acceptable values are "Automatic" "Top" "Bottom" "Center"
430 function alignVertical($valign)
432 // Automatic, Top, Bottom, Center
433 if ($valign != 'Automatic'
435 && $valign != 'Bottom'
436 && $valign != 'Center')
438 $this->_addError(__FUNCTION__,'vertical alignment was not valid "'.$valign.'"');
441 $this->valign = $valign;
442 $this->useAlignment = true;
446 * Changes the horizontal alignment setting for the style
447 * @param string $halign The value for the horizontal alignment. Acceptable
448 * values are "Automatic" "Left" "Center" "Right"
450 function alignHorizontal($halign)
452 // Automatic, Left, Center, Right
453 if ($halign != 'Automatic'
455 && $halign != 'Center'
456 && $halign != 'Right')
458 $this->_addError(__FUNCTION__,'horizontal alignment was not valid "'.$halign.'"');
459 $halign = 'Automatic';
461 $this->halign = $halign;
462 $this->useAlignment = true;
466 * Changes the rotation setting for the style
467 * @param mixed $rotate The value for the Rotation. Value must be a
468 * number between -90 and 90
470 function alignRotate($rotate)
472 // Degrees to rotate the text
473 if (!is_numeric($rotate))
475 $this->_addError(__FUNCTION__,'rotation was not numeric "'.$rotate.'"');
478 if (abs($rotate) > 90)
480 $rotate = $rotate % 90;
481 $this->_addError(__FUNCTION__,'rotation was greater than 90, defaulted to "'.$rotate.'"');
483 $this->rotate = $rotate;
484 $this->useAlignment = true;
488 * Changes the Shrink To Fit setting for the style
489 * ShrinkToFit shrinks the text so that it fits within the cell.
490 * This doesn't actually work.
492 function alignShrinktofit()
494 $this->shrinktofit = 1;
495 $this->useAlignment = true;
499 * Changes the Vertical Text setting for the style.
500 * Text will be displayed vertically.
502 function alignVerticaltext()
504 $this->verticaltext = 1;
505 $this->useAlignment = true;
509 * Changes the Wrap Text setting for the style.
511 function alignWraptext()
514 $this->useAlignment = true;
516 /////////////////////////
520 * Changes the size of the font
521 * @param string $fontSize The value for the Size. Value must be greater
524 function fontSize($fontSize = 10)
526 if (!is_numeric($fontSize))
529 $this->_addError(__FUNCTION__,'font size was not a number, defaulted to 10 "'.$fontSize.'"');
534 $this->_addError(__FUNCTION__,'font size was less than zero, defaulted to 10 "'.$fontSize.'"');
536 $this->fontSize = $fontSize;
537 $this->useFont = true;
541 * Changes the color for the font
542 * @param string $fontColor The value for the Color.
543 * This can be a MS named color or a Hex web color.
545 function fontColor($fontColor = 'Automatic')
547 $pattern = "/[0-9a-f]{6}/";
548 $fontColor = $this->checkColor($fontColor);
549 if ($fontColor === false)
551 $this->_addError(__FUNCTION__,'font color was not valid "'.$fontColor.'"');
552 $fontColor = 'Automatic';
554 $this->fontColor = $fontColor;
555 $this->useFont = true;
559 * Changes the font for the cell
560 * @param string $fontName The value for the font name. This should be a
561 * standard windows font available on most systems.
563 function fontName($fontName = 'Arial')
565 $this->fontName = $fontName;
566 $this->useFont = true;
570 * Changes the family for the font
571 * @param string $fontFamily The value for the font family. Not really sure
573 * Win32-dependant font family.
574 * Values can be "Automatic" "Decorative"
575 * "Modern" "Roman" "Script" "Swiss"
577 function fontFamily($fontFamily = 'Swiss')
579 // Win32-dependent font family.
580 // Automatic, Decorative, Modern, Roman, Script, and Swiss
581 if ($fontFamily != 'Automatic'
582 && $fontFamily != 'Decorative'
583 && $fontFamily != 'Modern'
584 && $fontFamily != 'Roman'
585 && $fontFamily != 'Script'
586 && $fontFamily != 'Swiss')
588 $this->_addError(__FUNCTION__,'font family was not valid "'.$fontFamily.'"');
591 $this->fontFamily = $fontFamily;
592 $this->useFont = true;
596 * Makes the font bold for the named style
601 $this->useFont = true;
605 * Makes the font italic for the named style
607 function fontItalic()
610 $this->useFont = true;
614 * Makes the font strikethrough for the named style
616 function fontStrikethrough()
618 $this->strikethrough = 1;
619 $this->useFont = true;
623 * Makes the font underlined for the named style
624 * @param string $uStyle The type of underlining for the style.
625 * Acceptable values are "None" "Single" "Double" "SingleAccounting"
628 function fontUnderline($uStyle = 'Single')
630 // None, Single, Double, SingleAccounting, and DoubleAccounting
631 if ($uStyle != 'None'
632 && $uStyle != 'Single'
633 && $uStyle != 'Double'
634 && $uStyle != 'SingleAccounting'
635 && $uStyle != 'DoubleAccounting')
637 $this->_addError(__FUNCTION__,'underline type was not valid "'.$uStyle.'"');
640 $this->underline = $uStyle;
641 $this->useFont = true;
645 * Makes the font shadowed for the named style
647 function fontShadow()
650 $this->useFont = true;
654 * Makes the font outlines for the named style
656 function fontOutline()
659 $this->useFont = true;
661 //////////////////////////
666 * Sets the border for the named style.
667 * This function can be called multiple times to set different sides of the
668 * cell or set all sides the same at once.
669 * @param string $position Sets which side of the cell should be modified.
670 * Acceptable values are "All" "Left" "Top" "Right" "Bottom" "DiagonalLeft"
672 * @param integer $weight Thickness of the border. Default is 1 "Thin"
673 * @param string $color Color of the border. Default is "Automatic" but any
674 * 6-hexadecimal digit number in "#rrggbb" format or it can be any of the
675 * Microsoft® Internet Explorer named colors
676 * @param string $linestyle Type of line to use on the border.
677 * Default is "Continuous". Acceptable balues are "None" "Continuous"
678 * "Dash" "Dot" "DashDot" "DashDotDot" "SlantDashDot" "Double"
681 $position = 'All' // All, Left, Top, Right, Bottom, DiagonalLeft, DiagonalRight
682 ,$weight = '1' // 0
\97Hairline, 1
\97Thin, 2
\97Medium, 3
\97Thick
683 ,$color = 'Automatic' // Automatic, 6-hexadecimal digit number in "#rrggbb" format or it can be any of the Microsoft® Internet Explorer named colors
684 ,$linestyle = 'Continuous' // None, Continuous, Dash, Dot, DashDot, DashDotDot, SlantDashDot, Double
687 if ($position != 'All'
688 && $position != 'Left'
689 && $position != 'Top'
690 && $position != 'Right'
691 && $position != 'Bottom'
692 && $position != 'DiagonalLeft'
693 && $position != 'DiagonalRight')
695 $this->_addError(__FUNCTION__,'border position was not valid, defaulted to All "'.$position.'"');
699 if (is_numeric($weight))
701 if (abs($weight) > 3)
703 $this->_addError(__FUNCTION__,'line weight greater than 3, defaulted to 3 "'.$weight.'"');
709 $this->_addError(__FUNCTION__,'line weight not numeric, defaulted to 3 "'.$weight.'"');
713 $color = $this->checkColor($color);
714 if ($color === false)
716 $this->_addError(__FUNCTION__,'border color was not valid, defaulted to Automatic "'.$weight.'"');
717 $color = 'Automatic';
720 if ($linestyle != 'None'
721 && $linestyle != 'Continuous'
722 && $linestyle != 'Dash'
723 && $linestyle != 'Dot'
724 && $linestyle != 'DashDot'
725 && $linestyle != 'DashDotDot'
726 && $linestyle != 'SlantDashDot'
727 && $linestyle != 'Double')
729 $linestyle = 'Continuous';
730 $this->_addError(__FUNCTION__,'line style was not valid, defaulted to Continuous "'.$linestyle.'"');
735 'LineStyle' => $linestyle,
739 if ($position == 'Top' || $position == 'All') $this->borderTop = $tmp;
740 if ($position == 'Bottom' || $position == 'All') $this->borderBottom = $tmp;
741 if ($position == 'Left' || $position == 'All') $this->borderLeft = $tmp;
742 if ($position == 'Right' || $position == 'All') $this->borderRight = $tmp;
743 if ($position == 'DiagonalLeft' ) $this->borderDL = $tmp;
744 if ($position == 'DiagonalRight' ) $this->borderDR = $tmp;
746 $this->useBorder = true;
748 //////////////////////////
752 * Sets the background style of a style
753 * @param string $color Named color from MS or web color in HEX format (e.g.
755 * @param string $pattern Defaults to a None if not supplied.
756 * @param string $patternColor Defaults to a Automatic if not supplied.
758 function bgColor($color = 'Yellow',$pattern = 'Solid', $patternColor = null)
760 // 6-hexadecimal digit number in "#rrggbb" format
761 // Or it can be any of the Internet Explorer named colors
762 $color = $this->checkColor($color);
763 if ($color === false)
766 $this->_addError(__FUNCTION__,'cell color not valid, defaulted to Yellow "'.$color.'"');
768 $this->interiorColor = $color;
769 if ($pattern != 'None')
771 $this->bgPattern($pattern, $patternColor);
773 $this->useInterior = true;
777 * Sets the background pattern of a style.
779 * @param string $color Named color from MS or web color in HEX format (e.g.
781 * @param string $pattern Defaults to a solid if not supplied.
783 function bgPattern($pattern = 'None', $color = null)
785 // None, Solid, Gray75, Gray50, Gray25, Gray125, Gray0625,
786 // HorzStripe, VertStripe, ReverseDiagStripe, DiagStripe,
787 // DiagCross, ThickDiagCross, ThinHorzStripe, ThinVertStripe,
788 // ThinReverseDiagStripe, ThinDiagStripe, ThinHorzCross, and ThinDiagCross
789 if ($pattern != 'None'
790 && $pattern != 'Solid'
791 && $pattern != 'Gray75'
792 && $pattern != 'Gray50'
793 && $pattern != 'Gray25'
794 && $pattern != 'Gray125'
795 && $pattern != 'Gray0625'
796 && $pattern != 'HorzStripe'
797 && $pattern != 'VertStripe'
798 && $pattern != 'ReverseDiagStripe'
799 && $pattern != 'DiagStripe'
800 && $pattern != 'DiagCross'
801 && $pattern != 'ThickDiagCross'
802 && $pattern != 'ThinHorzStripe'
803 && $pattern != 'ThinVertStripe'
804 && $pattern != 'ThinReverseDiagStripe'
805 && $pattern != 'ThinDiagStripe'
806 && $pattern != 'ThinHorzCross'
807 && $pattern != 'ThinDiagCross')
810 $this->_addError(__FUNCTION__,'cell pattern was not valid, defaulted to Solid "'.$pattern.'"');
813 $this->interiorPattern = $pattern;
815 $this->bgPatternColor($color);
816 $this->useInterior = true;
820 * Specifies the secondary fill color of the cell when Pattern does not equal Solid.
821 * @see function bgPattern()
822 * @param string $color Named color from MS or web color in HEX format (e.g.
824 * @param string $pattern Defaults to a solid if not supplied.
826 function bgPatternColor($color = 'Yellow')
828 // 6-hexadecimal digit number in "#rrggbb" format
829 // Or it can be any of the Internet Explorer named colors
830 if ($color != 'Automatic')
832 $color = $this->checkColor($color);
833 if ($color === false)
835 $color = 'Automatic';
836 $this->_addError(__FUNCTION__,'cell pattern color was not valid, defaulted to Automatic "'.$color.'"');
839 $this->interiorPatternColor = $color;
840 $this->useInterior = true;
843 //////////////////////////
848 * Sets the number format of a style
849 * @param string $formatString Format string to be used by Excel for
850 * displaying the number.
852 function numberFormat($formatString) { $this->numberFormat = $formatString; }
855 * Sets a default date format for a style
857 function numberFormatDate($format=null) { $this->numberFormat($format==null?'mm/dd/yyyy':$format); }
860 * Sets a default time format for a style
862 function numberFormatTime($format=null) { $this->numberFormat($format==null?'hh:mm:ss':$format); }
865 * Sets a default date and time format for a style
867 function numberFormatDatetime($format=null) { $this->numberFormat($format==null?'mm/dd/yyyy\ hh:mm:ss':$format); }
868 //////////////////////////