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

Hi,

Wanted to create xlsx for below array values.

@array1 = "ss_dir1 ss_dir2 ss_dir3 ss_di4 ff_dir1 ff_dir2 ff_dir3 ff_d
+ir4 ff_dir5 ff_dir6 ff_dir7 ff_dir8";
@array2 = "sr2r hr2r si2r sr2o hi2r hr2o";
@array3 = "wns tns fep";

[download]

whith the above arrays created a hash.

Example.

'ss_dir1' => { 'sr2r' => 'wns tns fep'
               'hr2r' => 'wns tns fep'
               'si2r' => 'wns tns fep'
               'sr2o' => 'wns tns fep'
               'hi2r' => 'wns tns fep'
               'hr2o' => 'wns tns fep'
             }
'ss_dir2' => { 'sr2r' => 'wns tns fep'
               'hr2r' => 'wns tns fep'
               'si2r' => 'wns tns fep'
               'sr2o' => 'wns tns fep'
               'hi2r' => 'wns tns fep'
               'hr2o' => 'wns tns fep'
             }

[download]

etc..

And i need to get foreach @array1 i'll have @array2 and foreach @array2 i'll have @array3.

and foreach @array3 i'll get the values from a file inside ss_dir1 dir...

Please help me.

Thanks,

gpremala

2018-06-01 Athanasius added code and paragraph tags

Replies are listed 'Best First'.
Re: create xlsx
by Corion (Patriarch) on May 31, 2018 at 15:05 UTC
[reply]
Re: create xlsx
by thanos1983 (Parson) on May 31, 2018 at 14:56 UTC

    Hello gpremala,

    Welcome to the Monastery. Please enclose the sample of your code in between code tags e.g. <code>.

    It is very difficult to read your question and assist you the way that it is currently written.

    Read more here: How do I post a question effectively?.

    Update: I spend some time and I tried to decode your sample of code. From your sample of arrays it is correctly defined. e.g. @array = "1 2 3"; this is not exactly how array are defined. An array can be something like @array1 = (1, 2, 3);.

    Second part that I noticed from your sample of code. The third array @array3 = "wns tns fep"; it is not an array it is a string the way that you demonstrate it on your hash of hashes. Sample of hash of hashes of arrays your code:

    Read more... (899 Bytes)

    If I understand correctly you have data from a hash of hashes of arrays e.g.:

    Read more... (2 kB)

    Final point that it is not clear to me, what part are you trying to print to excel file? What exactly fields you want to print? For example A1 input A A2 input B etc. etc.

    We need more information, so far what you done and where you got stack?

    BR / Thanos

    Looking forward to your update.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
      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....

[reply]
[d/l]
[select]

        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
[reply]
[d/l]
Re: create xlsx
by anonymized user 468275 (Curate) on Jun 01, 2018 at 11:48 UTC
    I don't believe that this is what happened. e.g. 
    use Data::Dumper;

my @ar = "fred bert";

print Dumper(\@ar);

    [download]
    produces 
    $VAR1 = [
          'fred bert'
        ];

    [download]
    I can't match the summary with your full code either to see where it doesn't DWIM from your perspective. Perhaps you could step through the code using perl -d. Enter the h command to get the list of commands when using the perl debugger. At each step, the x command can be used to examine variables to see whether the last executed statement performed as you expected.

    One world, one people

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom