I am having 2 problems using XFunctionAccess with java:
1) Whatever function call I make, it only works on one spreadsheet of
my spreadsheet document. The document I am using has 3 spreadsheets, and I
loop through making each one active then calling callFunction(), but it
seems to only compute the function on the first spreadsheet.
2) Using an XCellRange as an argument to the callFunction() method
produces an IllegalArgumentException, even though the api states that this
is a valid argument.
Here is a snippet of my code:
PropertyValue[] loadProps = new PropertyValue[1];
PropertyValue p = new PropertyValue();
p.Name = "Hidden";
p.Value = true;
loadProps[0] = p;
XComponent document;
try {
document = xComponentLoader.loadComponentFromURL(
createUNOFileURL(path, xContext), "_hidden", 0,
loadProps);
if (document == null){
return propertyToValueHash;
}
XSpreadsheetDocument xsd =
(XSpreadsheetDocument)UnoRuntime.queryInterface(
XSpreadsheetDocument.class, document);
if (xsd == null){
return propertyToValueHash;
}
XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
XModel.class, document);
XController xController =
xSpreadsheetModel.getCurrentController();
XSpreadsheetView view = (XSpreadsheetView)UnoRuntime
.queryInterface(XSpreadsheetView.class, xController);
XSpreadsheets xSheets = xsd.getSheets();
String[] names = xSheets.getElementNames();
Vector<String> spreadsheetNames = new Vector<String>();
Object helper = xRemoteServiceManager.createInstanceWithContext(
"com.sun.star.sheet.FunctionAccess", xContext);
XFunctionAccess xFunctionAccess = (XFunctionAccess)UnoRuntime
.queryInterface(XFunctionAccess.class, helper);
for (String name : names){
spreadsheetNames.add(name);
Object sheet = xSheets.getByName(name);
XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime
.queryInterface(XSpreadsheet.class, sheet);
view.setActiveSheet(xSpreadsheet);
Object range = xSpreadsheet.getCellRangeByName("A1:A5");
XCellRange xRange =
(XCellRange)UnoRuntime.queryInterface(XCellRange.class, range);
Object[] array = new Object[1];
array[0] = xRange;
Object ret = xFunctionAccess.callFunction("COUNTA", array);
}
This produces an IllegalArgumentException. If I replace the XCellRange
argument with say:
Object[] array = new Object[5];
array[0] = "A1";
array[1] = "A2";
array[2] = "A3";
array[3] = "A4";
array[4] = "A5";
Then it works, but my return value for the callFunction() method returns me
the result of the first spreadsheet every time, instead of the active sheet
I have set. Furthermore, in Excel you can specify a cell in another
spreadsheet this way:
Object[] array = new Object[5];
array[0] = "Sheet1!A1";
array[1] = " Sheet1!A2";
array[2] = " Sheet1!A3";
array[3] = " Sheet1!A4";
array[4] = " Sheet1!A5";
but, using these arguments with different spreadsheet names results in a
return value always based on the first spreadsheet in the document. Any help
would be much appreciated.
