Calc/API/Programming

From Apache OpenOffice Wiki
< Calc‎ | API
Revision as of 17:48, 17 May 2006 by SergeMoutou (Talk | contribs)

Jump to: navigation, search

To avoid to search in the previous code where we insert the new listings given in this chapter, we first give it again (only the main() part) : [cpp] //Listing 1 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;

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

To find the Sheet

The most important interface for this chapter is XspreadsheetDocument. This interface supply the getSheets() method. This interface is inherited from XInterface which provide queries for a new interface to an existing UNO object : queryInterface.

An existing Sheet

If you know the sheet's name use the getByName method : [cpp] //Listing 2 How to find a Sheet in a Spreadsheet //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();

//use getByName to get a reference (type Any) Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet2")); <code> If we have a look at getByName method, we can see it can manage an “no such element” exception. The the previous code would be better written with a try and catch statement. <code>[cpp] Listing 3 Finding a Sheet and managing Exception //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();

//use getByName to get a reference (type Any) try { Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet2")); } catch( Exception &e ){ 

     OString o = OUStringToOString( e.Message, RTL_TEXTENCODING_ASCII_US );
     printf( "Error: No such element ;%s\n", o.pData->buffer );
  }

For sake of simplicity we will only use this exception managing only at the end of this document, when “helpers” will be tackled (Chapter 12). If you only know the sheet's number you can retrieve the sheet using this way : [cpp] //Listing 4 Finding a Sheet with an Index //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::container; // Don't forget to add : #include <com/sun/star/container/XIndexAccess.hpp> // Don't forget to add "com.sun.star.frame.XIndexAccess \" 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();

// query for the ::com::sun::star::container::XIndexAccess service 

   Reference< XIndexAccess > rSheetsByIndex (rSheets, UNO_QUERY);

//use getByName to get a reference (type Any) Any rSheet = rSheetsByIndex->getByIndex( (short)1 ); As you can see we have to query a new service XIndexAccess. This means adding two lines in the code and one in the makefile as seen in C++ comments. Take care : UNO considers the sheet numbering, starting from 0, and then 1 is the second sheet. To summarize, a diagram is presented

File:Ch5fig1ObtainSheet

Create, rename, copy and delete a Sheet

To create a sheet the method insertNewByName is used : [cpp] //Listing 5 Create a Sheet //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();

//use it to create a new sheet called MySheet rSheets->insertNewByName(OUString::createFromAscii("MySheet"), (short)0); (short)0 is the index of position where the new sheet is inserted. Here “MySheet” will be the first sheet.

How to move a sheet when its name is known: [cpp] //Listing 6 Moving a Sheet //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();

//use it to move a sheet called MySheet in third(back)/second(forward) position rSheets->moveByName(OUString::createFromAscii("MySheet"), (short)2); How to copy a sheet [cpp] //Listing 7 Copying a Sheet //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();

//use it to copy a sheet called MySheet in second position with the name "MySheet2" rSheets->copyByName(OUString::createFromAscii("MySheet"), 

                                OUString::createFromAscii("MySheet2"), (short)1);

MySheet2 is an exact copy of "MySheet" inserted as a second sheet. How to rename a sheet : [cpp] //Listing 8 Renaming a Sheet //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::container; // Don't forget to add : #include <com/sun/star/sheet/XNamed.hpp> // Don't forget to add "com.sun.star.sheet.XNamed \" 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("Sheet2"));

// query for XNamed Interface Reference< XNamed > rname (rSheet, UNO_QUERY);

// rename rname->setName(OUString::createFromAscii("MySheet"));

How to remove a sheet : [cpp] //Listing 9 Removing a Sheet // C++ rSheets->removeByName(OUString::createFromAscii("Sheet1")); works properly. You can use also the XnameContainer interface like : [cpp] //Listing 10 Removing a Sheet (an other way) // C++ // Don't forget to add : using namespace com::sun::star::container; // Don't forget to add : #include <com/sun/star/container/XNameContainer.hpp> // Don't forget to add "com.sun.star.container.xNameContainer \" in the makefile Reference<XNameContainer> xNameContainer (rSheets, UNO_QUERY);

xNameContainer->removeByName(OUString::createFromAscii("Sheet1")); //OK

Retrieve or change the currently focused Sheet

How to set a known-named sheet as currently focused sheet : [cpp] //Listing 11 Focus and C++ programming //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::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

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

// 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); Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets();

//use getByName to get a reference (type Any) Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet2")); // we want to make our new sheet the current sheet, so we need to ask the model // for the controller: first query the XModel interface from our spreadsheet component

//query for the XSpreadsheet interface Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY);

Reference< XModel > rSpreadsheetModel (rSheetDoc, UNO_QUERY);

// then get the current controller from the model Reference< XController > rSpreadsheetController = rSpreadsheetModel->getCurrentController();

// get the XSpreadsheetView interface from the controller, we want to call its method // setActiveSheet Reference< XSpreadsheetView > rSpreadsheetView (rSpreadsheetController, UNO_QUERY);

// make our newly inserted sheet the active sheet using setActiveSheet rSpreadsheetView->setActiveSheet(rSpSheet); How to retrieve the currently focused sheet ? This problem is in fact a little variation of the previous one but the test is not so straightforward. If a new spreadsheet is created as previously we have no chance to choose ourself a sheet with focus because the program take always the same default sheet when just created. Then before resolving this problem we have to modify the loadComponentFromURL as previously done ( default opened document) Here is a piece of code which retrieves the sheet under focus. [cpp] //Listing 12 Retrieve the Sheet under Focus //C++ 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" ));

// Don't forget the #include <com/sun/star/frame/XDesktop.hpp> // Don't forget to add com.sun.star.frame.XDesktop \ in the makefile //query the XDesktop Interface Reference< XDesktop > xDesktop (Desktop, UNO_QUERY);

Reference< XComponent > xcomponent = xDesktop->getCurrentComponent();

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

// 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 // getActiveSheet Reference< XSpreadsheetView > rSpreadsheetView (rSpreadsheetController, UNO_QUERY); Reference< XSpreadsheet> rSheet=rSpreadsheetView->getActiveSheet(); // for a test Reference< XCell > rCell = rSheet->getCellByPosition(0, 0); rCell->setFormula(OUString::createFromAscii("Hello")); 

   return 0;

}

The way to obtain a cell is explained above, then don't read the two last lines before the return : they only allow me to test the code.

How to obtain the Cell

When you have your sheet, obtain a particular cell is easy : [cpp] //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 XCell interface provide 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 is easy : [cpp] //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 is straightforward : replace the previous setValue by setFormula. This gives us : [cpp] //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 : [cpp] //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 (XTextCursor interface). The code below allows us this change : [cpp] //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 ?

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 CellContentType.idl : 

// 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 : [cpp] 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”.

Computing the Formula

Two methods calculate() and calculateAll() are available from the XCalculaTable interface. You can query this interface from aXSreadsheetDocument.

Formating a Cell

The straight forwarder 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 ... [cpp] //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 make it permanently visible. [cpp] //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 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 : [cpp] //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

The way to obtain a cell range is not so easy as in OooBasic. You have first to obtain the Xinterface to have access to the getcellRangeByName method : [cpp] //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 XCellRange interface you can also access the range with getCellRangeByPosition method. To query one cell in the cell range, add to the previous code : [cpp] //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 : 

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

Starting from the previous listing and adding : [cpp] //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.

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 XCellRangeAdressable interface. Starting from the cell range program, we add : [cpp] //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.

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 XModel interface, and query with getCurrentSelection an XInterface and then query an XCellRange interface. [cpp] //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 Developpers 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 you can find this translation as a complicate task but I have experimented it as a Java complete novice when writing the chapter on annotation and encounter 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 : [java] //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);

The C++ translation gives (keeping the same variables names as in Java code) : [cpp] //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); 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 with this piece of code : we can see two different kind of constant :

  • one with a specific name : GeneralFunction_AVERAGE instead of AVERAGE in Java. This problem of enumeration has already been tackled at the end of chapter 3.
  • one with the same name as in IDL and Java files but in a namespace which requires the classical “using namespace”.

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 ?

See also

  • Using C++ with OOo SDK : Main Page
  • OpenOffice Calc (Chapter 5 from UNO/C++ document)
  • Writing a Program to Control OpenOffice.org, by Franco Pingiori — Part 1 and Part 2, Linux Journal</code>
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Calc/API/Programming&oldid=10548"
Personal tools