Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Spreadsheet::WriteExcel

Reply
Thread Tools

Spreadsheet::WriteExcel

 
 
Fabio
Guest
Posts: n/a
 
      03-02-2004
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

 
Reply With Quote
 
 
 
 
Will Stranathan
Guest
Posts: n/a
 
      03-02-2004
Fabio <(E-Mail Removed)> wrote in message >
> But how can I put some values into the cells of an existing worksheet?
> Thanks for any advice.


Use either Spreadsheet:arseExcel::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
 
Reply With Quote
 
 
 
 
Tom Niesytto
Guest
Posts: n/a
 
      03-04-2004
Fabio <(E-Mail Removed)> wrote in message news:<c21l9f$9c2$(E-Mail Removed)>...
> 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;
}
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off




Advertisments