Cell Formatted as Percentage

Topics: Developer Forum, User Forum
Aug 31, 2010 at 9: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!

Thanks,

Neil.

Coordinator
Sep 1, 2010 at 12: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 1: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,

Thanks,

Neil.