Home » File Formats » Spreadsheet_Excel_Writer » Bug #1730

[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 "../inc.inc";

$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

This bug has been fixed in CVS.

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.

Temporary fix for mostly used formulas. Might break more complex ranges such as C20,D1:D20.