#!/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'
        };