Calc/API/Programming

From Apache OpenOffice Wiki
< Calc‎ | API
Revision as of 13:46, 11 May 2009 by SergeMoutou (Talk | contribs)

Jump to: navigation, search

Template:Documentation/Banner

Introduction

To avoid you to search in the previous chapters we give again our starting code where we insert the new listings given in this chapter.

  • We give first the ooConnect() part :
// Listing 0
// C++
#include <stdio.h>
#include <cppuhelper/bootstrap.hxx>
#include <com/sun/star/bridge/XUnoUrlResolver.hpp>
#include <com/sun/star/lang/XMultiServiceFactory.hpp>
// added
#include <com/sun/star/frame/XComponentLoader.hpp>
 
using namespace com::sun::star::uno;
using namespace com::sun::star::lang;
using namespace com::sun::star::bridge;
// added
using namespace com::sun::star::frame;
 
using namespace rtl;
using namespace cppu;
 
// a procedure for what the so called boostrap
Reference< XMultiServiceFactory > ooConnect(){
   // create the initial component context
   Reference< XComponentContext > rComponentContext = 
				defaultBootstrap_InitialComponentContext();
 
   // retrieve the servicemanager from the context
   Reference< XMultiComponentFactory > rServiceManager = 
				rComponentContext->getServiceManager();
 
   // instantiate a sample service with the servicemanager.
   Reference< XInterface > rInstance =  rServiceManager->createInstanceWithContext(
         OUString::createFromAscii("com.sun.star.bridge.UnoUrlResolver" ),rComponentContext );
 
   // Query for the XUnoUrlResolver interface
   Reference< XUnoUrlResolver > rResolver( rInstance, UNO_QUERY );
   if( ! rResolver.is() ){
      printf( "Error: Couldn't instantiate com.sun.star.bridge.UnoUrlResolver service\n" );
      return NULL;
   }
   try {
      // resolve the uno-url
      rInstance = rResolver->resolve( OUString::createFromAscii(
         "uno:socket,host=localhost,port=8100;urp;StarOffice.ServiceManager" ) );
 
      if( ! rInstance.is() ){
         printf( "StarOffice.ServiceManager is not exported from remote counterpart\n" );
         return NULL;
      }
 
      // query for the simpler XMultiServiceFactory interface, sufficient for scripting
      Reference< XMultiServiceFactory > rOfficeServiceManager (rInstance, UNO_QUERY);
 
      if( ! rOfficeServiceManager.is() ){
            printf( "XMultiServiceFactory interface is not exported for StarOffice.ServiceManager\n" );
            return NULL;
        }       
        return rOfficeServiceManager;
   }
   catch( Exception &e ){
      OString o = OUStringToOString( e.Message, RTL_TEXTENCODING_ASCII_US );
      printf( "Error: %s\n", o.pData->buffer );
      return NULL;
   }
   return NULL;
}

Note : You have eventually to change "port=8100" into "port=2083" for recent SDK (after 2.X).

You can have a look at :

  1. com.sun.star.uno.XComponentContext, com.sun.star.lang.XMultiComponentFactory, com.sun.star.uno.XInterface, com.sun.star.bridge.XUnoUrlResolver and com.sun.star.lang.XMultiServiceFactory interfaces,
  2. com.sun.star.bridge.UnoUrlResolver service
  • We second give the main() part :
//Listing 0b Again our starting main Code
int main( ) {
//retrieve an instance of the remote service manager
    Reference< XMultiServiceFactory > rOfficeServiceManager;
    rOfficeServiceManager = ooConnect();
    if( rOfficeServiceManager.is() ){
        printf( "Connected sucessfully to the office\n" );
    }
 
//get the desktop service using createInstance returns an XInterface type
    Reference< XInterface  > Desktop = rOfficeServiceManager->createInstance(
    OUString::createFromAscii( "com.sun.star.frame.Desktop" ));
 
//query for the XComponentLoader interface
    Reference< XComponentLoader > rComponentLoader (Desktop, UNO_QUERY);
    if( rComponentLoader.is() ){
        	printf( "XComponentloader successfully instanciated\n" );
    	}
 
//get an instance of the spreadsheet
    Reference< XComponent > xcomponent = rComponentLoader->loadComponentFromURL(
	OUString::createFromAscii("private:factory/scalc"),
        OUString::createFromAscii("_blank"),
        0,
        Sequence < ::com::sun::star::beans::PropertyValue >());
// add code here
    return 0;
}

This code is called a bootstrap and uses :

  1. com.sun.star.lang.XMultiServiceFactory, com.sun.star.uno.XInterface, com.sun.star.frame.XComponentLoader and com.sun.star.lang.XComponent interfaces
  2. com.sun.star.frame.Desktop service
  3. com.sun.star.beans.PropertyValue structure.

Remember each time you query for an interface you have to add code lines (if they don't exist) in the source code and a line in the makefile. I will generally add comments to prevent omissions. You can find more explanations here (if you are not a beginner).

Documentation note.png Important Note : The more important point in the compilation chain of the examples above is that cppumaker will construct every hpp and hdl files you need in your application. The SDK doesn't provide every hpp files, but you have to construt them starting from IDL files provided by SDK.
Documentation note.png It is possible to construct all the hpp files when installing the SDK as mentioned in a Windows installation. It's also possible with other OS. In doing so, you don't need modifying your MakeFile.

To find the Sheet

The Find the Sheet wiki-page describes in greater detail the Calc-API useful for managing sheets.

How to obtain the Cell

When you have your sheet, obtain a particular cell is easy (see com.sun.star.sheet.XSpreadsheetDocument, com.sun.star.sheet.XSpreadsheet and com.sun.star.table.XCell):

//Listing 13  Obtaining a Cell
//C++
// Don't forget to add : using namespace com::sun::star::sheet;
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetDocument \" in the makefile
// Don't forget to add : using namespace com::sun::star::table; (for XCell)
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheet.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheet \" in the makefile
//query for a XSpreadsheetDocument interface
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY);
 
//use it to get the XSpreadsheets interface
	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();
 
//use getByName to get a reference (type Any)
	Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet1"));
 
//query for the XSpreadsheet interface
	Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY);
 
	Reference< XCell > rCell = rSpSheet->getCellByPosition(0, 0);

The first parameter is the column number, and the second is the row number, both starting from 0. See above the example where we access to the top left cell called A1.

What can be done with a Cell ?

The com.sun.star.table.XCell interface provide six methods :

  1. getFormula returns the formula string of a cell.
  2. setFormula sets a formula into the cell.
  3. getValue returns the floating point value of the cell.
  4. setValue sets a floating point value into the cell.
  5. getType returns the type of the cell.
  6. getError returns the error value of the cell.


Numerical Value in a Cell

To put a numerical value in a cell is easy :

//Listing 14 Putting a numerical Value in a Cell
//C++
// Don't forget to add : using namespace com::sun::star::sheet;
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetDocument \" in the makefile
// Don't forget to add : using namespace com::sun::star::table; (for XCell)
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheet.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheet \" in the makefile
//query for a XSpreadsheetDocument interface
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY);
 
//use it to get the XSpreadsheets interface
	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();
 
//use getByName to get a reference (type Any)
	Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet1"));
 
//query the reference of type Any for the XSpreadsheet interface
	Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY);
 
	Reference< XCell > rCell = rSpSheet->getCellByPosition(0, 0);
 
//set the cell value then get another cell etc..
	rCell->setValue(219);
	rCell = rSpSheet->getCellByPosition(0, 1);
	rCell->setValue(25);

See also com.sun.star.sheet.XSpreadsheetDocument and com.sun.star.sheet.XSpreadsheet.

Formula in a Cell

To put a formula in a cell is straightforward : replace the previous setValue by setFormula. This gives us :

//Listing 15 Putting a Formula in a Cell
//C++
	Reference< XCell > rCell = rSpSheet->getCellByPosition(0, 0);
	rCell->setValue(21);
	rCell = rSpSheet->getCellByPosition(0, 1);
	rCell->setValue(23);
	rCell = rSpSheet->getCellByPosition(0, 2);
	rCell->setFormula(OUString::createFromAscii("=sum(A1:A2)"));

which puts in fact the value 44 in A3.

String in a Cell

We use a similar principle as in previous example :

//Listing 16 Putting a String (OUString) in a Cell
//C++ 
//set a string in the cell  
	rCell->setFormula(OUString::createFromAscii("Hello"));

It is possible to change the textual content of a cell with a Cursor (com.sun.star.text.XTextCursor interface obtained from com.sun.star.text.XText). The code below allows us this change :

//Listing 17 Change the String Content of a Cell
//C++
// Don't forget to add : using namespace com::sun::star::text;
// Don't forget to add : #include <com/sun/star/text/XText.hpp>
// Don't forget to add "com.sun.star.text.XText \" in the makefile
 
// query first the XText interface
	Reference< XText > rText (rCell, UNO_QUERY); 
 
//query for the XTextCursor interface 
	//Reference< XTextCursor > rTextCurs (rText, UNO_QUERY); 
	Reference< XTextCursor > rTextCurs = rText -> createTextCursor(); 
	rTextCurs -> gotoEnd(false);
	rTextCurs -> setString(OUString::createFromAscii(" Bye"));

Adding the code gives us “Hello Bye” in a cell.

See also com.sun.star.text.XText.

What is the Content Type of a Cell ?

We naturally use a getType method of the XCell interface. It returns one of the four possibilities : EMPTY, VALUE, TEXT or FORMULA (::com::sun::star::table) How do we see the values in C++ ? We give here the partial content of com.sun.star.table.CellContentType :

// IDL
namespace com
{
namespace sun
{
namespace star
{
namespace table
{
enum CellContentType
{
    CellContentType_EMPTY = 0,
    CellContentType_VALUE = 1,
    CellContentType_TEXT = 2,
    CellContentType_FORMULA = 3,
    CellContentType_MAKE_FIXED_SIZE = SAL_MAX_ENUM
};
 
} // table
} // star
} // sun
} // com

which shows us a “using namespace com::sun::star::table” allows us to write in C++ something like : CellContentType_EMPTY if we have added the following statement  :

#include <com/sun/star/table/CellContentType.hpp>

The code below is an example :

Listing 18 Retrieve the Cell Content Type
//C++
// Don't forget to add : using namespace com::sun::star::sheet;
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetDocument \" in the makefile
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheet.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheet \" in the makefile
 
// Don't forget to add #include <com/sun/star/table/CellContentType.hpp>
// Don't forget to add "com.sun.star.table.CellContentType \" in the makefile
 
//query for a XSpreadsheetDocument interface
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY);
	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();
 
	Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet1"));
	//query the reference of type Any for the XSpreadsheet interface
	Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY);
 
	Reference< XCell > rCell = rSpSheet->getCellByPosition(0, 0);
	rCell->setValue(43);
	switch (rCell->getType()){
		case CellContentType_EMPTY : printf("Empty\n");break;
		case CellContentType_VALUE : printf("Numerical Value\n");break;
		case CellContentType_TEXT : printf("Text\n");break;
		case CellContentType_FORMULA : printf("Formula\n");break;
	}

which writes out “Numerical Value”.

See also com.sun.star.sheet.XSpreadsheetDocument, com.sun.star.sheet.XSpreadsheet and com.sun.star.table.CellContentType

Computing the Formula

Two methods calculate() and calculateAll() are available from the com.sun.star.sheet.XCalculatable :calculate() and calculateAll(). You can query this interface from a com.sun.star.sheet.XSpreadsheetDocument.

Formatting a Cell

The straight forward way to format a cell is to use an already defined style in the spreadsheet. The predefined style's names are in English even they appear in other languages in Object Bar. They are  : Result, Result2, Default, Heading, Heading1 ...

//Listing 19 Formating a Cell
//C++
// Don't forget to add : using namespace com::sun::star::beans;
// Don't forget to add : #include <com/sun/star/beans/XPropertySet.hpp>
// Don't forget to add "com.sun.star.beans.XPropertySet \" in the makefile
 
//query for the cell property interface 
	Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); 
 
//create an Any to hold our property value 
	Any mPropVal; 
	mPropVal <<= OUString::createFromAscii("Result"); 
 
//set some properties 
	rCellProps->setPropertyValue(OUString::createFromAscii("CellStyle"), mPropVal);

See also com.sun.star.beans.XPropertySet.

Cell Annotations

The following example inserts an annotation and make it permanently visible.

//Listing 20 Putting and retrieve Cell Annotations
//C++
// Don't forget to add : using namespace com::sun::star::sheet;
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetDocument \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheet.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheet \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/XCellAddressable.hpp>
// Don't forget to add "com.sun.star.sheet.XCellAddressable \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/XSheetAnnotationsSupplier.hpp>
// Don't forget to add "com.sun.star.sheet.XSheetAnnotationsSupplier \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/XSheetAnnotationAnchor.hpp>
// Don't forget to add "com.sun.star.sheet.XSheetAnnotationAnchor \" in the makefile
 
//query for a XSpreadsheetDocument interface
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY);
	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();
 
	Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet1"));
 
//query the reference of type Any for the XSpreadsheet interface
	Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY);
 
//query for XCell Interface
	Reference< XCell > rCell = rSpSheet->getCellByPosition(0, 0);
 
//query for the XcellAddressable Interface
	Reference< XCellAddressable > rCellAddr (rCell, UNO_QUERY);
	CellAddress rAddress = rCellAddr -> getCellAddress();
 
//query for XSheetAnnotationsSupplier Interface 
	Reference< XSheetAnnotationsSupplier > rAnnotationSupp (rSheet, UNO_QUERY);
	Reference< XSheetAnnotations > rAnnotations = rAnnotationSupp -> getAnnotations();
	rAnnotations -> insertNew(rAddress,OUString::createFromAscii("This is an annotation"));
 
// make the annotation visible
 
//query the XSheetAnnotationAnchor
	Reference< XSheetAnnotationAnchor > rAnnotAnchor (rCell, UNO_QUERY);
	Reference< XSheetAnnotation > rAnnotation = rAnnotAnchor -> getAnnotation();
	rAnnotation->setIsVisible(true);

See also com.sun.star.sheet.XSpreadsheetDocument, com.sun.star.sheet.XSpreadsheet, com.sun.star.sheet.XCellAddressable, com.sun.star.sheet.XSheetAnnotationsSupplier and com.sun.star.sheet.XSheetAnnotationAnchor

If you want an annotation not always visible you only remove the three last lines. Very interesting to put an annotation, but how do you get it if it already exist ? You add for test into the previous code :

//Listing 21 Code for Test of previous Listing
// C++
// Don't forget to add : using namespace com::sun::star::text;
// Don't forget to add : #include <com/sun/star/text/XSimpleText.hpp>
// Don't forget to add "com.sun.star.sheet.XSimpleText \" in the makefile
 
// query the XSimpleTextInterface
	Reference< XSimpleText > rSimpleText (rAnnotation, UNO_QUERY);
 
	printf("Annotation : %s\n",
		OUStringToOString(rSimpleText->getString(),RTL_TEXTENCODING_ASCII_US).getStr());
// or if you want to put the text in the cell :
// 	rCell->setFormula(rSimpleText->getString());

which writes out the string literal :“Annotation : This is an annotation”.

See also com.sun.star.sheet.XSimpleText

Cell Range

The way to obtain a cell range is not so easy as in OooBasic. You have first to obtain the com.sun.star.table.XCellRange to have access to the getcellRangeByName method :

//Listing 22 Cell Range Manipulation
//C++
// Don't forget to add : using namespace com::sun::star::table;
// Don't forget to add : #include <com/sun/star/table/XCellRange.hpp>
// Don't forget to add "com.sun.star.table.XCellRange \" in the makefile
 
//query for a XSpreadsheetDocument interface 
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY); 
	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets(); 
 
//use getByName to get a reference (type Any) 
	Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet1")); 
 
// query for the XCellRange interface
	Reference< XCellRange > rSheetCellRange (rSheet, UNO_QUERY);
 
// query the cell range
	Reference< XCellRange> rCellRange = 
		rSheetCellRange->getCellRangeByName(OUString::createFromAscii("B2:F5"));

With the com.sun.star.table.XCellRange interface you can also access the range with getCellRangeByPosition method. To query one cell in the cell range, add to the previous code :

//Listing 23 Finding one Cell in the Range
//C++
	Reference< XCell > rCell = rCellRange->getCellByPosition(0, 0);
	rCell->setFormula(OUString::createFromAscii("it works"));

The X and Y position are then relative to the cell Range.

Retrieve the absolute X and Y Positions of a Cell

This subject has been already tackled in a previous section but embedded in other code. We need first a CellAddress type described in <OpenOffice.org1.1_SDK>/idl/com/sun/star/text/CellAddress.idl (or com.sun.star.text.CellAdress) :

// IDL
module com {  module sun {  module star {  module table {
struct CellAddress
{
	short Sheet;
	long Column;
	long Row;
};
}; }; }; };

Starting from the previous listing and adding :

//Listing 24 Absolute Position of a Cell
// C++
// Don't forget to add : #include <com/sun/star/sheet/XCellAddressable.hpp>
// Don't forget to add "com.sun.star.sheet.XCellAddressable \" in the makefile
 
// Don't forget to add : #include <com/sun/star/table/CellAddress.hpp>
// Don't forget to add "com.sun.star.table.CellAddress \" in the makefile
 
//query for the XcellAddressable Interface
	Reference< XCellAddressable > rCellAddr (rCell, UNO_QUERY);
	CellAddress rAddress = rCellAddr -> getCellAddress();
	printf("sheet : %d, column : %d row: %d \n",
				     rAddress.Sheet,rAddress.Column,rAddress.Row);

gives us a program which writes out the absolute X and Y position of a cell.

See also com.sun.star.sheet.XCellAddressable and com.sun.star.table.CellAddress

Retrieve the absolute Address of the Cell Range

We need first a CellRangeAddress type described in <OpenOffice.org1.1_SDK>/idl/com/sun/star/table/CellRangeAddress.idl :

// IDL
module com {  module sun {  module star {  module table {
 
struct CellRangeAddress
{
	short Sheet;
	long StartColumn;
	long StartRow;
	long EndColumn;
	long EndRow;
};
}; }; }; };

and a com.sun.star.sheet.XCellRangeAddressable interface. Starting from the cell range program, we add :

//Listing 25 Using XCellRangeAddressable Interface
// C++
// Don't forget to add : #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
// Don't forget to add "com.sun.star.sheet.XCellRangeAddressable \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/CellRangeAddress.hpp>
// Don't forget to add "com.sun.star.sheet.CellRangeAddress \" in the makefile
 
//query for the XCellRangeAddressable interface
	Reference< XCellRangeAddressable > rCellRangeAddr (rCellRange, UNO_QUERY);
	CellRangeAddress rRangeAddress = rCellRangeAddr -> getRangeAddress();
	printf("sheet : %d, start column : %d start row: %d ...\n",
		rRangeAddress.Sheet,rRangeAddress.StartColumn,rRangeAddress.StartRow);

This program only writes out three of the five cell range attribute.

See also com.sun.star.sheet.XCellRangeAddressable and com.sun.star.sheet.CellRangeAddress

Getting a Cell range with a Selection

We have tackled a similar problem in section ( Retrieve or change the currently focused Sheet) The way is to also start from a com.sun.star.frame.XModel interface, and query with getCurrentSelection an XInterface and then query an com.sun.star.table.XCellRange interface.

//Listing 26 Cell Range under Focus
// C++
	Reference< XInterface > rInterface = rSpreadsheetModel->getCurrentSelection();
 
// Don't forget to add : #include <com/sun/star/table/XCellRange.hpp>
// Don't forget to add "com.sun.star.table.XCellRange \" in the makefile
	Reference< XCellRange > rCellRange(rInterface,UNO_QUERY);
	rCell = rCellRange ->getCellByPosition(0, 0);
	rCell->setFormula(OUString::createFromAscii("Hello !!!"));

This code writes out “Hello !!!” in the top left corner of the selected range.

Ready to translate the Developers Guide into C++

When you want to go further, the Developers Guide is a good start. But examples are given in Java code. You have then to learn how to translate it into C++. At first glance this translation seems like a complicated task but I experimented with it as a complete Java novice when writing the chapter on annotation and encountered no difficulties. But I was lucky, I present now examples which give me more difficulties.

The Compute Function Example

Let's start with Compute Function SDK's example :

//Listing 27  Java first Example (from SDK)
// Java
// --- Sheet operation. --- 
// Compute a function 
	com.sun.star.sheet.XSheetOperation xSheetOp = 
		(com.sun.star.sheet.XSheetOperation) UnoRuntime.queryInterface
			(com.sun.star.sheet.XSheetOperation.class, xCellRange);
	double fResult = xSheetOp.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE); 	System.out.println("Average value of the data table A10:C30: " + fResult); 
// Clear cell contents
	xSheetOp.clearContents( com.sun.star.sheet.CellFlags.ANNOTATION |
		 com.sun.star.sheet.CellFlags.OBJECTS);

where interface com.sun.star.sheet.XSheetOperation is seen.

The C++ translation gives (keeping the same variables names as in Java code) :

//Listing 28  C++ Translation : calling a buildin Function
// C++
// Don't forget to add : #include <com/sun/star/sheet/XSheetOperation.hpp>
// Don't forget to add "com.sun.star.sheet.XSheetOperation \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/GeneralFunction.hpp>
// and see in GeneralFunction.hdl how AVERAGE is generated : here GeneralFunction_AVERAGE
// Don't forget to add "com.sun.star.sheet.GeneralFunction \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/CellFlags.hpp>
// and see in CellFlags.hdl how ANNOTATION and OBJECT are generated :  here ANNOTATION, OBJECT
// in the namespace CellFlags donc :
// Don't forget to add : using namespace com::sun::star::sheet::CellFlags;
// Don't forget to add "com.sun.star.sheet.CellFlags \" in the makefile
 
// --- Sheet operation. ---
// Compute a function
	Reference< XSheetOperation >  xSheetOp( xCellRange,UNO_QUERY);
	double fResult= xSheetOp->computeFunction(GeneralFunction_AVERAGE);
	printf("Average value of the data table B2:C4: %f", fResult);
// Clear cell contents
	 xSheetOp->clearContents( ANNOTATION | OBJECTS);

(See also com.sun.star.sheet.XSheetOperation, com.sun.star.sheet.GeneralFunction and com.sun.star.sheet.CellFlags).

We can see the UNO calls are simpler in C++ than in Java. I have added comments to explain how constants are managed. We are lucky here with this piece of code because we can see two different kind of constant we encounter in UNO C++ programming :

  • one with a specific name : GeneralFunction_AVERAGE instead of AVERAGE in Java. This problem of enumeration has already been tackled here.
  • one with the same name as in IDL and Java files but in a namespace which requires the classical “using namespace” (this problem with constant is tackled here).

To find information requires to generate hdl and hpp files from IDL files and to read not the hpp but hdl files. This job is let for novices, others can see all in IDL files : see the difference between :

// IDL
// extracted from CellFlags.idl
constants CellFlags
{
	const long VALUE = 1;
	const long DATETIME = 2;
	const long STRING = 4;
	const long ANNOTATION = 8;
	const long FORMULA = 16;
	const long HARDATTR = 32;
	const long OBJECTS = 128;
	const long EDITATTR = 256;
};

and

// IDL
// extracted from GeneralFunction.idl
enum GeneralFunction
{
	NONE,
	AUTO,
	SUM,
	COUNT,
	AVERAGE,
	MAX,
	MIN,
	PRODUCT,
	COUNTNUMS,
	STDEV,
	STDEVP,
	VAR,
	VARP
};

and the consequences on the constants use. You can deduce tips isn't it ?

A second Example : adding a Name to a Cell (or Cell Range)

We give first the Java code still found in SDK :

//Listing 29 Second Java Example
// Java
// insert a named range 
	com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
		UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xDocument);
	Object aRangesObj = xDocProp.getPropertyValue("NamedRanges");
	com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges) 			UnoRuntime.queryInterface(com.sun.star.sheet.XNamedRanges.class, aRangesObj);
	com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress(); 	 		aRefPos.Sheet = 0; 
	aRefPos.Column = 6; 
	aRefPos.Row = 44; 
	xNamedRanges.addNewByName("ExampleName", "SUM(G43:G44)", aRefPos, 0);
 // use the named range in formulas 
	xSheet.getCellByPosition(6, 44).setFormula("=ExampleName"); 
	xSheet.getCellByPosition(7, 44).setFormula("=ExampleName");

The same code in C++ :

//Listing 30 naming a Cell (Range)
// C++
// Don't forget to add : #include <com/sun/star/beans/XPropertySet.hpp>
// Don't forget to add "com.sun.star.beans.XPropertySet \" in the makefile
 
// Don't forget to add : #include <com/sun/star/sheet/XNamedRanges.hpp>
// Don't forget to add "com.sun.star.sheet.XNamedRanges \" in the makefile
 
// Don't forget to add : #include <com/sun/star/table/CellAddress.hpp>
// Don't forget to add "com.sun.star.table.CellAddress \" in the makefile
 
// insert a named range 
		// !!! rSheetDoc instead xDocument in our previous code !!!
 	Reference< XPropertySet > xDocProp(xDocument, UNO_QUERY); 
	Any aRangesObj = xDocProp->getPropertyValue(OUString::createFromAscii("NamedRanges"));
	Reference< XNamedRanges > xNamedRanges(aRangesObj,UNO_QUERY);
	CellAddress aRefPos;
	aRefPos.Sheet = 0;
	aRefPos.Column = 6;
	aRefPos.Row = 44;
	xNamedRanges->addNewByName(OUString::createFromAscii("ExampleName"),
			OUString::createFromAscii("SUM(G43:G44)"), aRefPos, 0);
 // use the named range in formulas
		// !!! rSpSheet instead xSheet in our previous code  !!!
	xSheet->getCellByPosition(6, 44)->setFormula(OUString::createFromAscii("=ExampleName"));
	xSheet->getCellByPosition(7, 44)->setFormula(OUString::createFromAscii("=ExampleName"));

(See also com.sun.star.beans.XPropertySet, com.sun.star.sheet.XNamedRanges and com.sun.star.table.CellAddress).

We can see Object is translated by Any. You have to add “OUString::createFromAscii “ before all strings. This program names a cell “ExampleName” and gives a value associated with this name : "SUM(G43:G44)". When you use this name it is replaced by its value.

A third Example

Here is an other example :

//Listing 31 Java third Example
// Java
// --- Cell range data --- 
com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
 	UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCellRange); 
Object[][] aValues = { {new Double(1.1), new Integer(10)}, 
						{new Double(2.2), new String("")},
			 			{new Double(3.3), new String("Text")} }; 
xData.setDataArray(aValues);

This very interesting example takes values from an array and put them in a cell range. The code is given below :

//Listing 32 Putting Cell Range Values in an Array
//C++
// Don't forget to add : using namespace com::sun::star::sheet;
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetDocument \" in the makefile
//query for a XSpreadsheetDocument interface
	Reference< XSpreadsheetDocument > rSheetDoc (xcomponent, UNO_QUERY);
 
//use it to get the XSpreadsheets interface
//	Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();
 
// Don't forget to add : using namespace com::sun::star::frame;
// Don't forget to add : #include <com/sun/star/frame/XModel.hpp>
// Don't forget to add "com.sun.star.frame.XModel \" in the makefile
	Reference< XModel > rSpreadsheetModel (rSheetDoc, UNO_QUERY);
 
// then get the current controller from the model
	Reference< XController > rSpreadsheetController = 
							rSpreadsheetModel->getCurrentController();
 
// Don't forget to add : #include <com/sun/star/sheet/XSpreadsheetView.hpp>
// Don't forget to add "com.sun.star.sheet.XSpreadsheetView \" in the makefile
// get the XSpreadsheetView interface from the controller, we want to call its method
// setActiveSheet
	Reference< XSpreadsheetView > rSpreadsheetView (rSpreadsheetController, UNO_QUERY);
	Reference< XSpreadsheet> rSheet=rSpreadsheetView->getActiveSheet();
 
	Reference< XInterface > rInterface = rSpreadsheetModel->getCurrentSelection();
 
// Don't forget to add : #include <com/sun/star/table/XCellRange.hpp>
// Don't forget to add "com.sun.star.table.XCellRange \" in the makefile
	Reference< XCellRange > rCellRange(rInterface,UNO_QUERY);
 
// Don't forget to add : #include <com/sun/star/sheet/XCellRangeData.hpp>
// Don't forget to add "com.sun.star.sheet.XCellRangeData \" in the makefile
	Reference< XCellRangeData> xData(rCellRange, UNO_QUERY);
 
// Java translation begins here .........
	Sequence< Sequence< Any > > aValues(3); // or aValues(1)(2); ?????
	Sequence< Any > aValues2(2);
	aValues2[0] <<=  (double) 1.1; aValues2[1] <<= (sal_Int32)10;
	aValues[0] = aValues2;
	aValues2[0] <<=  (double)2.2; aValues2[1] <<= OUString::createFromAscii("");
	aValues[1] = aValues2;
	aValues2[0] <<=  (double)3.3; aValues2[1] <<= OUString::createFromAscii("Text");
	aValues[2] = aValues2;
	xData -> setDataArray(aValues);

can be compiled and works if we have an opened OOoCalc document with focus on a cell range of two rows and three lines. This is important : the cell range size must be the same as the sequence sequence of any !

See also com.sun.star.sheet.XSpreadsheetDocument, com.sun.star.frame.XModel, com.sun.star.sheet.XSpreadsheetView, com.sun.star.table.XCellRange and com.sun.star.sheet.XCellRangeData

An Example from OooForum: opening a Password protected Document

Kscape posts : “There is some example java code for opening a passworded file which will prompt the user for the password at snippets but there is no example C++ code, and I cannot seem to get the services and queries around the right way. Any chance someone can translate that snippet or provide a full example C++ program which will open a passworded document?

Thanks in advance. “

Here is the corresponding Java code :

//Listing 33 Java starting Code
// Java
XInteractionHandler xHandler = (XInteractionHandler)UnoRuntime.queryInterface( XInteractionHandler.class, xSMGR.createInstance("com.sun.star.task.InteractionHandler"));
 
PropertyValue[] lArgs    = new PropertyValue[1];
lArgs[0] = new PropertyValue();
lArgs[0].Name  = "InteractionHandler";
lArgs[0].Value = xHandler;
 
xDoc = xDesktop.loadComponentFromURL(sURL, sTarget, nSearchFlags, lArgs);

Here is the C++ translation :

//Listing 34 The correspnding C++ Code
// C++ 
// Don't forget to add : #include <com/sun/star/task/XInteractionHandler.hpp> 
// Don't forget to add "com.sun.star.task.XInteractionHandler \" in the makefile 
// Don't forget to add : using namespace com::sun::star::task; 
 
	Reference <XInteractionHandler> xHandler = 
		Reference<XInteractionHandler>( rOfficeServiceManager->createInstance( 
			OUString( RTL_CONSTASCII_USTRINGPARAM( 
				"com.sun.star.task.InteractionHandler" ))), UNO_QUERY ); 
	if (xHandler.is()) printf("xHandler OK...\n"); else printf("Error xHandler ... \n"); 
 
	Sequence <PropertyValue > largs(1); 
	largs[0].Name = OUString::createFromAscii("InteractionHandler"); 
	largs[0].Value = makeAny(xHandler); 
 
//get an instance of the OOoDraw document 
	Reference< XComponent > xcomponent = rComponentLoader->loadComponentFromURL( 
		OUString::createFromAscii("private:factory/sdraw"), 
		OUString::createFromAscii("_blank"), 
		0, 
		largs);

See also com.sun.star.task.XInteractionHandler.

Event Listener

See also Creating Listeners and Handlers in OOoBasic.

First Event Listener

Event listener in C++ are not documented in the Developer's Guide. To find a way to resolve this problem I first read Java Code. I deduce if you want to implement an event listener you have to construct a class which inherit from a known class and then install it. My first try hang completely OOo (without a message...) After I decided to read OOo' source code and hope to find an example. It was the case in the file  : OOo_1.1.3_src/basic/source/basmgr/basmgr.cxx If you want to do that you have to use an helper to create the class :

//Listing 35 First Event Listener
// C++
// Don't forget the #include<com/sun/star/view/XSelectionChangeListener.hpp>
// Don't forget to add com.sun.star.view.XSelectionChangeListener \ in the makefile
 
// Don't forget the #include <cppuhelper/implbase1.hxx>
 
typedef ::cppu::WeakImplHelper1< ::com::sun::star::view::XSelectionChangeListener > 
                                                              SelectionChangeListenerHelper;
 
class SelectionChangeListener : public SelectionChangeListenerHelper
{
public:
	SelectionChangeListener()
	{printf("EventListener installed\n");fflush(stdout);}
 
	~SelectionChangeListener()
	{printf("EventListener released\n");fflush(stdout);}
 
	virtual void SAL_CALL selectionChanged(const com::sun::star::lang::EventObject& aEvent)
	 throw ( RuntimeException)
	{
		printf("Selection changed \n");fflush(stdout);
	}
 
	virtual void SAL_CALL disposing ( const com::sun::star::lang::EventObject& aEventObj )
		throw(::com::sun::star::uno::RuntimeException)
	{ printf("object listened to will be disposed\n"); }
};

The selectionChanged will be called when a selection changes : it only prints out a message. The fflush(stdout) is important if you want see the printf when the event occurs (thanks to Emmanuel GUY). Now we want to install this event listener :

//Listing 36 Installing an Event Listener
// C++
// event listener
// Don't forget the #include <com/sun/star/view/XSelectionSupplier.hpp>
// Don't forget to add com.sun.star.view.XSelectionSupplier \ in the makefile
 
	Reference< XSelectionSupplier > aSelSupp(rSpreadsheetController,UNO_QUERY);
 
	SelectionChangeListener  *xListener = new SelectionChangeListener();
 
	Reference< XSelectionChangeListener > xSelChListener = 
			static_cast< XSelectionChangeListener* > ( xListener );
 
	aSelSupp->addSelectionChangeListener(xSelChListener);
 
	getchar();
	aSelSupp->removeSelectionChangeListener(xSelChListener);

We first instantiate our class as usual with a new, but we use after a static_cast before to install it with an appropriate method. For us this method is a addSelectionchangeListener(). It is important now that the program doesn't terminate without removing the listener. We use the C getchar() function for that but with real program this important part has to change. To test this code you have to provide a rSpreadsheetController interface. A way is to use the previous code, removing the Java translation part. You then launch a OOocalc and select a cell range. You select an other cell range and see the message in the shell windows. MichaelJ1980 in oooforum indicates he has to write

// C++
SelectionChangeListener  *xListener = ::new SelectionChangeListener();

instead

// C++
SelectionChangeListener  *xListener = new SelectionChangeListener();

See also com.sun.star.view.XSelectionSupplier

A second Event Listener

Emmanuel GUY has posted an other example in OOoForum : events (XEventListener, XWindowListener, ...) in C++

Graphics

Embedding Images (Java Code waiting for a C++ Translation)

Adding external images to a spreadsheet is possible, and this is different from linking to images. Linking to images requires a user to have an external file that is always in the same location relative to the spreadsheet file, while embedding an image deals with including the image in the ODS file itself.

First, suppose we have a model implementing the XModel interface, and is related to a spreadsheet. We can get the spreadsheet and current (active) sheet using the code below:

XSpreadsheetDocument xDoc = (XSpreadsheetDocument)UnoRuntime.queryInterface(
	XSpreadsheetDocument.class, xDocModel);
XSpreadsheetView xsv = (XSpreadsheetView)UnoRuntime.queryInterface(
	XSpreadsheetView.class, m_xFrame.getController());
XSpreadsheet curSheet = xsv.getActiveSheet();

See also com.sun.star.sheet.XSpreadsheetDocument, com.sun.star.sheet.XSpreadsheetView, com.sun.star.sheet.XSpreadsheet and com.sun.star.frame.XFrame.

Next, we need to create a com.sun.star.lang.XMultiServiceFactory object so we can create components in the actual file. We'll create a BitmapTable, which will be tricked into storing the image internally ((source for Writer).

XMultiServiceFactory xmsf = (XMultiServiceFactory)UnoRuntime.queryInterface(
	XMultiServiceFactory.class, xDocModel);
XNameContainer xBitmapContainer = (XNameContainer) UnoRuntime.queryInterface(
	XNameContainer.class, xmsf.createInstance("com.sun.star.drawing.BitmapTable"));

See also com.sun.star.lang.XMultiServiceFactory, com.sun.star.container.XNameContainer and com.sun.star.drawing.BitmapTable.

The file itself is added to the container as a link, similar to how URLs are represented online. Thus, a path to an image in a user's home directory on Linux would be file:///home/username/image.png. We add this link to the container, and then use the AnyConverter class to get a URL to the container itself, called the internalURL. This internalURL will be used as the new link to the (now internal) image.

Template:Documentation/Linux The image itself is stored as an com.sun.star.drawing.XShape object, which is created below.

Object imgTemp = xmsf.createInstance("com.sun.star.drawing.GraphicObjectShape");
XShape xImage = (XShape)UnoRuntime.queryInterface(XShape.class, imgTemp);

See also com.sun.star.drawing.GraphicObjectShape and com.sun.star.drawing.XShape.

Now we set the properties of the actual shape (and image) (com.sun.star.beans.XPropertySet interface):

XPropertySet xpi = ( XPropertySet )UnoRuntime.queryInterface(XPropertySet.class, xImage );
xpi.setPropertyValue("GraphicURL", internalURL);
Size imgSize = new Size(2500, 5000);
xImage.setSize(imgSize);

And finally, we get the necessary objects to draw in the spreadsheet itself, and we draw the image. The last line of code simply removes the image entry from the temporary container. See com.sun.star.drawing.XDrawPageSupplier (deprecated) interface.

XDrawPageSupplier oDPS = (XDrawPageSupplier) UnoRuntime.queryInterface(
	XDrawPageSupplier.class, curSheet);
XDrawPage xDrawPage = oDPS.getDrawPage();  
xDrawPage.add(xImage);
xBitmapContainer.removeByName("someID");

See also

Personal tools