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