When I ouput a formula, it is behaving like text

Aug 13, 2008 at 4:37 PM
Edited Aug 13, 2008 at 4:41 PM
When I enter a certain formula, i.e. =Now(), in a cell when creating a spreadsheet using PHPExcel, it is displayed as text instead of correctly acting like a formula and returning a value.  This seems to be related to some obnoxious behavior of Excel, whereby it sometimes will display the text of a formula instead of the calculated results of a formula--probably because the cell itself is being interpreted as text.  For some background on this problem, see http://www.mrexcel.com/archive2/17600/19935.htm

Now, for the problem at hand in PHPExcel.  The Now() formula will display as text depending on the formatting applied to it.  If it is set to "general" formatting, then it displays a number.  If it is set to any of the date formats, or if no formatting is applied, it displays the formula as text:
    //gives me the string "=Now()" in the cell
    $excel->getActiveSheet()->setCellValueByColumnAndRow(5,7, '=Now()');
    $excel->getActiveSheet()->getStyleByColumnAndRow(5,7)->getNumberFormat()->setFormatCode('m/d/yyyy h:mm');

    //also gives me the string "=Now()" in the cell
    $excel->getActiveSheet()->setCellValueByColumnAndRow(5,7, '=Now()');

    //gives me a formula in the cell with the value 39673.44 being returned (i.e. the current timestamp I believe)    
    $excel->getActiveSheet()->setCellValueByColumnAndRow(5,7, '=Now()');

If in either of the first two examples after the spreadsheet is generated, I place my mouse in the cell as if I were going to edit the formula, and then simply hit "ENTER" as if I had edited it (but I don't change anything), then Excel seems to get itsself straigtened out, and it finally correctly interprets the cell as a formula, and displays it correctly with the formatting I had set. 

I have tried to explicity set the type of the cell, using the following:

    $excel->getActiveSheet()->getCellByColumnAndRow(5,7)->setValueExplicit('=Now()', PHPExcel_Cell_DataType::TYPE_FORMULA);

but this does not seem to help. 

I am using the excel5 writer, and the latest version 1.6.2 of PHPExcel.

Is there something in PHPExcel that is not outputting the formula as a formula, but as text instead?  Is the setValueExplicit not really tellling Excel what the cell data type is?  Is this an unavoidable consequence of Excel not keeping track of what is a formula and what is text correctly?
Aug 15, 2008 at 12:53 AM
I tried out your script, and I couldn't get it working either. Although, I'm not into this portion of the code, I'm not convinced that it has to do with your formula. Strange things seem to be happening even with direct entries. I have addressed the problem in the issue tracker:


Aug 15, 2008 at 2:10 AM
Thanks for looking into this.  Since it appears to be a bug, I'll do some work arounds and track the issue over time for (hopefully) a fix at some point in the future.
Oct 20, 2008 at 2:37 AM
Update: Now fixed
Feb 23, 2009 at 4:13 PM
i want the same thing i.e. want to apply "Text" format rather than "General" format
not for any single cell but for whole sheet
if i apply it cell by cell the page dies out of timeout
any thing possible here?