How do I append excel sheets into a single excel file by grepping on file creation time

#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };



my $dir = '/v/region/na/appl/qatools/util/data/reports/automation_framework/Results';

# get the files sorted by date
opendir DIR, $dir or die "could not open dir $dir: $!";
my @files = sort { (-M "$b") <=> (-M "$a") } grep { /\.xls/ and -f $_ } readdir DIR;
closedir DIR;

# create the new file
my $workbook = Spreadsheet::WriteExcel->new("$dir/final_res.xls") or die "could not create final_res.xls: $!";
my $sheet = $workbook->add_worksheet();
my $frow = 0;

for my $i (0..@files-1) {
    my $parser = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->Parse("$dir/$files[$i]") or die "could not parse $files[$i]: $!";
    my $worksheet = $workbook->workbook(0);
    my ($row_min, $row_max) = $worksheet->row_range();
    my ($col_min, $col_max) = $worksheet->col_range();
    $row_min++ if $i; # skip header after first
    for my $row ($row_min..$row_max) {
        for my $col ($col_min..$col_max) {
            my $cell = $worksheet->get_cell($row, $col);
            next unless defined($cell);
            $sheet->write($frow, $col, $cell->value);
        }
    }
}

Select allOpen in new window