Compute column widths in a HTML-like manner (based on cell contents)

Question

Hi,

I have a grid of data that I want to export to RTF, PDF etc. using various (and not perfect) PHP converters/generators.

What I am missing most is the HTML table automatic adjustment of column widths based on the lengths of strings in the cells (strings contain line breaks which complicate things a bit, as they should be preserved).

I need an algorithm that, given the contents of the cells (plain text), a total width of the table and an average width of a character, would return a width for each column. I wouldn't want to reinvent the wheel if something is already available.

Of course it can't be perfect if the font is variable width, but an approximation would do just fine. Or maybe it could have a configurable table with widths for each character.

Any hint would be appreciated. Thank you.

Answer 1

This isn't an easy one.

In PHPExcel, when a cell is set to autowidth, we use the gd library imagettfbbox() function

// font size should really be supplied in pixels in GD2,
// but since GD2 seems to assume 72dpi, pixels and points are the same
$fontFile = self::getTrueTypeFontFileFromFont($font);
$textBox = imagettfbbox($font->getSize(), $rotation, $fontFile, $text);

// Get corners positions
$lowerLeftCornerX  = $textBox[0];
$lowerLeftCornerY  = $textBox[1];
$lowerRightCornerX = $textBox[2];
$lowerRightCornerY = $textBox[3];
$upperRightCornerX = $textBox[4];
$upperRightCornerY = $textBox[5];
$upperLeftCornerX  = $textBox[6];
$upperLeftCornerY  = $textBox[7];

// Consider the rotation when calculating the width
$textWidth = max($lowerRightCornerX - $upperLeftCornerX, $upperRightCornerX - $lowerLeftCornerX);

return $textWidth;

It's very intensive though, especially when working with large worksheets, so we also have an alternative (approximation) method

// Calculate column width in pixels. We assume fixed glyph width. Result varies with font name and size.
switch ($fontName) {
    case 'Calibri':
        // value 8.26 was found via interpolation by inspecting real Excel files with Calibri 11 font.
        $columnWidth = (int) (8.26 * PHPExcel_Shared_String::CountCharacters($columnText));
        $columnWidth = $columnWidth * $fontSize / 11; // extrapolate from font size
        break;

    case 'Arial':
        // value 7 was found via interpolation by inspecting real Excel files with Arial 10 font.
        $columnWidth = (int) (7 * PHPExcel_Shared_String::CountCharacters($columnText));
        $columnWidth = $columnWidth * $fontSize / 10; // extrapolate from font size
        break;

    case 'Verdana':
        // value 8 was found via interpolation by inspecting real Excel files with Verdana 10 font.
        $columnWidth = (int) (8 * PHPExcel_Shared_String::CountCharacters($columnText));
        $columnWidth = $columnWidth * $fontSize / 10; // extrapolate from font size
        break;

    default:
        // just assume Calibri
        $columnWidth = (int) (8.26 * PHPExcel_Shared_String::CountCharacters($columnText));
        $columnWidth = $columnWidth * $fontSize / 11; // extrapolate from font size
        break;
}

// Calculate approximate rotated column width
if ($rotation !== 0) {
    if ($rotation == -165) {
        // stacked text
        $columnWidth = 4; // approximation
    } else {
        // rotated text
        $columnWidth = $columnWidth * cos(deg2rad($rotation))
                        + $fontSize * abs(sin(deg2rad($rotation))) / 5; // approximation
    }
}

// pixel width is an integer
$columnWidth = (int) $columnWidth;
return $columnWidth;