#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use feature 'say';
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]}) {
		next if ($HoH{$file}{$sheet}[$i-1][$j-1] // '') eq '';
		if ( grep { $_ eq chr(64+$j) } keys %hash ) {
		    if (grep { $_ eq $i} sort keys %{$hash{chr(64+$j)}}) {
			my $incrementRow = $i;
			while (grep { $_ eq $i} sort keys %{$hash{chr(64+$j)}}){
			    $incrementRow++;
			    last if ($i ne $incrementRow);
			}
			$worksheet->write(chr(64+$j) . $incrementRow,
					  ($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
			$hash{chr(64+$j)}{$incrementRow} =
			    ($HoH{$file}{$sheet}[$i-1][$j-1] // '');
		    }
		    else {
			$worksheet->write(chr(64+$j) . $i,
					  ($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
			$hash{chr(64+$j)}{$i} =
			    ($HoH{$file}{$sheet}[$i-1][$j-1] // '');
		    }
		}
		else {
		    # say "Column Index: " . $j . " Column Character: " . chr(64+$j) . " Text: " .  ($HoH{$file}{$sheet}[$i-1][$j-1] // '');
		    $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 = {
          'Z' => {
                   '2' => 'Line Z1 File 2',
                   '1' => 'Line Z1 File 1',
                   '5' => 'Line Z5 File 1',
                   '6' => 'Line Z5 File 2'
                 },
          'A' => {
                   '2' => 'Line A1 file 2',
                   '3' => 'Line A2 file 2',
                   '1' => 'Line A1 file 1'
                 },
          'B' => {
                   '3' => 'Line B2 file 2',
                   '1' => 'Line B1 File 1',
                   '2' => 'Line B1 file 2'
                 }
        };

##</code><code>##

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

# 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]}) {
		next if ($HoH{$file}{$sheet}[$i-1][$j-1] // '') eq '';
		my $cell = cr2cell ($j, $i);
		my ($column, $row) = split /(?<=\D)(?=\d)/, $cell;
		if ( grep { $_ eq $column } keys %hash ) {
		    if (grep { $_ eq $row} sort keys %{$hash{$column}}) {
			my $incrementRow = $row;
			while (grep { $_ eq $row} sort keys %{$hash{$column}}){
			    $incrementRow++;
			    last if ($row ne $incrementRow);
			}
			write2ExcelFile($column,
					$incrementRow,
					($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
		    }
		    else {
			write2ExcelFile($column,
					$row,
					($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
		    }
		}
		else {
		    write2ExcelFile($column,
				    $row,
				    ($HoH{$file}{$sheet}[$i-1][$j-1] // ''));
		}
	    }
	}
    }
}

print Dumper \%hash;

$workbook->close;

sub write2ExcelFile {
    my ( $column , $row , $data ) = @_;
    $worksheet->write($column . $row, $data);
    $hash{$column}{$row} = $data;
    return;
}

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

__END__

$ perl excel.pl
$VAR1 = {
          'B' => {
                   '3' => 'Line B2 file 2',
                   '1' => 'Line B1 File 1',
                   '2' => 'Line B1 file 2'
                 },
          'AA' => {
                    '2' => 'Line AA1 File 2',
                    '1' => 'Line AA1 File 1'
                  },
          'A' => {
                   '3' => 'Line A2 file 2',
                   '2' => 'Line A1 file 2',
                   '1' => 'Line A1 file 1'
                 },
          'Z' => {
                   '1' => 'Line Z1 File 1',
                   '2' => 'Line Z1 File 2',
                   '5' => 'Line Z5 File 1',
                   '6' => 'Line Z5 File 2'
                 }
        };