set percentage as cell format

[saginadav]

Hey all I want to set the format of a cell in xls to percentage. I am using phpoffice. the value that I want is 0.04% as percentage. the problem the origin $innervalue is 0.04% as string. this is my code:

$objPHPExcel     = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $objPHPExcel->getActiveSheet();

if($column == 'E') 
                        {
                            $innervalue = str_replace('%','',$innervalue); //remove the % sign for float
                            $innervalue = (float)$innervalue;              // set innervalue as float and not string
                            $sheet->setCellValueExplicit($cell, $innervalue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC); //put the inner value as number
                            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('#.##%'); // format the cell for 0.04%
                        }

the problem is that the cell displayed 4.% instead of 0.04%.

if I remove the line

`$sheet->getStyle($cell)->getNumberFormat()->setFormatCode('#.##%'); // format the cell for 0.04%`

in the xls displayed 0.04 as number, but I need it to display in the xls 0.04% as number
can someone advise how to write 0.04% as percentage in the xls

[MarkBaker]

Welcome to the way MS Excel works with percentages.

4% is 0.04 (4 parts out of 100); 0.04% is 0.0004 (0.04 parts out of 100)

So divide the value by 100, and set the format code as '0.00%

$sheet->setCellValueExplicit($cell, $innervalue / 100, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);
$sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');

You can also use the "built-in" NumberFormat::FORMAT_PERCENTAGE_00 format code for percentage with 2-decimal places

[saginadav]

thanks this what works for me:

 if($column == 'E') 
                       {
                         
                             $sheet->setCellValue($cell, $innervalue); //put the inner value as number
                             $sheet->getStyle($cell)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);

						 }