FAQ
Edit report at http://pear.php.net/bugs/bug.php?id=3203&edit=1

ID: 3203
Updated by: progi1984@gmail.com
Reported By: dbadekas at aia dot gr
Summary: Formulas not opening properly in Excel
-Status: Verified
+Status: Feedback
Type: Bug
Package: Spreadsheet_Excel_Writer
Operating System: irrelevant
Package Version: 0.9.2
PHP Version: 4.3.1
-Assigned To:
+Assigned To: progi1984
Roadmap Versions:
New Comment:

-Status: Verified
+Status: Feedback
-Assigned To:
+Assigned To: progi1984
Thank you for taking the time to report a problem with the package.
This problem may have been already fixed by a previous change that
is in the SVN of the package. Please checking out the SVN
repository of this package and upgrade

svn checkout
svn.php.net/repository/pear/packages/Spreadsheet_Excel_Writer/trunk
pear upgrade package2.xml

or

pear upgrade package.xml

If you are able to reproduce the bug with the latest SVN,
please change the status back to "Open".
Again, thank you for your continued support of PEAR.




Previous Comments:
------------------------------------------------------------------------

[2010-02-09 23:28:45] dcc24

These type of formulas are called "array formulas" or "cse formulas" in
Excel. An array formula is a formula that works with an array, or
series, of data values rather than a single data value. To enter a
formula as an array formula, you have to type the formula in the cell
and press the CTRL SHIFT and ENTER keys at the same time rather then
just ENTER (hence CSE).

I cannot find a way in Excel even with VBA to declare a formula or the
contents of a cell as an array formula unless you press CTRL SHIFT
ENTER.

I think we are out of luck in getting these fixed as MS didn't create a
way other than CTRL SHIFT ENTER to declare an array formula.

http://office.microsoft.com/en-us/excel/ha010872901033.aspx

------------------------------------------------------------------------

[2009-11-29 02:34:06] cschmitz

-Status: Open
+Status: Verified
-Package Version:
+Package Version: 0.9.2
I can confirm this with mgillow's test script.

------------------------------------------------------------------------

[2008-09-01 05:46:54] mgillow

I have this issue where I'm trying to SUM a selection of cells where
one or more has a string value.
If I use the =SUM(A1:C1) notation, it works fine, but using this one
=SUM(A1,B1,C1) gives the #VALUE error until you re-write it in Excel at
which point it works fine.

My test case is as follows:

<?
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$worksheet =& $workbook->addWorksheet("test");

$worksheet->write(0, 0, 1);
$worksheet->write(0, 1, A);
$worksheet->write(0, 2, 2);

$worksheet->writeFormula(0, 3, "=SUM(A1:C1)"); // Works OK
$worksheet->writeFormula(0, 3, "=SUM(A1,B1,C1)"); // Gives #Value

$workbook->send('test.xls');
$workbook->close();
?>

------------------------------------------------------------------------

[2008-03-22 09:52:25] doconnor

Works for me with OpenOffice Calc (2.3.1)

function xls_test()
{
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('blah.xls');
$worksheet =& $workbook->addWorksheet('whatever');
$worksheet->write(0, 0, 3);
$worksheet->write(1, 0, 4);
$worksheet->writeformula(2, 0, "=SUM(A1:A2)");
$workbook->close();
}

------------------------------------------------------------------------

[2008-03-19 13:43:00] amilling

Mine works for SUM but SUBTOTAL gives #VALUE!

Work around: after the Excel is generated, manually find and replace
SUBTOTAL with SUBTOTAL.

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://pear.php.net/bugs/bug.php?id=3203

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedApr 6, '10 at 11:28a
activeApr 6, '10 at 11:28a
posts1
users1
websitepear.php.net

1 user in discussion

Progi1984: 1 post

People

Translate

site design / logo © 2022 Grokbase