PEAR Forum :: PHP Extension and Application Repository

PEAR Forum Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister   ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
ExcelWriter, out put wrong data

 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    PEAR Forum Forum Index -> General PEAR questions
View previous topic :: View next topic  
Author Message
ali



Joined: 21 Feb 2008
Posts: 8

PostPosted: Wed Feb 27, 2008 1:15 pm    Post subject: ExcelWriter, out put wrong data Reply with quote

I have written formatted records to excel file using pear library, it out put wrong data after record no 130, i deleted that record but again it give error on the record line 130 & i deleted that records but again the same

so, after 129 records it give unfomatted data on 130 & then next all records are not properly written. what can be the bug.

i am sorry for poor english, please help me in this problem.

thanks,
Back to top
View user's profile Send private message
mark



Joined: 07 Jan 2007
Posts: 1235

PostPosted: Wed Feb 27, 2008 5:35 pm    Post subject: Reply with quote

What exactly is the problem? Do you see "wrong data" or do you get an error message? If you get an error message, it would be helpful to know the message.

Do you maybe create a new format object for each cell?
Back to top
View user's profile Send private message
salvi



Joined: 23 Jul 2008
Posts: 4

PostPosted: Wed Jul 23, 2008 5:59 pm    Post subject: Reply with quote

I have the same problem. After 42 proper rows the contents is messed - it looks like after each char the '?' is inserted, cells are messed etc.

My PHP code is:

Code:
<?php

$sql = "SELECT * FROM towary ORDER BY id";
$towary = $GLOBALS["mydb"]->select($sql);

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$workbook->send("test2.xls");
$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet('worksheet');
$worksheet->setInputEncoding('UTF-8');

for($a=0; $a<count($towary); $a++)
{
   $worksheet->write($a, 0, trim($towary[$a]['id'])); //INT
   $worksheet->write($a, 1, trim($towary[$a]['1_1'])); //TEXT
   $worksheet->write($a, 2, trim($towary[$a]['1_2'])); //TEXT
   $worksheet->write($a, 4, trim($towary[$a]['5_1_b'])); //INT
   $worksheet->write($a, 5, trim($towary[$a]['5_1_a'])); //INT
}

$workbook->close();

?>


When I remove a column with index '1_1' - it works.
When I put back this column and remove '1_2' - it works.
If I put column '1_1' twice or more (but without '1_2') - it works.
If I put column '1_2' twice or more (but without '1_1') - it works.

But when those columns are together (no matter what order) - it causes the contents of xls file messed.
The mentioned columns are TEXT in my MySQL database.

If I remove this line:
$worksheet->setInputEncoding('UTF-8');

the code works (all rows are displayed correctly) but language specific chars are destroyed, eg. ą becomes .

Whats wrong? How to solve this problem?
_________________
Kind regards,
Salvi

www.ty2.pl | www.h0.com.pl | www.e-warsaw.com
Back to top
View user's profile Send private message
ali



Joined: 21 Feb 2008
Posts: 8

PostPosted: Thu Jul 24, 2008 6:44 am    Post subject: Reply with quote

I had the same probleme, this is due to UTF-8 and a bug in the BIFF-8. Because of unicode, the "break" between blocks must not occur on a non "even" bytes number.
There are 2 solutions :

- change the codePage to use something else than UTF-8 (i have done it for cyrillic)
- I manage to modify the "continue" block after grabbing some informations on the net.

MS document are organized as BIFF and there are several format. A biff block has certain length and when there is more data than a biff can contain, there are continuing blocks. In ascii mode (or single byte) there is no problem, but in multiple byte the "cut" between block must not occur in a middle of a multiple byte sequence.
I have attached here a corrected version of the Workbook.php, try and let me know if your problems are solved.
Edit this file and reset the variable country_code and code_page to nothing.
Back to top
View user's profile Send private message
salvi



Joined: 23 Jul 2008
Posts: 4

PostPosted: Thu Jul 24, 2008 11:00 am    Post subject: Reply with quote

I can't see the mentioned attachment... Sad
_________________
Kind regards,
Salvi

www.ty2.pl | www.h0.com.pl | www.e-warsaw.com
Back to top
View user's profile Send private message
alex



Joined: 13 Sep 2006
Posts: 72

PostPosted: Tue Jul 29, 2008 4:37 pm    Post subject: Reply with quote

Hi !

Got the same problem too and whish the file was attached too :p

Thanks a lot !!
Back to top
View user's profile Send private message
alex



Joined: 13 Sep 2006
Posts: 72

PostPosted: Wed Jul 30, 2008 10:07 am    Post subject: Reply with quote

Reading this bug, http://pear.php.net/bugs/bug.php?id=1572, I've found this patch which worked for me :

http://bugs.gentoo.org/attachment.cgi?id=136412
Back to top
View user's profile Send private message
salvi



Joined: 23 Jul 2008
Posts: 4

PostPosted: Sun Aug 03, 2008 11:47 pm    Post subject: Reply with quote

Yes, the solutions is to replace the following functions:

_calculateSharedStringsSizes()
_storeSharedStringsTable()

in Workbook.php with the code below. Thanks to this it works for me. Of course, you use this on your own risk as I cannot give any warranty. Author of the code is Michal Fapso, see here: http://pear.php.net/bugs/bug.php?id=1572

Many thanks, Alex, for your help!

Code:

    /**
    * Calculate
    * Handling of the SST continue blocks is complicated by the need to include an
    * additional continuation byte depending on whether the string is split between
    * blocks or whether it starts at the beginning of the block. (There are also
    * additional complications that will arise later when/if Rich Strings are
    * supported).
    *
    * @access private
    */
    function _calculateSharedStringsSizes()
    {
        /* Iterate through the strings to calculate the CONTINUE block sizes.
           For simplicity we use the same size for the SST and CONTINUE records:
           8228 : Maximum Excel97 block size
             -4 : Length of block header
             -8 : Length of additional SST header information
           -8 : Arbitrary number to keep within _add_continue() limit
         = 8208
        */
        $continue_limit     = 8208;
        $block_length       = 0;
        $written            = 0;
        $this->_block_sizes = array();
        $continue           = 0;

        foreach (array_keys($this->_str_table) as $string) {
            $string_length = strlen($string);
         $headerinfo    = unpack("vlength/Cencoding", $string);
         $encoding      = $headerinfo["encoding"];
         $split_string  = 0;

            // Block length is the total length of the strings that will be
            // written out in a single SST or CONTINUE block.
            $block_length += $string_length;

            // We can write the string if it doesn't cross a CONTINUE boundary
            if ($block_length < $continue_limit) {
                $written      += $string_length;
                continue;
            }

            // Deal with the cases where the next string to be written will exceed
            // the CONTINUE boundary. If the string is very long it may need to be
            // written in more than one CONTINUE record.
            while ($block_length >= $continue_limit) {

                // We need to avoid the case where a string is continued in the first
                // n bytes that contain the string header information.
                $header_length   = 3; // Min string + header size -1
                $space_remaining = $continue_limit - $written - $continue;


                /* TODO: Unicode data should only be split on char (2 byte)
                boundaries. Therefore, in some cases we need to reduce the
                amount of available
                */
            $align = 0;

            # Only applies to Unicode strings
            if ($encoding == 1) {
               # Min string + header size -1
               $header_length = 4;

               if ($space_remaining > $header_length) {
                  # String contains 3 byte header => split on odd boundary
                  if (!$split_string && $space_remaining % 2 != 1) {
                     $space_remaining--;
                     $align = 1;
                  }
                  # Split section without header => split on even boundary
                  else if ($split_string && $space_remaining % 2 == 1) {
                     $space_remaining--;
                     $align = 1;
                  }

                  $split_string = 1;
               }
            }


                if ($space_remaining > $header_length) {
                    // Write as much as possible of the string in the current block
                    $written      += $space_remaining;

                    // Reduce the current block length by the amount written
                    $block_length -= $continue_limit - $continue - $align;

                    // Store the max size for this block
                    $this->_block_sizes[] = $continue_limit - $align;

                    // If the current string was split then the next CONTINUE block
                    // should have the string continue flag (grbit) set unless the
                    // split string fits exactly into the remaining space.
                    if ($block_length > 0) {
                        $continue = 1;
                    } else {
                        $continue = 0;
                    }
                } else {
                    // Store the max size for this block
                    $this->_block_sizes[] = $written + $continue;

                    // Not enough space to start the string in the current block
                    $block_length -= $continue_limit - $space_remaining - $continue;
                    $continue = 0;

                }

                // If the string (or substr) is small enough we can write it in the
                // new CONTINUE block. Else, go through the loop again to write it in
                // one or more CONTINUE blocks
                if ($block_length < $continue_limit) {
                    $written = $block_length;
                } else {
                    $written = 0;
                }
            }
        }

        // Store the max size for the last block unless it is empty
        if ($written + $continue) {
            $this->_block_sizes[] = $written + $continue;
        }


        /* Calculate the total length of the SST and associated CONTINUEs (if any).
         The SST record will have a length even if it contains no strings.
         This length is required to set the offsets in the BOUNDSHEET records since
         they must be written before the SST records
        */

      $tmp_block_sizes = array();
      $tmp_block_sizes = $this->_block_sizes;

      $length  = 12;
      if (!empty($tmp_block_sizes)) {
         $length += array_shift($tmp_block_sizes); # SST
      }
      while (!empty($tmp_block_sizes)) {
         $length += 4 + array_shift($tmp_block_sizes); # CONTINUEs
      }

      return $length;
    }

    /**
    * Write all of the workbooks strings into an indexed array.
    * See the comments in _calculate_shared_string_sizes() for more information.
    *
    * The Excel documentation says that the SST record should be followed by an
    * EXTSST record. The EXTSST record is a hash table that is used to optimise
    * access to SST. However, despite the documentation it doesn't seem to be
    * required so we will ignore it.
    *
    * @access private
    */
    function _storeSharedStringsTable()
    {
        $record  = 0x00fc;  // Record identifier
      $length  = 0x0008;  // Number of bytes to follow
      $total   = 0x0000;

        // Iterate through the strings to calculate the CONTINUE block sizes
        $continue_limit = 8208;
        $block_length   = 0;
        $written        = 0;
        $continue       = 0;

        // sizes are upside down
      $tmp_block_sizes = $this->_block_sizes;
//        $tmp_block_sizes = array_reverse($this->_block_sizes);

      # The SST record is required even if it contains no strings. Thus we will
      # always have a length
      #
      if (!empty($tmp_block_sizes)) {
         $length = 8 + array_shift($tmp_block_sizes);
      }
      else {
         # No strings
         $length = 8;
      }



        // Write the SST block header information
        $header      = pack("vv", $record, $length);
        $data        = pack("VV", $this->_str_total, $this->_str_unique);
        $this->_append($header . $data);




        /* TODO: not good for performance */
        foreach (array_keys($this->_str_table) as $string) {

            $string_length = strlen($string);
         $headerinfo    = unpack("vlength/Cencoding", $string);
         $encoding      = $headerinfo["encoding"];
            $split_string  = 0;

            // Block length is the total length of the strings that will be
            // written out in a single SST or CONTINUE block.
            //
            $block_length += $string_length;


            // We can write the string if it doesn't cross a CONTINUE boundary
            if ($block_length < $continue_limit) {
                $this->_append($string);
                $written += $string_length;
                continue;
            }

            // Deal with the cases where the next string to be written will exceed
            // the CONTINUE boundary. If the string is very long it may need to be
            // written in more than one CONTINUE record.
            //
            while ($block_length >= $continue_limit) {

                // We need to avoid the case where a string is continued in the first
                // n bytes that contain the string header information.
                //
                $header_length   = 3; // Min string + header size -1
                $space_remaining = $continue_limit - $written - $continue;


                // Unicode data should only be split on char (2 byte) boundaries.
                // Therefore, in some cases we need to reduce the amount of available
               // space by 1 byte to ensure the correct alignment.
               $align = 0;

            // Only applies to Unicode strings
            if ($encoding == 1) {
               // Min string + header size -1
               $header_length = 4;

               if ($space_remaining > $header_length) {
                  // String contains 3 byte header => split on odd boundary
                  if (!$split_string && $space_remaining % 2 != 1) {
                     $space_remaining--;
                     $align = 1;
                  }
                  // Split section without header => split on even boundary
                  else if ($split_string && $space_remaining % 2 == 1) {
                     $space_remaining--;
                     $align = 1;
                  }

                  $split_string = 1;
               }
            }


                if ($space_remaining > $header_length) {
                    // Write as much as possible of the string in the current block
                    $tmp = substr($string, 0, $space_remaining);
                    $this->_append($tmp);

                    // The remainder will be written in the next block(s)
                    $string = substr($string, $space_remaining);

                    // Reduce the current block length by the amount written
                    $block_length -= $continue_limit - $continue - $align;

                    // If the current string was split then the next CONTINUE block
                    // should have the string continue flag (grbit) set unless the
                    // split string fits exactly into the remaining space.
                    //
                    if ($block_length > 0) {
                        $continue = 1;
                    } else {
                        $continue = 0;
                    }
                } else {
                    // Not enough space to start the string in the current block
                    $block_length -= $continue_limit - $space_remaining - $continue;
                    $continue = 0;
                }

                // Write the CONTINUE block header
                if (!empty($this->_block_sizes)) {
                    $record  = 0x003C;
                    $length  = array_shift($tmp_block_sizes);

                    $header  = pack('vv', $record, $length);
                    if ($continue) {
                        $header .= pack('C', $encoding);
                    }
                    $this->_append($header);
                }

                // If the string (or substr) is small enough we can write it in the
                // new CONTINUE block. Else, go through the loop again to write it in
                // one or more CONTINUE blocks
                //
                if ($block_length < $continue_limit) {
                    $this->_append($string);
                    $written = $block_length;
                } else {
                    $written = 0;
                }
            }
        }
    }

_________________
Kind regards,
Salvi

www.ty2.pl | www.h0.com.pl | www.e-warsaw.com
Back to top
View user's profile Send private message
Display posts from previous:   
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    PEAR Forum Forum Index -> General PEAR questions All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



PEAR Forum topic RSS feed 
Powered by phpBB © 2001, 2005 phpBB Group

Provided by Ministry of Web developement