Page 1 of 1
[Solved] Perl/OLE getting opened and unsaved spreadsheets?
Posted: Sat Jun 14, 2014 5:11 pm
by Woland99
How do I get list of currently opened and unsaved spreadsheets?
With Excel APIs I could do sth like:
Code: Select all
eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
my $wbook;
my $wbooks = $excel->Workbooks();
foreach $wbook (in($wbooks)) {
unless($wbook->{Saved}) {
push(@retVal, $wbook->{Name});
}
}
Is there something similar in OO?
Sorry if this is a trivial question - I spent some time with Excel APIs and there translating from VB to Perl was more or less successful all the time. With OO I do not know where to start.
Re: Perl/OLE getting list of opened and unsaved spreadsheets
Posted: Sat Jun 14, 2014 6:23 pm
by FJCC
In OpenOffice Basic you could print the name of all unsaved spreadsheets with
Code: Select all
Components = StarDesktop.getComponents
Enum = Components.createEnumeration()
While Enum.hasMoreElements
oDoc = Enum.nextElement()
If oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") AND oDoc.isModified Then
print oDoc.Title
End If
Wend
I don't know how you can get a desktop object in Perl.
Re: Perl/OLE getting list of opened and unsaved spreadsheets
Posted: Sat Jun 14, 2014 8:08 pm
by Woland99
Thanks! For desktop object I usually do
Code: Select all
$objServiceManager =
Win32::OLE->GetActiveObject("com.sun.star.ServiceManager")
||
Win32::OLE->new("com.sun.star.ServiceManager")
||
die "CreateObject: $!"; # Get the currently running process or create a new one
$Stardesktop = $objServiceManager->createInstance("com.sun.star.frame.Desktop");
Re: Perl/OLE getting list of opened and unsaved spreadsheets
Posted: Sat Jun 14, 2014 9:24 pm
by Woland99
Thanks a lot! The following works now:
Code: Select all
$objServiceManager =
Win32::OLE->GetActiveObject("com.sun.star.ServiceManager")
||
Win32::OLE->new("com.sun.star.ServiceManager")
||
die "CreateObject: $!"; # Get the currently running process or create a new one
$Stardesktop = $objServiceManager->createInstance("com.sun.star.frame.Desktop");
#VB code
#Components = StarDesktop.getComponents
#Enum = Components.createEnumeration()
#While Enum.hasMoreElements
# oDoc = Enum.nextElement()
# If oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") AND oDoc.isModified Then
# print oDoc.Title
# End If
#Wend
my $components = $Stardesktop->getComponents();
my $Enum = $components->createEnumeration();
while($Enum->hasMoreElements()) {
my $oDoc = $Enum->nextElement();
if($oDoc->supportsService("com.sun.star.sheet.SpreadsheetDocument") && $oDoc->isModified()) {
push(@retVal, $oDoc->Title());
}
}
If I may trouble you a bit more - how does one extract annotation (multiline) from a Cell ?
To extract value I could do sth like:
Code: Select all
$calc = $Stardesktop->loadComponentfromUrl("$input_file_URL", "_blank", 0, \@inputFileProperties );
$sheet = $calc->getSheets->getByIndex(0);
$cell = $sheet->getCellByPosition($row_num,$col_num);
$cellValue = $cell->getString();
But when I try to get annotations:
Code: Select all
$cellComment = $cell->getAnnotation()->getString();
I get nothing. With Excel API the documentation is very obvious plus I can see objects in debugger - no such luck wit OO.
Re: Perl/OLE getting list of opened and unsaved spreadsheets
Posted: Sat Jun 14, 2014 9:49 pm
by Woland99
My bad - I was testing it on a spreadsheet that had no comments
Still if there is canonical VB way to do it it would be good to see.