set_align() of merged cells in Spreadsheet::WriteExcel

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Gentlemen,

My first post to this forum.

Using Spreadsheet::WriteExcel. I'm finding that set_align() has no effect when applied to cells that have been merged, either by the merge_cells() method or the set_merge() method. The token always comes out centred.

Here is example code meant to merge all the columns of the fist row up to $last_col, and then right align $page_number:

my $format_page_num = $workbook->addformat();
$format_page_num->set_size(10);
$format_page_num->set_font('courier');
$format_page_num->set_align('right');
$format_page_num->set_merge();

#merge cells and write page_num 
$worksheet->merge_cells(0, 0, 0, $last_column);
$worksheet->write(0, 0, $page_number, $format_page_num);
for $c (1 .. $last_column) {
      $worksheet->write_blank(0, $c, $format_page_num);
}
[download]

Am I doing something wrong, or is this a well-known feature of Spreadsheet::WriteExcel? (Otherwise, a super duper rockin' module.)

Thanks.

Comment on set_align() of merged cells in Spreadsheet::WriteExcel Download Code

Replies are listed 'Best First'.
Re: set_align() of merged cells in Spreadsheet::WriteExcel by jmcnamara (Monsignor) on Feb 10, 2002 at 22:38 UTC
Spreadsheet::WriteExcel is still based primarily on the Excel 5 file format. And in Excel 5 merged cells could only have a "Center" alignment. As such the alignments "right" and "merge" are mutually exclusive. However, you can obtain the effect that you want be using the `merge_cells()` method. This gives you access to some of the features of Excel 97+ merging. Specify the alignment as "right", ignore the "merge" property and force alignment using the `merge_cells()` method: `#!/usr/bin/perl -w use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("reload.xls"); my $worksheet = $workbook->addworksheet(); my $format = $workbook->addformat( align => 'right', border => 2 ); $worksheet->merge_cells("B4:D4"); $worksheet->write("B4", "Hello", $format); $worksheet->write("C4", "", $format); $worksheet->write("D4", "", $format);` [download] The documentation and examples will be clearer on this in the next release. -- John.	[reply] [d/l]
Re: Re: set_align() of merged cells in Spreadsheet::WriteExcel by Anonymous Monk on Feb 11, 2002 at 00:48 UTC
John, Wow - I'm more than a bit impressed to hear from the module author. Thanks for taking the time. I've tested your suggestion, and thought you might find some feedback useful: I'm designing for Excel 2000. In Excel 2000, the code you suggest results in the token being right aligned, but in the first cell, even though the specified cells appear merged. Similarly, changing the alignment to 'center' produces the token centred in the first cell. This therefore solves left-aligning, since specifying align => 'left' left-aligns the token in the first cell. To right align, your suggested code does disengage the Excel 95 'center' default, but I've had to add explicitly writing the token to the last cell with align => 'right', which right aligns the token in the last cell. And to centre-align, I use the merge_cells + set_merge combination of the original post. I hope this is useful for your next revision. Thanks, though, for an overall fab module.	[reply]
Re: set_align() of merged cells in Spreadsheet::WriteExcel by ignatz (Vicar) on Feb 11, 2002 at 17:20 UTC
Why just "Gentlemen"? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ()-() ()-() \"/ DON'T BLAME ME, I VOTED FOR PACO! \"/ ` `	[reply]
Re: Re: set_align() of merged cells in Spreadsheet::WriteExcel by Anonymous Monk on Feb 11, 2002 at 20:18 UTC
I'm sorry, is there a customary, or required, salutation on this board? I did peruse the board briefly before posting, but no such thing caught my attention.	[reply]
Re: Re: Re: set_align() of merged cells in Spreadsheet::WriteExcel by particle (Vicar) on Feb 12, 2002 at 00:54 UTC
i believe ignatz was referencing the gender-specific salutation... perlmonks is an equal opportunity monastary :) ~Particle	[reply]
Re: Salutation by Kozz (Friar) on Feb 12, 2002 at 00:43 UTC
(slightly offtopic, but anyhow...) Usually when I am seeking advice or help, I often begin my posts with, "Most wise monks, ..." I don't think you'll find any that object to that one. ;) By the way - I highly encourage you to register with PM and get a user account. It's quite nice. You can participate in several features that are otherwise unavailable as an Anonymous Monk.	[reply]


XP is just a number
	PerlMonks