Hello again mnakkach,

Although I agree with the fellow Monks, you did not showed us any effort on trying to resolve your problem, at the same time a I am a firm believer that every question right or wrong should be answered to some extend at least.

I have seen people asking on how to append excel sheets, so I thought to give it a try and try to play around with this issue for future reference in case that someone might need an answer to some extend (answer).

The script bellow that I wrote will do the very basic in just reading the characters in the cells and simply append them on the each sheet that is read by each document. The script should be able to read multiple input spread sheets files, with multiple sheets each file and be able to handle them. I have not fully tested it, just minor tests I have run, any improvements feel free to add.

#!/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]

Hope this helps, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!

In reply to Re: Merging 2 xlsx files by thanos1983
in thread Merging 2 xlsx files by mnakkach

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.