Home » File Formats » Spreadsheet_Excel_Writer » Bug #3203

[2005-01-14 17:09 UTC] dbadekas at aia dot gr

Description:
------------
The formulas produced by Spreadsheet_Excel_Writer are not produced when opened with Excel(2000). OpenOffice(1.1/1.0) has no problem and displays the formula values as expected.

Excel opens by displayin the #VALUE in place of the formulas.
The formulas are correct, because:
1) OOffice and Gnumeric open correctly
2) In Excel, when you click on the formlula in the edit field and hit return, then the formula pops a value ( which is correct)
3) Improting the file to OOffice and re-exporting to excel corrects this problem.

I will include a sample file later

[2005-01-24 20:48 UTC] rangi dot biddle at vlc dot twoa dot ac dot nz

I can also confirm that this same issue arises with Office 2003.

[2005-03-22 20:36 UTC] fearphage at gmail dot com

I don't see #VALUE displayed in Excel 2003. I see a 0 displayed. If I put the formula in edit mode and then press ctrl+shift+enter then the formula is evaluated properly. Is there any short-term workaround for this even?

[2005-03-22 20:40 UTC] fearphage at gmail dot com

This doesn't happen with simple formulas [ie. writeFormula(0,0,"=SUM(C2:C10)")]. When the formulas get more complex, the evaluation of these formulas stops and returns 0.

Example of an 'advanced' formula:

writeFormula(1,1,"=SUM(IF('data'!D2:D5000=\"Fearphage\",IF('data'!G2:G5000=23,1,0),0))")

[2005-11-12 15:56 UTC] xnoguer at php dot net

fearphage, can you explain to me what that formula is calculating?

[2005-11-24 09:46 UTC] sebab at info dot com dot pl

I also can confirm that problem. My formula causing #VALUE! is:

=IF(AND(Q18=0;Q21=0);0;Q21/(SUMIF(K19:P19;">0";K21:P21)+SUMIF(K19:P19;"<1";K18:P18)))

or even in much simpler shape:
=SUMIF(K19:P19;">0";K21:P21)

Effect is the same: #VALUE! after sheet opened and correct result after edit and return without changing anything.

[2005-11-24 13:22 UTC] 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.