How to Format Calc Spreadsheet Cells with C#

Formatting the Cells in an OpenOffice Document by Using C# Code

Jun 18, 2009

An OpenOffice.org Calc Spreadsheet user can format cells manually to provide a professional looking report. However, a C# programmer can easily automate the whole process

It is very easy use C# to create an OpenOffice.org Calc spreadsheet, to write information to its cells and to save the document (as shown in Creating an OpenOffice Calc Document with C#). That, of course, is only part of the story. The next stage is to format the cells in the spreadsheet, and the C# programmer may need to :

And so, when it comes to formatting the cells in the spreadsheet the C# programmer can:

Getting C# Ready to Work with OpenOffice.org Calc

The article Creating an OpenOffice Calc Document with C# explains how to add OpenOffice.org as a C# reference, and it discusses the use of the OpenOffice.org namespaces needed for the processing of new Calc spreadsheet documents. It's then just a matter of adding any namespaces required of the formatting of the cells:

using unoidl.com.sun.star.awt;

using unoidl.com.sun.star.style;

The C# application developer can now use code to format the cells in the spreadsheet.

Selecting a Calc Spreadsheet Sheet

Once an existing spreadsheet has been opened or a new one created the correct sheet must be selected. In this example the spreadsheet has been opened as an object with the name “oDoc":

XSpreadsheets oSheets = ((XSpreadsheetDocument)oDoc).getSheets();

XIndexAccess oSheetsIA = (XIndexAccess) oSheets;

XSpreadsheet oSheet = (XSpreadsheet) oSheetsIA.getByIndex(0).Value;

Here the first sheet (index 0) has been selected (as “oSheet") and this can be used to access any cell in the spreadsheet.

Accessing a Calc Spreadsheet Cell

Just like the sheet each cell is indexed, but they are indexed by means of the column and row numbers, so that A1 is (0,0) and B1 is (1,0):

XCell oCell = oSheet.getCellByPosition( 1, 0 );

((XText)oCell).setString("Calculate UK VAT");

Setting the Style of a Calc Cell

Perhaps the easiest way of modifying the look and feel of a Calc cell is by changing it's style. Normally this is done manually from a drop down combo-box, but the programmer can do this with a single line of code:

((XPropertySet)oCell).setPropertyValue("CellStyle", new uno.Any((string) "Heading"));

The “Heading" style will now be applied to the selected cell (as shown in figure 1 at the bottom of this article). However, as well as applying a whole style, individual properties can be be changed.

It's worth noting that the “Any" class is used to pass object references to the method. When this is done an implicit type must be stated (as shown in the above example).

Adding an Underline

The setPropertyValue updates any of the cell properties and the process is the same:

((XPropertySet)oCell).setPropertyValue("CharUnderline", new uno.Any((short) FontUnderline.DOUBLE));

“Double" is just one of 18 different underlines that are available to the user.

Making Text Bold

((XPropertySet)oCell).setPropertyValue("CharWeight", new uno.Any((Single) FontWeight.BOLD));

Like the character underline, the character weight has an number of possible value and the easiest what to examine these is to use the IDE's text completion facility. For example, if the programmer is using SharpDevelop then typing “FontWeight"followed by a full stop (or period) then all possible values will be displayed.

Changing Colors

It won't surprise the C# programmer to learn that the setPropertyValue method is used again to change the colors used in a cell:

((XPropertySet)oCell).setPropertyValue("CellBackColor", new uno.Any((int) 0xFFFF00));

((XPropertySet)oCell).setPropertyValue("CharColor", new uno.Any((int) 0xFF0000));

However, the color codes being used may (at first) seem confusing. That's because they are actually a hexadecimal representations of the RGB (Red-Green-Blue) codes for the colors. For example:

So, just by using these few lines of code the C# programmer will have produced a professional looking spreadsheet, formatted exactly as they require (and as shown in figure 2).

Bibliography

Bain, Mark Alexander. Learn OpenOffice.org Spreadsheet Macro Programming. Birmingham, United Kingdom: Packt Publishing, 2006

The copyright of the article How to Format Calc Spreadsheet Cells with C# in C Programming is owned by Mark Alexander Bain. Permission to republish How to Format Calc Spreadsheet Cells with C# in print or online must be granted by the author in writing.