Hey guys!
I have been working with SpreadSheet:
arseExcel, SpreadSheet::WriteExcel and Win32::OLE the last year.
Some comments comparing Spreadsheet vs Win32::OLE.
My point of view:
What I Like from Spreadsheet::WriteExcel and Spreadsheet :
arseexcel
1. Spreadsheet::WriteExcel and Spreadsheet :
arseexcel are very powerful if you want to manipulate format of cells, colours, fonts, reports. The module is very well documented and there are good examples. In the net you will get very good examples
2. Working with, Spreadsheet::WriteExcel and Spreadsheet ParseExcel, You don't need to be working in a WIN32 environment to use it, I was able to read excel file in UNIX HP System., also produce excel files, btw with very nice format.It works great. I loved it.
What I don't like from Spreadsheet:
arseExcel:
1.If you work with excel files that contains a lot of colours, diferent fonts or big data, it is slow, Take too much time analyzing the excel file.
2.If you have password protected excel files, like Workbook protected or shared workbook and protected, you need to pass the password during execution time, otherwise you won't be able to read the data. Spreadsheet:
arseExcel doesn't support it.
So in this case you have to simulate excel vba code using win32::OLe. Like this:
# ###################################################################
# Unprotecting Excel File
# ###################################################################
sub unprotecting_excel_file{
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0;
#$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Open("$filename_datafill");
$Book->UnprotectSharing("password");
$Book->Save;
}
After that protecting it again:
# ###################################################################
# Protecting Excel File
# ###################################################################
sub protecting_excel_file{
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0;
#$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Open("$filename_datafill");
$Book->ProtectSharing({Filename=>$filename_datafill,SharingPassword=>"password"});
$Book->Save;
}
3. Spreadsheet:
arseExcel doesn't support formulas.
4. Spreadsheet:
arseExcel doesn't support range functions.
for instance, use win32 module you can get the range from the excel file at once:
# Find Last Column and Row
my $LastRow = $Sheet_cnumber->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};
my $LastCol = $Sheet_cnumber->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByColumns})->{Column};
# Range Data
$firstcell="A".2;
$lastcell= "IV".$LastRow; #(Last Column Excel File. Last Row with Data)
my $array = $Sheet_cnumber->Range("$firstcell:$lastcell")->{'Value'};
get_data($array);
sub get_data{
my($array)= @_;
foreach my $ref_array (@$array) {
$var1_col1= @$ref_array[0];
$var_col2=@$ref_array[1];
}
}
Using Spreadsheet:
arseExcel you could use:
my $workbook = Spreadsheet:
arseExcel::Simple->read('myfile.xls');
my $worksheet = ($workbook->sheets())[0];
while ($worksheet->has_data()) {
my @fields_table = $worksheet->next_row();
@fields_table= trim(@fields_table);
$var_col1="$fields_table[0]";
$var_col2= "$fields_table[1]";
} # Closing While
But I seems that win32 module does it faster than the other one...Maybe the communication with excel???
To be honest, both modules are great. The authors did a very good job.
Anyway, how do you show up an existing excel file using Spreadsheets Modules? I don;t know if it is possible...I think is a limitation. I am able to open a new excel using writeexcel spreadsheet..but no existing ones...
Anyway, I am feel very well working with OLE or Spreadsheet, just depends what I need to do I choose one of them.....
Thanks for your great answers!
dmazzini
GSM System and Telecomm Consultant
