how to setWrapedText on merged cells

Topics: Developer Forum, User Forum
Jun 6, 2009 at 1:46 PM

Hi,

ps : I am french speaking

i am not able use setWrapText on merged cells

 

this code don't wrap cell A1

$workbook = new PHPExcel;

$sheet = $workbook->getActiveSheet();

$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(10);

$sheet->setCellValue('A1'.'Voici un trop long texte que je voudrais voire revenir à la ligne automatiquement');

$sheet->mergeCells('A1:C1');

$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

$writer = new PHPExcel_Writer_Excel5($workbook);
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');

 

Developer
Jun 6, 2009 at 2:13 PM

1. Download latest source code:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx


2. You have to set a height for the row (just like in Excel). Do like this:

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

$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(10);

$sheet->getRowDimension(1)->setRowHeight(45);

$sheet->setCellValue('A1', 'Voici un trop long texte que je voudrais voire revenir à la ligne automatiquement');
$sheet->mergeCells('A1:C1');
$sheet->getStyle('A1:C1')->getAlignment()->setWrapText(true);

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

Result:

Jun 6, 2009 at 2:31 PM

Thank,

So, i can't resize height automaitcally with API ?

Very Thank for all.

Developer
Jun 6, 2009 at 2:54 PM

>> So, i can't resize height automaitcally with API ?

No, text wrap and merged cells don't play well together in Excel. This is very annoying.

Even in MS Office Excel 2007 you will still have to adjust row height manually:

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

 

Jun 6, 2009 at 4:05 PM

Thank koyama, your génial .... :)

Jun 22, 2010 at 3:52 PM

Unfortunately, this still isn't working for me. I downloaded the latest snapshot, and "setWrapText()" still doesn't work. Here is a snippet of my code:

 

 

	$this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        if($styled) {
          $style_array = array(
            'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
            'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,))
          );
          $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
          $this->active_sheet->getRowDimension($row)->setRowHeight($this->default_row_height);
          $this->active_sheet->getStyleByColumnAndRow($col, $row)->getAlignment()->setWrapText(true);
        }

Why does the wrap text not work?

 

 

Coordinator
Jun 23, 2010 at 3:04 PM

Are you still working with merge cells? Can you try without merge cells?

Jun 23, 2010 at 3:36 PM
maartenba wrote:

Are you still working with merge cells? Can you try without merge cells?

Yes, the wrap text works when I do not use merge cells. The curious thing is that I wasn't using wrap text on the merged cells, but rather on other cells. 

 

Please advise.

Jun 30, 2010 at 3:11 PM

Still having the same issue. Is there any resolution to this?

Coordinator
Jul 1, 2010 at 9:45 AM

Just did some investigation on this:

  • Seems that Excel 2007 can not do this automatically, there is a workaround available through Excel: http://support.microsoft.com/kb/212010
  • I copied the output of the file after this workaround and injected it into the output of the above code
  • Excel ignored this completely, untill again I did this manually

Seems like this one will go unsolvable... A workaround can be to add newlines to your cell text manually every X words or so, based on the number of columns?

PS: I will be on a vacation for the next two weeks.

Dec 27, 2010 at 11:38 PM

Hi,

is automatic wordwrap still a problem on merged cells? I have the same issue. For now I am using 1.7.1. and will upgrade if this is solved in any later version, but it seems that it is a Microsoft bug.

Thanks for reply

Goran

Apr 7, 2011 at 5:20 PM

On the latest release of PHPExcel merged cells and wrapTExt are working in Excel2007 for me, when the .xlsx file is opened in OpenOffice or any other Office programs the wrapText and mergeCells is not working.

Coordinator
Apr 7, 2011 at 10:31 PM
chrislynch8 wrote:

On the latest release of PHPExcel merged cells and wrapTExt are working in Excel2007 for me, when the .xlsx file is opened in OpenOffice or any other Office programs the wrapText and mergeCells is not working.


If you create a file with merged cells and WrapText in MS Excel itself, then open it in Open or Libre Office, is the wrap text still working then?

Apr 12, 2011 at 11:29 AM

Hi,

This is what I've found so far.

Create a xlsx file in MS Excel 2007.

  1. If I enter text and then set the cell to wrap it wraps, I then set this cell and another to merge and the text is wrapped in the new merged cells.
  2. If I set two cells as merged and wrapped and then enter the text it will not wrap the text.

When I open the above file I save as xlsx in OpenOffice and LibreOffice the wrapped Text and Merged Cells work the same as above the same cells appear as wrapped, merged and auto adjusted row heights.

I will try to apply the logic in the first example above to my PHPExcel code, remove all the wrapping and merged cells and then set them after text has been added to the cells etc.

Also strangely I got it working with OpenOffice yesterday, but I have been unable to reproduce since. What I done was in the template set the cells as Shrink to Fit and Wrapped, then when this was exported it worked in OpenOffice my cells where wrapping correctly and the height was adjusted. But as I've said I can't reproduce this again. Still looking into it.

 

Rgds

Chris