Excel and OpenOffice: different rendering

[Giulio Orsero]

WriteExcel-2.18

The attached test script produces different outputs in Excel and OpenOffice.
The script was made targeting Excel, only recently I tried it out on
OpenOffice (2.2.1 Windows).

As rendered by Excel:
http://img116.imageshack.us/my.php?image=excelbj7.jpg

As rendered by OpenOffice Calc:
http://img463.imageshack.us/my.php?image=openofficerm7.jpg

As rendered by OpenOffice Calc after having it opened and saved by Excel:
http://img394.imageshack.us/my.php?image=openofficexlssavedvx6.jpg

Do you think this is a problem in WriteExcel or in OpenOffice?
Thanks

==== Test Script attached ====
#!/usr/bin/perl
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('xls1.xls');
my $xls = $workbook->addworksheet('Test');

my $fmt_h = $workbook->addformat();
$fmt_h->set_bg_color(26);
$fmt_h->set_font('Courier New');

my $fmt_l = $workbook->addformat();
$fmt_l->set_bold();
$fmt_l->set_align('center');
$fmt_l->set_bg_color(41);
$fmt_l->set_border();

$xls->write_string(0, 0, 'header');
$xls->set_row(0,undef,$fmt_h);
$xls->set_row(1,undef,$fmt_h);

$xls->write_string(2, 0, 'Code');
$xls->write_string(2, 1, 'Name');
$xls->set_row(2,undef,$fmt_l);

$xls->write_string(3, 0, 'ABC');
$xls->write_string(3, 1, 'Item ABC');
$xls->write_string(4, 0, 'DEF');
$xls->write_string(4, 1, 'Item ABC');

$workbook->close();
--
giu...@pobox.com

[jmcnamara]

On Jun 26, 2:59 pm, Giulio Orsero <giul...@gmail.com> wrote:
> WriteExcel-2.18
>
> The attached test script produces different outputs in Excel and OpenOffice.

Hi Giulio,

Thanks for your very detailed bug report.

I wasn't aware of this but I guess that you could call it a
"feature".

It should work as expected if you call set_row() before calling
write(). The docs suggest this anyway but for a different reason.

Try this:

#!/usr/bin/perl
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('xls3.xls');

my $xls = $workbook->addworksheet('Test');

my $fmt_h = $workbook->addformat();
$fmt_h->set_bg_color(26);
$fmt_h->set_font('Courier New');

my $fmt_l = $workbook->addformat();
$fmt_l->set_bold();
$fmt_l->set_align('center');
$fmt_l->set_bg_color(41);
$fmt_l->set_border();

$xls->set_row(0,undef,$fmt_h);
$xls->set_row(1,undef,$fmt_h);

$xls->write_string(0, 0, 'header');

$xls->set_row(2,undef,$fmt_l);

$xls->write_string(2, 0, 'Code');
$xls->write_string(2, 1, 'Name');

$xls->write_string(3, 0, 'ABC');

$xls->write_string(3, 1, 'Item ABC');
$xls->write_string(4, 0, 'DEF');
$xls->write_string(4, 1, 'Item ABC');

$workbook->close();

__END__

John.
--

[Giulio Orsero]

On 6/26/07, jmcnamara <jmcn...@cpan.org> wrote:

> It should work as expected if you call set_row() before calling
> write(). The docs suggest this anyway but for a different reason.

I missed that, changing the order of the calls as you said solved the issue.
Thanks.

Also, to solve the issue with OO not doing gridless fmt_h (I mean you
still see the grids) I added:

$fmt_h->set_border(1);
$fmt_h->set_border_color(26);

However, this is an issue with OO itself, since the problem exists
even for docs created directly in Excel (which when you set a
background color hides the grids, while OO does not unless you
activate the cell border with the same color of the bg).

--
giu...@pobox.com

[jmcnamara]

On Jun 26, 8:51 pm, "Giulio Orsero" <giul...@gmail.com> wrote:
> Also, to solve the issue with OO not doing gridless fmt_h (I mean you
> still see the grids) I added:
>
> $fmt_h->set_border(1);
> $fmt_h->set_border_color(26);
>
> However, this is an issue with OO itself, since the problem exists
> even for docs created directly in Excel (which when you set a
> background color hides the grids, while OO does not unless you
> activate the cell border with the same color of the bg).

Hi Giulio,

That is interesting. I never noticed that.

I think your method is a good workaround. There is also an option in
WriteExcel to turn the gridlines off via hide_gridlines(). However,
that may not always be what you want.

John.
--

>
> --
> giul...@pobox.com