Unable to retain the format in copying from one excel file to another

Question

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('template.xls');

my $parser1   = Spreadsheet::ParseExcel::SaveParser->new();
my $template1 = $parser1->Parse('test_perl.xls');

my $worksheet11 = $template->worksheet(0);

 $template1->AddWorksheet('New Data');

my $worksheet22 = $template1->worksheet(0);

my $cellz; my $valua;my $format_number;
    for (my $i = 0; $i < 400; $i++) {
        for (my $j = 0; $j < 20; $j++) {
            $cellz = $worksheet11->get_cell( $i, $j );
            if($cellz){
                $valua = $cellz->unformatted(); $format_number = $cellz->{FormatNo};
                $worksheet22->AddCell($i, $j, $valua,$format_number);
            }
        }   
    }

my $workbook;
$workbook = $template1->SaveAs('newfile1.xls');

I want to copy content from one excel file to another retaining the format too. Here by format I mean fill color, border, bold, italic, etc. I have written a code for this where I extract the value and format number from one excel file and simply write in the other excel sheet. I have referred the following link for that.

https://metacpan.org/pod/Spreadsheet::ParseExcel::SaveParser

The problem is that it is not retaining the format. I think the problem is that the format number is not universal which means that a format number means two different things in two different excel files. When I run this code I get the error

Use of uninitialized value in hash element at /pkg/qct/software/perl/5.18.0_test/lib/site_perl/5.18.0/Spreadsheet/ParseExcel.pm line 2383.

According to me, it means that the extracted format number doesn't mean anything to other file. Please let me know solution to this problem

Basically what my problem is that I have modified two xls file using Spreadsheet::ParseExcel::SaveParser and I want to merge those two xls files using Perl. Please suggest a way of merging two xls files using Perl in any other way such that formatting is retained. Please suggest non-Perl way too using some other coding language.

This question is also cross posted on perl-monks : https://perlmonks.org/index.pl?node_id=11101251

Answer 1

The solution to the above problem is same as what @simbabque said in the comments. You have to extract all properties of the format of every cell and create new format for each cell using these extracted properties. As very correctly said by @simbabque, the number of properties are fixed. Please see the below link:

https://metacpan.org/pod/Spreadsheet::ParseExcel#Format

I am also attaching the final code for merging two xls files. In this code two xls files are taken as argument and are merged into first one and saved with name "testing.xls".

Thanks @simbabque for your help. Please post your comment as a answer and I will accept it as a solution.

Following is the code:

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;

print "FILE 1 $ARGV[0]"."\n";
my $parser1   = Spreadsheet::ParseExcel::SaveParser->new();
my $template1 = $parser1->Parse($ARGV[0])  or die "Unable to open first xls file";

my $func_worksheet_count = $template1->worksheet_count();

my $parser2   = Spreadsheet::ParseExcel::SaveParser->new();
my $template2 = $parser2->Parse($ARGV[1])  or die "Unable to open second xls file";

my $test_worksheet_count = $template2->worksheet_count();

print "FILE 2 $ARGV[1]"."\n";


my $worksheet22;
my $worksheet11;
my $sheet_name;

 for (my $i = 0; $i < $test_worksheet_count; $i++) {
    $worksheet22 = $template2->worksheet($i);
    $sheet_name = $worksheet22->get_name();
        print $sheet_name."\n";
    $template1->AddWorksheet($sheet_name);
 } 

 my $font;
 my $AlignH;
 my $AlignV; 
 my $Indent;
 my $Wrap;
 my $Shrink;
 my $Rotate;
 my $JustLast;
 my $ReadDir;
 my $BdrStyle;
 my $BdrColor;
 my $BdrDiag;
 my $Fill;
 my $Lock;
 my $Hidden;
 my $Style;

 my $iFmt;
 my $format;
my $iF1;





 for (my $k = 0; $k < $test_worksheet_count; $k++) {

    $worksheet22 = $template2->worksheet($k);
    $worksheet11 = $template1->worksheet($func_worksheet_count+$k); 


    my $cellz; my $valua;my $format_number;
    for (my $i = 0; $i < 1000; $i++) {
        for (my $j = 0; $j < 30; $j++) {
            $cellz = $worksheet22->get_cell( $i, $j );
            if($cellz){
                $valua = $cellz->value(); $format_number = $cellz->{FormatNo};
                $format = $cellz->get_format(); 
                 $font = $format->{Font};
                 $AlignH = $format->{AlignH};
                 $AlignV = $format->{AlignV}; 
                 $Indent = $format->{Indent};
                 $Wrap = $format->{Wrap};
                 $Shrink = $format->{Shrink};
                 $Rotate = $format->{Rotate};
                 $JustLast = $format->{JustLast};
                 $ReadDir = $format->{ReadDir};
                 $BdrStyle = $format->{BdrStyle};
                 $BdrColor = $format->{BdrColor};
                 $BdrDiag = $format->{BdrDiag};
                 $Fill = $format->{Fill};
                 $Lock = $format->{Lock};
                 $Hidden = $format->{Hidden};
                 $Style = $format->{Style};


                 $iFmt =
                $template1->AddFormat(
                    Font => $font,
                    AlignH => $AlignH,
                    AlignV => $AlignV,
                    Indent => $Indent,
                    Wrap => $Wrap,
                    Shrink => $Shrink,
                    Rotate => $Rotate,
                    JustLast => $JustLast,
                    ReadDir => $ReadDir,
                    BdrStyle => $BdrStyle,
                    BdrColor => $BdrColor,
                    BdrDiag => $BdrDiag,
                    Fill => $Fill,
                    Lock => $Lock,
                    Hidden => $Hidden,
                    Style     =>  $Style,
                );


                $worksheet11->AddCell($i, $j, $valua, $iFmt);
            }
        }   
    }
 }



my $workbook3 = $template1->SaveAs("testing.xls");