OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to use "Xinterfaces" in Basic?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Tue Jan 27, 2004 2:44 pm    Post subject: How to use "Xinterfaces" in Basic? Reply with quote

I am reading through the DevGuide and all the examples are in Java... I have tried to convert the code into Basic but I am having difficulties getting the Xinterfaces to work.

I am looking at XMultipleOperations in particular:

Code:


// --- A value series, a formula series ---
    aFormulaRange = createCellRangeAddress(xSheet, "B8:D8");
    aColCell = createCellAddress(xSheet, "A8");
    // Row cell not needed
    xCellRange = xSheet.getCellRangeByName("A9:D13");

    xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell);
}


The part I don't understand (or can't translate into basic) is :
xMultOp = (com.sun.star.sheet.XMultipleOperation)
UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);


*********************************

Along the same lines I would like to use the XFunctionAccess Interface... so that I can use all the functions that calc has...

Again I am stuck with this code from the devGuide:

Code:
 
// --- Calculate a function ---
    Object aFuncInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionAccess");

    com.sun.star.sheet.XFunctionAccess xFuncAcc = (com.sun.star.sheet.XFunctionAccess)

        UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionAccess.class, aFuncInst);

    // put the data into a two-dimensional array
    double[][] aData = {{1.0, 2.0, 3.0}};

    // construct the array of function arguments
    Object[] aArgs = new Object[2];
    aArgs[0] = aData;
    aArgs[1] = new Double( 2.0 );
    Object aResult = xFuncAcc.callFunction("ZTEST", aArgs);
    System.out.println("ZTEST result for data {1,2,3} and value 2 is "
                                    + ((Double)aResult).doubleValue());


Can someone please explain the initialisation code:

Code:
    Object aFuncInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionAccess");

    com.sun.star.sheet.XFunctionAccess xFuncAcc = (com.sun.star.sheet.XFunctionAccess)

        UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionAccess.class, aFuncInst);


Thanks,

nom
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Jan 27, 2004 3:18 pm    Post subject: Reply with quote

The Java-Uno bridge is difficult to use and leads to much queryInterface() ugliness.

Let's start with this line, just one line before the queryInterface() call...
xCellRange = xSheet.getCellRangeByName("A9:D13");
I'll assume that xSheet contains a Spreadsheet object.

As you can see from the API docs, Spreadsheet implements the XSpreadsheet interface. XSpreadsheet inherits from a grandparent interface XCellRange. It is the XCellRange interface that provides the method getCellRangeByName().

We can see that getCellRangeByName() returns an XCellRange. But note that the detailed description of the method says to See also CellRange. (CellRange is a service which has the XCellRange interface.) So the xCellRange variable must actually contain a CellRange service with the XCellRange interface.

The very next statement is the queryInterface() call. queryInterface() is a dynamic type casting that must be done in Java.

Somewhere whoever wrote the queryInterface line must have some solid information that the object in variable xCellRange actually implements the XMultipleOperation interface. Assuming this is true, then the effect in Basic of the queryInterface line is simply an assignment...

xMultOp = xCellRange

But in Java, xMultOp and xCellRange are different Interfaces of the same object. (This is the ugliness.) In Basic, I can have one variable with an object, and directly access any method of any interface on that object.

If I have a spreadsheet document in oDoc, I can call the print() method on it like this in basic...

oDoc.print()

But in Java, I must queryInterface to get the XPrintable interface.

XPrintable oDoc_xPrint = (XPrintable) queryInterface( XPrintable.class, oDoc );

Yes, I know I abbreviated the above line for clarity. You would create a new local java variable of type XPrintable, and then call queryInterface to get you the XPrintable portion of the document. (It may in fact point to a different underlying java object -- so you must call queryInterface and not just do a plain typecast only.)

Then you could call the print() method on the new variable...

oDoc_xPrint.print();

In Basic I would not need to two different variables oDoc and oDoc_xPrint. I could directly call oDoc.print().

When translating code from Java to Basic, treat queryInterface() calls as simple assignments. In Basic translated from Java, you end up with two variables that have the same thing (assignment). So this line...

XPrintable oDoc_xPrint = (XPrintable) queryInterface( XPrintable.class, oDoc );

translated to Basic would be...

oDoc_xPrint = oDoc

Two variables, same object. In Java though, they are the same "conceptual" object, but not necessarily the same "concrete" underlying object. That is, what UNO calls a "Service" may actually be made up of multiple objects implementing different interfaces. Basic ans most other OOo language bridges hide this from you.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Tue Jan 27, 2004 4:25 pm    Post subject: Reply with quote

Hi Danny,

Thanks for the explanation... I tried using the XMultipleOperation interface and still can't get it to work.
Here is the code I am working on. Simply start up a spreadsheet and paste this code into a new macro. Run the macro then have a look a the first sheet.

Once you have done this uncomment the last line of code and run the macro again... this is where it falls over. If anyone can help me on this it will be much appreciated. (I have spent a few hours trying to work it out .)

Code:

Sub Main

Dim oDoc, oSheet As Object
Dim oRange, oCell As Object
Dim oFormulaCell, oStartCell, oOperationRange As Object

oDoc = ThisComponent
oSheet = oDoc.Sheets(0)

oRange = oSheet.getcellrangebyName("A1:L1")

oFormulaCell = oSheet.getCellRangeByName("A2")
oStartCell = oSheet.getCellRangeByName("A1")
oOperationRange = oSheet.getCellRangeByName("B1:L2")

' This is the same as oStartCell (Will get rid of later)
oCell = oRange.getCellByPosition(0,0)

' We need to format the range to use a "date" structure.
oRange.NumberFormat = 37 ' Got this from Paolo Mantovani's macro recorder

' We need to include "something" in the first cell for the "fillseries" method to work!
' We have just added something programmatically so that the macro will work
' "out of the box"
oCell.formula = "1/01/2004" ' We could also use oCell.formula = "=DATE(4;1;1)"
                        
oRange.fillseries(com.sun.star.sheet.FillDirection.TO_RIGHT,com.sun.star.sheet.FillMode.DATE, com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1,1.70E+307)     

' Now we want to find the month of the top row and display the answer in the row
' beneath it.
oFormulaCell.formulaLocal = "=MONTH(A1)"

' This is where it falls over??? (Uncomment the line below to see what I mean)
' The devguide tells us we don't need "oCell" (wich corresponds to the column cell)
'  since the TableOperationMode is set on 'ROW'
'oOperationRange.setTableOperation(oFormulaCell, com.sun.star.sheet.TableOperationMode.ROW, oCell, oStartCell)

End Sub
 


Thanks,

nom
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Jan 29, 2004 8:37 am    Post subject: Reply with quote

It may be that the XMultipleOperation interface simply is not one of the supported interfaces on the object that the queryInterface is being done on.

When I was looking through the API to formulate my earlier reply on this thread, I could not find any relationship to XMultipleOperation.

Is the code you are referring to actually in an SDK example? That would be interesting if so. I can't find a documented relationship. A queryInterface call will not convert it. I suppose in a Basic translation, we could try using Xray.

In Java, you could try using getAvailableServiceNames() as in this thread.
http://www.oooforum.org/forum/viewtopic.php?p=19992#19992

You could use other introspection techniques to obtain a list of the actual interfaces available on that object and see if this interface is actually supported at this object.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Tue Feb 03, 2004 11:10 am    Post subject: Reply with quote

This is what I am trying to reprocude in Basic:

Go to : http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm
and do a search for "Multiple Operations"

You will see a picture and some java code:
Code:

public void InsertMultipleOperation(com.sun.star.sheet.XSpreadsheet xSheet)
        throws RuntimeException, Exception {
    // --- Two independent value series ---
    com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress(xSheet, "A1");
    com.sun.star.table.CellAddress aColCell = createCellAddress(xSheet, "A2");
    com.sun.star.table.CellAddress aRowCell = createCellAddress(xSheet, "B1");
    com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A1:F6");
    com.sun.star.sheet.XMultipleOperation xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell);
    // --- A value series, a formula series ---
    aFormulaRange = createCellRangeAddress(xSheet, "B8:D8");
    aColCell = createCellAddress(xSheet, "A8");
    // Row cell not needed
    xCellRange = xSheet.getCellRangeByName("A9:D13");
    xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell);
}
/** Creates a com.sun.star.table.CellAddress and initializes it
    with the given range.
    @param xSheet  The XSpreadsheet interface of the spreadsheet.
    @param aCell  The address of the cell (or a named cell).
 */
public com.sun.star.table.CellAddress createCellAddress(
        com.sun.star.sheet.XSpreadsheet xSheet,
        String aCell ) throws RuntimeException, Exception {
    com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class,
            xSheet.getCellRangeByName(aCell).getCellByPosition(0, 0));
    return xAddr.getCellAddress();
}
/** Creates a com.sun.star.table.CellRangeAddress and initializes
    it with the given range.
    @param xSheet  The XSpreadsheet interface of the spreadsheet.
    @param aRange  The address of the cell range (or a named range).
 */
public com.sun.star.table.CellRangeAddress createCellRangeAddress(
        com.sun.star.sheet.XSpreadsheet xSheet, String aRange) {
    com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class,
            xSheet.getCellRangeByName(aRange));
    return xAddr.getRangeAddress();
}


More specifically I want to do this:
Code:
// --- A value series, a formula series ---
    aFormulaRange = createCellRangeAddress(xSheet, "B8:D8");
    aColCell = createCellAddress(xSheet, "A8");
    // Row cell not needed
    xCellRange = xSheet.getCellRangeByName("A9:D13");
    xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell);
}


I tried rewritting this in basic but it did not work.
It is important to setup the spreedsheet exactly as it is shown in the picture (as shown in the link above.) i.e. have the forumla "=SIN(A8 )" in the cell B8 etc...

Can someone get this to work in BASIC?

Thanks,

nom
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Feb 03, 2004 1:21 pm    Post subject: Reply with quote

I have no idea what this is supposed to do. Therefor I have no idea how to test it.

Anyway, here is a statement-by-statement translation.

Code:
Sub Main
   oCalcDoc = ThisComponent
   oSheet = oCalcDoc.getSheets().getByIndex( 0 )
   
   oFormulaRange = oSheet.getCellRangeByName( "A1" ).getRangeAddress()
   oColCell = oSheet.getCellRangeByName( "A2" ).getCellAddress()
   oRowCell = oSheet.getCellRangeByName( "B1" ).getCellAddress()
   oCellRange = oSheet.getCellRangeByName( "A1:F6" )
   oMultOp = oCellRange
   oMultOp.setTableOperation( oFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, oColCell, oRowCell )
   oFormulaRange = oSheet.getCellRangeByName( "B8:D8" ).getRangeAddress()
   oColCell = oSheet.getCellRangeByName( "A8" ).getCellAddress()
   ' Row cell not needed
   oCellRange = oSheet.getCellRangeByName( "A9:D13" )
   oMultOp = oCellRange
   oMultOp.setTableOperation( oFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, oColCell, oRowCell )
End Sub


There is a 1-to-1 statement correspondenace to the original.

Note how the simple assignment to oMultOp replaces the queryInterface call. I could really have just done away with the variable oMultOp and directly used variable oCellRange.

When I run this program on an empty spreadsheet, it executes without error. I have no idea what it is supposed to do?
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Feb 03, 2004 4:17 pm    Post subject: Reply with quote

I think I figured it out. I missed your link before. I think you meant to link to here....
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+3+1+4+Cell+Ranges
and then scroll down to Multiple Operations.

I tried it. Made a spreadsheet with cells that had contents like those pictured in the example. Then ran the macro. It filled the spreadsheet with some values?
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Wed Feb 04, 2004 2:48 pm    Post subject: Reply with quote

Thanks Danny!
It works for me too. I realised I forgot to use the ".getrangeAddress()" and ".getCellAddress()" methods.

All this makes a lot more sense now, thanks.

I noticed something interesting when playing around with this... Once you have executed the macro you can then go to the sheet and change the formulas and the other cells will update automatically WITHOUT having to run the macro again!
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Feb 05, 2004 5:34 am    Post subject: Reply with quote

nom wrote:
I noticed something interesting when playing around with this... Once you have executed the macro you can then go to the sheet and change the formulas and the other cells will update automatically WITHOUT having to run the macro again!


That's interesting. I'll have to try this again and play with it. Could it just be populating the cells with formulas? Or is there something else going on?

Quote:
Thanks Danny!

The Java ---> Basic translation was easy. It only took a few minutes, despite that I had no idea what this code was supposed to be doing. It would compile (no syntax errors or typos), and it would execute on an empty spreadsheet (no runtime errors). But until I saw your link which I had missed the first time, (too focused on the code), I didn't realize that I could test it.

Basic --> Java translation is not so straightforward. For every method call you have to figure out which interface the method belongs to, and then do a suitable queryInterface() to that interface, thus introducing a new variable. Maybe now you see what I meant earlier by my remark that Java OOo code has much queryInterface ugliness. Not the fault of Java the language. I think a better Java-UNO bridge could be built for any large program with a fixed API, such as OOo.

Basic <----> Python translation is very straightforward.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group