Automating OpenOffice Calc from Visual Studio 2022 using C#

• Download Visual Studio project - 73.5 KB

Introduction

Apache OpenOffice is a freely available open source office software suite largely comparable to Microsoft Office. While documentation does exist on automating the various applications, complete "how to" guides to get a Visual Studio app working are scarce. This article will give step by step instructions on how to install OpenOffice and the SDK, and to set up a test application to automate OpenOffice Calc - an alternative to Microsoft Excel. The tutorial will given enough detail in terms of setting some cells with various values and properties to get you going in your OpenOffice automation tasks. In particular, I describe configuring the test app to deal with mixed mode assemblies since the demo app is written in .NET 4.x but OpenOffice is developed using .NET 2.x. Reasonable familiarity with Visual Studio, C# and application development is assumed.

Installing OpenOffice and the SDK

Before beginning development, OpenOffice must be installed on your system. The OpenOffice suite installer can be downoaded from the Apache OpenOffice - Official Download site. For Windows, only a 32 bit installer is available so applications developed against it must also be 32 bit. The code for this article was written against the OpenOffice 4.1.14 release. I installed with the default installation location and options. On my system (Windows 10) the files were installed in C:\Program Files (x86)\OpenOffice 4. Note that if you install OpenOffice fresh for this project, you will need to run it once for it to go through some basic configuration before connecting through your application. Otherwise, on running your app, the automation process may seem to hang unexpectedly while some background configuration is unsuccessfully attempted.

In addition to the basic OpenOffice suite, you must download and install the OpenOffice SDK. General information on the SDK can be read at the Apache OpenOffice SDK site. The current version of the SDK should be available from the Apache OpenOffice - Downloads page. A Developer's Guide is also available. The version of the SDK must match the version of the OpenOffice suite installed - in my case 4.1.14. Again, I installed the SDK to the default location with default options. The SDK files were installed in C:\Program Files (x86)\OpenOffice 4\sdk. The Common Language Interface DLLs were installed in a cli subfolder in this directory. Note the locations of these files - you will need them later.

Installing Visual Studio 2022 Community

Some familiarity with essential C# development is assumed in this article. For this test application, I downloaded and installed the free Visual Studio Community edition. The only component of the Visual Studio install needed was the .NET desktop development component.

Creating the C# OpenOffice Calc Project

Creating the Windows Forms App

On startup of VS2022, select the item to Create a new project.

From the selectable dropdown filter lists at the upper right of the form, select C#, Windows and Desktop filters. Then find the Windows Forms App (.NET Framework) selection and click Next.

Give your solution an appropriate name, I chose Open Office Test, and select the Create button.

A basic Windows form will be created for you. Rename the form if desired by clicking on the form name in the Solution Exporer. Add a button on the form and name and label it appropriately.

Adding a .NET Framework Class Library

We will now add a .NET Framework class library to our solution. Right click on the Open Office Calc solution and select Add -> New Project...

For some strange reason, Visual Studio does not make adding a .NET Class Library using .NET Framework convenient. Even with the C#, Windows and Desktop filters selected, the .NET Framework class library selection does not show by default. Enter class library framework in the search box at the top of the Add a new project form, and then scroll down to the Other results based on your search section to find the Class Library (.NET Framework) selection and then click Next.

Name your class library OOCalc and press the Create button.

Right click on the file Class1.cs and select Rename. Rename the file to OOCalc and accept the renaming of the references to Class1 throughout the project.

Configuring for OpenOffice

Before we can proceed to interact with OpenOffice, we need to configure our projects to access OpenOffice components. Configuring your projects for OpenOffice access takes two steps:

• Adding references to the OpenOffice CLI DLLs
• Configuring your system for mixed mode assembly access

Adding References to the OpenOffice Common Language Interface DLLs

To add references in your project to the Common Language Interface (CLI) DLLs, right click on the References node of the OOCalc project in the Solution Explorer and select Add Reference...

In the Reference Manager form, select the Browse node in the left hand side and press the Browse button.

Browse to your OpenOffice install folder, e.g., C:\Program Files (x86)\OpenOffice 4, find the sdk\cli folder and select all the DLLs in that folder:

Pressing the Add button will add them to your project.

While adding references, right click on the Open Office Calc project and again select Add Reference... Select the Projects node in the left hand side and press OK to add a reference to the OOCalc project. This allows your Windows Form to access the OOCalc class library.

If you now build your application, you will likely encounter a build warning:

This is a known issue, does not affect the running of the system and can be ignored.

Configuring for Mixed Mode Assembly Access

If we were to proceed now to code an attempt to access our OpenOffice CLI objects, we would soon encounter this exception:

The issue here is that your current assembly, built with .NET 4.x, is attempting to access an OpenOffice subassembly built in .NET 2.x. While .NET permits side by side execution of two different .NET versions, it is not enabled by default. To enable this functionality in our OpenOffice app, edit the app.config file in our project to replace the <startup> tag with the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <!-- Replace <startup> tag -->
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <!-- Your application may differ in the runtime version being used -->
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

Some Background and Examples

Some understanding of the API architecture is helpful before diving into the application and spreadsheet code. Links are included below for more detailed explanations of each term or concept.

In overly simplified terms, the CLI DLLs expose a number of modules, the items you include at the top of each code file with using statements. Each module gives access to a number of services and objects. For example, the unoidl.com.sun.star.frame module provides a service to load an XSpreadsheetDocument object from which an XSpreadsheet object and interface can be obtained.

These objects expose multiple inheritance interfaces that provide access to the object's functionalities. The methods of each object are typically very specific, limited and simple, and to access related functionalities, the object must be cast into an alternate interface exposing some set of actions. Through the XSpreadsheet object, for example, you can eventually access an XCell object - a single cell in the sheet. Note how simple the interface is.

// pulled from metadata
namespace unoidl.com.sun.star.table
{
	public enum CellContentType
	{
		EMPTY,
		VALUE,
		TEXT,
		FORMULA
	}
	public interface XCell
	{
		string getFormula();
		void setFormula( [In] string aFormula );
		double getValue();
		void setValue( [In] double nValue );
		CellContentType getType();
		int getError();
	}
}

With the XCell interface, we can get or set the formula in the cell, get or set a floating point value of the cell, determine the cell type (empty, value, text or formula) or get an error state. Here is a code snippet to set the floating point value of a cell.

using unoidl.com.sun.star.sheet;  // for XSpreadsheet
using unoidl.com.sun.star.table;  // for XCellRange and XCell

public void SetValue(String strCellName, double dValue)
{
	// m_xSpreadsheet is XSpreadsheet interface, defined elsewhere
	XCellRange xCellRange = m_xSpreadsheet.getCellRangeByName( strCellName );
	XCell xCell = xCellRange.getCellByPosition( 0, 0 );
	xCell.setValue( dValue );
}

The XCell interface is focused on the numeric functionality of the cell. If you wish to operate on the cell as a text type, you may cast the interface into a XSimpleText interface provided by the unoidl.com.sun.star.text module.

namespace unoidl.com.sun.star.text
{
	public interface XSimpleText : XTextRange
	{
		XTextCursor createTextCursor();
		XTextCursor createTextCursorByRange( [In] XTextRange aTextPosition );
		void insertString( [In] XTextRange xRange, 
                           [In] string aString, [In] bool bAbsorb );
		void insertControlCharacter( [In] XTextRange xRange, 
             [In] short nControlCharacter, [In] bool bAbsorb );
	}
}  

This interface will permit you to set some text, create a text cursor or insert a control character and nothing more. Here, we set some text in a named cell.

using unoidl.com.sun.star.spreadsheet;  // for XSpreadsheet
using unoidl.com.sun.star.table;        // for XCell
using unoidl.com.sun.star.text;         // for XSimpleText and XTextCursor

public void SetText(String strCellName, String strText)
{
	// m_xSpreadsheet is a previously opened XSpreadsheet object
	XCellRange xCR = m_xSpreadsheet.getCellRangeByName(strCellName);
	XCell xCell = xCR.getCellByPosition(0, 0);
	XSimpleText xSimpleText = (XSimpleText)xCell;
	XTextCursor xCursor = xSimpleText.createTextCursor();
	xSimpleText.insertString(xCursor, strText, bAbsorb: false);
}

The objects also may contain many properties which are name value pairs that determine the characteristics of the object. For example, the XCell object has a "CellBackColor" property that determines the background color of the cell.

public void SetBackgroundColor( String strCellName, Color clr )
{
	// m_xSpreadsheet is a previously opened XSpreadsheet object
	XCellRange xCellRange = m_xSpreadsheet.getCellRangeByName( strCellName );
	XCell xCell = xCellRange.getCellByPosition( 0, 0 );
	XPropertySet xPropSet = (XPropertySet)xCell;
	UInt32 unClr = (UInt32)clr.R << 16 | (UInt32)clr.G << 8 | (UInt32)clr.B;
	xPropSet.setPropertyValue( "CellBackColor", new uno.Any( unClr ) );
}

The challenge in development is to find the appropriate interface or property to perform your desired task and how to access that interface or property. Finding your way around the documentation is essential.

Now Get to OpenOffice Coding!

We are now finally able to start to interact with OpenOffice. We will:

• determine if OpenOffice is installed on our system
• set up some useful class members
• start the OpenOffice desktop application
• create a spreadsheet document
• enter some data, a formula and text in some cells
• save the file
• demonstrate some Excel compatibility

Before adding items to your OOCalc class, you will need most of the following using statements at the top of your class file:

using uno;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.util;

These are the modules that will be used in the project. As new functionalities are explored, the easiest way to determine what module is needed is to browse to the definition of the object (right click on the symbol and select Go To definition), e.g., XStorable, and observe the namespace in which it exists. The namespace typically matches the module name.

// namespace here should be added with using statement
namespace unoidl.com.sun.star.frame
{
	public interface XStorable
	{
		bool hasLocation();
		string getLocation();
		...
  }
}

Is OpenOffice Installed?

To determine if OpenOffice is installed, check the registry key: Computer\HKEY_CURRENT_USER\SOFTWARE\OpenOffice

using Microsoft.Win32;
public bool bOpenOfficeInstalled
{
	get
	{
		RegistryKey RegKey = Registry.CurrentUser.OpenSubKey
                             ( @"SOFTWARE\OpenOffice", writable: false );
		return RegKey != null;
	}
}

Setting up Useful Class Members

The following members in your class will be useful throughout your interaction with OpenOffice:

// These don't really need to be saved, but can come in useful
// as more extensive OpenOffice features are accessed.
private XComponentContext m_xComponentContext = null;
private XMultiServiceFactory m_xMSFactory = null;
private XComponentLoader m_xLoader = null;

// You'll need these ones for many of your basic operations
private XSpreadsheetDocument m_xDocument;
private XSpreadsheet m_xSpreadsheet;

Starting OpenOffice and Creating a Calc Document

I will use the term workbook in the Excel sense of a spreadsheet document which may contain multiple sheets, which I will refer to as spreadsheets. Before opening a workbook, you will need to start the OpenOffice component loader after bootstrapping the OO system.

public bool bStartOpenOfficeLoader()
{
	try
	{
		if (m_xComponentContext == null)
			m_xComponentContext = uno.util.Bootstrap.bootstrap();
		if (m_xMSFactory == null)
			m_xMSFactory = 
            (XMultiServiceFactory)m_xComponentContext.getServiceManager();
		if (m_xLoader == null)
			m_xLoader = (XComponentLoader)m_xMSFactory.createInstance
                        ( "com.sun.star.frame.Desktop" );
		return true;
	}
	catch (unoidl.com.sun.star.uno.Exception e1)
	{
		Debug.Print( e1.Message );
	}
	return false;
}

Bootstrapping refers to creating a service manager with which you can create OpenOffice objects as needed. With this code, you could open any one of the OpenOffice applications - Writer, Calc, Base, etc. But to open Calc and create a workbook, use the following code.

public bool bCreateWorkbook()
{
	try
	{
		XComponent xComponent =
			m_xLoader.loadComponentFromURL
            ( "private:factory/scalc", "_blank", 0, new PropertyValue[0] );

		m_xDocument = (XSpreadsheetDocument)xComponent;
		XSpreadsheets xSheets = m_xDocument.getSheets();
		XIndexAccess xIndexAccess = (XIndexAccess)xSheets;
		Any any = xIndexAccess.getByIndex( 0 );
		m_xSpreadsheet = (XSpreadsheet)any.Value;
		return true;
	}
	catch (unoidl.com.sun.star.uno.Exception e)
	{
		Debug.Print( e.Message );
	}
	return false;
}

The "private:factory/scalc" argument to the component loader method loadComponentFromURL instructs the loader to construct a Calc application object. The "_blank" argument instructs the system to open a new empty workbook. The new PropertyValue[0] argument creates an empty array of PropertyValue objects. Many OpenOffice calls optionally require property arguments but when not used an empty argument at times must be supplied. The loadComponentFromURL call returns a generic XComponent object. The xComponent object is a multi inheritance interface object that exposes many interfaces. The one we want is the XSpreadsheetDocument, so we cast the xComponent object into that interface. The getSheets() call returns an XSpreadsheets object which at first, browsing to its definition, would not seem to contain a collection of spreadsheets:

public interface XSpreadsheets : XNameContainer
{
	void insertNewByName( [In] string aName, [In] short nPosition );
	void moveByName( [In] string aName, [In] short nDestination );
	void copyByName( [In] string aName, [In] string aCopy, [In] short nDestination );
}

public interface XNameContainer : XNameReplace
{
	void insertByName( [In] string aName, [In] Any aElement );
	void removeByName( [In] string Name );
}

However, exploring the documention for XSpreadsheetDocument, we see that we can use the interface XIndexAccess to access the collection of sheets and get the first one using the getByIndex method. This is an example of the multi inheritance interface methodology whereby one generic object can be cast into many different interfaces to access various capabilities.

XIndexAccess xIndexAccess = (XIndexAccess)xSheets;
Any any = xIndexAccess.getByIndex( 0 );
m_xSpreadsheet = (XSpreadsheet)any.Value;

The XIndexAccess object doesn't directly return a spreadsheet object, but returns an object of type uno.Any. The Any object is used frequently throughout the OpenOffice system to generically represent pretty much any object, hence its name. It has members Type which denotes its type and Value which contains a value which must be cast into some specific type to be used. In this case, we cast it into an XSpreadsheet type which can be used for many spreadsheet operations.

Tying this code into our form button handler, we can now see some results of our efforts:

private void btnRunOpenOfficeCalcTests_Click(object sender, EventArgs e)
{
	// Create a new Calc instance
	OOCalc.OOCalc ooCalc = new OOCalc.OOCalc();
	if (ooCalc.bOpenOfficeInstalled == false)
		return;
	ooCalc.bStartOpenOfficeLoader();
	ooCalc.bCreateWorkbook();
}

Running the code should start an instance of OpenOffice Calc in a separate process.

Setting Cell Data in the Spreadsheet

Since we'll mostly be illustrating setting spreadsheet cell functions, let's define a small function to conveniently get an XCell object:

private XCell xGetXCellByName( string strCellName )
{
	XCellRange xCR = m_xSpreadsheet.getCellRangeByName( strCellName );
	XCell xCell = xCR.getCellByPosition( nColumn: 0, nRow: 0 );
	return xCell;
}

Many spreadsheet functions work with cell ranges, in the function above, an XCellRange object. The name passed in to the function can be a typical cell location by column and row as in "A1". It can also be a named cell if a label has been assigned to a cell or range of cells, e.g., "InterestRate". Either way, the getCellByName function returns the cell range named if successful.

To access a specific individual cell, we use the getCellByPosition function call with zero based indices into the columns and rows of the cell. For those that may be familiar with Excel automation, this differs from Excel in that most indices in Excel are 1 based, not 0 based. Code ported or modeled from Excel will have to deal with this difference.

As noted previously, cells most typically contain one of a double precision value, a formula or text. Here are a few functions to set each of these. Note that exception handling code from here on will typically not be included for brevity.

public void SetValue( string strCellName, double dValue )
{
	XCell xCell = xGetXCellByName( strCellName );
	xCell.setValue( dValue );
}
public void SetFormula( string strCellName, string strFormula )
{
	XCell xCell = xGetXCellByName( strCellName );
	xCell.setFormula( strFormula );
}
public void SetText( string strCellName, string strText )
{
	XCell xCell = xGetXCellByName( strCellName );
	XSimpleText xST = (XSimpleText)xCell;
	XTextCursor xCursor = xST.createTextCursor();
	xST.insertString( xCursor, strText, bAbsorb: false );
}

The first two methods both set the numeric value and formula directly using the setValue and setFormula members of the XCell interface. In actuality, text can also be set by the setFormula method, but for more advanced text handling, the XCell object is cast into an XText or XSimpleText interface and the text setting functions of those intefaces are used.

Cells can also hold dates and times, but to display these successfully, the format properties of the cell have to be adjusted to display a value of type DATE.

public bool bSetDate( string strCellName, int nYear, int nMonth, int nDay )
{
	// Set the date value.
	XCell xCell = xGetXCellByName( strCellName );
	System.DateTime dt = new System.DateTime(nYear, nMonth, nDay);
	string strDateStr = dt.ToString( "M/dd/yyyy" );

	// You can also set "text" using the setFormula method
	xCell.setFormula( strDateStr );

	// Set date format.
	XNumberFormatsSupplier xFormatsSupplier = (XNumberFormatsSupplier)m_xDocument;
	XNumberFormatTypes xFormatTypes = 
           (XNumberFormatTypes)xFormatsSupplier.getNumberFormats();
	int nFormat = xFormatTypes.getStandardFormat( NumberFormat.DATE, new Locale() );

	XPropertySet xPropSet = (XPropertySet)xCell;
	xPropSet.setPropertyValue( "NumberFormat", new Any( nFormat ) );
	return true;
}

This code snippet illustrates having to access higher level objects, in this case, the XDocument object to access formatting capability. The "NumberFormat" property of the cell requires a numeric identifier of the format, but these are not particularly transparent to determine. Using the XNumberFormatsSupplier, XNumberFormatTypes and NumberFormat enumeration class, the numeric format can be determined as shown.

We also see the use of the XPropertySet interface. Many objects in the OpenOffice system have properties which are accessed using this interface. The object in question, in this case the xCell object, is cast to the XPropertySet interface and the setPropertyValue method called. The method is passed the property name and an Any object containing the value to be set.

Another example of setting a property of a cell is shown in a method to set the background color of a cell.

public void SetBackgroundColor( string strCellName, Color clr )
{
	XCell xCell = xGetXCellByName( strCellName );
	XPropertySet xPropSet = (XPropertySet)xCell;
	UInt32 unClr = (UInt32)clr.R << 16 | (UInt32)clr.G << 8 | (UInt32)clr.B;
	xPropSet.setPropertyValue( "CellBackColor", new Any( unClr ) );
}

We can now add calls to these methods in our form button click handler:

private void btnRunOpenOfficeCalcTests_Click(object sender, EventArgs e)
{
	// Create a new Calc instance
	OOCalc.OOCalc ooCalc = new OOCalc.OOCalc();

	// First check if OpenOffice is installed
	if (ooCalc.bOpenOfficeInstalled == false)
		return;

		ooCalc.bStartOpenOfficeLoader();
		ooCalc.bCreateWorkbook();
		ooCalc.bSetValue( "A1", 1234 );
		ooCalc.bSetValue( "B1", 2468 );
		ooCalc.bSetFormula( "C1", "=A1+B1" );
		ooCalc.bSetText("A2", "Text entered here.");
		ooCalc.bSetDate( "A3", 2023, 12, 25 );
		ooCalc.bSetBackgroundColor( "A3", Color.Red );
}

And the results in the Calc spreadsheet are:

Saving the OpenOffice Calc Document

Saving the Calc document can be done with the following code:

public string strSaveWorkbook( string strFilePath = null )
{
	XStorable xStorable = (XStorable)m_xDocument;
	string strFileURL = string.Empty;

	// If we don't have a file path passed in, check if the file has
	// been previously saved. If so, use that file name
	if (string.IsNullOrEmpty( strFilePath ))
	{
		// If we already have a file path, use it
		if (xStorable.hasLocation())
		{
			strFileURL = xStorable.getLocation();
			strFilePath = strUrlToPath( strFileURL );
		}
		// Otherwise prompt for one
		else
		{
			SaveFileDialog sfd = new SaveFileDialog();
			sfd.Filter = "ODF Spreadsheet (*.odf)|*.odf";
			DialogResult dr = sfd.ShowDialog();
			if (dr == DialogResult.Cancel)
				return String.Empty;
			strFilePath = sfd.FileName;
		}
	}
	if ( string.IsNullOrEmpty( strFileURL ) )
		strFileURL = strPathToURL( strFilePath );
	
	// Save with no args for default file format
	xStorable.storeAsURL( strFileURL, new PropertyValue[0] );
	return strFilePath;
}

Saving a document requires accessing the XStorable interface from the document. This interface will allow us to determine if the workbook has already been stored to a file using the hasLocation() method and the getLocation() method to retrieve the file location if it has been saved.

In the code above, if no file path is passed in, the current document is queried to determine if one has been set already. If so, that path will be used. If not, the user will be prompted for a file path using the .NET SaveFileDialog. In any case, a file path should now be determined.

However, as OpenOffice is a multiplatform application, it does not use Windows file path format but a more generic URL format. If the filename is retrieved from the XStorable interface, it will need to be converted back to a Windows file path to be useful in Windows. And vice versa, if we wish to save a Calc worksheet to disk, we will have to convert a Windows path to URL format for this purpose.

private string strPathToURL( string strFilePath )
{
	string strURL = "file:///" + strFilePath.Replace( "\\", "/" );
	return strURL;
}
private string strUrlToPath( string strFileURL )
{
	string strFilePath = strFileURL;
	if (strFileURL.Substring( 0, 8 ) == "file:///")
		strFilePath = strFileURL.Substring( 8 );
	strFilePath = strFilePath.Replace( "/", "\\" );
	return strFilePath;
}

Finally, now that we have a file path in appropriate URL format, we call the storeAsURL method to save the file. Note that if a file with that name already exists, the function will fail and throw an exception. Also note that the native OpenOffice format is the Open Document Format with the odf file extension.

Saving in Excel Format

There are times when Excel compatibility is desired. OpenOffice has some ability to open and save in Excel format. Excel has two common formats, the Excel 97 ".xls" format and the later ".xlsx" format. While OpenOffice Calc can open both of these to some extent, it can only save in Excel 97 format, with the ".xls" extension. If you desire better compatibility with the later ".xlsx" format, the LibreOffice product is a better choice and much of the above code will work in LibreOffice.

Here is the code to save in Excel 97 format. Assuming a file has already been saved in .odf format, the following code will save it to Excel 97 format with the same filename but .xls extension.

public string strSaveWorkbookAsExcel()
{
	XStorable xStorable = (XStorable)m_xDocument;

	if (xStorable.hasLocation() == false)
		return string.Empty;

	string strFileURL = xStorable.getLocation(),
		strFilePath = strUrlToPath( strFileURL );

	PropertyValue[] apv = new PropertyValue[1];
	apv[0] = new PropertyValue();
	apv[0].Name = "FilterName";
	apv[0].Value = new Any( "MS Excel 97" );
	strFilePath = Path.ChangeExtension( strFilePath, "xls" );
	strFileURL = strPathToURL(strFilePath );
	xStorable.storeAsURL( strFileURL, apv );
	return strFilePath;
}

This function illustrates the passing in of a valid PropertyValue array configured to pass in the "FilterName" property set to "MS Excel 97". A list of other supported formats can be found in the OpenOffice Wiki Filter Options page.

Closing OpenOffice Calc

When we're done with the document, we can leave it open or we can shut OpenOffice down. Here is a function to simply close it down.

public void CloseWorkbook()
{
	if (m_xDocument != null)
	{
		XComponent xComponent = (XComponent)m_xDocument;
		xComponent.dispose();
	}
}

The XDocument does not have a dispose method, so the object must be cast to the XComponent interface to dispose of it. Simply calling the dispose method closes the document down.

Our complete OpenOffice test button handler now looks like this:

private void btnRunOpenOfficeCalcTests_Click(object sender, EventArgs e)
{
	// Create a new Calc instance
	OOCalc.OOCalc ooCalc = new OOCalc.OOCalc();
	if (ooCalc.bOpenOfficeInstalled == false)
		return;
	ooCalc.bStartOpenOfficeLoader();
	ooCalc.bCreateWorkbook();
	ooCalc.bSetValue( "A1", 1234 );
	ooCalc.bSetValue( "B1", 2468 );
	ooCalc.bSetFormula( "C1", "=A1+B1" );
	ooCalc.bSetText("A2", "Text entered here.");
	ooCalc.bSetDate( "A3", 2023, 12, 25 );
	ooCalc.bSetBackgroundColor( "A3", Color.Red );
	string strFilePath = ooCalc.strSaveWorkbook();
	ooCalc.bCloseWorkbook();

	// Reopen it and save in Excel format
	ooCalc.bOpenWorkbook( strFilePath );
	strFilePath = ooCalc.strSaveWorkbookAsExcel();
	Console.WriteLine( "Saved as: " + strFilePath );

	ooCalc.bCloseWorkbook();
}

Your Next Challenge

Included with the SDK is an examples folder (mine was located in C:\Program Files (x86)\OpenOffice 4\sdk\examples\CLI\CSharp\Spreadsheet). Try adding a few more buttons and pulling this code into your project. The sample code offers quite extensive Calc examples with tables, charts, formatting, etc. and will be a huge help in moving your Calc education forward.

Wrapping Up

I found that the learning curve to programatically interacting with OpenOffice was quite arduous. Getting familiar with the online documentation is essential. Also, learning to gain insights from Java and C++ code examples helped at least to be pointed in the right direction to find a solution. Hopefully, this tutorial will give you a good foundation to move forward.

While I began my journey with OpenOffice, I actually transitioned very quickly to LibreOffice which has an almost identical API but is much more developed. For example, it supports the export of the later Excel .xlsx file format. I hope to update this tutorial with LibreOffice code at some point and republish.

History

• 3^rd April, 2023: Initial version