Re: [PEAR-BUGS] [PEAR-BUG] Bug #19284 [Com]: RC2 breaks header in excel files from Spreadsheet_Excel_Writer

iphone

On feb 22, 2012, at 18:21, "[email protected]" <[email protected]> wrote:

> Edit report at https://pear.php.net/bugs/bug.php?id=19284&edit=1
> 
> ID:               19284
> Comment by:       [email protected]
> Reported By:      mgfreshour at gmail dot com
> Summary:          RC2 breaks header in excel files from
>                    Spreadsheet_Excel_Writer
> Status:           Feedback
> Type:             Bug
> Package:          OLE
> Operating System: CentOS release 5.6 (Final)
> Package Version:  1.0.0RC2
> PHP Version:      5.2.17
> Roadmap Versions: 
> New Comment:
> 
> The bug is introduced between 7a0455f79a and bb5bd8897c.  This is the
> only code that changed:
> 
> $ diff OLE.20090716.7a0455f79a/PPS/Root.php
> OLE.20120125.bb5bd8897c/PPS/Root.php
> 35a36,41
>>    * Flag to enable new logic
>>    * @var bool
>>    */
>>    var $new_func = true;
>> 
>>    /**
> 203a210,212
>>        if($this->new_func)
>>          return $this->_create_header($iSBDcnt, $iBBcnt, $iPPScnt);
>> 
> 408a418,420
>>      if($this->new_func)
>>        return $this->_create_big_block_chain($iSbdSize, $iBsize,
> $iPpsCnt);
>> 
> 484a497,736
>> 
>> 
>> 
>>    /**
>>     * New method to store Bigblock chain
>>     *
>>     * @access private
>>     * @param integer $num_sb_blocks - number of Smallblock depot
> blocks
>>     * @param integer $num_bb_blocks - number of Bigblock depot
> blocks
>>     * @param integer $num_pps_blocks - number of PropertySetStorage
> blocks
>>     */
>>    function _create_big_block_chain($num_sb_blocks, $num_bb_blocks,
> $num_pps_blocks)
>>    {
>>      $FILE = $this->_FILEH_;
>> 
>>      $bbd_info = $this->_calculate_big_block_chain($num_sb_blocks,
> $num_bb_blocks, $num_pps_blocks);
>> 
>>      $data = "";
>> 
>>      if($num_sb_blocks > 0)
>>        {
>>          for($i = 0; $i<($num_sb_blocks-1); $i++)
>>            $data .= pack("V", $i+1);
>>          $data .= pack("V", -2);
>>        }
>> 
>>      for($i = 0; $i<($num_bb_blocks-1); $i++)
>>        $data .= pack("V", $i + $num_sb_blocks + 1);
>>      $data .= pack("V", -2);
>> 
>>      for($i = 0; $i<($num_pps_blocks-1); $i++)
>>        $data .= pack("V", $i + $num_sb_blocks + $num_bb_blocks + 1);
>>      $data .= pack("V", -2);
>> 
>>      for($i = 0; $i < $bbd_info["0xFFFFFFFD_blockchain_entries"];
> $i++)
>>        $data .= pack("V", 0xFFFFFFFD);
>> 
>>      for($i = 0; $i < $bbd_info["0xFFFFFFFC_blockchain_entries"];
> $i++)
>>        $data .= pack("V", 0xFFFFFFFC);
>> 
>>      // Adjust for Block
>>      $all_entries = $num_sb_blocks + $num_bb_blocks + $num_pps_blocks
> + $bbd_info["0xFFFFFFFD_blockchain_entries"] +
> $bbd_info["0xFFFFFFFC_blockchain_entries"];
>>      if($all_entries % $bbd_info["entries_per_block"])
>>        {
>>          $rest = $bbd_info["entries_per_block"] - ($all_entries %
> $bbd_info["entries_per_block"]);
>>          for($i = 0; $i < $rest; $i++)
>>            $data .= pack("V", -1);
>>        }
>> 
>>      // Extra BDList
>>      if($bbd_info["blockchain_list_entries"] >
> $bbd_info["header_blockchain_list_entries"])
>>        {
>>          $iN=0;
>>          $iNb=0;
>>          for($i = $bbd_info["header_blockchain_list_entries"]; $i <
> $bbd_info["blockchain_list_entries"]; $i++, $iN++)
>>            {
>>              if($iN >= ($bbd_info["entries_per_block"]-1))
>>                {
>>                  $iN = 0;
>>                  $iNb++;
>>                  $data .= pack("V", $num_sb_blocks + $num_bb_blocks +
> $num_pps_blocks + $bbd_info["0xFFFFFFFD_blockchain_entries"] + $iNb);
>>                }
>> 
>>              $data .= pack("V", $num_bb_blocks + $num_sb_blocks +
> $num_pps_blocks + $i);
>>            }
>> 
>>          $all_entries = $bbd_info["blockchain_list_entries"] -
> $bbd_info["header_blockchain_list_entries"];
>>          if(($all_entries % ($bbd_info["entries_per_block"] - 1)))
>>            {
>>              $rest = ($bbd_info["entries_per_block"] - 1) -
> ($all_entries % ($bbd_info["entries_per_block"] - 1));
>>              for($i = 0; $i < $rest; $i++)
>>                $data .= pack("V", -1);
>>            }
>> 
>>          $data .= pack("V", -2);
>>        }
>> 
>>      /*
>>        $this->dump($data, 0, strlen($data));
>>        die;
>>      */
>> 
>>      fwrite($FILE, $data);
>>    }
>> 
>>    /**
>>     * New method to store Header
>>     *
>>     * @access private
>>     * @param integer $num_sb_blocks - number of Smallblock depot
> blocks
>>     * @param integer $num_bb_blocks - number of Bigblock depot
> blocks
>>     * @param integer $num_pps_blocks - number of PropertySetStorage
> blocks
>>     */
>>    function _create_header($num_sb_blocks, $num_bb_blocks,
> $num_pps_blocks)
>>    {
>>      $FILE = $this->_FILEH_;
>> 
>>      $bbd_info = $this->_calculate_big_block_chain($num_sb_blocks,
> $num_bb_blocks, $num_pps_blocks);
>> 
>>      // Save Header
>>      fwrite($FILE,
>>             "\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1"
>>             . "\x00\x00\x00\x00"
>>             . "\x00\x00\x00\x00"
>>             . "\x00\x00\x00\x00"
>>             . "\x00\x00\x00\x00"
>>             . pack("v", 0x3b)
>>             . pack("v", 0x03)
>>             . pack("v", -2)
>>             . pack("v", 9)
>>             . pack("v", 6)
>>             . pack("v", 0)
>>             . "\x00\x00\x00\x00"
>>             . "\x00\x00\x00\x00"
>>             . pack("V", $bbd_info["blockchain_list_entries"])
>>             . pack("V", $num_sb_blocks + $num_bb_blocks) //ROOT
> START
>>             . pack("V", 0)
>>             . pack("V", 0x1000)
>>             );
>> 
>>      //Small Block Depot
>>      if($num_sb_blocks > 0)
>>        fwrite($FILE, pack("V", 0));
>>      else
>>        fwrite($FILE, pack("V", -2));
>> 
>>      fwrite($FILE, pack("V", 1));
>> 
>>      // Extra BDList Start, Count
>>      if($bbd_info["blockchain_list_entries"] <
> $bbd_info["header_blockchain_list_entries"])
>>        {
>>          fwrite($FILE,
>>                 pack("V", -2).      // Extra BDList Start
>>                 pack("V", 0)        // Extra BDList Count
>>                 );
>>        }
>>      else
>>        {
>>          fwrite($FILE, pack("V", $num_sb_blocks + $num_bb_blocks +
> $num_pps_blocks + $bbd_info["0xFFFFFFFD_blockchain_entries"]) .
> pack("V", $bbd_info["0xFFFFFFFC_blockchain_entries"]));
>>        }
>> 
>>      // BDList
>>      for ($i=0; $i < $bbd_info["header_blockchain_list_entries"] and
> $i < $bbd_info["blockchain_list_entries"]; $i++)
>>        {
>>          fwrite($FILE, pack("V", $num_bb_blocks + $num_sb_blocks +
> $num_pps_blocks + $i));
>>        }
>> 
>>      if($i < $bbd_info["header_blockchain_list_entries"])
>>        {
>>          for($j = 0; $j <
> ($bbd_info["header_blockchain_list_entries"]-$i); $j++)
>>            {
>>              fwrite($FILE, (pack("V", -1)));
>>            }
>>        }
>>    }
>> 
>>    /**
>>     * New method to calculate Bigblock chain
>>     *
>>     * @access private
>>     * @param integer $num_sb_blocks - number of Smallblock depot
> blocks
>>     * @param integer $num_bb_blocks - number of Bigblock depot
> blocks
>>     * @param integer $num_pps_blocks - number of PropertySetStorage
> blocks
>>     */
>>    function _calculate_big_block_chain($num_sb_blocks,
> $num_bb_blocks, $num_pps_blocks)
>>    {
>>      $bbd_info["entries_per_block"] = $this->_BIG_BLOCK_SIZE /
> OLE_LONG_INT_SIZE;
>>      $bbd_info["header_blockchain_list_entries"] =
> ($this->_BIG_BLOCK_SIZE - 0x4C) / OLE_LONG_INT_SIZE;
>>      $bbd_info["blockchain_entries"] = $num_sb_blocks +
> $num_bb_blocks + $num_pps_blocks;
>>      $bbd_info["0xFFFFFFFD_blockchain_entries"] =
> $this->get_number_of_pointer_blocks($bbd_info["blockchain_entries"]);
>>      $bbd_info["blockchain_list_entries"] =
> $this->get_number_of_pointer_blocks($bbd_info["blockchain_entries"] +
> $bbd_info["0xFFFFFFFD_blockchain_entries"]);
>> 
>>      // do some magic
>>      $bbd_info["ext_blockchain_list_entries"] = 0;
>>      $bbd_info["0xFFFFFFFC_blockchain_entries"] = 0;
>>      if($bbd_info["blockchain_list_entries"] >
> $bbd_info["header_blockchain_list_entries"])
>>        {
>>          do
>>            {
>>              $bbd_info["blockchain_list_entries"] =
> $this->get_number_of_pointer_blocks($bbd_info["blockchain_entries"] +
> $bbd_info["0xFFFFFFFD_blockchain_entries"] +
> $bbd_info["0xFFFFFFFC_blockchain_entries"]);
>>              $bbd_info["ext_blockchain_list_entries"] =
> $bbd_info["blockchain_list_entries"] -
> $bbd_info["header_blockchain_list_entries"];
>>              $bbd_info["0xFFFFFFFC_blockchain_entries"] =
> $this->get_number_of_pointer_blocks($bbd_info["ext_blockchain_list_entries"]);
>>              $bbd_info["0xFFFFFFFD_blockchain_entries"] =
> $this->get_number_of_pointer_blocks($num_sb_blocks + $num_bb_blocks +
> $num_pps_blocks + $bbd_info["0xFFFFFFFD_blockchain_entries"] +
> $bbd_info["0xFFFFFFFC_blockchain_entries"]);
>>            }
>>          while($bbd_info["blockchain_list_entries"] <
> $this->get_number_of_pointer_blocks($bbd_info["blockchain_entries"] +
> $bbd_info["0xFFFFFFFD_blockchain_entries"] +
> $bbd_info["0xFFFFFFFC_blockchain_entries"]));
>>        }
>> 
>>      return $bbd_info;
>>    }
>> 
>>    /**
>>     * Calculates number of pointer blocks
>>     *
>>     * @access public
>>     * @param integer $num_pointers - number of pointers
>>     */
>>    function get_number_of_pointer_blocks($num_pointers)
>>    {
>>      $pointers_per_block = $this->_BIG_BLOCK_SIZE /
> OLE_LONG_INT_SIZE;
>> 
>>      return floor($num_pointers / $pointers_per_block) +
> (($num_pointers % $pointers_per_block)? 1: 0);
>>    }
>> 
>>    /**
>>     * Support method for some hexdumping
>>     *
>>     * @access public
>>     * @param string $data - Binary data
>>     * @param integer $from - Start offset of data to dump
>>     * @param integer $to - Target offset of data to dump
>>     */
>>    function dump($data, $from, $to)
>>    {
>>      $chars = array();
>>      $i = 0;
>>      for($i = $from; $i < $to; $i++)
>>        {
>>          if(sizeof($chars) == 16)
>>            {
>>              printf("%08X (% 12d) |", $i-16, $i-16);
>>              foreach($chars as $char)
>>                printf(" %02X", $char);
>>              print " |\n";
>> 
>>              $chars = array();
>>            }
>> 
>>          $chars[] = ord($data[$i]);
>>        }
>> 
>>      if(sizeof($chars))
>>        {
>>          printf("%08X (% 12d) |", $i-sizeof($chars),
> $i-sizeof($chars));
>>          foreach($chars as $char)
>>            printf(" %02X", $char);
>>          print " |\n";
>> 
>>          $chars = array();
>>        }
>>    }
> 
> 
> 
> Code to generate the XLS output file:
> 
> <?php
> 
> error_reporting(0);
> 
> require_once('PEAR.php');
> require_once('Spreadsheet/Excel/Writer.php');
> 
> $workbook = new Spreadsheet_Excel_Writer();
> $workbook->setVersion(8);
> 
> $worksheet = $workbook->addWorksheet('My first worksheet');
> 
> $worksheet->setLandscape();
> 
> $format_even_rows = $workbook->addFormat();
> 
> $workbook->setCustomColor(12, 204, 204, 204);
> 
> $format_even_rows->setFgColor(12);
> $format_even_rows->setBottom(1);
> $format_even_rows->setLeft(1);
> $format_even_rows->setRight(1);
> $format_even_rows->setTop(1);
> $format_even_rows->setNumFormat('@');
> 
> $format_even_rows1 = $workbook ->addFormat();
> $format_even_rows1->setFgColor(12);
> $format_even_rows1->setBottom(1);
> $format_even_rows1->setLeft(1);
> $format_even_rows1->setRight(1);
> $format_even_rows1->setTop(1);
> $format_even_rows1->setNumFormat('#,##0');
> 
> for($i=0; $i<1000; $i++)
> {
>        $worksheet->write($i, 0, 'purple', $format_even_rows);
>        $worksheet->write($i, 1, 'monkey',  $format_even_rows);
>        $worksheet->write($i, 2, 'dishwasher', $format_even_rows);
>        $worksheet->write($i, 3, '999', $format_even_rows);
>        $worksheet->write($i, 4, '1000', $format_even_rows1);
> }
> 
> $worksheet->setColumn(0, 4, 50);
> // Let's send the file
> $workbook->send('test.xls');
> $workbook->close();
> 
> ?>
> 
> 
> 
> Binary comparison between the 2 XLS files (similar difference in header
> as indicated by Morgan above, additional differences later in the file
> can be ignored as they change every time output is regenerated):
> 
> $ cmp -l OLE.20090716.7a0455f79a.output.xls
> OLE.20120125.bb5bd8897c.output.xls
>   61   0 376
>   62   0 377
>   63   0 377
>   64   0 377
> 79974  32 236
> 79975 247 176
> 79976 214 244
> 79982  32 236
> 79983 247 176
> 79984 214 244
> 
> 
> Windows XP with Excel 2003 does not have an issue.
> 
> Windows 7 with Excel 2010 reports corrupt file error.
> 
> Our temporary workaround is to freeze OLE at RC1.
> 
> Take care,
> Jon
> 
> 
> Previous Comments:
> ------------------------------------------------------------------------
> 
> [2012-02-22 20:23:12] jonl
> 
> Just realized no one maintains OLE
> 
> This week I'll try to go thru the commits sequentially from RC1 to RC2
> to narrow it down
> 
> ------------------------------------------------------------------------
> 
> [2012-02-22 18:28:38] jonl
> 
> We are also experiencing this problem via Spreadsheet_Excel_Writer on
> Windows 7 with Excel 2010.  Windows XP with Excel 2003 works fine.
> 
> We are experiencing in both Slackware-12ish/Apache-2.2.22/PHP-5.3.10 and
> Slackware-10.2ish/Apache-1.3.42/PHP-5.3.10.  All PEAR modules are
> current.
> 
> Let us know what you need -- test cases, beer -- and we will oblige.
> 
> ------------------------------------------------------------------------
> 
> [2012-02-17 12:06:05] ttischbein
> 
> I have the same problem. But I can open the corrupt xls file with
> OpenOffice Calc. Another notice: If I write less rows to the sheet, the
> output xls file is ok.
> I have a php script to reconstruate the Bug but where I can upload it?
> 
> ------------------------------------------------------------------------
> 
> [2012-02-10 09:08:04] doconnor
> 
> -Status: Open
> +Status: Feedback
> 
> 
> ------------------------------------------------------------------------
> 
> [2012-02-10 09:07:57] doconnor
> 
> Also, which of the comments (Jan 25 2012? earlier) do you think
> introduced this?
> https://github.com/pear/OLE/commits/master
> 
> ------------------------------------------------------------------------
> 
> The remainder of the comments for this report are too long. To view
> the rest of the comments, please view the bug report online at
>    http://pear.php.net/bugs/bug.php?id=19284
> 
> -- 
> Edit this bug report at https://pear.php.net/bugs/bug.php?id=19284&edit=1
> 
> 
> -- 
> PEAR Bugs Mailing List (http://pear.php.net/bugs/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>