The exact autosize does not work correctly

[ivanwonders]

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?

The size of the columns is adjusted exactly to the text as in Excel in the columns B, C, and D.

What is the current behavior?

Despite setting the exact calculation method and the setAutoSize to true, there is still a lot of margin on the sides.

What are the steps to reproduce?

You can use the attached code to check it

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

// add code that show the issue here...
$col = 1;
$row = 1;

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getDefaultStyle()->getFont()->setName('Calibri');
$spreadsheet->getDefaultStyle()->getFont()->setSize(11);
$sheet = $spreadsheet->getActiveSheet();
$sheet->getColumnDimensionByColumn($col++)->setWidth(120, \PhpOffice\PhpSpreadsheet\Helper\Dimension::UOM_PIXELS);
$sheet->setCellValueByColumnAndRow($col++, $row, 'Column2');
$sheet->getStyle("B1")
    ->getFont()
    // ->setName('Arial')
    ->setSize(20);
$sheet->setCellValueByColumnAndRow($col++, $row, 'Col3');
$sheet->setCellValueByColumnAndRow($col++, $row, 'Big Column in 4 position');
$row++;
$sheet->getStyle('A1:' . $sheet->getColumnDimensionByColumn($col)->getColumnIndex() . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

//As the server is in a debian environment, we tried to copy the ttf from the calibri font. This gave better results but they are still bad.
\PhpOffice\PhpSpreadsheet\Shared\Font::setTrueTypeFontPath(__DIR__."/fonts/");

\PhpOffice\PhpSpreadsheet\Shared\Font::setAutoSizeMethod(\PhpOffice\PhpSpreadsheet\Shared\Font::AUTOSIZE_METHOD_EXACT);

foreach (range('B', $sheet->getHighestColumn()) as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$file= date('Y-m-d_His').'.xlsx';
$writer->save($file);

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

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?

Which versions of PhpSpreadsheet and PHP are affected?

PHP Version 7.4.33
PhpSpreadsheet 1.29.0

[ivanwonders]

Some extra information I found out trying to debug the bug. Cell B1 detects that the text width is 96px (correct) but then determines that the column width should be 140px. If I adjust the column size in Excel (double click) it stays at 109px.

[oleibman]

It seems that Shared\Font::calculateColumnWidth adds some padding for Exact calculation:

        if (!$approximate) {
            try {
                $columnWidthAdjust = ceil(
                    self::getTextWidthPixelsExact(
                        str_repeat('n', 1 * (($filterAdjustment ? 3 : 1) + ($indentAdjustment * 2))),
                        $font,
                        0
                    ) * 1.07
                );

                // Width of text in pixels excl. padding
                // and addition because Excel adds some padding, just use approx width of 'n' glyph
                $columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation) + $columnWidthAdjust;

It does this for Approximate as well, but that's not the case in hand, and it is more justifiable for Approximate than Exact. At any rate, if I zero out $columnWidthAdjust for Exact, I think the result looks as you expect it to. This would be a breaking change, but I would be willing to create a PR for it.

[ivanwonders]

Hi, I tried changing the line
$columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation) + $columnWidthAdjust;
for
$columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation) + 0;
But the result was not the desired one either, Excel when adjusting the columns leaves a margin, it doesn't stay at 0. And for example, column D with this modification is out of the cell.
The expected outcome would be:
A -> 109px
B -> 34px
C -> 158px
But with this modification we get:
A -> 96px
B -> 26px
C -> 144px
I think Excel when it adjusts the column size puts left and right padding of 4px or 5px approximately.
Thanks.

[oleibman]

I'm sorry, I do not understand what you want. Part of the problem is that I think you're specifying A, B, and C in your immediately preceding comment when I think you mean B, C, and D. Even so, my observations don't seem to jibe with yours. Another problem is the multitude of units which Excel is using. At any rate, let me put down what I see, displaying the results of calculateColumnWidth and inspecting the Xml.

            echo $col, ' changed to ',
                \PhpOffice\PhpSpreadsheet\Shared\Font::calculateColumnWidth($sheet->getStyle("{$col}1")->getFont(),
               $sheet->getCell("{$col}1")->getValue()), "\n";

No change:
B: columnWidthExact=15.4248 Excel width 19.09(unit=?) 217 pixels XMLwidth=19.71
C: columnWidthExact= 4.5703 Excel width 6.36(?) 77 pixels XMLwidth=6.998
D: columnWidthExact=21.9946 Excel width 23.82(?) 269 pixels XMLwidth=24.423

Change to eliminate padding:
B: columnWidthExact=13.7109 Excel width 13.09(unit=?) 151 pixels XMLwidth=13.7111
C: columnWidthExact= 3.5706 Excel width 2.91(?) 39 pixels XMLwidth=3.571
D: columnWidthExact=20.9949 Excel width 20.36(?) 231 pixels XMLwidth=20.995

And the result doesn't show any overflow:

I have no idea why XMLwidth and columnWidthExact track so well with no padding and so poorly with padding. Nor any idea why the ratios appear to be non-constant. But, most importantly, I'm not sure what you're looking for. Excel doesn't publish its padding algorithm; we just make our best guess by adding a character n on each side. If you have a suggestion which you think will work better, let us know what that is.

It sounds like you are not interested in the "no padding" option. Accordingly, I will close that PR in a couple of days.

[ivanwonders]

OK, I'll put screenshots of what happens and what I understand we should get.

Without making any modification, just having in the script the directory with the font we are using "Calibri":

By changing $columnWidthAdjust to 0 in my case they go outside the columns (B and D most notable):

And the expected result, what happens in excel when we double click on the column guides:

if for example we change $columnWidthAdjust to 11 the result is almost exact in this example.

Anyway, it doesn't seem that setting that fixed value simulates what excel does well because if I try it in my real case it seems to depend on the characters in the cell for that to work well. In this case 11 would be too many to simulate what excel would do. I put the two images in a row to make it easier to see (below the excel auto)

Do you think that if we could get $columnWidthAdjust to adjust correctly we would get the closest result to what Excel does?

[oleibman]

I'm sorry, I think I've gone about as far as I can with this problem. The fact that your Column D overflows the cell while mine doesn't (on both a Windows and a Linux system) is disappointing. I cannot match behavior, especially when it seems so capricious as this, in the absence of a spec. If you feel my PR is useful as-is, I can proceed with it; if not, I will scrap it. And, of course, even if I do scrap it, please feel free to create a PR of your own based on it that meets your needs.

[ivanwonders]

Yes, it seems to depend on more factors than I initially thought and I don't know how to solve it for everyone.
I think the PR you propose could be useful for more people, even if you could specify the px you want to use as padding instead of just 0.
At the moment I'm setting the value of $columnWidthAdjust to 8 which is the closest I can get to what Excel does.
Thank you for your help

[oleibman]

Letting the new property be numeric rather than boolean is an interesting suggestion. I've changed my PR to do that. Can you test with it and see if comes close to meeting your needs.

[ivanwonders]

is going great 😉

[oleibman]

Thanks for the feedback. I will take my PR out of draft status and move it forward in the next few days.