why setAutoSize(true) does not work ?

Topics: Developer Forum, Project Management Forum, User Forum
Jul 27, 2009 at 4:58 AM

i have so many table values export into excel format. i need autosize of column width. for this i used following code, it doesnot work,

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

only this  $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setwidth(10); can work.

please help me

By

Dhruba

Developer
Jul 27, 2009 at 9:46 PM

>> i need autosize of column width. for this i used following code, it doesnot work,

Does it have no effect, or is it just a bit inaccurate?

A fully accurate setAutoSize() method will be very tricky to implement because PHPExcel will need to know the exact width of each character. Right now it is a bit inaccurate because it is using approximations.

However, if you are using PHPExcel 1.6.7, can you try latest source code as some improvements have been made on this.
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Jul 30, 2009 at 5:30 PM
Edited Jul 30, 2009 at 5:31 PM

I was having the same problem as dhrubasearch and I decided to take a look inside the code for the PHPExcel_Shared_Font class. I found that both methods (the one in the stable release, 1.6.7, and the one from SVN) where not taking into consideration the font face or weight (see below, for references porpouses). I googled a little bit and I seem to have found a possible solution to this.

1.6.7:

 

$columnWidth = ((strlen($columnText) * $fontSize + 5) / $fontSize * 256 ) / 256; 

 

SVN:

 

$columnWidth = 1.025 * strlen($columnText) + 0.584;

 

The possible solution (inspired by http://www.simplemachines.org/community/index.php?topic=122913.0):

 

public static function calculateColumnWidth($fontSize = 9, $fontSizeInPixels = false, $columnText = '', $rotation = 0) {
    // If it is rich text, use rich text...
    if ($columnText instanceof PHPExcel_RichText) {
        $columnText = $columnText->getPlainText();
    }
		
    // Only measure the part before the first newline character
    if (strpos($columnText, "\r") !== false) {
        $columnText = substr($columnText, 0, strpos($columnText, "\r"));
    }
    if (strpos($columnText, "\n") !== false) {
        $columnText = substr($columnText, 0, strpos($columnText, "\n"));
    }
		
    // XXX: Modifications made below
    //
    //	-> Line modified
    //
    // Calculate column width
    // values 1.025 and 0.584 found via interpolation by inspecting real Excel files with
    // Calibri font. May need further adjustment
    #$columnWidth = 1.025 * strlen($columnText) + 0.584; // Excel adds some padding
    $columnWidth = PHPExcel_Shared_Font::getTextWidth($columnText, $fontSize);

    if (!$fontSizeInPixels) {
        // Translate points size to pixel size
        $fontSize = PHPExcel_Shared_Font::fontSizeToPixels($fontSize);
    }

    // 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
        }
    }

    // Return
    return round($columnWidth, 6);
}

 

 

/**
* XXX: Modifications made below
*
* -> Function created
*
* Get text width, given the font size and the .ttf file
*
* @param string $text Text whose width will be returned
* @param int $fontSize Font size (in points)
* @param string $fontPath Path to the font used for calculations
*/
private static function getTextWidth($text, $fontSize = 10, $fontPath = 'C:/Windows/Fonts/Arial.ttf')
{
$bbox = imagettfbbox($fontSize, 0, $fontPath, $text);

$xcorr = 0 - $bbox[6]; //northwest X $ycorr = 0 - $bbox[7]; //northwest Y $textWidth = $bbox[2] + $xcorr; return $textWidth * (14 / 100) + 2; // convertion plus empiric padding }


Off course that there are a lot of improvements to make on this, such as allowing a better (more automatic) selection of the $fontPath and considering bold fonts (that normally have a larger width). For now, this code is working pretty well! What do you guys think about this?

 

Developer
Jul 30, 2009 at 9:07 PM

@alextercete: This looks promising. Will copy to work item so we can discuss this.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10375