5,7 → 5,7 |
* The majority of this is _NOT_ my code. I simply ported it from the |
* PERL Spreadsheet::WriteExcel module. |
* |
* The author of the Spreadsheet::WriteExcel module is John McNamara |
* The author of the Spreadsheet::WriteExcel module is John McNamara |
* <jmcnamara@cpan.org> |
* |
* I _DO_ maintain this code, and John McNamara has nothing to do with the |
32,7 → 32,7 |
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
*/ |
|
//require_once('PEAR.php'); |
require_once 'PEAR.php'; |
|
/** |
* Class for generating Excel XF records (formats) |
245,14 → 245,14 |
/** |
* Constructor |
* |
* @access public |
* @access private |
* @param integer $index the XF index for the format. |
* @param array $properties array with properties to be set on initialization. |
*/ |
function Spreadsheet_Excel_Writer_Format($index = 0,$properties = array()) |
function Spreadsheet_Excel_Writer_Format($BIFF_version, $index = 0, $properties = array()) |
{ |
$this->_xf_index = $index; |
|
$this->_BIFF_version = $BIFF_version; |
$this->font_index = 0; |
$this->_font_name = 'Arial'; |
$this->_size = 10; |
266,11 → 266,11 |
$this->_font_script = 0; |
$this->_font_family = 0; |
$this->_font_charset = 0; |
|
|
$this->_num_format = 0; |
|
|
$this->_hidden = 0; |
$this->_locked = 1; |
$this->_locked = 0; |
|
$this->_text_h_align = 0; |
$this->_text_wrap = 0; |
282,22 → 282,23 |
$this->_bg_color = 0x41; |
|
$this->_pattern = 0; |
|
|
$this->_bottom = 0; |
$this->_top = 0; |
$this->_left = 0; |
$this->_right = 0; |
|
$this->_diag = 0; |
|
$this->_bottom_color = 0x40; |
$this->_top_color = 0x40; |
$this->_left_color = 0x40; |
$this->_right_color = 0x40; |
|
$this->_diag_color = 0x40; |
|
// Set properties passed to Spreadsheet_Excel_Writer_Workbook::addFormat() |
foreach($properties as $property => $value) |
foreach ($properties as $property => $value) |
{ |
if(method_exists($this,'set'.ucwords($property))) |
{ |
if (method_exists($this, 'set'.ucwords($property))) { |
$method_name = 'set'.ucwords($property); |
$this->$method_name($value); |
} |
314,14 → 315,13 |
function getXf($style) |
{ |
// Set the type of the XF record and some of the attributes. |
if ($style == "style") { |
if ($style == 'style') { |
$style = 0xFFF5; |
} |
else { |
} else { |
$style = $this->_locked; |
$style |= $this->_hidden << 1; |
} |
|
|
// Flags to indicate if attributes have been set. |
$atr_num = ($this->_num_format != 0)?1:0; |
$atr_fnt = ($this->font_index != 0)?1:0; |
333,62 → 333,112 |
$atr_pat = (($this->_fg_color != 0x40) || |
($this->_bg_color != 0x41) || |
$this->_pattern)?1:0; |
$atr_prot = 0; |
|
$atr_prot = $this->_locked | $this->_hidden; |
|
// Zero the default border colour if the border has not been set. |
if ($this->_bottom == 0) { |
$this->_bottom_color = 0; |
} |
} |
if ($this->_top == 0) { |
$this->_top_color = 0; |
} |
} |
if ($this->_right == 0) { |
$this->_right_color = 0; |
} |
} |
if ($this->_left == 0) { |
$this->_left_color = 0; |
} |
|
} |
if ($this->_diag == 0) { |
$this->_diag_color = 0; |
} |
|
$record = 0x00E0; // Record identifier |
$length = 0x0010; // Number of bytes to follow |
|
if ($this->_BIFF_version == 0x0500) { |
$length = 0x0010; // Number of bytes to follow |
} |
if ($this->_BIFF_version == 0x0600) { |
$length = 0x0014; |
} |
|
$ifnt = $this->font_index; // Index to FONT record |
$ifmt = $this->_num_format; // Index to FORMAT record |
|
$align = $this->_text_h_align; // Alignment |
$align |= $this->_text_wrap << 3; |
$align |= $this->_text_v_align << 4; |
$align |= $this->_text_justlast << 7; |
$align |= $this->_rotation << 8; |
$align |= $atr_num << 10; |
$align |= $atr_fnt << 11; |
$align |= $atr_alc << 12; |
$align |= $atr_bdr << 13; |
$align |= $atr_pat << 14; |
$align |= $atr_prot << 15; |
|
$icv = $this->_fg_color; // fg and bg pattern colors |
$icv |= $this->_bg_color << 7; |
|
$fill = $this->_pattern; // Fill and border line style |
$fill |= $this->_bottom << 6; |
$fill |= $this->_bottom_color << 9; |
|
$border1 = $this->_top; // Border line style and color |
$border1 |= $this->_left << 3; |
$border1 |= $this->_right << 6; |
$border1 |= $this->_top_color << 9; |
|
$border2 = $this->_left_color; // Border color |
$border2 |= $this->_right_color << 7; |
|
$header = pack("vv", $record, $length); |
$data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align, |
$icv, $fill, |
$border1, $border2); |
return($header.$data); |
if ($this->_BIFF_version == 0x0500) { |
$align = $this->_text_h_align; // Alignment |
$align |= $this->_text_wrap << 3; |
$align |= $this->_text_v_align << 4; |
$align |= $this->_text_justlast << 7; |
$align |= $this->_rotation << 8; |
$align |= $atr_num << 10; |
$align |= $atr_fnt << 11; |
$align |= $atr_alc << 12; |
$align |= $atr_bdr << 13; |
$align |= $atr_pat << 14; |
$align |= $atr_prot << 15; |
|
$icv = $this->_fg_color; // fg and bg pattern colors |
$icv |= $this->_bg_color << 7; |
|
$fill = $this->_pattern; // Fill and border line style |
$fill |= $this->_bottom << 6; |
$fill |= $this->_bottom_color << 9; |
|
$border1 = $this->_top; // Border line style and color |
$border1 |= $this->_left << 3; |
$border1 |= $this->_right << 6; |
$border1 |= $this->_top_color << 9; |
|
$border2 = $this->_left_color; // Border color |
$border2 |= $this->_right_color << 7; |
|
$header = pack("vv", $record, $length); |
$data = pack("vvvvvvvv", $ifnt, $ifmt, $style, $align, |
$icv, $fill, |
$border1, $border2); |
} elseif ($this->_BIFF_version == 0x0600) { |
$align = $this->_text_h_align; // Alignment |
$align |= $this->_text_wrap << 3; |
$align |= $this->_text_v_align << 4; |
$align |= $this->_text_justlast << 7; |
|
$used_attrib = $atr_num << 2; |
$used_attrib |= $atr_fnt << 3; |
$used_attrib |= $atr_alc << 4; |
$used_attrib |= $atr_bdr << 5; |
$used_attrib |= $atr_pat << 6; |
$used_attrib |= $atr_prot << 7; |
|
$icv = $this->_fg_color; // fg and bg pattern colors |
$icv |= $this->_bg_color << 7; |
|
$border1 = $this->_left; // Border line style and color |
$border1 |= $this->_right << 4; |
$border1 |= $this->_top << 8; |
$border1 |= $this->_bottom << 12; |
$border1 |= $this->_left_color << 16; |
$border1 |= $this->_right_color << 23; |
$diag_tl_to_rb = 0; // FIXME: add method |
$diag_tr_to_lb = 0; // FIXME: add method |
$border1 |= $diag_tl_to_rb << 30; |
$border1 |= $diag_tr_to_lb << 31; |
|
$border2 = $this->_top_color; // Border color |
$border2 |= $this->_bottom_color << 7; |
$border2 |= $this->_diag_color << 14; |
$border2 |= $this->_diag << 21; |
$border2 |= $this->_pattern << 26; |
|
$header = pack("vv", $record, $length); |
|
$rotation = $this->_rotation; |
$biff8_options = 0x00; |
$data = pack("vvvC", $ifnt, $ifmt, $style, $align); |
$data .= pack("CCC", $rotation, $biff8_options, $used_attrib); |
$data .= pack("VVv", $border1, $border2, $icv); |
} |
|
return($header . $data); |
} |
|
|
/** |
* Generate an Excel BIFF FONT record. |
* |
403,11 → 453,15 |
$uls = $this->_underline; // Underline |
$bFamily = $this->_font_family; // Font family |
$bCharSet = $this->_font_charset; // Character set |
$rgch = $this->_font_name; // Font name |
|
$cch = strlen($rgch); // Length of font name |
$record = 0x31; // Record identifier |
$length = 0x0F + $cch; // Record length |
$encoding = 0; // TODO: Unicode support |
|
$cch = strlen($this->_font_name); // Length of font name |
$record = 0x31; // Record identifier |
if ($this->_BIFF_version == 0x0500) { |
$length = 0x0F + $cch; // Record length |
} elseif ($this->_BIFF_version == 0x0600) { |
$length = 0x10 + $cch; |
} |
$reserved = 0x00; // Reserved |
$grbit = 0x00; // Font attributes |
if ($this->_italic) { |
422,16 → 476,22 |
if ($this->_font_shadow) { |
$grbit |= 0x20; |
} |
|
|
$header = pack("vv", $record, $length); |
$data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls, |
$sss, $uls, $bFamily, |
$bCharSet, $reserved, $cch); |
return($header . $data. $this->_font_name); |
if ($this->_BIFF_version == 0x0500) { |
$data = pack("vvvvvCCCCC", $dyHeight, $grbit, $icv, $bls, |
$sss, $uls, $bFamily, |
$bCharSet, $reserved, $cch); |
} elseif ($this->_BIFF_version == 0x0600) { |
$data = pack("vvvvvCCCCCC", $dyHeight, $grbit, $icv, $bls, |
$sss, $uls, $bFamily, |
$bCharSet, $reserved, $cch, $encoding); |
} |
return($header . $data . $this->_font_name); |
} |
|
|
/** |
* Returns a unique hash key for a font. |
* Returns a unique hash key for a font. |
* Used by Spreadsheet_Excel_Writer_Workbook::_storeAllFonts() |
* |
* The elements that form the key are arranged to increase the probability of |
447,10 → 507,10 |
$key .= "$this->_font_strikeout$this->_bold$this->_font_outline"; |
$key .= "$this->_font_family$this->_font_charset"; |
$key .= "$this->_font_shadow$this->_color$this->_italic"; |
$key = str_replace(" ","_",$key); |
$key = str_replace(' ', '_', $key); |
return ($key); |
} |
|
|
/** |
* Returns the index used by Spreadsheet_Excel_Writer_Worksheet::_XF() |
* |
460,7 → 520,7 |
{ |
return($this->_xf_index); |
} |
|
|
/** |
* Used in conjunction with the set_xxx_color methods to convert a color |
* string into a number. Color range is 0..63 but we will restrict it |
473,55 → 533,50 |
function _getColor($name_color = '') |
{ |
$colors = array( |
'aqua' => 0x0F, |
'cyan' => 0x0F, |
'black' => 0x08, |
'blue' => 0x0C, |
'brown' => 0x10, |
'magenta' => 0x0E, |
'fuchsia' => 0x0E, |
'gray' => 0x17, |
'grey' => 0x17, |
'green' => 0x11, |
'lime' => 0x0B, |
'navy' => 0x12, |
'orange' => 0x35, |
'purple' => 0x14, |
'red' => 0x0A, |
'silver' => 0x16, |
'white' => 0x09, |
'yellow' => 0x0D |
'aqua' => 0x07, |
'cyan' => 0x07, |
'black' => 0x00, |
'blue' => 0x04, |
'brown' => 0x10, |
'magenta' => 0x06, |
'fuchsia' => 0x06, |
'gray' => 0x17, |
'grey' => 0x17, |
'green' => 0x11, |
'lime' => 0x03, |
'navy' => 0x12, |
'orange' => 0x35, |
'purple' => 0x14, |
'red' => 0x02, |
'silver' => 0x16, |
'white' => 0x01, |
'yellow' => 0x05 |
); |
|
|
// Return the default color, 0x7FFF, if undef, |
if($name_color == '') { |
if ($name_color === '') { |
return(0x7FFF); |
} |
|
|
// or the color string converted to an integer, |
if(isset($colors[$name_color])) { |
if (isset($colors[$name_color])) { |
return($colors[$name_color]); |
} |
|
|
// or the default color if string is unrecognised, |
if(preg_match("/\D/",$name_color)) { |
if (preg_match("/\D/",$name_color)) { |
return(0x7FFF); |
} |
|
// or an index < 8 mapped into the correct range, |
if($name_color < 8) { |
return($name_color + 8); |
} |
|
|
// or the default color if arg is outside range, |
if($name_color > 63) { |
if ($name_color > 63) { |
return(0x7FFF); |
} |
|
|
// or an integer in the valid range |
return($name_color); |
} |
|
|
/** |
* Set cell alignment. |
* |
533,53 → 588,127 |
if (preg_match("/\d/",$location)) { |
return; // Ignore numbers |
} |
|
$location = strtolower($location); |
|
if ($location == 'left') { |
$this->_text_h_align = 1; |
} |
if ($location == 'centre') { |
$this->_text_h_align = 2; |
} |
if ($location == 'center') { |
$this->_text_h_align = 2; |
} |
if ($location == 'right') { |
$this->_text_h_align = 3; |
} |
if ($location == 'fill') { |
$this->_text_h_align = 4; |
} |
if ($location == 'justify') { |
$this->_text_h_align = 5; |
} |
if ($location == 'merge') { |
$this->_text_h_align = 6; |
} |
if ($location == 'equal_space') { // For T.K. |
$this->_text_h_align = 7; |
} |
if ($location == 'top') { |
$this->_text_v_align = 0; |
} |
if ($location == 'vcentre') { |
$this->_text_v_align = 1; |
} |
if ($location == 'vcenter') { |
$this->_text_v_align = 1; |
} |
if ($location == 'bottom') { |
$this->_text_v_align = 2; |
} |
if ($location == 'vjustify') { |
$this->_text_v_align = 3; |
} |
if ($location == 'vequal_space') { // For T.K. |
$this->_text_v_align = 4; |
} |
} |
|
/** |
* Set cell horizontal alignment. |
* |
* @access public |
* @param string $location alignment for the cell ('left', 'right', etc...). |
*/ |
function setHAlign($location) |
{ |
if (preg_match("/\d/",$location)) { |
return; // Ignore numbers |
} |
|
$location = strtolower($location); |
|
if ($location == 'left') { |
$this->_text_h_align = 1; |
} |
} |
if ($location == 'centre') { |
$this->_text_h_align = 2; |
} |
} |
if ($location == 'center') { |
$this->_text_h_align = 2; |
} |
} |
if ($location == 'right') { |
$this->_text_h_align = 3; |
} |
} |
if ($location == 'fill') { |
$this->_text_h_align = 4; |
} |
} |
if ($location == 'justify') { |
$this->_text_h_align = 5; |
} |
} |
if ($location == 'merge') { |
$this->_text_h_align = 6; |
} |
} |
if ($location == 'equal_space') { // For T.K. |
$this->_text_h_align = 7; |
} |
} |
} |
|
/** |
* Set cell vertical alignment. |
* |
* @access public |
* @param string $location alignment for the cell ('top', 'vleft', 'vright', etc...). |
*/ |
function setVAlign($location) |
{ |
if (preg_match("/\d/",$location)) { |
return; // Ignore numbers |
} |
|
$location = strtolower($location); |
|
if ($location == 'top') { |
$this->_text_v_align = 0; |
} |
} |
if ($location == 'vcentre') { |
$this->_text_v_align = 1; |
} |
} |
if ($location == 'vcenter') { |
$this->_text_v_align = 1; |
} |
} |
if ($location == 'bottom') { |
$this->_text_v_align = 2; |
} |
} |
if ($location == 'vjustify') { |
$this->_text_v_align = 3; |
} |
} |
if ($location == 'vequal_space') { // For T.K. |
$this->_text_v_align = 4; |
} |
} |
} |
|
|
/** |
* This is an alias for the unintuitive setAlign('merge') |
* |
589,7 → 718,7 |
{ |
$this->setAlign('merge'); |
} |
|
|
/** |
* Sets the boldness of the text. |
* Bold has a range 100..1000. |
602,26 → 731,26 |
*/ |
function setBold($weight = 1) |
{ |
if($weight == 1) { |
if ($weight == 1) { |
$weight = 0x2BC; // Bold text |
} |
if($weight == 0) { |
if ($weight == 0) { |
$weight = 0x190; // Normal text |
} |
if($weight < 0x064) { |
if ($weight < 0x064) { |
$weight = 0x190; // Lower bound |
} |
if($weight > 0x3E8) { |
if ($weight > 0x3E8) { |
$weight = 0x190; // Upper bound |
} |
$this->_bold = $weight; |
} |
|
|
|
|
/************************************ |
* FUNCTIONS FOR SETTING CELLS BORDERS |
*/ |
|
|
/** |
* Sets the width for the bottom border of the cell |
* |
632,7 → 761,7 |
{ |
$this->_bottom = $style; |
} |
|
|
/** |
* Sets the width for the top border of the cell |
* |
643,7 → 772,7 |
{ |
$this->_top = $style; |
} |
|
|
/** |
* Sets the width for the left border of the cell |
* |
654,7 → 783,7 |
{ |
$this->_left = $style; |
} |
|
|
/** |
* Sets the width for the right border of the cell |
* |
665,8 → 794,8 |
{ |
$this->_right = $style; |
} |
|
|
|
|
/** |
* Set cells borders to the same style |
* |
680,17 → 809,17 |
$this->setLeft($style); |
$this->setRight($style); |
} |
|
|
|
|
/******************************************* |
* FUNCTIONS FOR SETTING CELLS BORDERS COLORS |
*/ |
|
|
/** |
* Sets all the cell's borders to the same color |
* |
* @access public |
* @param mixed $color The color we are setting. Either a string (like 'blue'), |
* @param mixed $color The color we are setting. Either a string (like 'blue'), |
* or an integer (range is [8...63]). |
*/ |
function setBorderColor($color) |
700,7 → 829,7 |
$this->setLeftColor($color); |
$this->setRightColor($color); |
} |
|
|
/** |
* Sets the cell's bottom border color |
* |
712,7 → 841,7 |
$value = $this->_getColor($color); |
$this->_bottom_color = $value; |
} |
|
|
/** |
* Sets the cell's top border color |
* |
724,7 → 853,7 |
$value = $this->_getColor($color); |
$this->_top_color = $value; |
} |
|
|
/** |
* Sets the cell's left border color |
* |
736,7 → 865,7 |
$value = $this->_getColor($color); |
$this->_left_color = $value; |
} |
|
|
/** |
* Sets the cell's right border color |
* |
748,8 → 877,8 |
$value = $this->_getColor($color); |
$this->_right_color = $value; |
} |
|
|
|
|
/** |
* Sets the cell's foreground color |
* |
760,8 → 889,11 |
{ |
$value = $this->_getColor($color); |
$this->_fg_color = $value; |
if ($this->_pattern == 0) { // force color to be seen |
$this->_pattern = 1; |
} |
} |
|
|
/** |
* Sets the cell's background color |
* |
772,8 → 904,11 |
{ |
$value = $this->_getColor($color); |
$this->_bg_color = $value; |
if ($this->_pattern == 0) { // force color to be seen |
$this->_pattern = 1; |
} |
} |
|
|
/** |
* Sets the cell's color |
* |
785,7 → 920,7 |
$value = $this->_getColor($color); |
$this->_color = $value; |
} |
|
|
/** |
* Sets the fill pattern attribute of a cell |
* |
797,7 → 932,7 |
{ |
$this->_pattern = $arg; |
} |
|
|
/** |
* Sets the underline of the text |
* |
809,7 → 944,7 |
{ |
$this->_underline = $underline; |
} |
|
|
/** |
* Sets the font style as italic |
* |
821,7 → 956,7 |
} |
|
/** |
* Sets the font size |
* Sets the font size |
* |
* @access public |
* @param integer $size The font size (in pixels I think). |
830,7 → 965,7 |
{ |
$this->_size = $size; |
} |
|
|
/** |
* Sets text wrapping |
* |
856,16 → 991,28 |
$this->_rotation = 0; |
break; |
case 90: |
if ($this->_BIFF_version == 0x0500) { |
$this->_rotation = 3; |
} elseif ($this->_BIFF_version == 0x0600) { |
$this->_rotation = 180; |
} |
break; |
case 270: |
if ($this->_BIFF_version == 0x0500) { |
$this->_rotation = 2; |
} elseif ($this->_BIFF_version == 0x0600) { |
$this->_rotation = 90; |
} |
break; |
case -1: |
if ($this->_BIFF_version == 0x0500) { |
$this->_rotation = 1; |
} elseif ($this->_BIFF_version == 0x0600) { |
$this->_rotation = 255; |
} |
break; |
default : |
$this->raiseError("Invalid value for angle.". |
return $this->raiseError("Invalid value for angle.". |
" Possible values are: 0, 90, 270 and -1 ". |
"for stacking top-to-bottom."); |
$this->_rotation = 0; |
927,6 → 1074,16 |
$this->_font_script = $script; |
} |
|
/** |
* Locks a cell. |
* |
* @access public |
*/ |
function setLocked() |
{ |
$this->_locked = 1; |
} |
|
/** |
* Unlocks a cell. Useful for unprotecting particular cells of a protected sheet. |
* |
936,5 → 1093,17 |
{ |
$this->_locked = 0; |
} |
|
/** |
* Sets the font family name. |
* |
* @access public |
* @param string $fontfamily The font family name. Possible values are: |
* 'Times New Roman', 'Arial', 'Courier'. |
*/ |
function setFontFamily($font_family) |
{ |
$this->_font_name = $font_family; |
} |
} |
?> |