Conditional Formatting lost on save

Question

I'm trying to create a simple setup to load a template, insert some data and save as a new file. However I need some conditional formatting on some of the cells and when I get the newly created files the conditional formatting is missing. It's not being overridden by some other formatting, the rules are missing from the conditional formatting menu. I'm using PHP 5.2, PHPExcel 1.7.8 and Excel 2010.

<?php
class template {
    static $objPHPExcel;
    function __construct() {
        define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
        require_once '../Classes/PHPExcel/IOFactory.php';
        if (!file_exists("template.xlsx")) {
            exit("template missing." . EOL);
        }
        echo date('H:i:s') , " Load from Excel2007 file" , EOL;
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(false);
        self::$objPHPExcel = $objReader->load("template.xlsx");
    }
    function insertdata($dataArray){ /* unused */ }
    function save($name){
        define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
        require_once '../Classes/PHPExcel/IOFactory.php';
        echo date('H:i:s') , " Write to Excel2007 format" , EOL;
        $objWriter = PHPExcel_IOFactory::createWriter(self::$objPHPExcel, 'Excel2007');
        $objWriter->save($name);
        echo date('H:i:s') , " File written to: ".$name , EOL;
    }
$temp=new template();
$temp->save("savethis.xlsx");

I'm trying to preserve a Graded 2 color scale (Formatting based on cell values, Minimum is type Number=1, Maximum is type Number=10). The cell in question has a formula attached that references another sheet (all data has been saved correctly).

Aside from the fact that `define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '
');` in both the constructor and in the save() method causing an "already defined" error, and the fact that you don't need to require the PHPExcel library multiple times (though the require_once prevents that being an issue, it's still an unnecessary overhead), there's no obvious reason why conditional styles read from the template should be lost when you save. `$objReader->setReadDataOnly(false);` is also redundant, as this is the default — Mark Baker, Dec 04 '14 at 17:26
However, I might suggest updating your version of PHPExcel, because there have been some fixes to conditional styles since version 1.7.8 — Mark Baker, Dec 04 '14 at 17:29
I'll run some tests over the next couple of days to see if I can replicate your problem — Mark Baker, Dec 04 '14 at 17:29
I can't get the "setAutoFilter", and "getDataValidation" from the input file to the output file, the "Conditional Style" is also lost, so after almost loosing my hair I created my document (template) from zero using the PHPExcel class alone. — Rodrigo Polo, Dec 08 '14 at 08:02

score 1 · Answer 1 · edited May 23 '17 at 11:58

I have found that is is hopeless to read, modify and save an XLS/XLSX file getting the "auto filter", "Data Validation" and "Conditional Format" from the original file, the final solution I found is to make the template using the PHPExcel library.

Sadly, as it was stated on the other answer (https://stackoverflow.com/a/13172890/218418):

PHPExcel is not a library for "editing" workbook files: you're not using PHPExcel to change a file, you're changing a PHPExcel object that can be loaded from a file, and can subsequently be written to a file.

I'm amused that something like "editing" an Excel file using a template with just data validation, conditional formating and auto filter is not possible, but I understand.

score 0 · Answer 2 · answered Apr 10 '19 at 22:58

This is an old thread, but I recently ran into the same issue. It seems like PHPSpreadsheet (the new incarnation of PHPExcel) supports creating spreadsheets with conditional formatting, but when I tried to read an existing spreadsheet and then write it out to a new file, the conditional formatting was broken.

I'm planning to use xlsx-populate instead - it's nodeJS rather than PHP, but it seems to be better at writing Excel files while maintaining features of the spreadsheet (like conditional formatting) that it doesn't understand.

In my case, I have a need to populate one sheet of a large, complex spreadsheet. Values from that sheet are used in calculations and conditional formatting on other sheets, which I don't need to modify directly.

With xlsx-populate, I was able to open the spreadsheet, populate it, and save it without breaking the formatting.

Conditional Formatting lost on save

2 Answers2