In this part we extend the program developed in Part 1 code to manipulate the spreadsheet further.
we use ::cppu::bootstrap() to get the remote office context of a running
office instance
Reference< XComponentContext > xContext( ::cppu::bootstrap() );
Obtain the service manager from the component context
Reference< XMultiComponentFactory > xServiceManager = xContext->getServiceManager();
Create Desktop service
Reference< XInterface > xDesktop = xServiceManager->createInstanceWithContext( OUString("com.sun.star.frame.Desktop"), xContext );
Get XDesktop2 interface from XInterface reference we got in previous step
Reference< XDesktop2 > xDesktop2( xDesktop, UNO_QUERY );
Load a blank spreadsheet document using XDesktop2 interface
Reference< XComponent > xComponent = xDesktop2->loadComponentFromURL( OUString( "private:factory/scalc" ), // URL to the ods file
OUString( "_blank" ), 0,
Sequence < ::com::sun::star::beans::PropertyValue >() );
Note that the last parameter can be used to describe the properties of the document, and can
use the property names in MediaDescriptor
See below for details on Sequence and PropertyValue types.
In fact the xComponent object we got supports XSpreadsheetDocument interface, but
we need to explicitly convert by letting the compiler know by :
Reference< XSpreadsheetDocument > xSpreadsheetDocument(xComponent, UNO_QUERY);
Get the "collection of spreadsheets" interface XSpreadsheets :
Reference< XSpreadsheets > xSpreadsheets = xSpreadsheetDocument->getSheets();
Create a new sheet named MySheet
xSpreadsheets->insertNewByName( OUString("MySheet"), (short)0 );
See what is the type of each element in the collection XSpreadsheets
Type aElemType = xSpreadsheets->getElementType();
fprintf( stdout, "\n>>> xSpreadsheets container has elements each of type = %s\n",
OUStringToOString( aElemType.getTypeName(), RTL_TEXTENCODING_ASCII_US ).getStr() );
This should print the type as "com.sun.star.sheet.XSpreadsheet"
Get the object corresponding to the sheet named "MySheet" and query its
XSpreadsheet interface
Any aSheet = xSpreadsheets->getByName( OUString("MySheet") );
Reference< XSpreadsheet > xSpreadsheet( aSheet, UNO_QUERY );
Get cells A1, A2, A3 each by using getCellByPosition method of XCellRange interface inherited XSpreadsheet
Set cell A1 to 105 and A2 to 501 and insert a formula to A3 =SUM(A1:A2)
Reference< XCell > xCell = xSpreadsheet->getCellByPosition(0, 0);
xCell->setValue(105);
xCell = xSpreadsheet->getCellByPosition(0, 1);
xCell->setValue(501);
xCell = xSpreadsheet->getCellByPosition(0, 2);
xCell->setFormula("=sum(A1:A2)");
Get XPropertySet interface of xCell object which is really a Cell service object
Reference< XPropertySet > xCellProps( xCell, UNO_QUERY );
Set the property "CellStyle" of A3 cell to the value "Result" and
the property "VertJustify" to the enum CellVertJustify2::TOP
xCellProps->setPropertyValue( OUString("CellStyle"), makeAny( OUString("Result") ) );
xCellProps->setPropertyValue( OUString("VertJustify"), makeAny( CellVertJustify2::TOP ) );
Note that makeAny() function converts any type passed to it to Any type.
Get XModel interface from the spreadsheet component object we got in step 5)
then get XController interface object using XModel's getCurrentController() method
and query XSpreadsheetView from XController object. Using the XSpreadsheetView set the
"MySheet" tab to be the active one.
Reference< XModel > xSpreadsheetModel( xComponent, UNO_QUERY );
Reference< XController > xSpreadsheetController = xSpreadsheetModel->getCurrentController();
Reference< XSpreadsheetView > xSpreadsheetView( xSpreadsheetController, UNO_QUERY );
xSpreadsheetView->setActiveSheet( xSpreadsheet );
Get XCellRangesQuery supporting object from the "MySheet" object.
Get the cell ranges where there are formulas in the new sheet
(we know that there is only one cell where there are formulas)
Then get the collection of formula cells in the obtained range
using XEnumerationAccess and XEnumeration interfaces.
Reference< XCellRangesQuery > xCellQuery( aSheet, UNO_QUERY );
Reference< XSheetCellRanges > xFormulaCells = xCellQuery->queryContentCells( (short)CellFlags::FORMULA );
Reference< XEnumerationAccess > xFormulasEnumAccess = xFormulaCells->getCells();
Reference< XEnumeration > xFormulaEnum = xFormulasEnumAccess->createEnumeration();
Loop over the enumerated collection of formula cells by using XEnumeration interface's
hasMoreElements() and nextElement() methods
Check http://api.libreoffice.org for the signature of hasMoreElements() and nextElement() methods
as an exercise.
while ( xFormulaEnum->hasMoreElements() )
{
Any aNextElement = xFormulaEnum->nextElement();
xCell = Reference< XCell >( aNextElement, UNO_QUERY );
Reference< XCellAddressable > xCellAddress( xCell, UNO_QUERY );
fprintf( stdout, ">>> Formula cell in column = %d, row = %d, with formula = %s\n",
xCellAddress->getCellAddress().Column,
xCellAddress->getCellAddress().Row,
OUStringToOString( xCell->getFormula(), RTL_TEXTENCODING_ASCII_US ).getStr() );
fflush( stdout );
}
The relation between UNO types like any, string, short etc to the corresponding C++ types can be seen at CommonTypes
Refer UNO Data Types for a full coverage on the UNO data types. Some of them are explained here.
Struct type : These are similar to C++ struct where there are only public member variables only. Example
::com::sun::star::beans::PropertyValue
IDL source file : PropertyValue.idl
module com { module sun { module star { module beans {
published struct PropertyValue
{
string Name;
long Handle;
any Value;
com::sun::star::beans::PropertyState State;
};
}; }; }; };
Note : Structs should not be contructed using service manager.
Instantiating of a struct in C++ can be done as usual using either static allocation/dynamic allocation using new keyword
Example :
::com::sun::star::beans::PropertyValue aProp;
aProp.Name = OUString( "Readonly" );
aProp.Value = makeAny( ( sal_Bool )true );
Struct may also inherit from other structs.
Polymorphic struct : This is a struct with one or more type parameters and the members of this struct can have these parameters as types.
Example of polymorphic struct :
// A polymorphic struct type template with two type parameters:
struct Poly<T,U> {
T member1;
T member2;
U member3;
long member4;
};
Example showing the usage of polymorphic struct :
// Using an instantiation of Poly as a UNO type:
interface XIfc { Poly<boolean, any> fn(); };
Any type : This type can hold on to one of the C++ types that are convertible to UNO types.
To set an Any type variable, use <<= operator
To get from an Any type variable use >>= operator
Example :
sal_Int32 cellColor;
Any any;
any = rCellProps->getPropertyValue(OUString::createFromAscii( "CharColor" ));
// extract the value from any
any >>= cellColor;
Sequence type : This corresponds to "sequence" UNO type. This represents a homogenous collection of values of one UNO type with variable number of elements. For remote access this interfaces that take or return "sequence" type is important for performance.
Example a) Empty sequence of PropertyValue struct :
Sequence< ::com::sun::star::beans::PropertyValue > loadProps;
Example b) Non empty sequence of PropertyValue structs :
Sequence< ::com::sun::star::beans::PropertyValue > loadProps( 1 );
// the structs are default constructed
loadProps[0].Name = OUString::createFromAscii( "ReadOnly" );
loadProps[0].Value <<= true;
Reference< XComponent > rComponent = rComponentLoader->loadComponentFromURL(
OUString::createFromAscii("private:factory/swriter"),
OUString::createFromAscii("_blank"),
0,
loadProps);
Element access is the way to access a object from a collection of objects. The most important element access interfaces are :
com::sun::star::container::XNameContainercom::sun::star::container::XIndexContainercom::sun::star::container::XEnumerationAll these three inherit from XElementAccess interface and it has two methods :
type getElementType()
boolean hasElements()
getElementType() returns the type of elements of the container as a com::sun::star::uno::Type object.
If container is heterogenous it returns void type.
hasElements() tells whether the set contains any element at all.
Name access
The basic interface inherited by com::sun::star::container::XNameContainer is com::sun::star::container::XNameAccess
which has the following three methods
any getByName( [in] string name)
sequence< string > getElementNames()
boolean hasByName( [in] string name)
Index access
The basic interface inherited by com::sun::star::container::XIndexContainer is com::sun::star::container::XIndexAccess
It has two methods :
any getByIndex( [in] long index)
long getCount()
Example : com::sun::star::sheet::XSpreadsheets supports both Index access and Named access
Enumeration access
The interface com::sun::star::container::XEnumerationAccess creates enumerations that allow traveling across a set of objects.
It has one method:
com::sun::star::container::XEnumeration createEnumeration()
The returned XEnumeration object supports two methods :
boolean hasMoreElements()
any nextElement()
We used the above methods in getting all formula cells in the C++ program developed above.
In the program we used XCellRangesQuery interface to get cell ranges object corresponding
to cells having formulas. XCellRangesQuery interface defines the method :
XSheetCellRanges queryContentCells(short cellFlags)
The returned XSheetCellRanges object has the following methods :
XEnumerationAccess getCells()
String getRangeAddressesAsString()
sequence< com.sun.star.table.CellRangeAddress > getRangeAddresses()
We used getCells() to get hold of XEnumerationAccess object to iterate through the cells with formula.
The complete C++ program can be compiled and run using :
$ git clone https://github.com/niocs/ManipulateSpreadsheet.git
$ cd ManipulateSpreadsheet
$ make
$ make ManipulateSpreadsheet.run
After running make ManipulateSpreadsheet.run you should see the following strings in the terminal
remote ServiceManager is available.
opened spreadsheet document...
>>> xSpreadsheets container has elements each of type = com.sun.star.sheet.XSpreadsheet
>>> Formula cell in column = 0, row = 2, with formula = =SUM(A1:A2)
In addition you should see in Calc a sheet named "MySheet" with the following contents :
If you see a crash and stack trace after running make ManipulateSpreadsheet.run like
Application Error
Fatal exception: Signal 6
Stack:
/ssd1/work/dennis/core/instdir/program/libuno_sal.so.3(+0x63706)[0x7f9eae7c1706]
/ssd1/work/dennis/core/instdir/program/libuno_sal.so.3(+0x638ef)[0x7f9eae7c18ef]
/ssd1/work/dennis/core/instdir/program/libuno_sal.so.3(+0x63a30)[0x7f9eae7c1a30]
/lib64/libc.so.6(+0x34770)[0x7f9eae04f770]
/lib64/libc.so.6(gsignal+0x35)[0x7f9eae04f6f5]
/lib64/libc.so.6(abort+0x16a)[0x7f9eae0512fa]
...
you need to go to /tmp/ in your machine and see if any named pipes belonging to previous LO instances (could be created by LO runs in Part1) that looks like :
OSL_PIPE_1000_SingleOfficeIPC_1198aa9b6c6d53d496efee257b794f7
OSL_PIPE_1000_uno48105118116146272311915591153248511282101
Try removing these files before retrying the build/run instructions.