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

=HYPERLINK()

In order to make this happen, I set

setPreCalculateFormulas(false);
setOffice2003Compatibility(true);

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

Cheers

(1.7.2 on IIS7.5)

Coordinator
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 

setPreCalculateFormulas(false);

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

I also expected it to leave the 

$INFO.B$1

as such and not lower case it to

$info.b$1

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

Cheers

Edit:
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 

HYPERLINK()

without

setPreCalculateFormulas(false);
setOffice2003Compatibility(true);

Coordinator
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("http://example.microsoft.com/report/budget 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 OpenOffice.org ;-)

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

setPreCalculateFormulas(false);

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.

Thanks

Edit:
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)