Description: ------------ I use Spreadsheet_Excel_Writer to pull information from our Oracle Applications Database. This works 90% of the time. However, certain php programs will only work if the dataset being retrieved is small. Past a certain point, the file receives an "[file].xls cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding". It is almost like the entire Excel file is not being generated. Could this be due to something timeing out?? I am rather new to php, so it may be something small I am missing. Kevin. Expected result: ---------------- Excel file opens with data from sql.

You are most likely hitting either memory_limit or max_execution_time limits. http://fi.php.net/manual/en/ini.php#ini.list Try to change these to a bigger value and see if that helps.

I changed max_execution_time to 600 and memory_limit to 32M and the problem persist. Do you think these values are still too low?

I have the same problem, but with small excel files. Not working example: <?php include "Spreadsheet/Excel/Writer.php"; $xls =& new Spreadsheet_Excel_Writer(); $sheet =& $xls->addWorksheet('test'); for ( $i=0;$i<100;$i++ ) // only 100 iterations { $sheet->write($i,0,$i); } $xls->send('test.xls'); $xls->close(); ?> Working example: <?php include "Spreadsheet/Excel/Writer.php"; $xls =& new Spreadsheet_Excel_Writer(); $sheet =& $xls->addWorksheet('test'); for ( $i=0;$i<500;$i++ ) // 500 iterations { $sheet->write($i,0,$i); } $xls->send('test.xls'); $xls->close(); ?> Maybe my PC is too fast? LOL (Intel Pentium D 2x 3GHz Core)

I found same problems. My environment is Windows XP, PHP5.1.2J, Pear 1.4.9, OLE 0.5, Spreadsheet_Excel_Writer-0.9.0. I could make a excel file, but I coundn't open it because of 'read only alert'. I'd like to know how to soluve this problem.

I solved a problem - excel file is unopenable... In my config in php.ini, when function-overload = 7, that problem occurred, but without that cofig, I could open an excel file.

Tried removing the function overload, and it didn't help. The funny thing is that the normal $xl->send('test.xls') also doesn't work properly; it sends a garbled file. Win2k Pro; Apache 2; PHP 5.1.4

Regarding the above comment, the garbled file was due to a space before the <?php; now even when I do a $xl->send, Excel responds with a cannot be accessed.

Has a resolution to this problem been found yet? I'm having the same issue with tiny test code. My environment is: Mac OS X 10.4.7 Apache 1.3.33 PHP 5.1.4 OLE 0.5 Spreadsheet_Excel_Writer 0.9.0 I noticed that, when run from a Terminal session, and OLE output/temp file is create that isn't when run through Apache (OLE_PPS_Filexxxxxx); could the OLE component be causing this? Thanks.

I have the same problem. The problem occurs when the file size is over 1.6 MB in my case. I had to stop using this package and use the simple codes, which cannot format the cells. I hope someone fix the problem ASAP.

This isn't solved yet, using Spreadsheet_Excel_Writer-0.9.1. This bug is old enough so that someone finally could give an answer to this :)

Seriously there is no solution to this yet? I'm copying and pasting the example from http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.intro.php and it doesn't work. WTF? The other error I keep getting is: <b>Fatal error</b>: Call to undefined method stdClass::close() in <b>/lockdown/includes/pear/Spreadsheet/Excel/Writer/Workbook.php</b> on line <b>510</b><br />

> <b>Fatal error</b>: Call to undefined method stdClass::close() in <b>/lockdown/includes/pear/Spreadsheet/Excel/Writer/Workbook.php</b> on line <b>510</b><br /> The same error i get too

Good news everyone! I discovered the cause for this bug in PHP 5. If your php.ini contains "zend.ze1_compatibility_mode = On", the spreadsheet gets corrupted. I'm not sure why it happens, it just does. Just set the value to "Off" and the xls files will be generated without problems. I'm not sure if to report this problem on the PHP page, because it's a "pear related bug". In any case I'm sure that the PEAR code can be changed to work around this problem, so it will work when zend.ze1_compatibility_mode is off. Please fix this soon! Thanks :)

erm, I meant to say that the code must be changed so it will work when compatibility mode is *On* (sorry again).

I've been spelunking the code and performing some tests (blessed be the public object members! This way i could replicate the code by copying the member functions in outside code, and doing var_export on them). This is what I found: Both the workbook and worksheet data remain identical regardless the compatibility flag. In the function Spreadsheet_Excel_Writer_Workbook::_storeOLEFile(), there is a new OLE_PPS_File(OLE::Asc2Ucs('Book')); The contents of this object after processing remain the same regardless the flag. HOWEVER... and this is where things get interesting, the following code (inside the same Spreadsheet_Excel_Writer_Workbook::_storeOleFile()) produces different output, depending on whether the compatibility flag is on or off: $root = new OLE_PPS_Root(time(), time(), array($OLE)); $res = $root->save($this->_filename); If the compatibility flag is "Off", the excel output of a sample workbook object is 3,584 bytes. When the flag is "On", the excel output is (drum rolls please...) 2,048 bytes. I tried to open the "Off" file in MS Excel, and the worksheet opened perfectly. When I tried to open the "On" file (the truncated one), I got the "cannot be accessed" error. It seems that we found the culprit. I'll report back when I find out more.

Update: There might be many inconsistencies in Root.php, so I may not be able to find them all until the first one is fixed. In function save($sFile), $this->_savePpsSetPnt($aList) has inconsistencies regarding the zend1 compatibility flag. If the flag is "Off", we have: $aList = array ( 0 => OLE_PPS_Root::__set_state(array( 'No' => 0, 'Name' => 'R\000o\000o\000t\000 \000E\000n\000t\000r\000y\000', 'Type' => 5, 'PrevPps' => 4294967295, 'NextPps' => 4294967295, 'DirPps' => 1, ... and if it's "On", we have: $aList = array ( 0 => OLE_PPS_Root::__set_state(array( 'No' => NULL, 'Name' => 'R\000o\000o\000t\000 \000E\000n\000t\000r\000y\000', 'Type' => 5, 'PrevPps' => NULL, 'NextPps' => NULL, 'DirPps' => NULL, And in PPS.php we have: function _savePpsSetPnt(&$raList) { array_push($raList, &$this); I'm going to replace that for $raList[] = &$this and see what happens.

IT WORKS!!! The file could be opened in MS Excel! So, to fix the bug... (at least the zend1 compatibility version of the bug), you have to edit OLE/PPS.php, in function _savePpsSetPnt(&$raList) and change array_push($raList, &$this); to: $raList[] = &$this; And you'll be able to save your data to Excel files even if you have "zend.ze1_compatibility_mode = On" in your php.ini. (Note: If this line was already changed in the latest version of PPS.php, then I'm a moron and you'd have to excuse me ^^;)

Final note: I reported this erroneous array_push behavior to php.net on http://bugs.php.net/42119

I am seeing this issue on FC7 php5.2.4 and it does not seem to be related to the zend comp flag. Is anyone still seeing this issue??

I am having this problem still as well. It is occurring in both win (xs pro, php 5.1.4) and in Linux (php 5.2.4 with Zend Core, Linux Red Hat Enterprise). My script used to work fine on my windows laptop. I used it to access my remote database on a linux box. After recently upgrading the Linux server to a current release of Red Hat, MySQL5 and PHP 5, it no longer works. I have upgraded my pear packages for both OLE and Spreadsheet_Excel_Writer to the most recent releases. I tried running the script on the Linux server with the appropriate modifications and get the same error. I've tried turning the zel1 from on to off and back in the ini file and no difference - I still get "Call to undefined method stdClass::close() in /usr/local/Zend/Core/share/pear/Spreadsheet/Excel/Writer/Workbook.php on line 510" (this is from Linux server, same message with different path on windows.) Has anyone found a solution to this problem? Marianne

Hi Marianne I investigated this because I had the same problem. The bug correction is the following: add one line in Workbook.php, after line 499 (in _storeWorkbook): if(count($this->_worksheets)==0) $this->addworksheet(); Try it and let me know if it works. Regards, Aurelio

Hi Aurelio, it don't works for me... PHP 5.2.4 Spreadsheet_Excel_Writer 0.9.1 Mac OS X 10.4 Excel 2004 for Mac V 11.3.7 (070724) Ati

Guys, Ive been reading all this doco, and tried all the alternative options no luck. Here is what my environment is: * Red Hat Enterprise Linux ES release 4 (Nahant Update 4) * PHP 5.2.5 * Pear packages: OLE 1.0.0RC1 beta PEAR 1.6.1 stable Spreadsheet_Excel_Writer 0.9.1 beta Im trying to export around 150,000 rows into excel. What I do is every time I reach 50,000 rows, I create a new workbook to avoid the excel row limit. My code is as follows: public function executeIndex() { require_once "Spreadsheet/Excel/Writer.php"; $rowNum = 1; $rowLimit = 10000; //total in a batch $offset = 1; $stopLimit = 150000; //this is random, sometimes it works for 150,000 rows, sometimes it doesnt $totalLoops = $stopLimit/$rowLimit; $this->logMessage("Creating spreadsheet", "debug"); $workbookNumber = 1; $workbook = new Spreadsheet_Excel_Writer('/tmp/test.xls'); for ($i=0; $i<$totalLoops;$i++){ if ($rowNum==1){ $this->logMessage("Creating initial workbook", "debug"); $worksheet = $this->writeExcelHeaders ($workbook, "wb_".$workbookNumber); }else{ if (($rowNum+$rowLimit)>50000){ $workbookNumber++; $this->logMessage("Creating new workbook sum is ".($rowNum+$rowLimit), "debug"); $worksheet = $this->writeExcelHeaders ($workbook, "wb_".$workbookNumber); $rowNum = 1; } } $this->idwUsage = $this->populateSqlQuery($offset, $rowLimit); set_time_limit(120); foreach ($this->idwUsage as $row){ $this->writeRow($worksheet,$rowNum,$row); $rowNum++; $offset++; unset($row); } unset($this->idwUsage); } $workbook->close(); return sfView::SUCCESS; } public function populateSqlQuery($offset, $rowLimit){ $c = new Criteria(); $c->setOffset($offset-1); $c->setLimit($rowLimit); $idwUsage = IdwUsagePeer::doSelect($c); return ($idwUsage); } public function writeExcelHeaders($workbook, $workbookName){ $worksheet =& $workbook->addWorksheet($workbookName); $worksheet->write(0, 0, 'account_number'); $worksheet->write(0, 1, 'service_number'); $worksheet->write(0, 2, 'user_name'); $worksheet->write(0, 3, 'bill_stmt_bill_run_num'); $worksheet->write(0, 4, 'call_start_dt'); $worksheet->write(0, 5, 'call_start_tm'); $worksheet->write(0, 6, 'call_dur'); $worksheet->write(0, 7, 'bill_call_dur'); $worksheet->write(0, 8, 'call_chrg_am'); $worksheet->write(0, 9, 'bill_rate_plan_cd'); $worksheet->write(0, 10, 'src_origtg_loc'); $worksheet->write(0, 11, 'call_termntg_no'); $worksheet->write(0, 12, 'misc_call_ds'); return ($worksheet); } public function writeRow($worksheet,$rowNum,$row){ $worksheet->write($rowNum, 0, $row->getId()); $worksheet->write($rowNum, 1, $row->getServiceNumber()); $worksheet->write($rowNum, 2, 'user_name'); $worksheet->write($rowNum, 3, $row->getBillStmtBillRunNum()); $worksheet->write($rowNum, 4, $row->getCallStartDt()); $worksheet->write($rowNum, 5, $row->getCallStartTm()); $worksheet->write($rowNum, 6, $row->getCallDur()); $worksheet->write($rowNum, 7, $row->getBillCallDir()); $worksheet->write($rowNum, 8, $row->getCallChargeAm()); $worksheet->write($rowNum, 9, $row->getBillRatePlanCd()); $worksheet->write($rowNum, 10, $row->getSrcOrigtgLoc()); $worksheet->write($rowNum, 11, $row->getCallTermntgNo()); $worksheet->write($rowNum, 12, $row->getMiscCallDs()); } The output I have is totally RANDOM, sometimes it works fine, sometimes it doesnt. Usually If I reduce the number of rows per worksheet, and reduce the total number of rows to < 100k, it seems to work fine. Sometimes it doesnt, most of the time it does. I keep getting the "file cannot be accessed. File may be read-only" errors - and the excel file is corrupt.

I should also note that "zend.ze1_compatibility_mode = Off" Its off, and always been off in my php.ini

I've been working on this a bit further, and here is what I've observed. * It's got nothing to do with the total amount of workbooks. I tried writing 5,000 rows over 40+ workbooks - and that was not an issue * My main issues when when I am trying to export over 120,000 rows across several workbooks (with around 50,000 rows per workbook) - it always produces a corrupt file. * I can't find a way to span 120,000 rows in the same spreadsheet across multiple workbooks. I've reduced the total number of rows per workbook - and even tried to reduce the amount of rows written in every batch. Still no luck. * I have NO issues by writing a NEW spreadsheet every 50,000 rows. It produces, 5, 6 files - but there are no issues with each file. The problem seems to be with writing a large amount of data within the same spreadsheet. The problem does NOT occur when you are working with <100,000 rows, and randomly occurs over 100,000 rows. The total number of rows per workbook does NOT affect the corruption of the file. Hope this helps in whoever is trying to analyse the issue...

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.

In my case, nothing solved the problem, but setting all magic quotes directives in php.ini to FALSE. I was using version 0.9.2, wich was said to have all bugs regarding this problem fixed. It just wasn't fixed for me. If any of you is facing this problem, try setting your magic quotes directives OFF.

It looks like the issue has not been fixed yet. I'm using Spreadsheet_Excel_Writer 0.9.2 beta. I believe this issue if somehow related to PHP configuration, because we have two servers and it works fine for one of them but does not work for another one server. PEAR, PHP and Apache are the same version.

Correction to my previous message: My problem with "File cannot be accessed" was related to another PHP bug: http://bugs.php.net/bug.php?id=48697 Please ignore my previous message in the context of this issue.

I had problems described above. Reason was that there was no access to temp directory of Spreadsheet_Excel_Writer. I created new directory, set 0777 rights and after $xls =& new Spreadsheet_Excel_Writer(); added $xls->setTempDir($_SERVER['DOCUMENT_ROOT'].'/tmp_xls/'); where tmp_xls is new temp dir. Also before creating Spreadsheet_Excel_Writer instance i cleaned files older than yesterday in tmp_xls.