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

My question is regarding Spreadsheet::WriteExcel or any other module that writes to Excel. All the code documentation I have found always start with : 
my $s = Excel->new("here.xls");

[download]
Which creates a new spreadsheet, does this also open an existing spreasheet to modify? I created an excel template and need to open the existing file and modify it each time I run the script. I tried it but I didnt work and wrote over my existing. Thanks everyone.

Replies are listed 'Best First'.
Re: write over existing Excel file
by jmcnamara (Monsignor) on Oct 09, 2005 at 15:59 UTC

    This a FAQ in relation to Spreadsheet::WriteExcel and here is the usual (but not completely satisfactory) answer.

    --
    John.

[reply]
      Thanks for the help though... IF I cant find anything else, I will use this but would probably need some clarification... Thanks again..
[reply]
      I would still need to do this even though I would only need to change data in 5 specific cells out of the whole sheet? There's gotta be an easier way somehow??
[reply]

        I would still need to do this even though I would only need to change data in 5 specific cells out of the whole sheet?

        Unfortunately, yes.

        There's gotta be an easier way somehow?

        Have a look at Excel::Template.

        --
        John.

[reply]
[d/l]
Re: write over existing Excel file
by davidrw (Prior) on Oct 09, 2005 at 16:47 UTC
    You can create perl code for Spreadsheet::WriteExcel that creates everything that your template has (note: the 'record a macro in excel and translate the vb to perl' method might be useful here), and make that code re-usable and just call it each time .. 
        sub addMyHeadersEtc {
      my $workbook = shift or die;
      my $worksheet = $workbook->add_worksheet('foo');
      $worksheet->write(0, 0, "STANDARD TITLE");
    }

    my $workbook  = Spreadsheet::WriteExcel->new('filename.xls');  # c
+reate a new file
    addMyHeadersEtc( $workbook );     # add all your standard stuff
    
    # now add all the custom stuff ...
    my $worksheet = $workbook->sheets()[0];
    $worksheet->write(5, 0, "Hi Excel!");

    [download]
[reply]
[d/l]
Re: write over existing Excel file
by Zed_Lopez (Chaplain) on Oct 09, 2005 at 15:30 UTC
    The Spreadsheet::WriteExcel docs say: "This module cannot be used to write to an existing Excel file." I don't know what module, if any, can.
[reply]
Re: write over existing Excel file
by chester (Hermit) on Oct 09, 2005 at 15:31 UTC
[reply]
      Doesn't this module only reads from an excel file rather than prints to an excel file?
[reply]

Back to Seekers of Perl Wisdom