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

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

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


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

[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.

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

[2008-03-01 04:10:28] thomas

This bug can be reproduced with a trivial formula, viewed with Excel
Viewer 8.0:

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

I'm using Spreadsheet_Excel_Writer 0.9.1 on debian. The result yo see
is 0

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

[2005-11-24 08:22:25] sebab at info dot com dot pl

More details about this bug. Perl implementation of this class
generates correct XLS files. Please check following code snippets:

in PERL:

#!/usr/bin/perl -w

use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->add_worksheet();

$worksheet->write(0, 0, "AC" ,'' ); # write_string()
$worksheet->write(1, 0, "AAC" ,'' ); # write_string()
$worksheet->write(2, 0, 2 ,'' ); # write_number()
$worksheet->write(3, 0, 3.00001 ,'' ); # write_number()
$worksheet->write(4, 0, "" ,'' ); # write_blank()
$worksheet->write(5, 0, '' ,'' ); # write_blank()
$worksheet->write(6, 0, undef ,''); # write_blank()
$worksheet->write(7, 0 ,''); # write_blank()
$worksheet->write(8, 0, '=SUMIF(A1:A2,"=AC",A3:A4)','');

$workbook->close();


in PHP:
<?
require('Spreadsheet/Excel/Writer.php');
$workbook = new Spreadsheet_Excel_Writer('testphp.xls');
$worksheet =& $workbook->addWorksheet();

$worksheet->write(0, 0, "AC" ,'' ); # write_string()
$worksheet->write(1, 0, "AAC" ,'' ); # write_string()
$worksheet->write(2, 0, 2 ,'' ); # write_number()
$worksheet->write(3, 0, 3.00001 ,'' ); # write_number()
$worksheet->write(4, 0, "" ,'' ); # write_blank()
$worksheet->write(5, 0, '' ,'' ); # write_blank()
$worksheet->write(6, 0, 'undef' ,'' ); # write_blank()
$worksheet->write(7, 0 ,''); # write_blank()
$worksheet->write(8, 0, '=SUMIF(A1:A2,"=AC",A3:A4)','');

$workbook->close();

?>

Hope this'll help to find source of the problem.

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

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
postedNov 29, '09 at 1:34a
activeNov 29, '09 at 1:34a
posts1
users1
websitepear.php.net

1 user in discussion

Carsten Schmitz Hh: 1 post

People

Translate

site design / logo © 2022 Grokbase