| Package home | Report new bug | New search | Development Roadmap | Status: Open | Feedback | All | Closed Since Version 0.9.4 |
| Bug #3203 | Formulas not opening properly in Excel | ||
|---|---|---|---|
| Submitted: | 2005-01-14 17:09 UTC | ||
| From: | dbadekas at aia dot gr | Assigned: | progi1984 |
| Status: | No Feedback | Package: | Spreadsheet_Excel_Writer (version 0.9.2) |
| PHP Version: | 4.3.1 | OS: | irrelevant |
| Roadmaps: | (Not assigned) | ||
[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
Comments
[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.
[2008-03-01 09:10 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2008-03-19 17:43 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2008-03-22 13:52 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2008-09-01 09:46 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2009-11-29 07:34 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2010-02-10 04:28 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2010-04-06 16:22 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2012-01-27 19:21 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!