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

[nemadepsn]

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 has been cross-posted on stackoverflow : https://stackoverflow.com/questions/56545694/unable-to-retain-the-format-in-copying-from-one-excel-file-to-another

#!/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 = $cell
+z->{FormatNo};
                $worksheet22->AddCell($i, $j, $valua,$format_number);
            }
        }   
    }

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

[dsheroh]

It's generally considered polite to mention when a question is cross-posted to another site (this one also appears on StackOverflow) so that respondents know not to make the effort of posting answers which have already been provided elsewhere.

[nemadepsn]

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 ?

[FreeBeerReekingMonk]

First thing I would do is to understand the format. Save your files as xlsx, then change the extension to .zip, unzip them and peruse the data.

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.

[nemadepsn]

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");

2019-07-28 Athanasius linkified the link

[haukex]

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? ;-)

[nemadepsn]

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