Is it Possible to change Worksheet names when useing ParseExcel?

Question

Wrote a Perl script to parse an existing Excel workbook using Spreadsheet::ParseExcel (as a template), and create new file while adding updated data from our DB.

Is there a way to update the name of the individual worksheets prior to running the SaveAs function? I'd like to rename them to a customer number instead of the default Sheet1, Sheet2, etc. I did read the PerlDoc's and did see there is a get_name() option, but nothing to change and re-save.

Any help would be great.

Are you talking about creating a new workbook using Spreadsheet::WriteExcel? — simbabque
No, only using data from an existing workbook, then making changes to certain cells, and saving as a different file. In this particular case, I'm not creating any new worksheets, just editing existing. Since the main file is a template, I want to save the worksheets used (there's 30 of them) by a customer number pertaining to the new data that is on each updated worksheet. I've been able to do all of this so far with using only Spreadsheet::ParseExcel. — Kevin

simbabque simbabque · Accepted Answer · 2012-09-19T18:13:35

If you are using SpreadSheet::WriteExcel you can specify the name of the worksheet as the method add_worksheet's first param. This is what the doc says:

$worksheet1 = $workbook->add_worksheet();           # Sheet1
$worksheet2 = $workbook->add_worksheet('Foglio2');  # Foglio2
$worksheet3 = $workbook->add_worksheet('Data');     # Data
$worksheet4 = $workbook->add_worksheet();           # Sheet4

So what you'd want to do is probably something like this:

while (my $res = $dbh->fetchrow_hashref) {
  my $worksheet = $workbook->add_worksheet($res->{'customerName'});
  # do stuff with that sheet
}

Update: Since you're only using Spreadsheet::ParseExcel here's another idea. There's no setter method for the name, so let's take a deeper look to find a workaround.

The code of Spreadsheet::ParseExcel::Worksheet shows us how the name of a workbook-object is stored:

###############################################################################
#
# get_name()
#
# Returns the name of the worksheet.
#
sub get_name {

    my $self = shift;

    return $self->{Name};
}

You can just access the key Name directly via the worksheet-object.

$worksheet->{'Name'} = $res->{'customerName'};

Disclaimer: You should never meddle with internal values of objects, especially if you do not have control over their source. The internal structure may change in a future release, thus breaking your code!

Is it Possible to change Worksheet names when useing ParseExcel?

1 Answers