how do I draw a border around a select range of cells?

[daprie]

I am having GREAT success using this module for the first time, but I
have run into one problem that I can't seem to get around. I can't
figure out how to draw a box around a select range of cells (i.e. cell
range - A1:I8) using a thick border. Please let me know if there is
an easier way to do this or a way to fix what I have already done.

Below is the code I have put together so far. It is a variation on
the tab2xls.pl code provided in the package on CPAN. It just seems to
create borders that go on forever. :)

use strict;
use Spreadsheet::WriteExcel;

# Check for valid number of arguments
if (($#ARGV < 1) || ($#ARGV > 2))
{
die("Usage: camp_waterfall_tab2xls tabfile.tab newfile.xls\n");
};

# Open the tab delimited file
open (TABFILE, $ARGV[0]) or die "$ARGV[0]: $!";

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new($ARGV[1]);
my $worksheet = $workbook->add_worksheet();

my $format1 = $workbook->add_format(top => 5, bottom => 5);
my $format2 = $workbook->add_format(bottom => 1);
my $format3 = $workbook->add_format(right => 1);
my $format4 = $workbook->add_format(right => 1, bottom => 5);
my $format5 = $workbook->add_format(left => 5);
my $format6 = $workbook->add_format(right => 5);
my $format7 = $workbook->add_format(top => 1, bottom => 1, right => 1,
left => 1, align => 'center');

$worksheet->set_column('A:A', 34.43, $format5); # Column A width set
to 34.43
$worksheet->set_column('B:B', 10.14); # Column B width set to 10.14
$worksheet->set_column('C:C', 9.29); # Column C width set to 9.29
$worksheet->set_column('D:D', 9.43); # Column D width set to 9.43
$worksheet->set_column('E:E', 8.43); # Column E width set to 8.43
$worksheet->set_column('F:F', 15.57); # Column F width set to 15.57
$worksheet->set_column('G:G', 10.14); # Column G width set to 10.14
$worksheet->set_column('H:H', 9.29); # Column H width set to 9.29
$worksheet->set_column('I:I', 5.00, $format6); # Column I width set to
5.00

$worksheet->set_row(0, 13.50, $format1); # Row 1 formated
$worksheet->set_row(1, 13.50, $format2); # Row 2 formated
$worksheet->set_row(2, 13.50, $format2); # Row 3 formated

$worksheet->set_row(3, 13.50, $format3); # Row 4 formated
$worksheet->set_row(4, 13.50, $format3); # Row 5 formated
$worksheet->set_row(5, 13.50, $format3); # Row 6 formated
$worksheet->set_row(6, 13.50, $format3); # Row 7 formated
$worksheet->set_row(7, 13.50, $format4); # Row 8 formated

# Row and column are zero indexed
my $row = 0;

while (<TABFILE>)
{
chomp;
# Split on single tab
my @Fld = split('\t', $_);

my $col = 0;
foreach my $token (@Fld)
{
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}

$worksheet->merge_range('B3:C3', 'Allocated to', $format7);
$worksheet->merge_range('G3:H3', 'Allocated to', $format7);

Thanks,
daprie

[jmcnamara]

On May 14, 3:47 pm, daprie <dap...@gmail.com> wrote:
> I am having GREAT success using this module for the first time, but I
> have run into one problem that I can't seem to get around. I can't
> figure out how to draw a box around a select range of cells (i.e. cell
> range - A1:I8) using a thick border. Please let me know if there is
> an easier way to do this or a way to fix what I have already done.

Hi,

Spreadsheet::WriteExcel doesn't provide a facility to apply a format
to a range of cells (apart from an entire row or column).

Therefore, you will have to make a write() call with a format for each
cell that you want formatted.

If you are familiar with Perl templating mechanisms you may find
Excel::Template or Excel::Template::TT more useful for this sort of
thing.

John.
--