Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.9.2

Bug #3203 Formulas not opening properly in Excel
Submitted: 2005-01-14 12:09 UTC Modified: 2009-11-29 02:34 UTC
From: dbadekas at aia dot gr Assigned:
Status: Verified Package: Spreadsheet_Excel_Writer (version 0.9.2)
PHP Version: 4.3.1 OS: irrelevant
Roadmaps: (Not assigned)    
Subscription  
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes. If this is not your bug, you can add a comment by following this link. If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: dbadekas at aia dot gr
New email:
PHP Version: Package Version: OS:

 

 [2005-01-14 12: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 15: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 15: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 15: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 10:56 UTC] xnoguer at php dot net
fearphage, can you explain to me what that formula is calculating?
 [2005-11-24 04: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 08: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 04:10 UTC] thomas (Thomas Andrews)
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
 [2008-03-19 13:43 UTC] amilling (Adam Milling)
Mine works for SUM but SUBTOTAL gives #VALUE! Work around: after the Excel is generated, manually find and replace SUBTOTAL with SUBTOTAL.
 [2008-03-22 09:52 UTC] doconnor (Daniel O'Connor)
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-09-01 04:46 UTC] mgillow (Martin Gillow)
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(); ?>
 [2009-11-29 02:34 UTC] cschmitz (Carsten Schmitz)
-Status: Open +Status: Verified -Package Version: +Package Version: 0.9.2
I can confirm this with mgillow's test script.