worksheet.php

完美的在线教育系统

    * @access public
    * @param integer $row     Zero indexed row
    * @param integer $col     Zero indexed column
    * @param string  $formula The formula text string
    * @param mixed   $format  The optional XF format
    * @return integer
    */
    function writeFormula($row, $col, $formula, $format = null)
    {
        $record    = 0x0006;     // Record identifier

        // Excel normally stores the last calculated value of the formula in $num.
        // Clearly we are not in a position to calculate this a priori. Instead
        // we set $num to zero and set the option flags in $grbit to ensure
        // automatic calculation of the formula when the file is opened.
        //
        $xf        = $this->_XF($format); // The cell format
        $num       = 0x00;                // Current value of formula
        $grbit     = 0x03;                // Option flags
        $unknown   = 0x0000;              // Must be zero


        // Check that row and col are valid and store max and min values
        if ($this->_checkRowCol($row, $col) == false) {
            return -2;
        }

        // Strip the '=' or '@' sign at the beginning of the formula string
        if (preg_match("/^=/", $formula)) {
            $formula = preg_replace("/(^=)/", "", $formula);
        } elseif (preg_match("/^@/", $formula)) {
            $formula = preg_replace("/(^@)/", "", $formula);
        } else {
            // Error handling
            $this->writeString($row, $col, 'Unrecognised character for formula');
            return -1;
        }

        // Parse the formula using the parser in Parser.php
        $error = $this->_parser->parse($formula);
        if ($this->isError($error)) {
            $this->writeString($row, $col, $error->getMessage());
            return -1;
        }

        $formula = $this->_parser->toReversePolish();
        if ($this->isError($formula)) {
            $this->writeString($row, $col, $formula->getMessage());
            return -1;
        }

        $formlen    = strlen($formula);    // Length of the binary string
        $length     = 0x16 + $formlen;     // Length of the record data

        $header    = pack("vv",      $record, $length);
        $data      = pack("vvvdvVv", $row, $col, $xf, $num,
                                     $grbit, $unknown, $formlen);

        $this->_append($header . $data . $formula);
        return 0;
    }

    /**
    * Write a hyperlink.
    * This is comprised of two elements: the visible label and
    * the invisible link. The visible label is the same as the link unless an
    * alternative string is specified. The label is written using the
    * writeString() method. Therefore the 255 characters string limit applies.
    * $string and $format are optional.
    *
    * The hyperlink can be to a http, ftp, mail, internal sheet (not yet), or external
    * directory url.
    *
    * Returns  0 : normal termination
    *         -2 : row or column out of range
    *         -3 : long string truncated to 255 chars
    *
    * @access public
    * @param integer $row    Row
    * @param integer $col    Column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function writeUrl($row, $col, $url, $string = '', $format = null)
    {
        // Add start row and col to arg list
        return($this->_writeUrlRange($row, $col, $row, $col, $url, $string, $format));
    }

    /**
    * This is the more general form of writeUrl(). It allows a hyperlink to be
    * written to a range of cells. This function also decides the type of hyperlink
    * to be written. These are either, Web (http, ftp, mailto), Internal
    * (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $string Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */

    function _writeUrlRange($row1, $col1, $row2, $col2, $url, $string = '', $format = null)
    {

        // Check for internal/external sheet links or default to web link
        if (preg_match('[^internal:]', $url)) {
            return($this->_writeUrlInternal($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        if (preg_match('[^external:]', $url)) {
            return($this->_writeUrlExternal($row1, $col1, $row2, $col2, $url, $string, $format));
        }
        return($this->_writeUrlWeb($row1, $col1, $row2, $col2, $url, $string, $format));
    }


    /**
    * Used to write http, ftp and mailto hyperlinks.
    * The link type ($options) is 0x03 is the same as absolute dir ref without
    * sheet. However it is differentiated by the $unknown2 data stream.
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlWeb($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow

        if (!$format) {
            $format = $this->_url_format;
        }

        // Write the visible label using the writeString() method.
        if ($str == '') {
            $str = $url;
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) || ($str_error == -3)) {
            return $str_error;
        }

        // Pack the undocumented parts of the hyperlink stream
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");
        $unknown2    = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");

        // Pack the option flags
        $options     = pack("V", 0x03);

        // Convert URL to a null terminated wchar string
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
        $url         = $url . "\0\0\0";

        // Pack the length of the URL
        $url_len     = pack("V", strlen($url));

        // Calculate the data length
        $length      = 0x34 + strlen($url);

        // Pack the header data
        $header      = pack("vv",   $record, $length);
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);

        // Write the packed data
        $this->_append($header . $data .
                       $unknown1 . $options .
                       $unknown2 . $url_len . $url);
        return($str_error);
    }

    /**
    * Used to write internal reference hyperlinks such as "Sheet1!A1".
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlInternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow

        if (!$format) {
            $format = $this->_url_format;
        }

        // Strip URL type
        $url = preg_replace('/^internal:/', '', $url);

        // Write the visible label
        if ($str == '') {
            $str = $url;
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) || ($str_error == -3)) {
            return $str_error;
        }

        // Pack the undocumented parts of the hyperlink stream
        $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000");

        // Pack the option flags
        $options     = pack("V", 0x08);

        // Convert the URL type and to a null terminated wchar string
        $url         = join("\0", preg_split("''", $url, -1, PREG_SPLIT_NO_EMPTY));
        $url         = $url . "\0\0\0";

        // Pack the length of the URL as chars (not wchars)
        $url_len     = pack("V", floor(strlen($url)/2));

        // Calculate the data length
        $length      = 0x24 + strlen($url);

        // Pack the header data
        $header      = pack("vv",   $record, $length);
        $data        = pack("vvvv", $row1, $row2, $col1, $col2);

        // Write the packed data
        $this->_append($header . $data .
                       $unknown1 . $options .
                       $url_len . $url);
        return($str_error);
    }

    /**
    * Write links to external directory names such as 'c:\foo.xls',
    * c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'.
    *
    * Note: Excel writes some relative links with the $dir_long string. We ignore
    * these cases for the sake of simpler code.
    *
    * @access private
    * @see writeUrl()
    * @param integer $row1   Start row
    * @param integer $col1   Start column
    * @param integer $row2   End row
    * @param integer $col2   End column
    * @param string  $url    URL string
    * @param string  $str    Alternative label
    * @param mixed   $format The cell format
    * @return integer
    */
    function _writeUrlExternal($row1, $col1, $row2, $col2, $url, $str, $format = null)
    {
        // Network drives are different. We will handle them separately
        // MS/Novell network drives and shares start with \\
        if (preg_match('[^external:\\\\]', $url)) {
            return; //($this->_writeUrlExternal_net($row1, $col1, $row2, $col2, $url, $str, $format));
        }
    
        $record      = 0x01B8;                       // Record identifier
        $length      = 0x00000;                      // Bytes to follow
    
        if (!$format) {
            $format = $this->_url_format;
        }
    
        // Strip URL type and change Unix dir separator to Dos style (if needed)
        //
        $url = preg_replace('/^external:/', '', $url);
        $url = preg_replace('/\//', "\\", $url);
    
        // Write the visible label
        if ($str == '') {
            $str = preg_replace('/\#/', ' - ', $url);
        }
        $str_error = $this->writeString($row1, $col1, $str, $format);
        if (($str_error == -2) or ($str_error == -3)) {
            return $str_error;
        }
    
        // Determine if the link is relative or absolute:
        //   relative if link contains no dir separator, "somefile.xls"
        //   relative if link starts with up-dir, "..\..\somefile.xls"
        //   otherwise, absolute
        
        $absolute    = 0x02; // Bit mask
        if (!preg_match("/\\\/", $url)) {
            $absolute    = 0x00;
        }
        if (preg_match("/^\.\.\\\/", $url)) {
            $absolute    = 0x00;
        }
        $link_type               = 0x01 | $absolute;
    
        // Determine if the link contains a sheet reference and change some of the
        // parameters accordingly.
        // Split the dir name and sheet name (if it exists)
        /*if (preg_match("/\#/", $url)) {
            list($dir_long, $sheet) = split("\#", $url);
        } else {
            $dir_long = $url;
        }
    
        if (isset($sheet)) {
            $link_type |= 0x08;
            $sheet_len  = pack("V", strlen($sheet) + 0x01);
            $sheet      = join("\0", split('', $sheet));
            $sheet     .= "\0\0\0";
        } else {
            $sheet_len   = '';
            $sheet       = '';
        }*/
        $dir_long = $url;
        if (preg_match("/\#/", $url)) {
            $link_type |= 0x08;
        }


    
        // Pack the link type
        $link_type   = pack("V", $link_type);
    
        // Calculate the up-level dir count e.g.. (..\..\..\ == 3)
        $up_count    = preg_match_all("/\.\.\\\/", $dir_long, $useless);
        $up_count    = pack("v", $up_count);
    
        // Store the short dos dir name (null terminated)
        $dir_short   = preg_replace("/\.\.\\\/", '', $dir_long) . "\0";
    
        // Store the long dir name as a wchar string (non-null terminated)
        //$dir_long       = join("\0", split('', $dir_long));
        $dir_long       = $dir_long . "\0";
    
        // Pack the lengths of the dir strings
        $dir_short_len = pack("V", strlen($dir_short)      );
        $dir_long_len  = pack("V", strlen($dir_long)       );
        $stream_len    = pack("V", 0);//strlen($dir_long) + 0x06);
    
        // Pack the undocumented parts of the hyperlink stream
        $unknown1 = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'       );
        $unknown2 = pack("H*",'0303000000000000C000000000000046'               );
        $unknown3 = pack("H*",'FFFFADDE000000000000000000000000000000000000000');
        $unknown4 = pack("v",  0x03                                            );
    
        // Pack the main data stream
        $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                          $unknown1     .
                          $link_type    .
                          $unknown2     .
                          $up_count     .
                          $dir_short_len.
                          $dir_short    .
                          $unknown3     .
                          $stream_len   ;/*.
                          $dir_long_len .
                          $unknown4     .
                          $dir_long     .
                          $sheet_len    .
                          $sheet        ;*/
    
        // Pack the header data
        $length   = strlen($data);
        $header   = pack("vv", $record, $length);
    
        // Write the packed data
        $this->_append($header. $data);
        return($str_error);
    }


    /**
    * This method is used to set the height and format for a row.
    *
    * @access public
    * @param integer $row    The row to set
    * @param integer $height Height we are giving to the row.
    *                        Use null to set XF without setting height
    * @param mixed   $format XF format we are giving to the row
    * @param bool    $hidden The optional hidden attribute
    * @param integer $level  The optional outline level for row, in range [0,7]
    */
    function setRow($row, $height, $format = null, $hidden = false, $level = 0)
    {
        $record      = 0x0208;               // Record identifier
        $length      = 0x0010;               // Number of bytes to follow

        $colMic      = 0x0000;               // First defined column
        $colMac      = 0x0000;               // Last defined column
        $irwMac      = 0x0000;               // Used by Excel to optimise loading
        $reserved    = 0x0000;               // Reserved
        $grbit       = 0x0000;               // Option flags
        $ixfe        = $this->_XF($format);  // XF index

        // set _row_sizes so _sizeRow() can use it
        $this->_row_sizes[$row] = $height;

        // Use setRow($row, null, $XF) to set XF format without setting height
        if ($height != null) {
            $miyRw = $height * 20;  // row height
        } else {
            $miyRw = 0xff;          // default row height is 256
        }

        $level = max(0, min($level, 7));  // level should be between 0 and 7
        $this->_outline_row_level = max($level, $this->_outline_row_level);


        // Set the options flags. fUnsynced is used to show that the font and row
        // heights are not compatible. This is usually the case for WriteExcel.
        // The collapsed flag 0x10 doesn't seem to be used to indicate that a row
        // is collapsed. Instead it is used to indicate that the previous row is
        // collapsed. The zero height flag, 0x20, is used to collapse a row.

        $grbit |= $level;
        if ($hidden) {
            $grbit |= 0x0020;
        }
        $grbit |= 0x0040; // fUnsyn