Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Moving Excel cells from one worksheet to another

by TravelAddict (Novice)
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!

Replies are listed 'Best First'.
Re: Moving Excel cells from one worksheet to another
by hdb (Monsignor) 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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1031405]
Approved by ww
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2017-11-24 23:01 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (354 votes). Check out past polls.