Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.9.4

Bug #2942 Problems with files with over 200 row / or 30kb of filesize
Submitted: 2004-12-10 13:29 UTC
From: alex dot petri at gmx dot de Assigned: alan_k
Status: Closed Package: Spreadsheet_Excel_Writer
PHP Version: 4.3.9 OS: Linux
Roadmaps: (Not assigned)    

 [2004-12-10 13:29 UTC] alex dot petri at gmx dot de
Description: ------------ I have some Problems if i set: $xls->_codepage='1250'; $xls->setVersion(8); the file is corrupt if it is larger than ca 30kb and/or has more than 150-200 rows... this happens on the server which runs linux - Apache/1.3.33 (Unix) PHP/4.3.9 on my testserver (W2K) everything is okay..


 [2005-04-09 22:34 UTC] andyetemadi at gmail dot com
I am having the same problem but with heavy formatting and approximately 2,000 rows.
 [2005-09-23 14:16 UTC] blindcopy00-spam at yahoo dot com
I am experiencing similar problems on Redhat Enterprise Linux 4 with php-4.3.9 and after upgrading to php-4.3.9-3.8
 [2005-10-04 09:22 UTC] gr at alpinfor dot com
Same problems, with 2000 rows. I think it's a time out from the server...
 [2005-10-04 13:10 UTC] fabian dot hess at gmx dot de
I got the same problem (200+ rows), yet on a WinXP machine with PHP 5.0.0.
 [2005-10-04 13:13 UTC] tuupola at php dot net
Try to do a ini_set('max_execution_time', 0); in the script you produce the excel and see if the problem persists.
 [2005-12-19 15:46 UTC] blindcopy00-spam at yahoo dot com
Unfotunatley, this did not work for me: ini_set('max_execution_time', 0);
 [2006-01-13 15:12 UTC] blindcopy00-spam at yahoo dot com
I've also tried to set max execution time to 600 and max post size to 16M with no change in the curruption. The corruption seems to occur in excel files larger than approx 200 records.
 [2006-01-13 15:13 UTC] blindcopy00-spam at yahoo dot com
I meant to say greater than 2000 records.
 [2007-03-19 14:42 UTC] purs (Dima Pursanov)
WOW!Spent the whole day on THIS problem!REALLY needed its solving..even did some changes in pear class,included other classes...AND!suddenly found that thet problm appears when u do the following: $workbook->setVersion(8); $worksheet->setInputEncoding('UTF-8'); -- if you leave the default encoding-there is no prob with crushing xls!so..this was my solving: $workbook->setVersion(8); /*code*/ while ($temp_str = pg_fetch_array($exec)) { $worksheet->write($i, 0, $temp_str[0]); $worksheet->setInputEncoding('UTF-8');//changing unicode $worksheet->write($i, 1, $temp_str[1]);//need THIS cell to be ubicode-encrypted $worksheet->setInputEncoding();//return to default $worksheet->write($i, 2, $temp_str[2]); $worksheet->write($i, 3, $temp_str[3]); $i++; } //-- that operation must be performed to any cell you want to be with unicode. hope my method will help someone
 [2007-06-13 07:20 UTC] fullstack (Romain Bossut)
Me and my collegue we have exactly the same problem. The file is corrupt if it has many characters (around 2000), or too many lines (around 200). Purs's solution doesn't work for us.
 [2007-06-15 08:49 UTC] fullstack (Romain Bossut)
We've found a solution. In fact, BIFFWriter doesn't really support the setVersion(8). Comment or delete the line setVersion(8), and use utf8_decode on all your textfields ... That's seems dirty, but I think it's the only reliable solution.
 [2007-08-23 04:44 UTC] johnnz (John Christini)
I have narrowed down the problem. At this stage still trying to figure out the fix. When using unicode and when the string table (BIFF8 0xFC STT) grows bigger than the size limit it will require a continue record (BIFF8 0x3C CONTINUE). The Excel format has some additional rules about splitting strings and these are not considered by the BIFFWrite _addContinue function. It splits the data and breaks the rules. From the Documention of the Excel File Format ... Unicode strings are split in a special way. At the beginning of each CONTINUE record the option flags byte is repeated. Only the character size flag will be set in this flags byte, the Rich-Text flag and the Far-East flag are set to zero. In each CONTINUE record it is possible that the character size changes from 8-bit characters to 16-bit characters and vice versa. !Never a Unicode string is split until and including the first character. That means, all header fields (string length,option flags, optional Rich-Text size, and optional Far-East data size) and the first character of the string have tooccur together in the leading record, or have to be moved completely into the CONTINUE record. !Formatting runs (➜2.5.1) cannot be split between their components (character index and FONT record index). If a string is split between two formatting runs, the option flags field will not be repeated in the CONTINUE record ... If you use non-unicode I guess you will avoid this problem.
 [2007-08-23 04:58 UTC] johnnz (John Christini)
Oops, further to my previous comment. I see that _calculateSharedStringSizes in ExcelWorkbook does consider the split for the continue record, but its either not yet implemented or not correctly implemented. I am still looking at that bit. Whichever way, at the moment the result is not correct.
 [2007-09-28 09:34 UTC] vtsuper (Victor Mok)
I got the same problem with the others, I believe the setVersion and setInputEncoding('utf-8') is not work all the time when I set $file->setVersion(8); $sheet->setInputEncoding('utf-8'); and the excel content with Chinese character then it works. but if the excel with only English character then it didn't works. will it has a update version soon?
 [2007-11-20 10:25 UTC] valli (Valentin Schmid)
Try out the the patch in (Miso's patch) You can also download the patched Workbook.php from
 [2008-11-24 19:00 UTC] mek7 (Matej Kurpel)
valli, thank you very much, you saved me a day and my hair, wow! :)
 [2009-04-01 13:38 UTC] tomaszrup (Tomasz Rup)
Test file: require_once "Spreadsheet/Excel/Writer.php"; class xls { /** * @var Spreadsheet_Excel_Writer */ private $xls; /** * @var Spreadsheet_Excel_Writer_Worksheet */ private $sheet; private $row = 0; private $colDateMasks; private function init($filename) { $this->xls = new Spreadsheet_Excel_Writer($filename); $this->xls->setVersion(8); $this->sheet =& $this->xls->addWorksheet('Test'); $this->sheet->setInputEncoding('iso-8859-1'); } private function createRow($cols) { for($col = 0; $col < $cols; $col++) { $content = date($this->colDateMasks[$col], strtotime('2009-01-01 00:00:00') + ($this->row + $col)); $this->sheet->writeString($this->row, $col, $content); } $this->row++; } private function finalize() { $this->xls->close(); } public function test($filename, $cols, $rows, $colDateMasks) { $this->colDateMasks = $colDateMasks; $this->init($filename); for($row = 0; $row < $rows; $row++) { $this->createRow($cols); } $this->finalize(); } } $xls = new xls; $xls->test('test1.xls', 1, 201, array('Y-m-d H:i:s')); unset($xls); $xls = new xls; $xls->test('test2.xls', 2, 137, array('Y-m-d H:i:s', 'H:i:s')); This code produce 2 files without letters. First file broken on row 201. Second on row 137. Both files have a size above 14k My configuration: Linux PHP 5.2.9 PEAR::Spreadsheet_Excel_Writer 0.9.1
 [2009-04-01 14:07 UTC] tomaszrup (Tomasz Rup)
Miso's patch work good, but binary file generated by my test and generated by OO.o 3.0.1 are different. [tomek@localhost tmp]$ hexdump test1.xls > test1.hex [tomek@localhost tmp]$ hexdump test1_miso.xls > test1_miso.hex [tomek@localhost tmp]$ diff test1.hex test1_miso.hex 62c62 < 0000560 7473 00fc 2020 00c9 0000 00c9 0000 0013 --- > 0000560 7473 00fc 2017 00c9 0000 00c9 0000 0013 575,576c575,576 < 0002570 003a 0031 0039 0013 3201 3000 3000 3900 < 0002580 2d00 3000 3100 003c 001a 0000 002d 0030 --- > 0002570 003a 0031 0039 0013 3201 3000 3c00 2300 > 0002580 0100 0030 0039 002d 0030 0031 002d 0030 776,777c776,777 < 0003260 0000 0000 3a00 c4f5 b2b5 01c9 3a00 c4f5 < 0003270 b2b5 01c9 0000 0000 0000 0000 0000 0000 --- > 0003260 0000 0000 ef00 f0c5 b2b8 01c9 ef00 f0c5 > 0003270 b2b8 01c9 0000 0000 0000 0000 0000 0000 [tomek@localhost tmp]$ hexdump test2.xls > test2.hex [tomek@localhost tmp]$ hexdump test2_miso.xls > test2_miso.hex [tomek@localhost tmp]$ diff test2.hex test2_miso.hex 62c62 < 0000560 7473 00fc 2020 0112 0000 0112 0000 0013 --- > 0000560 7473 00fc 2018 0112 0000 0112 0000 0013 575,576c575,576 < 0002570 3a00 3100 3600 0800 0100 0030 0030 003a < 0002580 0030 0032 003a 003c 0005 3100 3700 0a00 --- > 0002570 3a00 3100 3600 0800 0100 0030 0030 003c > 0002580 000d 3a01 3000 3200 3a00 3100 3700 0a00 839,840c839,840 < 0003660 0000 0000 3a00 c4f5 b2b5 01c9 3a00 c4f5 < 0003670 b2b5 01c9 0000 0000 0000 0000 0000 0000 --- > 0003660 0000 0000 ef00 f0c5 b2b8 01c9 ef00 f0c5 > 0003670 b2b8 01c9 0000 0000 0000 0000 0000 0000
 [2009-08-11 01:26 UTC] jrble819 (Justin Beeler)
I has some success using writeBlank(<row>, <col>, <format>); before rewriting to a cell. This eliminated all of my problems.
 [2009-10-06 12:00 UTC] alan_k (Alan Knowles)
-Status: Open +Status: Closed -Assigned To: +Assigned To: alan_k
This bug has been fixed in SVN. If this was a documentation problem, the fix will appear on by the end of next Sunday (CET). If this was a problem with the 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. The patch at the end of this bug has been applied Tested and working
 [2015-12-30 06:56 UTC] barbarianking (BarBarian King)