[Solved] Perl/OLE getting opened and unsaved spreadsheets?

[Hagar Delest]

How do I get list of currently opened and unsaved spreadsheets?
With Excel APIs I could do sth like:

Code: Select all   Expand viewCollapse view

   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.

[FJCC]

In OpenOffice Basic you could print the name of all unsaved spreadsheets with

Code: Select all   Expand viewCollapse view

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.

[Woland99]

Thanks! For desktop object I usually do

Code: Select all   Expand viewCollapse view

    $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");


[Woland99]

Thanks a lot! The following works now:

Code: Select all   Expand viewCollapse view

        $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   Expand viewCollapse view

   $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   Expand viewCollapse view

   $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.

[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.