Open LibreOffice Calc with C++, part 3

This is part 3 where we connect to LibreOffice, open up a new LibreOffice Calc Spreadsheet and insert some text and numbers into some cells. Part 1 covers the integration of LibreOffice with C++, and part 2 covers possible errors when interfacing against LibreOffice.

Here is a version that works both in Windows 7 and Windows 10 with Visual Studio 2015 update 3, and this trimmed down version is just a couple of steps. Complete listing and source at GitHub below.

How to create LibreOffice Calc Spreadsheet

Start by creating a component context, which is used in LibreOffice components. It connects to the remote application. Remote is being the other application your application is talking to, and in this case it’s going to be LibreOffice Calc.

Reference<XComponentContext> xContext(::cppu::bootstrap());

Next, make a factory interface for creating component instances giving a context from which to retrieve deployment values. It connects through the remote component context created previously, and gets the remote office service manager.

Reference<XMultiComponentFactory> xServiceManager(
	xContext->getServiceManager());

Now create the component from the context. This is the actual UNO bridge we’ll send queries through, and in turn starting the LibreOffice Calc application.

Reference<XDesktop2> xComponentLoader = Desktop::create(xContext);

Next step is to load the component XComponent, which is the LibreOffice Calc application. It will open a blank spreadsheet, just as if you’ve created a new blank spreadsheet through the GUI. This is also where the properties like hidden can be supplied if the application should be hidden from the user.

Reference<XComponent> xComponent(xComponentLoader->loadComponentFromURL(
	OUString("private:factory/scalc"),
	OUString("_blank"), 0,
	Sequence < ::com::sun::star::beans::PropertyValue >()));

Obtain a reference to the document. From the above reference, which is the application itself, we acquire a reference to the document / spreadsheet. From this reference we can do just about anything with a spreadsheet.

Reference <XSpreadsheetDocument> spreadDoc(xComponent, UNO_QUERY);

Get a list of all the sheets and retrieve the first sheet by name.

Reference<XSpreadsheets> rSheets = spreadDoc->getSheets();
Any rSheet = rSheets->getByName(OUString::createFromAscii("Sheet1"));
Reference<XSpreadsheet> sheet(rSheet, UNO_QUERY);

And insert data into some cells. insertIntoCell is an overloaded method, one for string values which are treat like formulas, and one for numbers (double) which are treated as numbers.

// Insert cells
insertIntoCell(1, 1, "I was inserted from C++", sheet);
insertIntoCell(1, 2, 3.14, sheet);
insertIntoCell(0, 3, "Formula pi^2:", sheet);
insertIntoCell(1, 3, "=B3*B3", sheet);

Insert a formula or text into a cell.

Reference<XCell> xCell = nullptr;
xCell = sheet->getCellByPosition(CellX, CellY);
xCell->setFormula(OUString::createFromAscii(value.c_str()));

Insert a number into a cell, as a double.

Reference<XCell> xCell = nullptr;
xCell = sheet->getCellByPosition(CellX, CellY);
xCell->setValue(value);

The full listings

Insert a string value or a formula with this overload.

int insertIntoCell(int CellX, int CellY,
	const std::string & value,
	Reference<XSpreadsheet> sheet)
{
	try {
		Reference<XCell> xCell = nullptr;
		xCell = sheet->getCellByPosition(CellX, CellY);
		xCell->setFormula(OUString::createFromAscii(value.c_str()));
	}
	catch (const com::sun::star::lang::IndexOutOfBoundsException & ex) {
		std::cerr << "Could not get Cell";
		return 1;
	}

	return 0;
}

Insert numbers with this overload.

int insertIntoCell(int CellX, int CellY,
	double value,
	Reference<XSpreadsheet> sheet)
{
	try {
		Reference<XCell> xCell = nullptr;
		xCell = sheet->getCellByPosition(CellX, CellY);
		xCell->setValue(value);
	}
	catch (const com::sun::star::lang::IndexOutOfBoundsException & ex) {
		std::cerr << "Could not get Cell";
		return 1;
	}

	return 0;
}

Method to actually start LibreOffice Calc.

int start_libreoffice_calc()
{
	try
	{
		// Get the remote office component context
		Reference<XComponentContext> xContext(::cppu::bootstrap());
		if (!xContext.is())
		{
			std::cerr << "No component context!\n";
			return 1;
		}

		// get the remote office service manager
 		Reference<XMultiComponentFactory> xServiceManager(
 			xContext->getServiceManager());
 		if (!xServiceManager.is())
 		{
 			std::cerr << "No service manager!\n";
 			return 1;
 		}

		// get an instance of the remote office desktop UNO service
		// and query the XComponentLoader interface
		Reference<XDesktop2> xComponentLoader = Desktop::create(xContext);

		// open a spreadsheet document
		Reference<XComponent> xComponent(xComponentLoader->loadComponentFromURL(
			OUString("private:factory/scalc"),
			OUString("_blank"), 0,
			Sequence < ::com::sun::star::beans::PropertyValue >()));
		if (!xComponent.is())
		{
			std::cerr << "opening spreadsheet document failed!\n";
			return 1;
		}

		// Reference to the document
		Reference <XSpreadsheetDocument> spreadDoc(xComponent, UNO_QUERY);

		// Get the first sheet
		Reference< XSpreadsheets > rSheets = spreadDoc->getSheets();
		Any rSheet = rSheets->getByName(OUString::createFromAscii("Sheet1"));
		Reference< XSpreadsheet > sheet(rSheet, UNO_QUERY);

		// Insert cells
		insertIntoCell(1, 1, "I was inserted from C++", sheet);
		insertIntoCell(1, 2, 3.14, sheet);
		insertIntoCell(0, 3, "Formula pi^2:", sheet);
		insertIntoCell(1, 3, "=B3*B3", sheet);

	}
	catch (::cppu::BootstrapException & e)
	{
		std::cerr << "Caught BootstrapException: " 
			<< OUStringToOString(e.getMessage(), RTL_TEXTENCODING_ASCII_US).getStr() 
			<< std::endl;
		return 1;
	}
	catch (Exception & e)
	{
		std::cerr << "Caught UNO exception: "
			<< OUStringToOString(e.Message, RTL_TEXTENCODING_ASCII_US).getStr()
			<< std::endl;
		return 1;
	}

	return 0;
}

Main method.

int main()
{
	start_libreoffice_calc();

	return 0;
}

The end result should look something like this:

The whole source code is available at GitHub.