[Solved] Select whole columns / Object model documentation

Creating a macro - Writing a Script - Using the API

[Solved] Select whole columns / Object model documentation

Postby Neutrino » Thu Sep 24, 2009 3:36 pm

I'm writing a macro to import some data into a Calc document where it will be analyzed. Here are a few issues I'm having that I hope someone can help me with.

Once I have the data imported I need to set one of the columns to be date formatted and several other columns I'd like to have a custom number format. So my first question is how in a macro do I select a single column, and a contiguous range of columns? I've Googled and not come up with anything on this. Recording a macro doesn't help either. If I record myself selecting a range of cells then the recorded macro contains the code required to perform that task. But if I record myself selecting one or more entire columns the recorded macro contains nothing pertaining to any selection being made, which seems very strange.

My other question is what is the best source of documentation on the OOo object model? I haven't found anything at all in the documentation that comes with OOo. I have found the online wiki and its API documentation here http://wiki.services.openoffice.org/wik ... /API_Intro but that seems to document the API from a C/C++ perspective and there appears to be a substantial translation layer between the C/C++ code and the BASIC code which makes that documentation pretty useless for end users writing macros.
Last edited by Hagar Delest on Thu Oct 08, 2009 1:41 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 3.1.1 on Windows XP + Ubuntu
Neutrino
 
Posts: 23
Joined: Tue Jul 15, 2008 11:37 pm

Re: Select complete columns and object model documentation

Postby Villeroy » Thu Sep 24, 2009 4:17 pm

[moderation: Moved from Calc]
The API is language neutral, but mostly documented in Java since you can translate from the more verbose Java style to Basic but not the other way round.
If you import the right numbers into the right template, oRange.CellStyle="MyDates" is all you have to do in order to apply arbitrary complex formatting attributes to a range of cells. You may not even need that statement, since your template already uses the right styles in the right places.
A tool like http://extensions.services.openoffice.org/project/MRI is mandatory for all macro coders in any language.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27214
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Select complete columns and object model documentation

Postby Neutrino » Wed Oct 07, 2009 10:02 am

Thanks for the tips. I've installed the Mri and despite the fact that the documentation is very sketchy I've managed to guess a lot of what I need to know to get some use out of it but there is one thing I still can't work out.

When it loads up its initial target is set to com.sun.star.sheet.SpreadsheetDocument by default, and I can select Methods or Properties from the scope selector and view useful information about this service. In the main panel it lists two entries under SupportedServiceNames and loads more entries under AvailableServiceNames. But if I click Target->Service and then in the box which prompts me for a service name enter 'com.sun.star.sheet.Spreadsheet' it displays a message saying 'com.sun.star.sheet.Spreadsheet is not a service'.

It does this for everything except SpreadsheetDocument. How do I navigate to a different service?
OOo 3.1.1 on Windows XP + Ubuntu
Neutrino
 
Posts: 23
Joined: Tue Jul 15, 2008 11:37 pm

Re: Select complete columns and object model documentation

Postby Villeroy » Wed Oct 07, 2009 10:37 am

Don't guess. Learn! Meanwhile the online documentation is excellent, but too heavy for the casual VBA coder who used to record most VBA macros.
More links: viewtopic.php?f=13&t=16964&p=106430#p78453

MRI is just an object inspector with links to the API's online documentation. It does not read any documentation files. Every UNO object can tell everything about itself.
menu:Tools>Add-Ons>MRI inspects the current document.
menu:Tools>Add-Ons>"MRI selection" inspects the current selection.
MRI adds a small Basic wrapper to call the inspector from Basic. Similar code works in any other language since MRI registers itself as UNO service.

The API is language independent. It works with any language someone developed an UNO bridge for, thus it has to be far more abstract and strictly typed than the fuzzy logic in VBA. It does not know any optional arguments and complex arguments are structs or arrays of property structs.

For scripting in Python, Basic and similar interpreted languages services are analog to objects having properties and interfaces, interfaces can be seen as bundles of methods.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27214
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Select complete columns and object model documentation

Postby Neutrino » Wed Oct 07, 2009 5:06 pm

Thanks for the links. I'd already read that and the only thing I could find in the excellent online documentation about the Tools->Service menuitem and what it is supposed to do is the following snippet:


Mri Documentation wrote:Services
If you select this entry, dialog box having the edit control is open and input a service name that you want to instantiate as a target.


From reading this I think I can be fairly sure what it is supposed to do, but as described in my last post this is clearly not what it is doing. Given your familiarity with this tool is my usage of the tool as described in my last post correct? Are you aware of any configuration issues which might cause Mri not to know what is a valid service and what is not?
OOo 3.1.1 on Windows XP + Ubuntu
Neutrino
 
Posts: 23
Joined: Tue Jul 15, 2008 11:37 pm

Re: Select complete columns and object model documentation

Postby Villeroy » Wed Oct 07, 2009 7:56 pm

I don't know. I never used that MRI feature. Seems to do nothing. But that is not important as long as you can pass any object variable as starting point and browse all methods and properties.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27214
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Select complete columns and object model documentation

Postby hanya » Wed Oct 07, 2009 9:26 pm

Neutrino wrote:Thanks for the links. I'd already read that and the only thing I could find in the excellent online documentation about the Tools->Service menuitem and what it is supposed to do is the following snippet:

It function instantiate a service that you input but you need to understand which service can be instantiated. They are listed in AvailableServiceNames, start MRI, choose Targets -> ServiceManager and then select "Services".
For example, com.sun.star.ui.dialogs.FilePicker service is used to let user choose a file and its name can be found the list. If you input its name, MRI instantiate the service as a target.

You can find so many service names in the IDL reference but you can not instantiate all of them. IDLs define individual services but an actual component includes two or more services. This means an UNO component is registered with a service name. But the service name does not indicate its component, you can register some UNO component as the same name. The regstered components as the same name are choosed internally at runtime or used last one.

Targets->Service, Struct and Configuration menus are used by the user who understand what kind of services can be instantiated or who feel troublesome to write code to check the object.

In your case, com.sun.star.sheet.Spreadsheet is not listed in the list of the avaiable service names. Because (I think) it represents a spreadsheet of a spreadsheet document but it can not be exist individually. You can get a object supports com.sun.star.sheet.Spreadsheet service in Spreadsheet documents. Make a Spreadsheet document and run MRI from Add-ons menu. Now you can see com.sun.star.sheet.SpreadsheetDocument service name in the list of the supported service names. And then execute getSheets method, after that call getByIndex method and choose 0 from the list displayed on the opened dialog. Check its Sercices.
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Select complete columns and object model documentation

Postby Villeroy » Wed Oct 07, 2009 9:46 pm

Thank you for the intervention, hanya (and for MRI, of course)

Neutrino,
Call MRI from a spreadsheet document.
Select category "Services"
Code: Select all   Expand viewCollapse view
(SupportedServiceNames)
com.sun.star.sheet.SpreadsheetDocument
com.sun.star.sheet.SpreadsheetDocumentSettings

(AvailableServiceNames)
com.sun.star.chart2.data.DataProvider
[and some dozend other availlable services]

The spreadsheet "supports" 2 services. I would rather say, it includes all the methods and properies of the 2 services.
The "availlable services" are services (objects) that can be instanciated as new objects from the spreadsheet.

When you call
MRI-menu:Targets>Service... com.sun.star.chart2.data.DataProvider
you get a new instance of a com.sun.star.chart2.data.DataProvider (just an arbitrary example, don't ask me what it's good for). Interesting feature, but not so important, I think.
In Basic you could do the same like this:
Code: Select all   Expand viewCollapse view
Sub inspect
GlobalScope.BasicLibraries.loadLibrary("MRILib")
obj =  ThisCOmponent.createInstance("com.sun.star.chart2.data.DataProvider")
MRILib.Module1.Mri obj
End SUb
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27214
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Select complete columns and object model documentation

Postby Neutrino » Thu Oct 08, 2009 11:50 am

Thanks guys, that's just what I needed to know :)
OOo 3.1.1 on Windows XP + Ubuntu
Neutrino
 
Posts: 23
Joined: Tue Jul 15, 2008 11:37 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot], RPG and 3 guests