setPreCalculateFormulas(false) seems broken

Topics: Developer Forum, User Forum
Jun 8, 2010 at 9:23 AM
Edited Jun 9, 2010 at 1:23 AM

Hi folks,

Just run into some strange problem, trying to use


In order to make this happen, I set


but then these two formulars still get broken

$objPHPExcel->getActiveSheet()->setCellValue('F'.$i, '=HYPERLINK($INFO.B$1&D'.$i.'&""""&D6&"""" ; "Art")');
$objPHPExcel->getActiveSheet()->setCellValue('G'.$i, '=HYPERLINK($INFO.D$1&D'.$i.' ; "ArtS")');

They show up in Excel and OO as

=HYPERLINK($info.b$1&D2&""""&D6&"""" | "Art")
=HYPERLINK($info.d$1&D2 | "ArtS")

which breaks the formular, of course, as ; is swaped for |

Any chance this can be fixed?

Thanks for the good work & and any help regarding this


(1.7.2 on IIS7.5)

Jun 8, 2010 at 9:55 AM

Formulae should be defined using English format, so ; isn't valid as a separator because English Excel uses a , instead

Otherwise, that's exactly what I'd expect to see from your formulae. What were you expecting?

Jun 9, 2010 at 2:06 AM
Edited Jun 9, 2010 at 2:51 AM

Thanks Mark,

I was thinking 


would leave the formula alone altogether—wrong assumption on my part, I guess.

I also expected it to leave the 


as such and not lower case it to


which I can fix if I have to, but would break our naming convention for this workbook.


Just read the new relase notes:

- Feature:  (MBaker) Work Item  2346 - Implement more Excel calculation functions
            -    Implemented the HYPERLINK() Lookup and Reference function

Does that mean I can use 




Jun 9, 2010 at 8:49 AM

I'm not completely sure what $INFO is in your formulae because it doesn't match anything I know about Excel formulae.


But yes, it should be possible for you to use a formula like =HYPERLINK(" report.xls", "Click for report") to quote an example from the online help without worrying about setPreCalculateFormulas() and setOffice2003Compatibility();


Jun 10, 2010 at 7:44 AM
Edited Jun 10, 2010 at 10:02 AM

Yes, things are a bit different in ;-)

OO=HYPERLINK($INFO.B$1"&D15&""""&D6&""""  ,"CL")
MS=HYPERLINK(INFO!B$1&""""&D6&"""", "CL-MS")

So it's just a Ref to Work Sheet INFO.

As we only work with OO, I tend to do it their way and never had any real problems.
And as said—wrong assumption on my part


would leave the formula alone altogether.

So anyway, upgraded to 1.7.3 and HYPERLINK() works like charm without setPreCalculateFormulas() and setOffice2003Compatibility();
Just not in Excel5, but that was to be expected.


I'll have to take that back "works like charm without setPreCalculateFormulas()"

This is only true for OO. When I went to a machine with MS Excel 2007 I got heaps of problems, not using setPreCalculateFormulas(false)