Number Format Lost - Conditional Formatting - Read 'Excel2007' -> Write 'Excel2007'

[Hectic247]

Semi-Pseudo Code below to give you an idea of how I came across this issue followed by explanation:

// Create master workbook
$masterWorkbook = new PHPExcel();

// Load template (containing conditional formatting rules)
$reader = PHPExcel_IOFactory::createReader("Excel2007");
$template = $reader->load("template.xlsx");

// Get template sheets
$templateSheets->getAllSheets();

// Add to template sheets to master workbook
foreach ($templateSheets as $sheet) {

  // Add some data (to fields with conditional formatting rules applied)
  $sheet->setCellValueByColumnAndRow();
  ....

  // Add updated sheet to master worksheet
  $masterWorkbook->addExternalSheet($sheet);
}

// Write master workbook (export)
$writer = PHPExcel_IOFactory::createWriter($masterWorkbook, "Excel2007");
$writer->save("php://output");

I have some conditional format rules applied to certain rows and columns in the template, with a mixture of 'General' and 'Number' 0.00 formats specified in the rules. When I view the outputted excel sheet (see above), the logic of the conditional formatted rules works just fine, but all formats I have specified in these rules have reset to 'General'.

I had a quick look at the source code, more specifically /Classes/PHPExcel/Reader/Excel2007.php in the latest dev build of version 1.8.2. I uncommented lines 1764 to 1771 in static function readStyle() and Voilá, everything works as expected.

Change the following code:

// format code
//        if (isset($style->numFmt)) {
//            if (isset($style->numFmt['formatCode'])) {
//                $docStyle->getNumberFormat()->setFormatCode((string) $style->numFmt['formatCode']);
//            } else {
                $docStyle->getNumberFormat()->setFormatCode($style->numFmt);
//            }
//        }

to this:

// format code
       if (isset($style->numFmt)) {
            if (isset($style->numFmt['formatCode'])) {
                $docStyle->getNumberFormat()->setFormatCode((string) $style->numFmt['formatCode']);
            } else {
                $docStyle->getNumberFormat()->setFormatCode($style->numFmt);
            }
 }

My question is, why are these lines commented out in the first place? Not sure what is happening here.

[PowerKiKi]

It was added, already commented out, in 13f227c by Mark. It is unclear why it was done that way since the commit seems to be unrelated to styles.

Would you please try to create a PR to enable this code in PhpSpreadsheet develop branch and add tests to cover it ?

[Hectic247]

@PowerKiKi I would, but the folder structure is unrecognizable in PhpSpreadsheet from PHPExcel, Where is the equivalent of /Classes/PHPExcel/Reader/Excel2007.php ? without having to spend too long searching for it.

[PowerKiKi]

The migrator class should give you a good idea of how things changed.