Format.php

Go to the documentation of this file.

<?php
/*
*  Module written/ported by Xavier Noguer <xnoguer@rezebra.com>
*
*  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
*  <jmcnamara@cpan.org>
*
*  I _DO_ maintain this code, and John McNamara has nothing to do with the
*  porting of this code to PHP.  Any questions directly related to this
*  class library should be directed to me.
*
*  License Information:
*
*    Spreadsheet_Excel_Writer:  A library for generating Excel Spreadsheets
*    Copyright (c) 2002-2003 Xavier Noguer xnoguer@rezebra.com
*
*    This library is free software; you can redistribute it and/or
*    modify it under the terms of the GNU Lesser General Public
*    License as published by the Free Software Foundation; either
*    version 2.1 of the License, or (at your option) any later version.
*
*    This library is distributed in the hope that it will be useful,
*    but WITHOUT ANY WARRANTY; without even the implied warranty of
*    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
*    Lesser General Public License for more details.
*
*    You should have received a copy of the GNU Lesser General Public
*    License along with this library; if not, write to the Free Software
*    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

require_once 'PEAR.php';

class Spreadsheet_Excel_Writer_Format extends PEAR
{
    var $_xf_index;

    var $font_index;

    var $_font_name;

    var $_size;

    var $_bold;

    var $_italic;

    var $_color;

    var $_underline;

    var $_font_strikeout;

    var $_font_outline;

    var $_font_shadow;

    var $_font_script;

    var $_font_family;

    var $_font_charset;

    var $_num_format;

    var $_hidden;

    var $_locked;

    var $_text_h_align;

    var $_text_wrap;

    var $_text_v_align;

    var $_text_justlast;

    var $_rotation;

    var $_fg_color;

    var $_bg_color;

    var $_pattern;

    var $_bottom;

    var $_bottom_color;

    var $_top;

    var $_top_color;

    var $_left;

    var $_left_color;

    var $_right;

    var $_right_color;

    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;
        $this->_bold           = 0x0190;
        $this->_italic         = 0;
        $this->_color          = 0x7FFF;
        $this->_underline      = 0;
        $this->_font_strikeout = 0;
        $this->_font_outline   = 0;
        $this->_font_shadow    = 0;
        $this->_font_script    = 0;
        $this->_font_family    = 0;
        $this->_font_charset   = 0;

        $this->_num_format     = 0;

        $this->_hidden         = 0;
        $this->_locked         = 0;

        $this->_text_h_align   = 0;
        $this->_text_wrap      = 0;
        $this->_text_v_align   = 2;
        $this->_text_justlast  = 0;
        $this->_rotation       = 0;

        $this->_fg_color       = 0x40;
        $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)
        {
            if (method_exists($this, 'set'.ucwords($property))) {
                $method_name = 'set'.ucwords($property);
                $this->$method_name($value);
            }
        }
    }


    function getXf($style)
    {
        // Set the type of the XF record and some of the attributes.
        if ($style == 'style') {
            $style = 0xFFF5;
        } 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;
        $atr_alc     = ($this->_text_wrap)?1:0;
        $atr_bdr     = ($this->_bottom   ||
                        $this->_top      ||
                        $this->_left     ||
                        $this->_right)?1:0;
        $atr_pat     = (($this->_fg_color != 0x40) ||
                        ($this->_bg_color != 0x41) ||
                        $this->_pattern)?1: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
        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
        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);
    }

    function getFont()
    {
        $dyHeight   = $this->_size * 20;    // Height of font (1/20 of a point)
        $icv        = $this->_color;        // Index to color palette
        $bls        = $this->_bold;         // Bold style
        $sss        = $this->_font_script;  // Superscript/subscript
        $uls        = $this->_underline;    // Underline
        $bFamily    = $this->_font_family;  // Font family
        $bCharSet   = $this->_font_charset; // Character set
        $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) {
            $grbit     |= 0x02;
        }
        if ($this->_font_strikeout) {
            $grbit     |= 0x08;
        }
        if ($this->_font_outline) {
            $grbit     |= 0x10;
        }
        if ($this->_font_shadow) {
            $grbit     |= 0x20;
        }

        $header  = pack("vv",         $record, $length);
        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);
    }

    function getFontKey()
    {
        $key  = "$this->_font_name$this->_size";
        $key .= "$this->_font_script$this->_underline";
        $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);
        return ($key);
    }

    function getXfIndex()
    {
        return($this->_xf_index);
    }

    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
                       );

        // Return the default color, 0x7FFF, if undef,
        if ($name_color == '') {
            return(0x7FFF);
        }

        // or the color string converted to an integer,
        if (isset($colors[$name_color])) {
            return($colors[$name_color]);
        }

        // or the default color if string is unrecognised,
        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) {
            return(0x7FFF);
        }

        // or an integer in the valid range
        return($name_color);
    }

    function setAlign($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;
        }
        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;
        }
    }

    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;
        }
    }

    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;
        }
    }

    function setMerge()
    {
        $this->setAlign('merge');
    }

    function setBold($weight = 1)
    {
        if ($weight == 1) {
            $weight = 0x2BC;  // Bold text
        }
        if ($weight == 0) {
            $weight = 0x190;  // Normal text
        }
        if ($weight <  0x064) {
            $weight = 0x190;  // Lower bound
        }
        if ($weight >  0x3E8) {
            $weight = 0x190;  // Upper bound
        }
        $this->_bold = $weight;
    }


    /************************************
    * FUNCTIONS FOR SETTING CELLS BORDERS
    */

    function setBottom($style)
    {
        $this->_bottom = $style;
    }

    function setTop($style)
    {
        $this->_top = $style;
    }

    function setLeft($style)
    {
        $this->_left = $style;
    }

    function setRight($style)
    {
        $this->_right = $style;
    }


    function setBorder($style)
    {
        $this->setBottom($style);
        $this->setTop($style);
        $this->setLeft($style);
        $this->setRight($style);
    }


    /*******************************************
    * FUNCTIONS FOR SETTING CELLS BORDERS COLORS
    */

    function setBorderColor($color)
    {
        $this->setBottomColor($color);
        $this->setTopColor($color);
        $this->setLeftColor($color);
        $this->setRightColor($color);
    }

    function setBottomColor($color)
    {
        $value = $this->_getColor($color);
        $this->_bottom_color = $value;
    }

    function setTopColor($color)
    {
        $value = $this->_getColor($color);
        $this->_top_color = $value;
    }

    function setLeftColor($color)
    {
        $value = $this->_getColor($color);
        $this->_left_color = $value;
    }

    function setRightColor($color)
    {
        $value = $this->_getColor($color);
        $this->_right_color = $value;
    }


    function setFgColor($color)
    {
        $value = $this->_getColor($color);
        $this->_fg_color = $value;
        if ($this->_pattern == 0) { // force color to be seen
            $this->_pattern = 1;
        }
    }

    function setBgColor($color)
    {
        $value = $this->_getColor($color);
        $this->_bg_color = $value;
        if ($this->_pattern == 0) { // force color to be seen
            $this->_pattern = 1;
        }
    }

    function setColor($color)
    {
        $value = $this->_getColor($color);
        $this->_color = $value;
    }

    function setPattern($arg = 1)
    {
        $this->_pattern = $arg;
    }

    function setUnderline($underline)
    {
        $this->_underline = $underline;
    }

    function setItalic()
    {
        $this->_italic = 1;
    }

    function setSize($size)
    {
        $this->_size = $size;
    }

    function setTextWrap()
    {
        $this->_text_wrap = 1;
    }

    function setTextRotation($angle)
    {
        switch ($angle)
        {
            case 0:
                $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 :
                return $this->raiseError("Invalid value for angle.".
                                  " Possible values are: 0, 90, 270 and -1 ".
                                  "for stacking top-to-bottom.");
                $this->_rotation = 0;
                break;
        }
    }

    function setNumFormat($num_format)
    {
        $this->_num_format = $num_format;
    }

    function setStrikeOut()
    {
        $this->_font_strikeout = 1;
    }

    function setOutLine()
    {
        $this->_font_outline = 1;
    }

    function setShadow()
    {
        $this->_font_shadow = 1;
    }

    function setScript($script)
    {
        $this->_font_script = $script;
    }

     function setLocked()
     {
         $this->_locked = 1;
     }

    function setUnLocked()
    {
        $this->_locked = 0;
    }

    function setFontFamily($font_family)
    {
        $this->_font_name = $font_family;
    }
}
?>