Incorrect setAutoSize(true) width for cells with colors in numberFormat

[Denitz]

This is:

- [x] a bug report

What is the expected behavior?

Setting column dimension setAutoSize(true) should set approximate column width.

What is the current behavior?

If a column cell has colors in numberFormat, the calculated cell value includes color pattern, hence the cell width is treated bigger.
See Worksheet::calculateColumnWidths()

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$cell = $sheet->getCellByColumnAndRow(1, 1);
$cell->setValueExplicit(170, 'n');
$cell->getStyle()->getNumberFormat()->setFormatCode('[Color 10]+#,##0;[Red]-#,##0;""');

$dimension = $sheet->getColumnDimension('A');
$dimension->setAutoSize(true);

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('test.xlsx');

You can see the differences of automatic column width with/without setFormatCode() line.

Worksheet::calculateColumnWidths() tries to get the cell value and next use its length to calculate the width.
The cell value is 170, displayed value is +170, but this code detects $cellValue as [Color 1170]+170:

                        // Calculated value
                        // To formatted string
                        $cellValue = NumberFormat::toFormattedString(
                            $cell->getCalculatedValue(),
                            $this->getParent()->getCellXfByIndex($cell->getXfIndex())
                                ->getNumberFormat()->getFormatCode()
                        );

What features do you think are causing the issue

• Calculations

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

XLS, XLSX

Which versions of PhpSpreadsheet and PHP are affected?

All versions.

[MarkBaker]

As you've noted, this isn't about using colours generally, but more specifically using a colour referenced by index [Color 10]. If you make your value negative (-170), then the colour [red] will be applied, and this will calculate the correct width.

This is because the built-in formatter doesn't recognise colours by index, only by name; so the problem lies specifically with the style formatter.

[Denitz]

But [Color 10] is valid Excel color, can we update formatter to recognize it? Default [Green] for positive numbers is really toxic.

[MarkBaker]

But [Color 10] is valid Excel color, can we update formatter to recognize it?

That's why I've assigned this to myself

[oleibman]

Fixed by PR #3512.