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

My problem is I am trying to copy an excel spreadsheet which has both data and graph . so it is not possible to use the normal row and column copying functions to manipulate it and copy it to another worksheet in another excel spread sheet.. Please comment I want to open an excel spreadsheet which has two graphs and some data (eg book1.xls) and copy the whole worksheet to another spreadsheet(book2.xls) with around 10 worksheets. I want to overwrite the data in the second spreadsheet in a particular worksheet ( eg height ). height is the number 8 worksheet in the spreedsheet(book2.xls).please help and this is urgent thanks a lot. I am very new to perl.

Replies are listed 'Best First'.
Re: using perl in excel
by daddyefsacks (Pilgrim) on Jan 16, 2005 at 23:22 UTC

    If you have some code you're working on could you post what you have so far?

    If not, are you aware of the Excel spreadsheet modules from the CPAN? Have a look at Spreadsheet::ParseExcel::Simple, from the synopsis:

    my $xls = Spreadsheet::ParseExcel::Simple->read('spreadsheet.xls'); foreach my $sheet ($xls->sheets) { while ($sheet->has_data) { my @data = $sheet->next_row; } }
    and Spreadsheet::WriteExcel::Simple, again from the synopsis:
    my $ss = Spreadsheet::WriteExcel::Simple->new; $ss->write_bold_row(\@headings); $ss->write_row(\@data); print $ss->data; # or $ss->save("filename.xls");

    That should get you started anyway, once you get it working if you need more complex processing use Spreadsheet::WriteExcel and Spreadsheet::ReadExcel and post some code detailing where you are stuck.

    Hope that helps.

      thanks a lot . Thanks for your help. I will try it out and get back at you.
Re: using perl in excel
by redhotpenguin (Deacon) on Jan 16, 2005 at 23:23 UTC
    Take a look at Using Win32::OLE and Excel - Tips and Tricks. Also next time you should use the search box on the site to look at other questions out there that may help you out before posting a new question. That's how I found the node I just referenced :-)

    Update: thanks for the tip dimar, I updated my link style for this post.

      Good advice redhotpenguin. Moreover, you might want to take a look at this node which shows how to make a hyperlink to another node on perlmonks.

      If you use style1, it causes minor hassles if someone clicks on it while logged into perlmonks.

      ### style1 <a href="http://www.perlmonks.org/">foo link</a>
      ### style2 [id://17441|foo link]

      The perlmonks FAQ chock full of useful tidbits ... it's useful to glance at it every so often, in addition to regular visits to search and super-search.