Spreadsheet::WriteExcel

Fabio <fa***@anti-spam.inet.alpha.pl> wrote in message >

But how can I put some values into the cells of an existing worksheet?
Thanks for any advice.

Use either Spreadsheet::ParseExcel::SaveParser for rudimentary changes
to existing workbooks. If you need to do complex stuff, it may
(unfortunately) be easier to use OLE to do it.

w

Fabio <fa***@anti-spam.inet.alpha.pl> wrote in message news:<c2**********@nemesis.news.tpi.pl>...

Hello,

there's a module called Spreadsheet::WriteExcel. It allows me to create
a new worksheet and edit its cells:

my $workbook = Spreadsheet::WriteExcel->new('my.xls');
my $worksheet = $workbook->addworksheet();
$worksheet -> write('A2', 222);

But how can I put some values into the cells of an existing worksheet?
Thanks for any advice.

..:: fabio

Dunno Spreadsheet::WriteExcel.
Here is sth I wrote recently using Win32::OLE
-------------
#for OLE automation
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE 'in';
use Win32::OLE::Variant;
#get current directory
use Cwd;

#writes array of changes to existing excel spreadsheet as new excel spreadsheet
#arguments:
# input_file - name of existing Excel spreadsheet
# output_file - name under which changed file is to be save under (SaveAs option)
# change_array_ref - reference to array of changes - (row \t column \t new value)

sub write_changes_into_excel_file
{
#name of existing Excel spreadsheet
my $input_file = shift;
#name under which changed file is to be save under (SaveAs option)
my $output_file = shift;

#reference to array of changes - (row, column, new value)
my $change_array_ref = shift;

my $dir = cwd();
my $fullname_input_file = $dir.'/'.$input_file;
my $fullname_output_file = $dir.'/'.$output_file;

#check if input file exists in first place
unless(-f $fullname_input_file)
{
print STDERR "Input file: $fullname_input_file does not exists.\n";
exit;
}

# use existing instance if Excel is already running
eval
{
$excel = Win32::OLE->GetActiveObject('Excel.Application')
};
die "Excel not installed" if $@;

unless (defined $excel)
{
$excel = Win32::OLE->new('Excel.Application', 'Quit')
or die "Oops, cannot start Excel";
}
#to avoid excessive dialogs when saving in non-Excel format
$excel->{DisplayAlerts} = 0;

my $wbook;
if($wbook = Win32::OLE->GetObject($fullname_input_file)){;}
else
{
#the failure og GetObject is potentially due to the fact
#that file $fullname_input_file is already opened.
#In such case we need to find workbook that corresponds
#to already opened file and if we cannot we print error
#message and exit.
my $wbooks = $excel->Workbooks();
my $name = Variant($fullname_input_file);
if($wbooks->{"$name"}->Activate())
{
$wbook = $wbooks->{"$name"};
}
else
{
print STDERR "Could not open the file $fullname_input_file: $!\n";
exit;
}
}

# write to a particular cell
my $wsheet = $wbook->Worksheets(1);

my $line;
my $cell_value;
my $row_num;
my $col_num;
my $do_next;
foreach $line (@{$change_array_ref})
{
(
$row_num,
$col_num,
$cell_value
)
= split("\t",$line);

$row_num =~ s/\s//g;
$col_num =~ s/\s//g;

$wsheet->Cells($row_num,$col_num)->{Value} = "$cell_value";
$wsheet->Cells($row_num,$col_num)->Select();
}

$wbook->SaveAs($fullname_output_file);
undef $wbook;
}