[Solved] Select whole columns / Object model documentation

[Neutrino]

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.

[Villeroy]

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

[Neutrino]

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?

[Villeroy]

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: http://user.services.openoffice.org/en/ ... 430#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.

[Neutrino]

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:

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?

[Villeroy]

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.

[hanya]

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.

[Villeroy]

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

Neutrino,
Call MRI from a spreadsheet document.
Select category "Services"

Code: Select all

(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

Sub inspect
GlobalScope.BasicLibraries.loadLibrary("MRILib")
obj =  ThisCOmponent.createInstance("com.sun.star.chart2.data.DataProvider")
MRILib.Module1.Mri obj
End SUb

[Neutrino]

Thanks guys, that's just what I needed to know