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

[download]

In reply to Unable to retain the format in copying from one excel file to another by nemadepsn

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.