conditional formatting not working

Question

(am using PHPExcel 1.7.8)

I create a sheet and populate it with a table of numeric data. Then Excel refuses to apply conditional formatting of the 3-color-transition type to the numeric data.

That means, too:

1) I can use conditional formatting of the "top 10 values" kind

2) If I populate right next to the generated table few cells with numbers then I can use the cond formatting of transitional kind

3) If I copy two cells from the generated table somewhere else in a simple copy/paste fashion still can't use the transitional formatting

4) If I copy two cells from the generated table somewhere else using the "just the value"-pasting I do can use the transitional formatting

Another important observation here is, that when I select a cell of the generated table and click into the value bar - right at that moment - the cell changes its color to what it should be regarding the conditional formatting!

This is somewhat similar to another phenomena I observed with PHPExcel generated spreadsheets. Sometimes it happens that, when I double click into a cell to get into editing mode - the cell turns pit black. But still I can change the value.

There seems to be something wrong with how a cell is represented in the Excel-file I guess. Something related to the control of the coloring ... !?

I could of course copy/(value-)paste everything. But maybe I am just using PHPExcel the wrong way? Or there is a quick way to convert an Excel file at once in a useful way?

The full code I use is this:

$excelWorkbook = null;

    if(file_exists($filename)) {
        $reader = PHPExcel_IOFactory::createReader("Excel2007");
        $excelWorkbook = $reader->load($filename);
    } else {
        $excelWorkbook = new PHPExcel();
    }


    $sheet = $excelWorkbook->getSheetByName($tabName);

    if ($sheet !== null) {
        $excelWorkbook->removeSheetByIndex($excelWorkbook->getIndex($sheet));
    }

    $sheet = new PHPExcel_Worksheet($excelWorkbook, $tabName);
    $sheet = $excelWorkbook->addSheet($sheet);

    $columns = array_keys($targetArray);
    $rows = array_keys($targetArray[$columns[0]]);

    for($i = 0; $i < count($columns); $i++){
        $sheet->setCellValueByColumnAndRow($i+1,1,$columns[$i]);
    }

    for($i = 0; $i < count($rows); $i++){
        $sheet->setCellValueByColumnAndRow(0,$i+2,$rows[$i]);
    }

    for($i = 0; $i < count($columns); $i++){
        for($j = 0; $j < count($rows); $j++) {
            $sheet->setCellValueByColumnAndRow($i+1, $j+2, $targetArray[$columns[$i]][$rows[$j]]);
        }
    }

    $excelWorkbook->setActiveSheetIndex($excelWorkbook->getIndex($sheet));

    $xlsx = new PHPExcel_Writer_Excel2007($excelWorkbook);
    $xlsx->save($filename);

Raffael Raffael · Accepted Answer · 2012-11-16T12:44:50

The workaround-type solution is to select all cells in the respective sheet and choose for background color 'none'.

Apparently the cells are colored white and this coloring overlays the conditional coloring.

conditional formatting not working

2 Answers