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

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

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

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

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

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