Excel::Writer::XLSX - Text wrap is not working for text written with write_rich_string()

[rtimms]

Hi,

I want to write multi-line, rich formatted text to a merged cell but text wrap does not seem to work correctly when using write_rich_string().

For example, I ran some tests by modifying the cell allignments portion of the formats.pl example code provided with the download, the only changes I made were as follows:

I set the format variable as follows:
  my $format06 = $workbook->add_format();
  $format06->set_text_wrap();

then merge the range:
  $worksheet->merge_range("F1:F2", '', $format06 );

now, writing cell contents using write() works correctly (although it's not possible to format lines individually). When the spreadsheet is opened the cell contents are wrapped:
  $worksheet->write( 'F1', "text\nwrap", $format06 );

but writing cell contents with write_rich_string the cell contents are not wrapped when the spreadsheet is opened:
$worksheet->write_rich_string( 'F1', $format06, "text\nwrap");
however, when clicking on the cell I can see the text is wrapped in the formula bar, and when double clicking on the cell the contents wrap correctly and stay wrapped if I click elsewhere.

I have attached spreadsheet examples of both and also the perl source. See the cell F1 on the Alignment worksheet and lines 459 and 460 in the perl source.

Any ideas?



Cheers,
Ross.

[jmcnamara]

On Sunday, 30 December 2012 02:53:06 UTC, rtimms wrote:

I want to write multi-line, rich formatted text to a merged cell but text wrap does not seem to work correctly when using write_rich_string().

Hi Ross,

It should work. The trick is to add the wrap format to the entire cell by adding it to the end of the rich_string() arg list. 

Quote docs:

As with Excel, only the font properties of the format such as font name, style, size, underline, color and effects are applied to the string fragments. Other features such as border, background and alignment must be applied to the cell.

The write_rich_string() method allows you to do this by using the last argument as a cell format (if it is a format object). The following example centers a rich string in the cell:

    my $bold   = $workbook->add_format( bold  => 1 );

    my $center = $workbook->add_format( align => 'center' );

    $worksheet->write_rich_string( 'A5',

        'Some ', $bold, 'bold text', ' centered', $center );

End docs.

I'll update the docs to add text wrap to that list.

Here is a working example with text wrap:

    #!/usr/bin/perl

    use strict;

    use warnings;

    use Excel::Writer::XLSX;

    my $workbook  = Excel::Writer::XLSX->new( 'rich_wrap.xlsx' );

    my $worksheet = $workbook->add_worksheet();

    $worksheet->set_column( 'A:A', 30 );

    $worksheet->set_row( 0, 60 );

    my $bold   = $workbook->add_format( bold      => 1 );

    my $italic = $workbook->add_format( italic    => 1 );

    my $wrap   = $workbook->add_format( text_wrap => 1 );

    $worksheet->write_rich_string( 'A1',

        "This is\n",

        $bold, "bold\n",

        "and this is\n",

        $italic, 'italic',

        $wrap );

    __END__

Regards,

John.

-- 

 

[rtimms]

Thanks John. I had missed the distinction between text and cell formats!

Cheers,
Ross.