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

I have a curent Excel file that I need to add an additional month of data by adding another worksheet to the workbook. The problem is it must look the same but only the numbers change. Its hard for me to re-create in Excel because Im not that good with excel(so I just copy and paste). Is there a way that I could automate my current process of(copying) the worksheet over to a new one and change the numbers within it? I tried to use Spreadsheet-WriteExcel but it opens a new file over writing my current one. Thanks in Advance, Fue....

Replies are listed 'Best First'.
Re: Adding Excel sheet
by InfiniteSilence (Curate) on Oct 17, 2005 at 21:42 UTC
    There are lots of nodes here at PerlMonks with sample code for Excel Automation. Use the search bar as a starting point. The code that allows you to copy sheets (from running a macro in Excel and looking at the macro code) is: 
    #!/usr/bin/perl -w
use strict;
use Win32::OLE;

my $excel = Win32::OLE->new('Excel.Application');
$excel->{'Visible'} = 1;
my $Book = $excel->Workbooks->Open("c:\\temp\\foo.xls");

my $Sheets = $Book->Sheets;
my $sheet1 = $Sheets->Item(1);
#    Sheets("Sheet1").Copy Before:=Sheets(1)
#    Sheets("Sheet1 (2)").Select
$Sheets->{"Sheet1"}->Copy($sheet1);

1;

    [download]

    Celebrate Intellectual Diversity

[reply]
[d/l]
Re: Adding Excel sheet
by davidrw (Prior) on Oct 17, 2005 at 22:31 UTC
    As suggested already, you will get lots of hits using Super Search. Also be sure to check out the couple links that are in the Tutorials, and also the node Index of Spreadsheet FAQs contains lots of good information and references.

    As for your specific issue, note that the Spreadsheet::WriteExcel pod states in the description that "This module cannot be used to write to an existing Excel file." So I also recommend the Win32::OLE approach (including porting macros), or I would create Spreadsheet::WriteExcel code that can recreate your workbooks from scratch as reusable code, and just call that each time to prep your new workbook, add the new worksheet, and write it out.

    Also see the thread write over existing Excel file from a few days ago, including my reply to it.
[reply]
Re: Adding Excel sheet
by ickyb0d (Monk) on Oct 18, 2005 at 00:58 UTC
    Whenever i've used the Spreadsheet module, i also found it hard to create an Excel file from scratch, since the module doesn't handle formatting too well. What i ended up doing was just creating my template and formatting in windows, then loading the template using Spreadsheet::ParseExcel. After that it's just a matter of writing and saving it using WriteExcel and SaveParser. Here are some snippets from some code i had laying around. Hope this helps. 
    #excel modules
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

#loading template
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('timesheets/old_template.xls')
        or die "Unable to open template";
my $temp_workbook;

#adding info...
#user name
setCellFormat(4,2);
$template->AddCell(0, 4, 2, "$real_name", $format);

#project name
setCellFormat(5,2);
$template->AddCell(0, 5, 2, "$project_name", $format);

#save data
my $workbook;

{       #ignore SaveAs errors and warnings
        local $^W = 0;
        $workbook = $template->SaveAs('/home/~myname/httpdocs/spreadsh
+eet.xls');
}

    [download]
[reply]
[d/l]
      I would like to thank everyone here for their help.... Alot of good information. I post if I have more infor, I think this is a great start for me. Thanks again all...
[reply]
      This works great except for one thing.... AddCell just adds a cell, I need it to replace the cell rather than AddCell, I would need something like 'EditCell'. WriteExcel wont be able to write to this. How would you edit the cell? Is it possible to delete the cell and replace it with 'AddCell'?
[reply]
        Looking at your original post, I'm a little confused as to why you need to edit a cell. If you use Spreadsheet::ParseExcel::SaveParser you load in your existing spreadsheet then create your new worksheet: 
        $oBook->AddWorksheet('February');

        [download]
        To this new blank worksheet first add the cells from the prior month's worksheet that are the same in the new month's worksheet for example: 
        my $oOld_Sheet = $oBook->{Worksheet}[0];
for (my $row = 0; $row <= 4; $row++) {
    my $oCell = $oOld_Sheet->{Cells}[$row][0];
    if (defined $oCell->{Val}) {
        $oBook->AddCell(1, $row, 0, $oCell->{Val}, $oCell);
     }
 }

        [download]
        Adding the sales for the new month to the new sheet using the AddCell method as well. Finally save the workbook under a new name using the SaveAs method.

        If you really need to edit a cell then Win32::OLE as InfiniteSilence and davidrw mentioned is probably what you will have to use, also if the worksheet you are adding contains formulas.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom