#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Excel::Writer::XLSX;
use Spreadsheet::Read qw( ReadData );

# read from all spreadsheets
my @spreadsheets = ( 'test_file1.xlsx' , 'test_file2.xlsx' );

my %HoH;
foreach my $spreadsheet (@spreadsheets) {
    my $book = ReadData ($spreadsheet);

    my @sheets = @$book[0]->{sheets};

    foreach my $sheet (@sheets) {
    my @rows = Spreadsheet::Read::rows($book->[$sheet]);
    $HoH{$spreadsheet}{$sheet} = \@rows;
    }
}

my $outputXLSX = 'simple.xlsx';
my $workbook  = Excel::Writer::XLSX->new( $outputXLSX );
my $worksheet = $workbook->add_worksheet();

my %hash;
foreach my $file ( sort keys %HoH ) {
    foreach my $sheet ( sort keys %{ $HoH{$file} } ) {
    $workbook->add_worksheet() if $sheet > 1;
    foreach my $i (1 .. scalar @{$HoH{$file}{$sheet}}) {
        foreach my $j (1 .. scalar @{$HoH{$file}{$sheet}[$i-1]}) {
        if ( grep { $_ eq chr(64+$j) . $i } keys %hash ) {
            my @keys = sort keys %hash;
            my ($char, $lastIndex) = split //, $keys[-1];
            $lastIndex++;
            $worksheet->write(chr(64+$j) . $lastIndex,
                      ($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
            $hash{chr(64+$j) . $lastIndex} =
            ($HoH{$file}{$sheet}[$i-1][$j-1] // '');
        }
        else {
            $hash{chr(64+$j) . $i} =
            ($HoH{$file}{$sheet}[$i-1][$j-1] // '');
            $worksheet->write(chr(64+$j) . $i,
                      ($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
        }
        }
    }
    }
}

print Dumper \%hash;

$workbook->close;

# remove output file if you are
# going to rerun the script
# I used it for debuging purposes
# unlink($outputXLSX);

__END__

$ perl excel.pl
$VAR1 = {
          'A3' => 'Line 3',
          'A4' => 'Line 4',
          'A1' => 'Line 1',
          'A2' => 'Line 2'
        };
[download]