Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: copy excel sheets to a different workbook

by DutchCoder (Scribe)
on Nov 11, 2008 at 08:04 UTC ( #722801=note: print w/replies, xml ) Need Help??

in reply to copy excel sheets to a different workbook

Why Perl? Excel has functionality to make a macro that should be able do exactly what you describe. The only reason I can think of for doing it with Perl is if you want to avoid using Excel, but seeing that you want to put the information and the original format to another worksheet....? If you are not experienced with Perl, please go another route.
  • Comment on Re: copy excel sheets to a different workbook

Replies are listed 'Best First'.
Re^2: copy excel sheets to a different workbook
by sagnal (Initiate) on Nov 11, 2008 at 08:18 UTC
    I already have a small perl script which gathers data from text files and dumps them into different templates. Each of these template has a summary sheet with lots of tables and formulae. I wanted to make a summary workbook of all the summary sheets from different templates for publishing to managers who will definitely not be interested in lot of data nor will they be inclined in browsing multiple workbooks. I hope I'm making myself clear. Summary is, I can use Excel macro but it will be running two different programs, one for gathering data and another to generate report. If possible, I would lie to use Perl. Thanks again.
Re^2: copy excel sheets to a different workbook
by sagnal (Initiate) on Nov 12, 2008 at 08:43 UTC
    The following peice of code works up to an extent. It does not copy the format of the original file.
    use Spreadsheet::WriteExcel; use Spreadsheet::BasicRead; my $ReportName = $ScriptDir . "\\TR" . ".xls"; my $tlfile= "D:\\Logs\\TL.xls"; my $WorkBook = Spreadsheet::WriteExcel->new($ReportName); my $TL = new Spreadsheet::BasicRead($tlfile) || die "Could not open '$ +tlfile': $!"; while ($TL->getNextSheet()) { my $SheetName = $TL->currentSheetName(); $TL->setRow(0); if ($SheetName eq "SUMMARY") { next; } else { my $Sheet = $WorkBook->add_worksheet($SheetName); my $firstrow = $TL->getFirstRow(); $Sheet->write_row(0, 0, $firstrow); my $col = 0; while (my $nextrow = $TL->getNextRow()) { my $row = $TL->getRowNumber(); print "ROW: $row\n"; $Sheet->write_row($row, 0, $nextrow); } } }
    I have not been able to read formats from the original file.
    Can you please help?
    Thank you.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://722801]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2022-01-27 14:40 GMT
Find Nodes?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:

    Results (70 votes). Check out past polls.