CODEWITHSUNDEEP
excel-2010phpexcel
Raffael
Raffael

Reputation: 20045

conditional formatting not working

(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);

Upvotes: 1

Views: 4960

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

This may be a bug due to the fact that for solid fills in cells Excel reverses the meaning of foreground and background colours but in conditional (dxf) formats it doesn't.

Or, to put it another way background and foreground are stored differently for cell and conditional formats.

I encountered this issue in the Perl Excel::Writer::XLSX module.

Upvotes: 0

Raffael
Raffael

Reputation: 20045

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.

Upvotes: 1

Related Questions