Difference between revisions of "Calc/API/Sheet Operations"

From Apache OpenOffice Wiki
< Calc‎ | API
Jump to: navigation, search
m (→‎An existing Sheet)
m (→‎To find the Sheet)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 
= To find the Sheet =
 
= To find the Sheet =
  
−
The most important interface for this chapter is <idl>com.sun.star.sheet.XSpreadsheetDocument</idl>. 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.
+
The most important interface for this chapter is <idl>com.sun.star.sheet.XSpreadsheetDocument</idl>. This interface supplies the <idlm>com.sun.star.sheet.XSpreadsheetDocument:getSheets</idlm> method. This interface is inherited from XInterface which provide queries for a new interface to an existing UNO object : queryInterface.
  
 
== An existing Sheet ==
 
== An existing Sheet ==
  
 
If you know the sheet's name use the getByName method :
 
If you know the sheet's name use the getByName method :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 2 How to find a Sheet in a Spreadsheet
 
//Listing 2 How to find a Sheet in a Spreadsheet
 
//C++
 
//C++
Line 20: Line 20:
 
//use getByName to get a reference (type Any)  
 
//use getByName to get a reference (type Any)  
 
Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet2"));
 
Any rSheet = rSheets->getByName( OUString::createFromAscii("Sheet2"));
−
</source>  
+
</syntaxhighlight>  
−
If we have a look at getByName method, we can see it can throw a “no such element” exception. The previous code would be better written with a try and catch statement.  
+
 
−
<source lang="cpp">
+
If we have a look at getByName method, we can see it can throw a “no such element” exception. The previous code would be better written with a try and catch statement.
 +
 +
<syntaxhighlight lang="cpp">
 
Listing 3 Finding a Sheet and managing Exception
 
Listing 3 Finding a Sheet and managing Exception
 
//C++
 
//C++
Line 42: Line 44:
 
       printf( "Error: No such element ;%s\n", o.pData->buffer );
 
       printf( "Error: No such element ;%s\n", o.pData->buffer );
 
   }
 
   }
−
</source>
+
</syntaxhighlight>
 +
 
 
For sake of simplicity we will only use this exception managing only at the end of this document, when “helpers” will be tackled in ([[Constructing_Helpers|Constructing Helpers]]).  
 
For sake of simplicity we will only use this exception managing only at the end of this document, when “helpers” will be tackled in ([[Constructing_Helpers|Constructing Helpers]]).  
 
If you only know the sheet's number you can retrieve the sheet using this way :
 
If you only know the sheet's number you can retrieve the sheet using this way :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 4 Finding a Sheet with an Index
 
//Listing 4 Finding a Sheet with an Index
 
//C++
 
//C++
Line 65: Line 68:
 
//use getByName to get a reference (type Any)  
 
//use getByName to get a reference (type Any)  
 
Any rSheet = rSheetsByIndex->getByIndex( (short)1 );  
 
Any rSheet = rSheetsByIndex->getByIndex( (short)1 );  
−
</source>
+
</syntaxhighlight>
 +
 
 
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.
 
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.
 
Take care : UNO considers the sheet numbering, starting from 0, and then 1 is the second sheet.
Line 71: Line 75:
 
==Create, rename, copy and delete a Sheet==
 
==Create, rename, copy and delete a Sheet==
 
To create a sheet the method insertNewByName is used :
 
To create a sheet the method insertNewByName is used :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 5 Create a Sheet
 
//Listing 5 Create a Sheet
 
//C++
 
//C++
Line 85: Line 89:
 
//use it to create a new sheet called MySheet  
 
//use it to create a new sheet called MySheet  
 
rSheets->insertNewByName(OUString::createFromAscii("MySheet"), (short)0);
 
rSheets->insertNewByName(OUString::createFromAscii("MySheet"), (short)0);
−
</source>
+
</syntaxhighlight>
 
(short)0 is the index of position where the new sheet is inserted. Here “MySheet” will be the first sheet.
 
(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:
 
How to move a sheet when its name is known:
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 6 Moving a Sheet
 
//Listing 6 Moving a Sheet
 
//C++
 
//C++
Line 103: Line 107:
 
//use it to move a sheet called MySheet in third(back)/second(forward) position   
 
//use it to move a sheet called MySheet in third(back)/second(forward) position   
 
rSheets->moveByName(OUString::createFromAscii("MySheet"), (short)2);
 
rSheets->moveByName(OUString::createFromAscii("MySheet"), (short)2);
−
</source>
+
</syntaxhighlight>
 
How to copy a sheet  
 
How to copy a sheet  
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 7 Copying a Sheet
 
//Listing 7 Copying a Sheet
 
//C++
 
//C++
Line 120: Line 124:
 
rSheets->copyByName(OUString::createFromAscii("MySheet"),
 
rSheets->copyByName(OUString::createFromAscii("MySheet"),
 
                                 OUString::createFromAscii("MySheet2"), (short)1);
 
                                 OUString::createFromAscii("MySheet2"), (short)1);
−
</source>
+
</syntaxhighlight>
 
MySheet2 is an exact copy of "MySheet" inserted as a second sheet.
 
MySheet2 is an exact copy of "MySheet" inserted as a second sheet.
  
 
How to rename a sheet (see <idl>com.sun.star.container.XNamed</idl>) :  
 
How to rename a sheet (see <idl>com.sun.star.container.XNamed</idl>) :  
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 8 Renaming a Sheet
 
//Listing 8 Renaming a Sheet
 
//C++  
 
//C++  
Line 148: Line 152:
 
// rename
 
// rename
 
rname->setName(OUString::createFromAscii("MySheet"));  
 
rname->setName(OUString::createFromAscii("MySheet"));  
−
</source>
+
</syntaxhighlight>
  
 
How to remove a sheet (see <idl>com.sun.star.container.XNameContainer</idl>) :
 
How to remove a sheet (see <idl>com.sun.star.container.XNameContainer</idl>) :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 9  Removing a Sheet
 
//Listing 9  Removing a Sheet
 
// C++
 
// C++
 
rSheets->removeByName(OUString::createFromAscii("Sheet1"));  
 
rSheets->removeByName(OUString::createFromAscii("Sheet1"));  
−
</source>
+
</syntaxhighlight>
 
works properly. You can use also the <idl>com.sun.star.container.XNameContainer</idl> interface like :
 
works properly. You can use also the <idl>com.sun.star.container.XNameContainer</idl> interface like :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 10 Removing a Sheet (an other way)
 
//Listing 10 Removing a Sheet (an other way)
 
// C++
 
// C++
Line 166: Line 170:
  
 
xNameContainer->removeByName(OUString::createFromAscii("Sheet1")); //OK
 
xNameContainer->removeByName(OUString::createFromAscii("Sheet1")); //OK
−
</source>
+
</syntaxhighlight>
  
 
==Retrieve or change the currently focused Sheet==
 
==Retrieve or change the currently focused Sheet==
  
 
How to set a known-named sheet as currently focused sheet :
 
How to set a known-named sheet as currently focused sheet :
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 11 Focus and C++ programming
 
//Listing 11 Focus and C++ programming
 
//C++  
 
//C++  
Line 210: Line 214:
 
// make our newly inserted sheet the active sheet using setActiveSheet  
 
// make our newly inserted sheet the active sheet using setActiveSheet  
 
rSpreadsheetView->setActiveSheet(rSpSheet);  
 
rSpreadsheetView->setActiveSheet(rSpSheet);  
−
</source>
+
</syntaxhighlight>
 
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 (
 
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 (
 
[[ UNO_automation_with_a_binary_%28executable%29#The_default_opened_Document|default opened document]])
 
[[ UNO_automation_with_a_binary_%28executable%29#The_default_opened_Document|default opened document]])
 
Here is a piece of code which retrieves the sheet under focus.  
 
Here is a piece of code which retrieves the sheet under focus.  
−
<source lang="cpp">
+
<syntaxhighlight lang="cpp">
 
//Listing 12 Retrieve the Sheet under Focus
 
//Listing 12 Retrieve the Sheet under Focus
 
//C++
 
//C++
Line 262: Line 266:
 
     return 0;
 
     return 0;
 
}
 
}
−
</source>
+
</syntaxhighlight>
  
 
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.
 
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.

Latest revision as of 14:18, 31 January 2021

To find the Sheet

The most important interface for this chapter is com.sun.star.sheet.XSpreadsheetDocument. This interface supplies 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 : 

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

If we have a look at getByName method, we can see it can throw a “no such element” exception. The previous code would be better written with a try and catch statement. 

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 in (Constructing Helpers). If you only know the sheet's number you can retrieve the sheet using this way : 

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

Create, rename, copy and delete a Sheet

To create a sheet the method insertNewByName is used : 

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

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

//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 (see com.sun.star.container.XNamed) : 

//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/container/XNamed.hpp>
// Don't forget to add "com.sun.star.container.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 (see com.sun.star.container.XNameContainer) : 

//Listing 9  Removing a Sheet
// C++
	rSheets->removeByName(OUString::createFromAscii("Sheet1"));

works properly. You can use also the com.sun.star.container.XNameContainer interface like : 

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

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

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

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Calc/API/Sheet_Operations&oldid=247588"
Personal tools