CODEWITHSUNDEEP
phpphpexcel
davidr360
davidr360

Reputation: 3

PHPExcel, why don't my line breaks appear if using PHPExcel_RichText?

I'm trying to use PHPExcel to produce something which looks like this:

Label:    
This is the value

Where "Label" is dark red, and "This is the value" is on a new line, in the same cell.

I've been using the following code:

$text = new PHPExcel_RichText();
$label = $text->createTextRun("This is the label: ");
$label->getFont()->setBold(true);
$label->getFont()->setItalic(true);
$label->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKRED));

$text->createText(PHP_EOL . "This is the value");

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

But for some reason this doesn't seem to be working. I've tried "\r" and "\n" in place of PHP_EOL (being careful to use double quotes instead of single ones).

I've also tried moving the PHP_EOL to the label text.

If I call setValue($text . PHP_EOL) I lose the formatting, but the line break works.

I'm on a Mac using Excel 2016 if that makes any difference. Unfortunately I'm not able to test on a different version of Excel. Can someone show me how to fix this?

Upvotes: 0

Views: 1135

Answers (2)

Régine Mangeard
Régine Mangeard

Reputation: 21

I have just tried your code on my Mac and it works fine but I have already patched several files of the phpExcel package.

I have unzipped the Excel file generated with phpExcel and compared the xml files with those from a regular Excel file and found some differences. One of them is how bold is specified in the sharedStrings.xml file. in a phpExcel generated file you have:

<b val="1"/>

and in a regular Excel file

<b/>

So in StringTable.php, in writeRichText, I have changed

                        // Bold 1
                    if($element->getFont()->getBold())
                    {
                    $objWriter->startElement($prefix.'b');
                    $objWriter->writeAttribute('val', ($element->getFont()->getBold() ? 'true' : 'false'));
                    $objWriter->endElement();
                    }

to

                        // Bold 1
                    if($element->getFont()->getBold())
                    {
                    $objWriter->startElement($prefix.'b');
                    $objWriter->endElement();
                    }

and I did the same for the other true/false properties.

It was not enough to solve my own problem of newlines disappearing in RichTextObjects, but i have given the other patches I've done in PHPExcel toRichTextObject only showing newlines after saving file again manually.

So that, when I use your code, the newlines are correctly set and visible. Hope it will solve your problem!

Upvotes: 1

coldwithin
coldwithin

Reputation: 1

Using Excel for Mac 2011 on latest OS X. Tried to add PHP_EOL and/or "\n".

$cellValue->createText(PHP_EOL);
$cellValue->createText("\n");

Neither is creating a new line when using PHPExcel_RichText.

Upvotes: 0

Related Questions