perl Excel::Writer::XLSX generates outline that is offset by 1 row

Question

I am trying to build a spreadsheet with outlined rows using perl module Excel::Writer::XLSX. I need the row outline symbol to appear above the outline level bar. When I run the script below, the outline symbols and level bars are correctly generated, but shifted down one row.

use strict;
use Excel::Writer::XLSX;

my $visible = 1;
my $symbols_below = 0;
my $symbols_right = 0;
my $auto_style = 0;

my $output_rpt = 'report.xlsx';
my $color1 = "#CBBEA0";
my $color2 = "#DDD7C5";

my $workbook   = Excel::Writer::XLSX->new( $output_rpt );
my $worksheet1 = $workbook->add_worksheet( 'report' );
$worksheet1->outline_settings($visible, $symbols_below, $symbols_right, $auto_style);

my $format1  = $workbook->add_format( bold => 1, bg_color => $color1, align => 'center' );
my $format2  = $workbook->add_format( bold => 1, bg_color => $color1, align => 'left'  );
my $format3  = $workbook->add_format( bold => 1, bg_color => $color2, align =>'left'  );
my $format4  = $workbook->add_format( align => 'center'  );

my @last = qw( Smith Jones Panglossian );
my @first = qw( Algonquin Mephibosheth Noah );
my @age = (24, 42, 962);
my @hidden    = (0, 0, 0);
my @level     = (0, 1, 1);
my @collapsed = (1, 0, 0);

for my $idx (0..2) {
    my $row = $idx+1;
    $worksheet1->set_row( $row, undef, undef, $hidden[$idx], $level[$idx], 
$collapsed[$idx] );
$worksheet1->write( 'A' . $row, $first[$idx], $format2);
$worksheet1->write( 'B' . $row, $last[$idx], $format3);
$worksheet1->write( 'C' . $row, $age[$idx], $format4);
}
$workbook->close();

The output spreadsheet is in the image below. Note that the outline bar is offset by 1 row

output spreadsheet

In the code: The collapse symbol is placed in row 1.and the outline bar is set to span rows 2 and 3. .In the resulting spreadsheet: The collapse symbol is in row 2 (one row below where I put it, row 1). the outline bar spans rows 3 and 4. (also 1 row below the span of 2 and 3). The outline bar is spanning a row that was never even mentioned in the code (row 4) — GMC, Mar 02 '18 at 21:08
My question is: What am I missing to make the outline span the rows that I want it to span? — GMC, Mar 02 '18 at 21:10

haukex · Accepted Answer · 2018-03-03T09:00:23.773

If you look at the Excel::Writer::XLSX documentation for the set_row method, you will see:

$worksheet->set_row( 0, 20 );    # Row 1 height set to 20

Which means that the index is 0-based, but you are giving it $idx+1. Change that line into the following and it should work as you expect (it worked for me when I tested it on my end):

$worksheet1->set_row( $idx, undef, undef, $hidden[$idx], $level[$idx], $collapsed[$idx] );
#                      ^^^

(There does appear to be a mistake a little later on in the documentation, where one of the examples shows a 1-based index. Issue reported.)

perl Excel::Writer::XLSX generates outline that is offset by 1 row

1 Answers1