Revision as of 17:07, 17 May 2006

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

   Reference< XInterface  > Desktop = rOfficeServiceManager->createInstance(
   OUString::createFromAscii( "com.sun.star.frame.Desktop" ));

   Reference< XComponentLoader > rComponentLoader (Desktop, UNO_QUERY);
   if( rComponentLoader.is() ){
       	printf( "XComponentloader successfully instanciated\n" );
   	}

   Reference< XComponent > xcomponent = rComponentLoader->loadComponentFromURL(

       OUString::createFromAscii("_blank"),
       0,
       Sequence < ::com::sun::star::beans::PropertyValue >());

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

   Reference< XInterface  > Desktop = rOfficeServiceManager->createInstance(
   OUString::createFromAscii( "com.sun.star.frame.Desktop" ));

   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 we do with a Cell ?

The XCell interface provide six methods :

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>