Spreadsheet::WriteExcel

[Fabio]

Hello,

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

my $workbook = Spreadsheet::Wr iteExcel->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

[Will Stranathan]

Re: Spreadsheet::Wr iteExcel

Fabio <fabio@anti-spam.inet.alpha .pl> wrote in message >[color=blue]
> But how can I put some values into the cells of an existing worksheet?
> Thanks for any advice.[/color]

Use either Spreadsheet::Pa rseExcel::SaveP arser 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

[Tom Niesytto]

Re: Spreadsheet::Wr iteExcel

Fabio <fabio@anti-spam.inet.alpha .pl> wrote in message news:<c21l9f$9c 2$1@nemesis.new s.tpi.pl>...[color=blue]
> Hello,
>
> there's a module called Spreadsheet::Wr iteExcel. It allows me to create
> a new worksheet and edit its cells:
>
> my $workbook = Spreadsheet::Wr iteExcel->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[/color]

Dunno Spreadsheet::Wr iteExcel.
Here is sth I wrote recently using Win32::OLE
-------------
#for OLE automation
use Win32::OLE;
use Win32::OLE::Con st "Microsoft Excel";
use Win32::OLE 'in';
use Win32::OLE::Var iant;
#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_re f - reference to array of changes - (row \t column \t new value)

sub write_changes_i nto_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_r ef = shift;

my $dir = cwd();
my $fullname_input _file = $dir.'/'.$input_file;
my $fullname_outpu t_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->GetActiveObjec t('Excel.Applic ation')
};
die "Excel not installed" if $@;

unless (defined $excel)
{
$excel = Win32::OLE->new('Excel.App lication', '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($ful lname_input_fil e)){;}
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($fullna me_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_arra y_ref})
{
(
$row_num,
$col_num,
$cell_value
)
= split("\t",$lin e);

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

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

$wbook->SaveAs($fullna me_output_file) ;
undef $wbook;
}