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

Bug #1730 Wrong operand class used for A1:A2
Submitted: 2004-06-28 05:43 UTC
From: bleister at au dot ibm dot com Assigned: tuupola
Status: Closed Package: Spreadsheet_Excel_Writer
PHP Version: 4.2.2 OS: Linux
Roadmaps: (Not assigned)    

 [2004-06-28 05:43 UTC] bleister at au dot ibm dot com
Description: ------------ I used =SUBTOTAL(9,A1:A2) in formula, got #Value! error on opening generated XLS, text of formula was OK, and when I pressed enter on the formula in Excel it was accepted OK. I then did a sample XLS, one formula as generated by PEAR package, and another corrected in Excel, and viewed the file using BIFFviewer, noticed the PHP token for the "A1:A2" was x65 whereas Excel used x25 - I changed th line in parser.php $class = 2; to $class = 0; and it now generates SUBTOTAL formula correctly! Reproduce code: --------------- <?php require_once 'Excel/Writer.php'; include "../"; $wb = new Spreadsheet_Excel_Writer(); $wb->send('subtot.xls'); $ws =& $wb->addWorksheet('sub total'); for ($i=1; $i<= 5; $i++) { $ws->write($i-1,0,$i); $ws->write($i-1,1,$i*2); } $ws->writeFormula(5,0,'=SUBTOTAL(9,A1:A5)'); $ws->writeFormula(5,1,'=SUBTOTAL(9,B1:B5)'); $wb->close(); ?>


 [2005-03-25 12:45 UTC] markadasilva at yahoo dot com dot au
I hate messing with things I don't understand. However, I can confirm that the fix mentioned above $class = 0; also worked for me on a similar problem formula syntax waqs correct but excel outputs VALUE error. Here is my code: $worksheetScores->writeFormula($row, $col, "=RANK({$xlsC}2, B2:{$endCol}2)"); Example Output: =RANK(B2,B2:W2)
 [2005-06-10 21:55 UTC] j dot val at hccnet dot nl
I did change $class to zero whichsolved my problem for writeFormula(22,2,"=COUNTIF(C1:C20,1)"); But for writeFormula(23,2,"=CORREL(C1:C20,D1:D20)"); I had to swirch it back to $class=2; I do not understand which token I do get in the function. Somehow their must be a test whether one or more ranges should be used. All the best John Val
 [2006-10-06 08:00 UTC] tuupola at php dot net (Mika Tuupola)
This bug has been fixed in CVS. 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. Temporary fix for mostly used formulas. Might break more complex ranges such as C20,D1:D20.