Hi Thanos,

Here is code which wrote. I'm a perl beginner, I may have some mistakes here. 

#!/usr/local/bin/perl 

use List::MoreUtils qw( pairwise );
use Spreadsheet::WriteExcel;
use Excel::Writer::XLSX;

my $date = `date +%d%b%Y` ;
chomp($date);

my $FileName = "Weekly_Status_${date}.xlsx";
my $workbook = Excel::Writer::XLSX->new($FileName);

  my $worksheet2 = $workbook->add_worksheet("PtCorner_Summary_$date");
  $worksheet2->set_row('0',20);
  $worksheet2->set_column('A:B',18);
  $worksheet2->set_column('B:Z',10);
  $worksheet2->freeze_panes('3','4');

  my @ptsliceorder = &getSubDirs("timing/rpts"); 
  #the above will get list of dirs (func_ss_dir1, func_ss_dir2, func_s
+s_dir3, func_ss_di4, func_ff_dir1, func_ff_dir2, func_ff_dir3, func_f
+f_dir4, func_ff_dir5, func_ff_dir6, func_ff_dir7, func_ff_dir8); 
  #here i get order of @ptsliceorder random, need to sort it to get ex
+actly as above.
  my @sliceorder = (sr2r, hr2r, si2r, sr2o, hi2r, hr2o);
  my %sliceIn;
  foreach $ptOrder (@ptsliceorder) {
    foreach $slice (@sliceorder) {
      if ($slice =~ m/hold/i) {
        $sliceIn{$ptOrder}{$slice} = "wns ths hfep";
      } else { $sliceIn{$ptOrder}{$slice} = "wns tns fep";}
    }
  }
  ##2nd Sheet Header Section
  ## Header section
  $flag  = 1 ;
  $row = 0;
  $col = 0;
  $nRow = $row   + 1 ;
  
  $worksheet2->write($row,$col++,  "PtCorner Wise Summary $date",     
+  $yellow    );
  $worksheet2->write_blank($row,$col++,           $yellow    );
  
  $col = 0;
  $nRow = $row   + 1 ;
  $worksheet2->write($nRow,$col++,  "Block",       $reg    );
  $worksheet2->write($nRow,$col++,  "Owner",      $reg    );

  #I got stuck here in between #### lines.
######################################################
foreach $slice (@ptsliceorder) {
  foreach $order (@sliceorder) {
    if ($flag == 1) { 
      $flag  =  0 ;
      $color = $orange ;
    } else {
      $flag  =  1 ;
      $color = $yellow ;
    }
    @pt = keys %{$sliceIn};
    @ptsect = keys %{$sliceIn{$slice}};
    @sect = split (" ", $sliceIn{$slice}{$order});
    #print "my pt @pt, my ptsect @ptsect, my scet @sect\n";
    $nCol = ($#ptsect+1)*($#sect+1)+1;
    $ncol = $#sect + 1 ;
    $nRow = $row   + 1 ;
    $nrow = $nRow  + 1 ;
    $ptcnt = -1 ;
    $cnt  = -1 ;
    $ptcol = $col;
 
    $worksheet2->write($row,$ptcol,"$slice", $color);
    $worksheet2->write($nRow,$ptcol++,"$ptsect[$ptcnt++]", $reg);
    while ($nCol > 1) {
      $nCol = $nCol - 1 ;
      $worksheet2->write_blank($row,$ptcol,           $color    );
      $worksheet2->write($nRow,$ptcol++,  "$ptsect[$ptcnt++]",        
+ $color    );
    }
    $worksheet2->write($nrow,$col++,"$sect[$cnt++]", $reg);
    while ($ncol > 1) {
      $ncol = $ncol - 1 ;
      $worksheet2->write_blank($nRow,$col,           $color    );
      $worksheet2->write($nrow,$col++,"$sect[$cnt++]", $reg);
    }
  }
}

foreach $ptOrder (@ptsliceorder) {
  foreach $order (@sliceorder) {
    $target = $order ;
    #printf "  Switch processing ... $target :: @sliceorder :: $ptOrde
+r\n";
######################################################################
+##########
    switch ($target) {
          case /\bsr2r\b/   {
                 if ( $stage !~ m/tim/ ) {
                   $sr2rwns =  "NA"; $sr2rtns = "NA"; $sr2rvep = "NA";
+ 
                 } elsif ( $stage =~ m/tim/ ) {
                   
                   print "my rpt => $rpt\n";
                   $rpt = "timing/rpts/$ptOrder/${ptOrder}.slack_metri
+cs.gz";
                   if ( ! -e $rpt ) { $sr2rwns =  "NA"; $sr2rtns = "NA
+"; $sr2rvep = "NA"; } else {
                     @Sr2r = getPtTiming($rpt,"clktoclk","Setup");
                     $sr2rwns = $Sr2r[0]; $sr2rtns = $Sr2r[1]; $sr2rve
+p = $Sr2r[2];
                   }
                 }
                     $worksheet2->write($tileCnt,$col++,  $sr2rwns,   
+ $reg     );
                     $worksheet2->write($tileCnt,$col++,  $sr2rtns,   
+ $reg     );
                     $worksheet2->write($tileCnt,$col++,  $sr2rvep,   
+ $reg     );
                 }
          etc...
    }
  }
}
$rowStart = 4;
$rowEnd   = 2 ;
$col      = 2;

$col--; $nMinusCol  = &columnMap($col--) ;
$autoFilCol = "$colText"."3";
$worksheet2->autofilter("A3:$autoFilCol");
$worksheet2->set_column("$colText:$colText",80);

$lastCel    = "$nMinusCol"."$rowEnd";
$worksheet2->conditional_formatting("A4:$lastCel", { type => 'text', c
+riteria => 'ends with', value => 'NA', format => $coral });
$workbook->close();

sub getSubDirs {
  my $path = $_[0];
  opendir(DIR, $path) or die $!;
  while (my $file = readdir(DIR)) {
    # Use a regular expression to ignore files beginning with a period
    next if ($file =~ m/^\./);
    # A file test to check that it is a directory
    # Use -f to test for a file
    next unless (-d "$path/$file");
    #print "$file\n";
    push @sub_dirs, $file;
  }
  closedir(DIR);
  return @sub_dirs;
}

[download]

my desired Output in xlsx: 

                                func_ss_dir1                          
+                                              func_ss_dir1
      sr2r           hr2r          si2r             sr2o           hi2
+r         hr2o           sr2r         hr2r          si2r             
+sr2o           hi2r         hr2o
  wns tns fep     wns tns fep   wns tns fep    wns tns fep  wns tns fe
+p  wns tns fep  wns tns fep   wns tns fep   wns tns fep    wns tns fe
+p  wns tns fep  wns tns fep
  -1   -2  2     -1   -2  2    -1   -2  2     -1   -2  2   -1   -2  2 
+  -1   -2  2   -1   -2  2    -1   -2  2    -1   -2  2     -1   -2  2 
+  -1   -2  2   -1   -2  2

[download]

The above values are $sr2rwns $sr2rtns $sr2rfep....

In reply to Re^2: create xlsx by gpremala
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.