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;
}