Error with conditional formatting if document opened in Microsoft Office

Question

Error with conditional formatting if document opened in Microsoft Office

Navigation

#1 by (3 votes)

3

When applying conditional formatting to a% spreadsheet generated with PHPExcel , it works without problems when the document is open at LibreOffice :

$objConditional = new PHPExcel_Style_Conditional();

$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
               ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL)
               ->addCondition('='.$index_min_cell);

$objConditional->getStyle()->getFont()->getColor()->setRGB('FFFFFF');
$objConditional->getStyle()->getFont()->setSize(8);
$objConditional->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()->setRGB('5c8526');
$objConditional->getStyle()->getNumberFormat()->setFormatCode("[$$-409]#,##0.00;-[$$-409]#,##0.00");


$explode=explode(',', $condition_min_med);

for ($m=0; $m<count($explode); $m++) {
    $sheet_00->getStyle($explode[$m])->setConditionalStyles(array($objConditional));
}

But when the document is opened using Microsoft Office, it has the following information-rich error:

Functionality Removed: Conditional formatting of the /xl/worksheets/sheet1.xml part

With Link to an XML with the following information:

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error033680_04.xml</logFileName>
  <summary>Foram detectados erros no ficheiro 'C:\Users\JohnDoe\AppData\Local\Temp\super_bubu.xlsx'</summary>
  <removedFeatures summary="Segue-se uma lista de funcionalidades removidas:">
    <removedFeature>Funcionalidade Removida: Formatação condicional da parte /xl/worksheets/sheet1.xml</removedFeature>
  </removedFeatures>
</recoveryLog>

Question

Since the information regarding the error only indicates that the functionality has been removed, and taking into account that the document when opened in LibreOffice works without problems using the conditional formatting applied:

For any reason Microsoft Office Excel 2010 ignores the conditional formatting that is being applied?

Note:
This is part of a very large file that tries to generate the entire spreadsheet, but this particular part, if removed, causes Excel to stop displaying the error ... Of course, conditional formatting is not there!

php phpexcel

asked by anonymous 07.01.2014 / 23:17

1 answer

Trigger UPDATE write TXT file Text input text box on the right

score 3 · Accepted Answer

Apparently, for Microsoft Office Excel, when defining the type of operator, we can not indicate the same operator in the condition:

Where is:

$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
               ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL)
               ->addCondition('='.$index_min_cell);

Change to:

$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
               ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL)
               ->addCondition($index_min_cell);

That is, as we already defined through setOperatorType() the desired operator:

->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL)

We should not apply the same in the condition through addCondition() :

->addCondition('='.$index_min_cell);

Only the required condition should be left:

->addCondition($index_min_cell);

As I understand it, LibreOffice, when it finds the type of operator OPERATOR_EQUAL and the condition preceded by the = operator, leaves only a = in the construction of the condition.

Microsoft Office Excel, I suspect you're trying to type ==condição , and by not interpreting this, it bursts whole and ignores conditional formatting altogether.