Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

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 cooling their heels in the Monastery: (9)
As of 2018-04-25 15:01 GMT
Find Nodes?
    Voting Booth?