Spreadsheet::Wr iteExcel - Writing into existing file

[rasmitasah25]

hi,

I am very new to perl.I have written a perl script which is writing data into an excel file.The problem is that it is creating one new excel file while executing but my need id to write into an existing .xls file.
currently I am using:
my $workbook = Spreadsheet::Wr iteExcel::Big->new("file.xls" );
for that which method Should I use??
can anyone please help me...

Thanks

Rasmia

[AndyHunt]

Have you tried using Spreadsheet::Pa rseExcel to read out the data, and then use WriteExcel to put it all back into another new file?

It's a bit of a drag, but the only other way I know how to do it is to use Win32::OLE which is (IMHO) much more awkward.

Andy

[rasmitasah25]

Thank You Andy..Due to our internal system problem I hav not tried Spreadsheet::Pa rseExcel .I am writing the perl script which will run on the Solaries environment.Wil l Win32::OLE work there???

My code is...

use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
 
my $wrksheet = "Details";
my $sheet_name;
my $excel_look;
my $v_name = "Shilpy";
my $v_empid= 194050;
#my $sFile = "test1.xls";
my $workbook = Spreadsheet::WriteExcel::Big->new("file.xls");
my $worksheet = $workbook->add_worksheet("Details");
 
#$sheet_name = $excel_sheet->addworksheet($wrksheet);
$excel_look = $workbook->addformat();
$excel_look->set_size(10);
$excel_look->set_color('black');
$excel_look->set_align('center');
$excel_look->set_font('Arial');
 
$worksheet->activate();
$worksheet->write(0,0,"Name",$excel_look);
$worksheet->write(0,1,"Emp Id",$excel_look);
 

Please suggest me what needs to change...

Thanks a lot

Rasmita

[miller]

As Andy points out, it is not directly possible to edit an exiting excel spreadsheet. Instead you must first parse and then create a new excel sheet.

cpan Spreadsheet::Pa rseExcel
cpan Spreadsheet::Wr iteExcel

- Miller

[rasmitasah25]

Hi,

Thanks Miller.I hav gone through the given links,but not get any satisfactory result.My query is when ever I will execute the script it will create the new file and erase the previous data,this is not my requirement.I want to append new data,when I will execute the script 2nd time.

Please help me, its very very urgent...

Thanks
Rasmita

[karthikkotha]

Hi Rasmita,

I recently did something quite similar. The script adds tabs to an existing excel file each time it is run and saves the file. I did it using Win32::OLE. You could do something like the below:

$filepath = 'file.xls';
if (-e $filepath) {
    $Book = $Excel->Workbooks->Open($filepath);
    $Exists = "true";
} else {
    $Book = $Excel->Workbooks->Add();
    $Exists = "false";
}
 
if ($Exists eq "true") {
    $previousSheet = $Book->ActiveSheet;
    $currentSheet = $Book->Worksheets->Add();
    $Book->ActiveSheet->{Name} = "new tab";
} else {
    $previousSheet = 0;
}
 
# ******** add your code here ************
 
if ($Exists eq "true") {
    $Bookook->Save();
} else{
    $Book->SaveAs("$filepath") or die $!;
}
 

Hope this helps.

KARTHIK