Description: ------------ When generating an XLS file with Spreadsheet_Excel_Writer 0.9.3 (last released) and OLE-1.0.0RC2, the file comes out corrupted (according to Excel 2010 on Win7 64bit). With OLE-1.0.0RC1 everything works fine. The difference is in header bytes 0x3C- 0x3F. Working version has 0x00 for all four bytes, non-working has 0xFE 0xFF 0xFF 0xFE.

Oh dear. Morgan, are you able to add an executable test script by any chance?

Also, which of the comments (Jan 25 2012? earlier) do you think introduced this? https://github.com/pear/OLE/commits/master

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?

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.

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

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

Spreadsheet_Excel_Writer 0.9.2 actually works fine with OLE-1.0.0RC2. I am not sure which is a better combination, but thought I'd mention it just in case it helps someone.

Actually I ended up fully reverting to RC1 and 0.9.2 because even though the combinations I mentioned above seemed ok, I discovered that larger(?) spreadsheets were corrupt when viewed with Windows Excel 2010, even though they were fine on Mac Excel 2011.

We have expierenced the same issues with Spreadsheet_Excel_Writer 0.9.3 and OLE-1.0.0RC2 : On Windows 7 and Office 2010 clients it resulted in corrupt XLS-files, if the spreadsheet doesn't contain a lot of rows it still works fine. Reverting to RC1 and 0.9.2 solved the problem.

My colleague was able to find a minimal script that produces 192 rows and causes the file to be corrupt in Microsoft Excel 2007 on Windows 7. If the number of rows is 191, then the file is no longer considered corrupt. <?php require_once "Spreadsheet/Excel/Writer.php"; $xls = new Spreadsheet_Excel_Writer(); $sheet =& $xls->addWorksheet("Sheet1"); for ($i = 0; $i < 192; $i++) { $sheet->write($i, 0, 'test'); } $return = $xls->close();

I have created a xls file using pear.File works fine in excel 2007 but in excel 2010 it shows corrupt XLS-files.I want to know the reason of that and plz tell me how o fix this problem.

Reverting to RC1 and 0.9.2 solved the problem.

2 days over this problem ... The solution is change in Root.php \ line 623 : fwrite($FILE, pack("V", 1)); to fwrite($FILE, pack("V", $num_sb_blocks)); to be consistent with prévious RC : Root.php \ line 256 : . pack("V", $iSBDcnt)

THANKYOU indentit. Solved my problem! This should be committed ASAP as this is a major bug!

This bug has been fixed in SVN. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.

Thank you very much!

This worked for me as well; thanks! #confirmed

This worked for me as well thanks...

Did you guys ever fix this issue?

Thanks identit Modifying line 623 of OLE \ Root.php worked

Thanks identit!!! Solved!!!!

Verified problem still exists as of 7/8/15. Fix by identit fixes the problem with opening the excel output files in windows 7.