Wrong foreground color for Mac Numbers XLSX spreadsheet (missing indexed colors)

[joseph101039]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

After we save a Numbers XLSX file on MAC and simply loaded and saved the file by the library,
the foreground colors of cells should be same as the original one.

What is the current behavior?

The foreground colors of cells are changed after saved.

What are the steps to reproduce?

1. Install the latest version of library
   composer require phpoffice/phpspreadsheet:1.25.2

2. Open a xlsx file by macOS (version 10.15.7) Numbers (version 6.2).

3. Add foreground colors at some cells

4. Export to xlsx file formats in Numbers on Mac (File > Export to > Excel). For example, we get excel_file1_mac.xlsx

5. Simply load and save the xlsx file (see the sample code).
   We get the output xlsx file excel_file1_mac_ouput.xlsx which has different foreground colors.

<?php

require __DIR__ . '/vendor/autoload.php';

  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
  $spreadsheet = $reader->load("excel_file1_mac.xlsx");
  (new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet))
      ->save("excel_file1_mac_output.xlsx");

Possible root cause

The issue may be the xlsx reader loses the indexed color styles after loading spreadsheet.

Unzip the source file excel_file1_mac.xlsx and open the stylesheet. (xl/styles.xml)

<?xml version="1.0" encoding="UTF-8"?>
<styleSheet>
     <!-- ignore -->
    <fills count="7">
        <fill>
            <patternFill patternType="solid">
                <fgColor indexed="11"/>
                <bgColor auto="1"/>
            </patternFill>
        </fill>
        <fill>
            <patternFill patternType="solid">
                <fgColor indexed="12"/>
                <bgColor auto="1"/>
            </patternFill>
        </fill>
        <!-- ignore -->
   </fills>
    <colors>
        <indexedColors>
            <!-- ignore -->
            <rgbColor rgb="ffaaaaaa"/>
            <rgbColor rgb="ffc0c0c0"/>
            <rgbColor rgb="ffffff00"/>
            <rgbColor rgb="ffdfa7a6"/>
            <rgbColor rgb="ff7ba0cd"/>
        </indexedColors>
    </colors>
</styleSheet>

The sample xlsx file has totally 16 indexed colors.

When I see the function extractPalette(), it filters all the indexed colors if there are not exactly 64 indexed colors.

PhpOffice\PhpSpreadsheet\Reader\Xlsx::extractPalette()

    private static function extractPalette(?SimpleXMLElement $sxml): array
    {
        $array = [];
        if ($sxml && $sxml->colors->indexedColors) {
            foreach ($sxml->colors->indexedColors->rgbColor as $node) {
                if ($node !== null) {
                    $attr = $node->attributes();
                    if (isset($attr['rgb'])) {
                        $array[] = (string) $attr['rgb'];
                    }
                }
            }
        }
        return (count($array) === 64) ? $array : [];
    }

Because there is not indexedColors palette, but still has some cells refer to the indexed color. The program use default palette PhpOffice\PhpSpreadsheet\Style\Color::INDEXED_COLORS to render the spreadsheet. Therefore the output xlsx file has wrong colors.

I think that replace the line

return (count($array) === 64) ? $array : [];

with

return $array;

can fix the problem.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

The issue affects all the border, font, cell foreground colors of the xlsx spreadsheet which is exported by Numbers.

Which versions of PhpSpreadsheet and PHP are affected?

php: 7.4.30
phpoffice/phpspreadsheet: 1.25.2

[oleibman]

You are correct about the cause. I had never actually seen indexed colors before the issue which caused me to add support for it, and, since the only example I had used the full complement of 64 colors (and documentation of this possibility is lacking), I was unwilling to extrapolate for any other number. Complicating this is the fact that I don't have a Mac, but perhaps your sample file is sufficient and I don't need such access. I will certainly look into it.