Phpspreadsheet: Support for autofitting the rows height

[leandrogm]

This is:

- [ ] a bug report
- [X] a feature request
- [X] **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 lib should be able to have a function that autofits the rows based on the largest cell. I couldn't find any place to do it.

After struggling with this issue I came up with a solution that I would like to share. That's why i'm creating this issue.

What is the current behavior?

I'm not sure if it only happens on Libre Office Calc but if I have a text that has been wrapped before using getAlignment()->setWrapText(true) I'm was not able to fit the rows based on the new height of the cells, like when we double click excel/calc rows.

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "1.0.0-beta",
"php": ">=5.6.4"

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[ianef]

Hi leandrodm. A good start but there are some errors in you code and it doesn't take into account merged cellls.

Here is a version of your method that can be placed into the client code and does not require your fork to implement the function.

Rather than use a row range, I've gone for performing the action on a single row but this could be adapted to your methodology easily. To get the row you need to do the following:

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Row;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
...
    const ROW_PADDING = 5;
    const DEFAULT_CELL_WIDTH = 9.14;
    const DEFAULT_ROW_HEIGHT = 15;
...
            $row = new Row($myWorksheet, $myRowNum);
            $this->autofitRowHeight($row);

    /**
     * Auto-fit the row height based on the largest cell
     *
     * @param Row $start
     * @return Worksheet
     */
    public function autofitRowHeight(Row $row, $rowPadding = self::ROW_PADDING)
    {
        $ws = $row->getWorksheet();
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true);

        $maxCellLines = 1;
        /* @var $cell Cell */
        foreach ($cellIterator as $cell) {
            $cellLength = strlen($cell->getValue());
            $cellWidth = $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->getWidth();
            // If no column width is set, set the default
            if($cellWidth === -1) {
                $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->setWidth(self::DEFAULT_CELL_WIDTH);
                $cellWidth = $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->getWidth();
            }
            // If the cell is in a merge range we need to determine the full width of the range
            if($cell->isInMergeRange()) {
                // We only need to do this for the master (first) cell in the range, the rest need to have a line height of 1
                if($cell->isMergeRangeValueCell()) {
                    $mergeRange = $cell->getMergeRange();
                    if($mergeRange) {
                        $mergeWidth = 0;
                        $mergeRefs = Coordinate::extractAllCellReferencesInRange($mergeRange);
                        foreach($mergeRefs as $cellRef) {
                            $mergeCell = $ws->getCell($cellRef);
                            $width = $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->getWidth();
                            if($width === -1) {
                                $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->setWidth(self::DEFAULT_CELL_WIDTH);
                                $width = $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->getWidth();
                            }
                            $mergeWidth += $width;
                        }
                        $cellWidth = $mergeWidth;
                    } else {
                        $cellWidth = 1;
                    }
                } else {
                    $cellWidth = 1;
                }
            }

            // Calculate the number of cell lines with a 10% additional margin
            $cellLines = ceil(($cellLength * 1.1) / $cellWidth);
            $maxCellLines = $cellLines > $maxCellLines ? $cellLines : $maxCellLines;
        }

        $rowDimension= $ws->getRowDimension($row->getRowIndex());
        $rowHeight = $rowDimension->getRowHeight();
        // If no row height is set, set the default
        if($rowHeight === -1) {
            $rowDimension->setRowHeight(self::DEFAULT_ROW_HEIGHT);
            $rowHeight = $rowDimension->getRowHeight();
        }

        $rowLines = $maxCellLines <= 0 ? 1 : $maxCellLines;

        $rowDimension->setRowHeight( ($rowHeight * $rowLines) + $rowPadding);

        return $ws;
    }

[gitrequests]

Hi leandrodm. A good start but there are some errors in you code and it doesn't take into account merged cellls.

Here is a version of your method that can be placed into the client code and does not require your fork to implement the function.

Rather than use a row range, I've gone for performing the action on a single row but this could be adapted to your methodology easily. To get the row you need to do the following:

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Row;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
...
    const ROW_PADDING = 5;
    const DEFAULT_CELL_WIDTH = 9.14;
    const DEFAULT_ROW_HEIGHT = 15;
...
            $row = new Row($myWorksheet, $myRowNum);
            $this->autofitRowHeight($row);

    /**
     * Auto-fit the row height based on the largest cell
     *
     * @param Row $start
     * @return Worksheet
     */
    public function autofitRowHeight(Row $row, $rowPadding = self::ROW_PADDING)
    {
        $ws = $row->getWorksheet();
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true);

        $maxCellLines = 1;
        /* @var $cell Cell */
        foreach ($cellIterator as $cell) {
            $cellLength = strlen($cell->getValue());
            $cellWidth = $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->getWidth();
            // If no column width is set, set the default
            if($cellWidth === -1) {
                $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->setWidth(self::DEFAULT_CELL_WIDTH);
                $cellWidth = $ws->getColumnDimension($cell->getParent()->getCurrentColumn())->getWidth();
            }
            // If the cell is in a merge range we need to determine the full width of the range
            if($cell->isInMergeRange()) {
                // We only need to do this for the master (first) cell in the range, the rest need to have a line height of 1
                if($cell->isMergeRangeValueCell()) {
                    $mergeRange = $cell->getMergeRange();
                    if($mergeRange) {
                        $mergeWidth = 0;
                        $mergeRefs = Coordinate::extractAllCellReferencesInRange($mergeRange);
                        foreach($mergeRefs as $cellRef) {
                            $mergeCell = $ws->getCell($cellRef);
                            $width = $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->getWidth();
                            if($width === -1) {
                                $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->setWidth(self::DEFAULT_CELL_WIDTH);
                                $width = $ws->getColumnDimension($mergeCell->getParent()->getCurrentColumn())->getWidth();
                            }
                            $mergeWidth += $width;
                        }
                        $cellWidth = $mergeWidth;
                    } else {
                        $cellWidth = 1;
                    }
                } else {
                    $cellWidth = 1;
                }
            }

            // Calculate the number of cell lines with a 10% additional margin
            $cellLines = ceil(($cellLength * 1.1) / $cellWidth);
            $maxCellLines = $cellLines > $maxCellLines ? $cellLines : $maxCellLines;
        }

        $rowDimension= $ws->getRowDimension($row->getRowIndex());
        $rowHeight = $rowDimension->getRowHeight();
        // If no row height is set, set the default
        if($rowHeight === -1) {
            $rowDimension->setRowHeight(self::DEFAULT_ROW_HEIGHT);
            $rowHeight = $rowDimension->getRowHeight();
        }

        $rowLines = $maxCellLines <= 0 ? 1 : $maxCellLines;

        $rowDimension->setRowHeight( ($rowHeight * $rowLines) + $rowPadding);

        return $ws;
    }

Does this code work with line breaks?

[fbudin]

Hi,
I used a light version of this method to handle line breaks, but it assumes columns widths are corrects berforehand.
https://pastebin.com/ixTvfyBg

[Collie-IT]

In the Code from @ianfoulds is an little issue on line 61 by using the generated template again. The $width will added at every use again and again.

      $rowDimension->setRowHeight( ($rowHeight * $rowLines) + $rowPadding);

Change the code to

 $rowDimension->setRowHeight( (DEFAULT_ROW_HEIGHT * $rowLines) + $rowPadding);