Long cell text

Topics: Developer Forum
Nov 10, 2009 at 12:25 PM

Hello,

first... Thanks for this library!

Here is the problem now. I want store long text in cell but as I've found out Excel only displays about 1024 chars. But if we add line break \n it also display next line of chars. I hope you understand me :))

So I wrote this function. It isn't the best optimized one but could be helpful for someone. Also not tested very well.


/**
* If the text is longer than 1024 characters, add \n after certain width of the line.
* It's only working for Arial font now.
*
* @param string Text to break.
* @param int Width of text block in pixels.
* @param int Font size in points.
* @return string
**/

public function breakLongText($txt, $width, $font_size = 9)
{
if (strlen($txt) > 1024) // needs to break
{
// correct path has to be defined here
$font_path = './arial.ttf';

$breaked = '';
$words = explode(' ', $txt);

$pos = 1;
while (!empty($words))
{
if ($pos == count($words))
break;

$line = implode(' ', array_slice($words, 0, $pos));
$box_points = imagettfbbox($font_size, 0, $font_path, $line);
$textwidth = $box_points[2]-$box_points[0];

if ($textwidth > $width)
{
$breaked .= (empty($breaked) ? '' : "\n"). implode(' ', array_slice($words, 0, $pos-1));
$words = array_slice($words, $pos-1);
$pos = 0;
}

$pos++;
}
if (!empty($words))
$breaked .= (empty($breaked) ? '' : "\n"). implode(' ', $words);

$txt = $breaked;
}

return $txt;
}

 

Developer
Nov 11, 2009 at 12:54 AM

I was not aware of such trick! Thanks for posting your code.

Reference for others about the 1,024 character limit:

http://support.microsoft.com/kb/211580

My only concern about using that method is when the user makes the column narrower after the Excel file has been generated. Then the added line breaks may be an inconvenience since the text may not wrap that nicely anymore, but I guess it depends on the situation. Perhaps your method can be enhanced to instead set the correct row height which should also make characters above 1024 visible. We should perhaps look into this at some later point!

Nov 11, 2009 at 5:59 AM

No, set height won't help. That was my first solution. But chars after 1024 wasn't displayed just empty space. But if you want I could post you function to get count of rows depending on the text. It's somethink like:

1. remove "\n" from text.

2. pass text to imagettfbbox function and get width

3. number of rows = width/line_width

 

Developer
Nov 12, 2009 at 12:12 AM
Edited Nov 12, 2009 at 12:15 AM
waky wrote:

No, set height won't help. That was my first solution. But chars after 1024 wasn't displayed just empty space.

Can you try to run this example:

$excel = new PHPExcel();
$worksheet = $excel->getActiveSheet();

$text = <<<TEXT
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed ante velit, dictum eu laoreet bibendum, sagittis eu dolor. Nunc elementum arcu et risus porta eget fermentum metus egestas. Vestibulum dictum accumsan lacinia. Ut congue ornare blandit. Nam mattis tincidunt lectus ut elementum. Praesent rhoncus sapien sit amet lacus elementum aliquam. Vivamus nunc neque, adipiscing ac adipiscing quis, ullamcorper nec lectus. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Suspendisse hendrerit magna varius tortor lacinia ut euismod ipsum posuere. Curabitur feugiat vehicula mattis. Morbi nec odio at nibh cursus porta vitae non nibh. Suspendisse potenti. Fusce id neque tristique arcu gravida lacinia sed eget ipsum. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum vitae nisi id nunc fringilla ultricies. Nullam libero sapien, luctus ut porta id, pulvinar a neque.

Vestibulum blandit orci in diam feugiat quis ullamcorper metus iaculis. Duis sed bibendum dolor. Donec blandit facilisis magna, et bibendum magna consectetur quis. Vestibulum vitae dolor velit, in porttitor sem. Sed pellentesque, purus dictum tristique laoreet, felis nisi accumsan ligula, a suscipit orci eros porttitor tellus. Nunc vel libero in lectus iaculis tempor vitae at sem. Suspendisse posuere nibh ut mauris ornare aliquam. Integer vel dui quis magna consequat rutrum. Nullam porta vulputate lectus nec cursus. Donec porttitor sem suscipit libero suscipit consectetur. Quisque ipsum nisl, euismod sed mattis sed, luctus et lectus. Donec vel tristique enim. Donec lacus arcu, tempor ac vehicula at, scelerisque posuere ipsum. Aenean pellentesque odio sit amet nisi convallis faucibus. Donec quis dui arcu. Nullam ultricies, libero ultrices tempor mollis, enim orci egestas risus, in malesuada leo nisl at enim.

Integer porta pellentesque quam, id tristique purus consectetur sit amet. Mauris vestibulum orci nibh, vitae semper magna. Phasellus condimentum varius iaculis. Ut eleifend nulla nec mi facilisis vitae condimentum augue vestibulum. Curabitur tristique euismod fringilla. Duis eu leo elementum ante consectetur consectetur vitae in dui. Aliquam pharetra cursus mi, in suscipit augue fermentum ac. Vivamus euismod mattis luctus. Aliquam erat volutpat. Quisque adipiscing consectetur risus quis viverra. Donec id tellus et velit fringilla convallis ac sit amet turpis. Nullam sodales arcu at risus blandit tristique. Aenean bibendum varius nisl, sed volutpat odio viverra vitae. Nulla at nulla ac ipsum ultricies eleifend. Nullam in sem vel eros aliquet dapibus. Aliquam fermentum pellentesque enim et feugiat. Etiam tincidunt risus vitae metus congue pharetra.

Donec et ipsum felis, sit amet mollis turpis. Cras sit amet elit sapien. Ut sapien est, sagittis in egestas rhoncus, pellentesque nec ipsum. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam in lacus urna. Nam ornare scelerisque nunc, id tempus turpis luctus sit amet. Duis convallis scelerisque tellus vitae euismod. Suspendisse et sem eget erat pellentesque euismod ac vel eros. Aliquam erat volutpat. Nunc bibendum fringilla odio eget interdum. Etiam nunc velit, fringilla non volutpat id, sollicitudin quis magna. Pellentesque tincidunt, massa ut pulvinar ornare, libero enim imperdiet enim, scelerisque ornare erat arcu a dui.

Curabitur fermentum fermentum nisi, et suscipit elit elementum sed. Integer eget volutpat est. Proin vel lorem dui, volutpat tristique sapien. Integer mollis suscipit pharetra. Maecenas ut est est, in tempor nisi. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam aliquet egestas consequat. Suspendisse velit augue, condimentum non vulputate at, semper at augue. Donec non est vitae sem malesuada pulvinar eget ac tortor. Aliquam erat volutpat. Vivamus sodales nisi at arcu aliquam bibendum. Nullam at erat vitae ligula porta facilisis molestie id magna. Suspendisse tincidunt varius tellus, nec placerat arcu imperdiet ac. Curabitur nisl quam, bibendum eu suscipit at, scelerisque mollis justo. Donec tristique felis id erat accumsan id suscipit velit condimentum.
TEXT;

$worksheet->getCell('A1')->setValue($text);
$worksheet->getStyle('A1')->getAlignment()->setWrapText(true);

$worksheet->getRowDimension(1)->setRowHeight('350');
$worksheet->getColumnDimension('A')->setWidth('120');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');

Look, I can see more than 1024 characters:

Here is the file:

write-1024-limit.xlsx