Format number and conditionnal type at the same time

[phpoffice]

Format number and conditionnal type at the same time

[Progi1984]

Which writer do you use ?

[ajouno]

I instanciate the PHPExcel_Writer_Excel2007 writer

[MarkBaker]

This was certainly true for 1.7.7, but I've already fixed this issue on the develop branch here on github.

Using the following code to try and replicate your problem:

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', '0.25');

$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00
    );


$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
    ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN)
    ->addCondition('0');
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

$objConditional2 = new PHPExcel_Style_Conditional();
$objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
    ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL)
    ->addCondition('0');
$objConditional2->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_GREEN);

$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('A1')->getConditionalStyles();
array_push($conditionalStyles, $objConditional1);
array_push($conditionalStyles, $objConditional2);
$objPHPExcel->getActiveSheet()->getStyle('A1')->setConditionalStyles($conditionalStyles);


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

This works correctly: both the conditional background colour and the number format styles are maintained when the spreadsheet is saved. As I'm unable to replicate this issue, can you please upload a sample code that demonstrates your problem to the phpexcel.codeplex.com site.

[ajouno]

Hi Mark, sorry about the long silence, my boss need my help on other work...
Anyway, i'm back for this issue.

I try your code but i get an error : Class PHPExcelIOFactory not found.
I download the last commit on dev branch but i can't find any classe named PHPExcelIOFactory.

Where do i lost myself ?

When i use 1.7.7 writer style call :

$writer = new PHPExcel_Writer_Excel2007($objPHPExcel);
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:inline;filename=Fichier.xlsx ');
$writer->save('php://output');

I still get the original issue. bug seems to be fix on the writer.

Regards,
ajouno

[MarkBaker]

The PHPExcel_IOFactory class is in the file IOFactory.php in the /Classes/PHPExcel directory.... if your script can't find that, then something is very, very wrong indeed... like the PHPExcel code is missing or not defined in the path (though I'd expect PHPExcel.php to be problematic as well if that was the case)

[ajouno]

oh crap...
I just copy your code without my brain...

I copy from the email which contain :
$objWriter = PHPExcelIOFactory::createWriter($objPHPExcel, 'Excel2007');

and not this :

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

"_" change all...

So i can try your code.
Thanks

[ajouno]

I try your code and get the same issue as before.

A1 get a green color font with 0,25 in cell
when i select A1, the value in fx box is 25%

To get A1 with 25% display i need to get in conditionnal cell
edit the rule to add pourcent format with color font condition.

I can send you the xls file if you want.
Maybe an issue with european office 2007 ?

[ajouno]

I've done !

// ############################ this don't work ############################
// style define
$conditionOK = array( 'numberFormat' => array( 'code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00),
'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => '00FF00'),
'endcolor' => array('rgb' => '00FF00') ));

// style apply
$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$objConditional1->addCondition('0');
$objConditional1->getStyle()->applyFromArray($conditionNOK);
// ############################ this don't work ############################

// ############################ this work ################################
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN)->addCondition('0');
$objConditional1->getStyle()->getFill()->getStartColor()->setRGB('FF0000');
$objConditional1->getStyle()->getFill()->getEndColor()->setRGB('FF0000');
$objConditional1->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objConditional1->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
// ############################ this work ################################

issu seems to come from "applyFromArray" method.
Thanks for your attention.

Regards,
Aymeric

[aweizd]

Hi,

I have the same problem with 1.7.9 and the git develop branch. Tested with my code as well as Mark's code from above.

Basically, if you don't explicitly specify a number format in the conditional style array PHPExcel will always use "General". That means all cells that match the rule will be formated as General independent of what the original cell format is. Ajouno's solution of explicitly setting the format isn't really an option for me, because my conditional format spans multiple columns with different number formats.

As far as I can tell the problem is in Style/NumberFormat.php lines 202-214: public function setFormatCode, which sets the number format to "General" if none is specified.

I tested it by commenting line 205 and the result is what I expected. If no format is specified then no format is applied and all cells preserve their original formating. But this does cause some PHP warnings down the line, specifically in the function getHashCode() line 366 of the same file.

EDIT: I just realized I also had to explicitly set the conditional number format to null, otherwise it doesn't work.

I hope this help you fix this issue.