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

Java, C++, C#, Delphi, ??? - Using the UNO bridges

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

Postby Woland99 » Sat Jun 14, 2014 5:11 pm

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.
Last edited by Hagar Delest on Sun Jun 15, 2014 11:07 am, edited 1 time in total.
Reason: tagged [Solved].
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Perl/OLE getting list of opened and unsaved spreadsheets

Postby FJCC » Sat Jun 14, 2014 6:23 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Perl/OLE getting list of opened and unsaved spreadsheets

Postby Woland99 » Sat Jun 14, 2014 8:08 pm

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");
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Perl/OLE getting list of opened and unsaved spreadsheets

Postby Woland99 » Sat Jun 14, 2014 9:24 pm

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.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Perl/OLE getting list of opened and unsaved spreadsheets

Postby Woland99 » Sat Jun 14, 2014 9:49 pm

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.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am


Return to External Programs

Who is online

Users browsing this forum: No registered users and 3 guests