Formula changes when the Workbook is in edit mode

Nov 27, 2010 at 1:21 PM
Edited Nov 27, 2010 at 1:39 PM


I have a peculiar problem. I am using PHPEXCEL 1.7.4 and I am writing a xls file. I have set the formula for a cell as mentioned below:

$objWorksheet->setCellValue('G19', '="Revenue "& ROUND(K20/J21*100,0)&"%"');

Now when I open the workbook, it is open in protected mode with an "Enable Editing" button displayed on top of the workbook in excel. And the value for G19 is displayed as follows:

Revenue 7%

When I click on the "Enable Editing" button to enable edit mode, the formula for the cell is displayed as "Revenue " only. All other cells in the workbook remain intact.

Please let me know how to specify the formula when it has a combination of text and calculation so that it does not change in edit mode as well.

Thanks in advance,


Nov 27, 2010 at 9:48 PM

I assume that you're using the Excel5 Writer. Work Item 7895 lists a number of operators and features of Excel formulae that are not yet supported within the PHPExcel codebase. PHPExcel can correctly calculate the result of this formula, so the correct result is stored in the saved xls file; but the actual formula cannot be written correctly to the saved xls file. This includes the & (concatenation) operator, which appears in your formula.

There are two options available to you:

  1. The latest SVN code does now support the & operator within the Excel5 Writer, and you can download that from the "Latest Version" box to the right of the "Source Code" tab screen.
  2. You can use the Excel CONCATENATE() function within your formula instead of the & operator.