Adding Borders to Cells using Excel::Writer::XLSX

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

I have been trying to add borders only to the top sides of cells in Excel using the Excel::Writer module. The code samples now follows:



   $worksheet->set_row( 8, 0 );
   $worksheet->set_column( 'A:B', 30 );
   $worksheet->set_border('A:B',30); # Set borders on this range
   $worksheet->set_top('A:B',30); #Set border only to top
    
my $format_box = $workbook->add_format(
        border    => 1,
        underline => 0,
        color     => 'black',
        align     => 'left',
        valign    => 'top',
       );
[download]


my $format_box = $workbook->add_format(
        #border    => 1,
        underline => 0,
        color     => 'black',
        align     => 'left',
        valign    => 'top',    
    );

    $format_box = set_border(1); # Turn on borders
    $format_box = set_top(1); # Set value to true
[download]

Neither approaches work. What am I doing wrong?

Comment on Adding Borders to Cells using Excel::Writer::XLSX Select or Download Code

Replies are listed 'Best First'.
Re: Adding Borders to Cells using Excel::Writer::XLSX by roboticus (Chancellor) on Jul 07, 2014 at 15:37 UTC
GuiPerl: It works for me. Are you specifying the format when you write to the spreadsheet? `my $fmt_hdr = $workbook->add_format( border=>1, color=>'blue' ); my $fmt_cell = $workbook->add_format( border=>1, color=>'black' ); . . . $worksheet->write($row, $col, "HDR", $fmt_hdr); $worksheet->write($row+1, $col, "value", $fmt_cell);` [download] ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re^2: Adding Borders to Cells using Excel::Writer::XLSX by GuiPerl (Acolyte) on Jul 07, 2014 at 17:14 UTC
I had to change coding. This little example will work: `use strict; use warnings; use Excel::Writer::XLSX; my $workbook = Excel::Writer::XLSX->new( "Cell_Borders.xlsx" ); my $worksheet = $workbook->add_worksheet(); my $textbox1 ="Hello World!"; my $format_box = $workbook->add_format(); $format_box->set_top('5'); $format_box->set_bottom('5'); $worksheet->merge_range( 'A8:B8', "$textbox1", $format_box );` [download] This will add a border on the top and bottom cell. To fix the problem, I had to add the formatting calls individual (i.e. $format_box->set_top()) and not the to add_format() method.	[reply] [d/l]
Re^3: Adding Borders to Cells using Excel::Writer::XLSX by poj (Abbot) on Jul 07, 2014 at 17:23 UTC
`set_border(), set_top(), etc` are `FORMAT` methods not `WORKSHEET or WORKBOOK` methods. `#!perl use strict; use Excel::Writer::XLSX; my $workbook = Excel::Writer::XLSX->new( 'borders.xlsx' ); my $worksheet = $workbook->add_worksheet(); my $fmt_top = $workbook->add_format(); $fmt_top->set_top(); $worksheet->set_column( 'A:B', 30, $fmt_top );` [download] see Excel::Writer::XLSX poj	[reply] [d/l] [select]