Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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

by nemadepsn (Initiate)
on Jun 11, 2019 at 14:30 UTC ( #11101251=perlquestion: print w/replies, xml ) Need Help??

nemadepsn has asked for the wisdom of the Perl Monks concerning the following question:

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');
  • Comment on Unable to retain the format in copying from one excel file to another
  • Download Code

Replies are listed 'Best First'.
Re: Unable to retain the format in copying from one excel file to another
by dsheroh (Prior) on Jun 11, 2019 at 15:22 UTC
    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.
      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 ?
Re: Unable to retain the format in copying from one excel file to another
by FreeBeerReekingMonk (Deacon) on Jun 14, 2019 at 00:06 UTC
    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11101251]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (9)
As of 2019-06-25 19:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (107 votes). Check out past polls.

    Notices?