Cell Formatted as Percentage

Topics: Developer Forum, User Forum
Aug 31, 2010 at 10:34 AM

I am new to PHPExcel using it to display a spreadsheet as a web page where visitors can enter values and calculate the results.

Everything seems to be working OK except for calculations including cells formatted as percentages.

For example, if I enter '4' in a cell formatted as a percentage, the real value that should be used in a calculation is 0.04 but 4 is being used instead.

What do I need to do for PHPExcel to handle percentage formatted cells properly?

The spreadsheet is at: http://tinyurl.com/22udfl3

The web page is here: http://tinyurl.com/2f6qlwg

I have searched the discussions and the web and have not been able to find an answer so hopefully someone here can help me!



Sep 1, 2010 at 1:08 PM

Within Excel, when you enter 4 in a cell (A2), then format it as a percentage, the cell still contains the value 4 rather than 400%. If you use that cell value in a calculation =A1*A2 where A1 contains 10, your answer will be 40. If you have a cell that is formatted as a percentage, and enter a 4 in that cell, Excel automatically divides the number that you have entered by 4, so your cell will contain 0.04 instead of the 4 that you entered.

This behaviour can be emulated in PHPExcel if you use a value binder. I've not tested this, but something like:

class PHPExcel_Cell_AdvancedValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
   public function bindValue(PHPExcel_Cell $cell, $value = null)
      // Find out data type
      $dataType = parent::dataTypeForValue($value);
      if ($dataType == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         if ($cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat() == PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE ) {
            $cell->setValueExplicit( $value / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
            return true;

      // Not bound yet? Use parent...
      return parent::bindValue($cell, $value);

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel = new PHPExcel();

Note that using the AdvancedValueBinder provided in the distribution would allow you to set a cell value to a string '4%', and would automatically convert this to 0.04 and apply percentage formatting to that cell.


Sep 1, 2010 at 2:24 PM

Hi Mark!

Thanks for the prompt and useful response!

I was aware of the value binder from the documentation included in the PHPExcel download but there's no detailed example as you have shown!

I'll give it a try again,