Sorry for that. It was my first time. Can you suggest a solution other than copying every property of object format as mentioned in comments in the stackoverflow post ?

You can see that the data (text and numbers) is in one xml file and the styles (and which cell has what style) are kept in another. So, you will need 2 passes:

1. Need to check all unique formats your source data range uses, and copy only those (with an xml Perl module) then reference to them in your copied cells, but they will have a new format index number, which you will have to keep in a hash.

2. Once you update the style xml in the destination file (you can manipulate zip files with Perl), you can reopen the destination spreadsheet, and only paste the cell values, as their format was "pasted" in the first pass.

Also checked PHP:spreadsheet and big nope there too (no easy style cloning). Doing it cell by cell will make your styles xml much bigger than detecting similar styling and applying ranges, but if it's not many cells, it should be manageable.

Alternative 3:

LibreOffice has a commandline transformation (search for soffice --headless --convert-to), you could output to html, and then use xml parsers to parse everything, then convert the html back to an excel format. But I've noticed that things get lost in translation and the new xls will have lost formatting. (then again, I have an ancient Debian, so ymmv). Did not check .fods format, seems much more complete.

I have posted the solution on stackoverflow

Please see the below link

https://stackoverflow.com/questions/56545694/unable-to-retain-the-format-in-copying-from-one-excel-file-to-another/57095816#57095816

#!/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 firs
+t 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 seco
+nd 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->{Fo
+rmatNo};
                $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");
[download]

2019-07-28 Athanasius linkified the link

I have posted the solution on stackoverflow
Please see the below link

Thank you very much for the update, but why not post the code here as well for some extra XP? ;-)

I didn't know that. I will do that. Thanks for the info :)