Problems using text_wrap with merge_range

[Oberdan]

Hello All,

I'm trying to use text_wrap and merge_range together. Here is my
test script:

------------------------------
use strict;
use warnings;
use Spreadsheet::WriteExcel;
my $Long = 'This is a long text I typed just as a test trying to
figure out why text_wrap gets screwed when I open the spreadsheet
using Excel and it looks fine on BrOffice';

my $WorkBook = Spreadsheet::WriteExcel->new("Test.xls");
my $WorkSheet = $WorkBook->add_worksheet("Worksheet 1");

my $WrapFormat = $WorkBook->add_format();

$WrapFormat->set_properties( font => 'Arial',
size => 11,
color => 'black',
num_format => '@',
text_wrap => 1 );

$WorkSheet->set_portrait(); #Paper orientation
$WorkSheet->hide_gridlines(); #Hide all borders
$WorkSheet->set_margins_TB(0.5); #Top and bottom margins
$WorkSheet->set_margins_LR(0.3); #Left and right margins
$WorkSheet->center_horizontally(); #Alignment
$WorkSheet->set_paper(9); #A4
$WorkSheet->keep_leading_zeros();

$WorkSheet->merge_range( 'A1:C1' , $Long , $WrapFormat );

$WorkBook->close();

exit(0);
------------------------------

The generated spreadsheet looks fine when opened with BrOffice
( Brazilian version of OpenOffice ), but the text is not wrapped when
I use Excel 2003. If I use something like:

$WorkSheet->write( 0 , 0 , $Long , $WrapFormat ); instead of
merge_range, the text gets wrapped correctly. I think I'm missing
something very simple, but was not able to figure out what is it. Any
ideas?

The versions I'm using:

Perl 5.10.1
Spreadsheet::WriteExcel 2.25
BrOffice 3.3.1
Excel 2003 SP3

I tested the script on Windows XP SP3 and Red Hat 3, with the same
results.

Thanks in advance.

[]'s

Oberdan

[jmcnamara]

On Sep 15, 2:32 am, Oberdan <oberdan.l...@gmail.com> wrote:

>    The generated spreadsheet looks fine when opened with BrOffice
> ( Brazilian version of OpenOffice ), but the text is not wrapped when
> I use Excel 2003. If I use something like:
>
> $WorkSheet->write( 0 , 0 , $Long  , $WrapFormat ); instead of
> merge_range, the text gets wrapped correctly. I think I'm missing
> something very simple, but was not able to figure out what is it. Any
> ideas?

Hi Oberdan,

In order to get the wrapped text to display properly in a merged range
you will have to set the height of the row:

...
$WorkSheet->set_row(0, 100);

$WorkSheet->merge_range( 'A1:C1' , $Long , $WrapFormat );

...

OOo sets the row height automatically for the merged wrapped cells but
Excel doesn't. There is no way in the file format to tell Excel to
format the row height automatically. Therefore you will have to
specify an explicit row height to get the effect that you want.

Excel does format the row height automatically in the case of a non-
merged row but again that cannot be controlled by the file format. It
is something that the application does after the file is read.


John.
--

[Oberdan Luiz May]

Hello John,

I understand the problem... Is there any way of predicting the row
height based on the font type/size, assuming I'll use a monospaced
font like Courier New size 9 or 10? I tried some values here but none
of them worked for all cases...

Thanks,

Oberdan

[jmcnamara]

On Sep 15, 2:36 pm, Oberdan Luiz May <oberdan.l...@gmail.com> wrote:
>    Hello John,
>
>    I understand the problem... Is there any way of predicting the row
> height based on the font type/size, assuming I'll use a monospaced
> font like Courier New size 9 or 10? I tried some values here but none
> of them worked for all cases...

Hi,

As a workaround you could try wrapping the text with Text::Wrap (or
similar) and working out the height in advance:

http://search.cpan.org/perldoc/Text::Wrap

John.
--