http://www.perlmonks.org?node_id=722789

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

Hi, I'm new to perlmonks and perl too. Can anybody please help me? I have a set of excel workbooks with multiple worksheets. My requirement is to copy the all the sheets in specified existing workbooks to a single new workbook. The problem I'm facing is in preserving the original format in the created combined workbook. Thanks in advance.
  • Comment on copy excel sheets to a different workbook

Replies are listed 'Best First'.
Re: copy excel sheets to a different workbook
by DutchCoder (Scribe) on Nov 11, 2008 at 08:04 UTC
    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.
      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.
      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.
A reply falls below the community's threshold of quality. You may see it by logging in.