PHPExcel & getCalculatedValue with Linux

Topics: Developer Forum, User Forum
Aug 20, 2008 at 7:50 AM
Edited Aug 22, 2008 at 4:33 PM
OS : Ubuntu Linux 8.04
PHPExcel : 1.6.2

I have switched from a windows apache server to a linux apache server (Wamp to Lamp). After several permission problem that i have met and resolved to configure my personal network website, I have encounter a problem with PHPExcel. When my webserver was under windows, the resulting Excel file appeared with all the calculated value cells. when I wrote something like :

$objPHPExcel->getActiveSheet()->setCellValue('B8', '=SUM(B2:C5)');


I didn't need to specify anything else to see the cell with the calculated value, even without the 'getCalculatedValue' function.
Now even if I am on a windows terminal and require the Excel file from the Linux webserver, the resulting excel file appear properly but the cells with formula are not calculated.
I have so added

$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();


But nothing has changed... The formula still written and is not caculated

Does anyone has an idea to help ?

Developer
Aug 21, 2008 at 12:26 AM
Hmm... I have no idea what the problem is. What component are you using? Excel2007 writer or Excel5 writer?
Aug 21, 2008 at 8:21 AM
Edited Aug 21, 2008 at 8:21 AM
Thanx to reply koyama,

I am using Excel5 writer since I don't have Office 2007. Do you think the getCalculatedValue doesn't work with Excel5 writer ? When my webserver was under Windows, i didn't even need to specify the cell calculation. When the Excel was built, it simply deliver it with everything calculated and not visible formulas.

Since I still have the Windows webserver, i have tried to get the Excel report from the Linux computer (I have so 2 identical web portals : one under Linux and another one under Windows, the current official one). Everything works : The cells are calculated without even specify it in the code.


So to resume :
- From Windows Webserver ------> to Linux or Windows workstation, cell calculation works well even if i don't mention the getCalculatedValue function
- From Linux Webserver ------> to Windows or Linux workstation, cell calculation doesn't work even if I specify the getCalculatedValue function

I really need some lights on it, because that's the real important feature that I use with PHPExcel...
Developer
Aug 21, 2008 at 9:17 AM
Edited Aug 21, 2008 at 9:18 AM
I don't think I have the answer to this, but others may know. Is it a complex spreadsheet?

Can you please try out as simple test with the absolute simplest example and see if there are still no formulas showing up?

$PHPExcel = new PHPExcel();
$ws = $PHPExcel->getActiveSheet();
$ws->getCell('A1')->setValue('1');
$ws->getCell('A2')->setValue('2');
$ws->getCell('A3')->setValue('=SUM(A1:A2)');
$writer = new PHPExcel_Writer_Excel5($PHPExcel);
$writer->save('test.xls');

Please try with both the Excel2007 writer and the Excel5 writer and see if there are differences.

This thread could be related to this recent one:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=33773

Aug 22, 2008 at 4:50 PM
Edited Aug 22, 2008 at 4:52 PM
Your simple example works !... Why didn't I try the simple way before the hard way?...
I realized that the calculation is not the problem. The real problem seems to come from a style line for the cell that follow the calculation.

$ws->getStyle('B8')->getNumberFormat()->setFormatCode('[h]"h"mm"m"');


My purpose is to apply a style such like "328h32m" on the cell. Again from windows server, no problem; only from Linux server. I have tried to remove this format line and then the calculation works... Funny to notice the interpretations differences between Linux and Windows. Without the format line, windows workstations doesn't interpret the result at all ("16.6666..."); Linux (or OpenOffice) decides that it's a time cell and display "328:32:26". Again that's not what I want but the last one is better, even if he decides without me...

So my problem still, because i really need to be sure of the format cell whatever the OS. I really don't see what could be the problem with the setFormatCode function... Did I make any mistake ?

Thanx koyama for your help.
And... if somebody else has something interesting to tell, please help me ! :)