How to Modify and Rewrite Excel Files in perl

[bimleshsharma]

Hi, How to Modify and Rewrite existing Excel Files in perl? I tried below but not looking worthy. Please help.

#!/usr/bin/perl –w
use strict;
use Spreadsheet::WriteExcel;

# Create a new Excel file
my $FileName = “/home/selva/Report.xls";
my $workbook = Spreadsheet::WriteExcel->new($FileName);

# Add a worksheet
my $worksheet1 = $workbook->add_worksheet('PERL’);

# Define the format and add it to the worksheet
my $format = $workbook->add_format(
center_across => 1,
bold => 1,
size => 10,
border => 4,
color => 'black',
bg_color => 'cyan',
border_color => 'black',
align => 'vcenter',
);

# Change width for only first column
$worksheet1->set_column(0,0,20);

# Write a formatted and unformatted string, row and column
# notation.
$worksheet1->write(0,0, "PERL FLAVOURS", $format);
$worksheet1->write(1,0,"Active State PERL");
$worksheet1->write(2,0,"Strawberry PERL");
$worksheet1->write(3,0,"Vennila PERL");

[Corion]

Have you read what the Spreadsheet::WriteExcel documentation has to say about Modifying and Rewriting Excel Files? What parts of it do you have problems with?

[bimleshsharma]

I am getting format of cell but i am unknown about the actual properties. Because i have to use same format for other cell if bg_color=red. I am using code below to get format..

# Get the format from the cell
    my $format   = $template->{Worksheet}[$sheet]
                            ->{Cells}[$row][$col]
                            ->{FormatNo};

[Khen1950fx]

It's actually a trivial task. Try this:

#!/usr/bin/perl 

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new("Report.xls");
my $first    = $workbook->add_worksheet('Perl');
my $second   = $workbook->add_worksheet('Perl Flavours');
my $third    = $workbook->add_worksheet('ActivePerl');
my $fourth   = $workbook->add_worksheet('StrawberryPerl');
my $fifth    = $workbook->add_worksheet('VanilaPerl');

my $format = $workbook->add_format(
    center_across => 1,
    bold          => 1,
    size          => 10,
    border        => 4,
    color         => 'black',
    bg_color      => 'cyan',
    border_color  => 'black',
    align         => 'vcenter',
);

$first->set_column(0,0,20);
foreach my $worksheet ($workbook->sheets()) {
    $worksheet->write(0,0, "Perl", $format);
    $worksheet->write(1,0, "Perl FLAVOURS");
    $worksheet->write(2,0, "Active Perl");
    $worksheet->write(3,0, "Strawberry Perl");
    $worksheet->write(4,0, "Vanilla Perl");
}
$first->activate();