It will take a while to understand your code fully but try this and tell me if it is on the right track

#!/usr/local/bin/perl 
use strict;
use Excel::Writer::XLSX;
use Time::Piece;

my $date = localtime->strftime('%d%b%Y');
my $filename = "Weekly_Status_${date}.xlsx";
my $book = Excel::Writer::XLSX->new($filename);
my $fmt_merge = $book->add_format(
    border => 1,
    valign => 'vcenter',
    align  => 'center',
);

my $sheet = $book->add_worksheet("PtCorner_Summary_$date");
$sheet->set_row(0,20);
$sheet->set_column('A:A',18);
$sheet->set_column('B:Z',10);
$sheet->write('A1', "PtCorner Wise Summary $date");
$sheet->write_blank('B1');
$sheet->write('A2', "Block");
$sheet->write('B2', "Owner");
$sheet->freeze_panes(3,4);

my @ptsliceorder = qw( 
 func_ss_dir1 func_ss_dir2 func_ss_dir3 func_ss_di4 
 func_ff_dir1 func_ff_dir2 func_ff_dir3 func_ff_dir4 
 func_ff_dir5 func_ff_dir6 func_ff_dir7 func_ff_dir8 ); 

my @sliceorder = qw(sr2r hr2r si2r sr2o hi2r hr2o);

my $col = 4;
my $col0;
my $col1;
for my $ptOrder (@ptsliceorder) {
  $col0 = $col; # start col for merge
  for my $slice (@sliceorder) {
    $col1 = $col; # start col for merge
    my @f;
    if ($slice =~ m/hold/i) {
      @f = ('wns','ths','hfep');
    } else { 
      @f = ('wns','tns','fep');
    }
    for my $value (@f){
      $sheet->write(2,$col++, $value);
    }
    $sheet->merge_range( 1,$col1,1,$col-1,$slice, $fmt_merge);
  }
  $sheet->merge_range( 0,$col0,0,$col-1,$ptOrder, $fmt_merge);
}

$book->close();
print "$filename created\n";

[download]
poj

In reply to Re^3: create xlsx by poj
in thread create xlsx by gpremala

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.