Revision as of 08:32, 11 July 2018

doc

OOo

This article should be checked for accuracy and conformity to style.

1 Introduction
2 To find the Sheet
3 How to obtain the Cell
4 What can be done with a Cell ?
5 Cell Range
6 Ready to translate the Developers Guide into C++
7 Event Listener
- 7.1 First Event Listener
- 7.2 A second Event Listener
8 Graphics
- 8.1 Embedding Images (Java Code waiting for a C++ Translation)
9 See also

Introduction

To save you searching in the previous chapters the starting code is presented to insert into the new listings given in this chapter.

First the ooConnect() part is given as the following code:

// 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 :

Second the main() part is given as the following code:

//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 :

com.sun.star.lang.XMultiServiceFactory, com.sun.star.uno.XInterface, com.sun.star.frame.XComponentLoader and com.sun.star.lang.XComponent interfaces
com.sun.star.frame.Desktop service
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).

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.

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, see the following link to obtain a particular cell (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, the second is the row number and both start from zero (0). See above the example where access to the top left cell is called A1.

What can be done with a Cell ?

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

getFormula returns the formula string of a cell.
setFormula sets a formula into the cell.
getValue returns the floating point value of the cell.
setValue sets a floating point value into the cell.
getType returns the type of the cell.
getError returns the error value of the cell.

Numerical Value in a Cell

To put a numerical value in a cell see the following code:

//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);

Formula in a Cell

To put a formula in a cell replace the previous setValue by setFormula. This gives you the following code:

//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

You use a similar principle to put a string in a cell as shown in the 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 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.

What is the Content Type of a Cell ?

Use a getType method of the XCell interface to return one of the four possibilities: EMPTY, VALUE, TEXT or FORMULA (::com::sun::star::table) How are the values presented in C++ ? The partial content of com.sun.star.table.CellContentType is given below which shows us a “using namespace com::sun::star::table”:

// 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

This allows you to write in C++ CellContentType_EMPTY if you have added the following statement:

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

The code below is an example which writes out “Numerical Value”:

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;
	}

Computing the Formula

The following 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

To format a cell use a predefined style in the spreadsheet. The predefined style's names are in English even though they appear in other languages in the Object Bar. They are as follows: Result, Result2, Default, Heading, Heading1, and so forth.

//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);

Cell Annotations

The following example inserts an annotation and makes 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);

If you want an annotation to not always be visible you should only remove the last three lines. Annotations are useful, but to present them when they already exist, add the following test to 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”.

Cell Range

To obtain a cell range is not as easy as in OooBasic. First you have 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 the following code 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. First you need the 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 add the following code:

//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);

This gives you a program which presents the absolute X and Y position of a cell.

Retrieve the absolute Address of the Cell Range

First you need a CellRangeAddress type described in <OpenOffice.org1.1_SDK>/idl/com/sun/star/table/CellRangeAddress.idl and the com.sun.star.sheet.XCellRangeAddressable interface:

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

Starting from the cell range program, add the following code:

//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 presents three of the five cell range attributes.

Getting a Cell range with a Selection

A similar problem has been tackled 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 presents “Hello !!!” in the top left corner of the selected range.

Ready to translate the Developers Guide into C++

When you want to develop the software further refer to the Developers Guide. However examples are given in Java code. You have to learn how to translate it into C++. At first glance this translation seems like a complicated task. However a complete Java novice experimented with it when the chapter on annotation was written and no problems were encountered. However following are examples of more problems:

The Compute Function Example

Following is the 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 type 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);

The UNO calls are simpler in C++ than in Java. Comments have been added to explain how constants are managed. This code is important because you can see two different kinds of constants encountered in UNO C++ programming. They are as follows:

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 required to generate hdl and hpp files from IDL files and to not read the hpp but hdl files, this job is allowed for novices but others can see everything in IDL files. For example, see the following differences between the following code and the consequences on the constants use:

// 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
};

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

First, see the Java code 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");

Following is 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"));

The Object type is translated by the Any type. 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 example takes values from an array and puts 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);

It can be compiled to work if an OOoCalc document is opened and focused on a cell range of two rows and three columns.

This is important as the cell range size must be the same as the series of sequences.

An Example from OooForum: Opening a Password protected Document

Kscape posts the following message:

“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);

Event Listener

See also Creating Listeners and Handlers in OOoBasic.

First Event Listener

Event listener in C++ is not documented in the Developer's Guide. To find a way to resolve this problem first read the Java code. If you want to implement an event listener you have to construct a class which inherits from a known class and then installs it. The first time this was tried OOo hung completely (without a message) After reading the OOo source code to find an example,in the file OOo_1.1.3_src/basic/source/basmgr/basmgr.cxx you have to use a helper to create the class. See the following code:

//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 but it only prints out a message. The fflush(stdout) is important if you want to see the printf when the event occurs (thanks to Emmanuel GUY).

Next you need 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);

You first instantiate our class as usual with a new one but you need to use it after a static_cast before you install it using an appropriate method. This method is a addSelectionchangeListener().

It is important now that the program doesn't terminate without removing the listener.

Use the C getchar() function for that but with the real program this important part has to change.

To test this code you have to provide a rSpreadsheetController interface. To do this use the previous code, and remove the Java translation part. You then launch a OOoCalc document and select a cell range. Then select another cell range to see the message in the shell window.

MichaelJ1980 in OOoforum has stated that he wrote the following code:

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

instead

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

A second Event Listener

Emmanuel GUY has posted an other example in OOoForum which is listed as follows: events (XEventListener, XWindowListener, ...) in C++

Graphics

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

To add external images to a spreadsheet, which is different from to images which requires a user to have an external file that is always in the same location relative to where the spreadsheet file is located, whilst embedding an image adds the image in the ODS file itself.

First for the purpose of this example there will be a model implementing the XModel interface, and this will relate to a spreadsheet. You 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();

Next, you need to create a com.sun.star.lang.XMultiServiceFactory object so you can create components in the actual file. 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"));

The file itself is added to the container as a link, which is 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. Add this link to the container and then use the AnyConverter class to get a URL to the container itself. This is called the internalURL. This internalURL will be used as the new link to the (now internal) image.

String sPath = "file:///home/wojciech/Desktop/img.png"; 
xBitmapContainer.insertByName("someID", sPath);
String internalURL = AnyConverter.toString(xBitmapContainer.getByName("someID"));

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);

Now 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);

Finally, you get the necessary objects drawn in the spreadsheet itself and you can 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");

Return to Document Home page.

Difference between revisions of "Calc/API/Programming"