Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Moving Excel cells from one worksheet to another

by TravelAddict (Initiate)
on Apr 30, 2013 at 13:52 UTC ( #1031405=perlquestion: print w/ replies, xml ) Need Help??
TravelAddict has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I'm trying to write a script to read hundreds of Excel (XLSX) files and merging all the rows into a new file, while adding one column for the original file name. For that I'm using what I think are the correct XLSX parser and writer:
use Spreadsheet::XLSX; use Excel::Writer::XLSX;
The problem that I have is that the original cells that I'm reading are all formatted and I want to preserve the exact same format in the resulting Excel file. Using a couple of loops, I check every cell of the source files and I write them into the new file:
my $cell_data = $cell -> {Val}; $worksheet->write($row, $col, "$cell_data");
Unfortunately, I'm only getting the data inside the cells (text only) and I'm losing the bold, font, colors, etc. Is there a way to read a cell and copy it "as is" without having to read and transfer all the formatting information? Thanks!

Comment on Moving Excel cells from one worksheet to another
Select or Download Code
Re: Moving Excel cells from one worksheet to another
by hdb (Prior) on Apr 30, 2013 at 13:56 UTC

    If you are on Windows, you can use Win32::Ole and do copy/paste special values&formats. This would preserve all the formatting. If you are sure you have no formulae, then simple paste will do.

      Thanks for your reply, hdb! I never used Win32::OLE but after a first look it seems to be promising. I'll explore and give it a try!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1031405]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2014-09-18 06:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (108 votes), past polls