Formatting a cell to a percentage in PHPExcel

Question

I am automating an excel spreadsheet using PHP.

I have been looking for a way to pragmatically format a cell to a percentage in PHPExcel.

I want to change a value like

0.077922078

to

8%

Is there a solution for this?

Thanks in advance.

you can use ceil() to round to closest number and than append % before putting it in ur excel file..http://php.net/manual/en/function.ceil.php — Dinesh, May 07 '13 at 21:39

score 45 · Accepted Answer · answered May 07 '13 at 21:43

45

assuming your cell is A1 ..

$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getNumberFormat()->applyFromArray( 
        array( 
            'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00
        )
    );

answered May 07 '13 at 21:43

3

I guess that to get a straight percentage i.e. 2% i need to remove the _00 form FORMAT_PERCENTAGE_00 ? – cwiggo May 07 '13 at 21:55
3

Use PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE to display as an integer percentage; of "0.0%" or "0.000%" etc to display one or three decimal places.... or indeed any string that corresponds to a valid Excel number format mask – Mark Baker May 08 '13 at 06:29
2

taking off `_00` is the key!!! I tried it and it works like charm! Thank you @Chris – mongotop Jan 13 '14 at 22:42
good to see my questions being used :D good luck! thanks for the appreciation! – cwiggo Jan 14 '14 at 13:32

score 3 · Answer 2 · answered Aug 28 '16 at 13:53

PHPExcel library has predefined only few basic formatting constants. You can actually build your own for virtually any purpose (coloring, formatting decimals & thousands etc). Formatting capabilities in Excel are huge. Following will format percent with 3 decimal places and coloring negative values to red:

$workSheet
    ->getStyleByColumnAndRow($column, $row)
    ->getNumberFormat()
    ->setFormatCode('0.000%;[Red]-0.000%');

simhumileco · Answer 3 · 2017-07-19T13:17:19.970

0

You can try this code:

$colLetter = "A";
$rowNumber = "1";

$objPHPExcel->getActiveSheet()
    ->getStyle("$colLetter:$rowNumber")
    ->getNumberFormat()
    ->applyFromArray([
        "code" => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
    ]);

edited Jul 19 '17 at 13:17

answered Jul 19 '17 at 10:20

simhumileco

31,877
16
137
115

You should add an explanation of what your answer adds to the other. – gp_sflover Jul 19 '17 at 12:20
Yes of course you have right @gp_sflover, but I thought this piece of code was self-describing :) – simhumileco Jul 19 '17 at 13:21

score 0 · Answer 4 · answered Jul 09 '21 at 13:37

Just in case someone else is seeing this after PHPExcel turns to PHPSpreadsheet.

PHPSpreadsheet have a dedicated function to apply format codes. So, to set a number as percentage now we need to replace applyFromArray() to setFormatCode() as follow:

$spreadsheet
   ->getActiveSheet()
   ->getStyle("A1")
   ->getNumberFormat()
   ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);

Note: setFormatCode() seems to not support to apply to a entire column/row. So, if it is your case, you will need to create a loop over column/row to apply for each cell individually.

setFormatCode is a function in PHPExcel. The applyFromArray is used to set multiple styles as once. Also, you can uase "A1:E10" for the cell ID to set a format to multiple cells. — kainaw, Jul 15 '21 at 16:55

Formatting a cell to a percentage in PHPExcel

4 Answers4

Linked