This is an updated version of the article and sources. Changes in v1.1:

OpenOffice 3.0 support

OpenOffice 2.x is not supported anymore (but minor project changes should make it work)

Chart (diagram) support added

Introduction

Using C# with OpenOffice.org Calc sometimes seems to be quite a problem. And creating reports using different table processors (Excel and OOo Calc) separately is a bad habit and will be a source of errors in future.

So I hope this article (and source code, of course) will make "cross-table processor" life for C# programmers a bit easier. You won't find a comprehensive solution for using both table processors here (some functionality is not implemented for OpenOffice and "cross-table-processor" interface is quite small too), but I believe that my library is:

Useful for creating simple reports (I use it myself for small business solutions)

A tool which makes communication with OpenOffice Calc easier (even if you wouldn't use the Excel and abstract parts of the library)

A good source of OpenOffice Calc samples for C#

For historical reasons, I started working with Excel and later I had to convert my projects to use both — OOo and Excel. That's why the abstract table processor's interface is much like the Excel's one. Having experience working with Excel API should help you understand the code better.

Design

The basic design is quite simple:

4 abstract base classes:

TableProcessor

TableSheet

TableRange

TableDiagram

4 Excel classes, derived from abstract classes mentioned:

ExcelApp

ExcelSheet

ExcelRange

ExcelDiagram

and similarly, 4 OpenOffice Calc classes:

OOApp

OOSheet

OORange

OODiagram

I hope you've got the naming scheme and I have no need to draw the inheritance diagram.

Also, there is a set of enums for constants, constant converter (my consts to Excel or OOo) and several support classes.

The INull interface and classes which implement it are designed to support the "Null object" pattern.

Abstract Interface

TableProcessor — Used for an "Application Object"

ExcelAvailable and OOAvailable

These props should be used for determining if the desired table processor is installed. Both methods are based on a registry query. For OOo, it looks like this query determines only if the whole OpenOffice is installed, but I can't find any better solution.

CreateNextPage

Maybe, should be named CreateNextSheet. Guess what it does ... The startRow parameter is discussed below.

TableSheet — Represents a Single Table Processor Page

CurrentRow (_curRow inside the class)

Can help you create reports row by row. You can use it for your own needs, but the common usage is for the AddArray method (see below). The initial value (default is 1) is specified by the startRow parameter for TableProcessor.CreateNextPage or TableProcessor.CreateAvailable. Row indexes start from 1 (Excel style).

AddArray

Adds an array you give it, starting at cell [CurrentRow, 1] and increases the CurrentRow according to the array's height. Row and cell indexes start from 1.

CreateAvailable

Static method. Uses TableProcessor.CreateAvailable, but you get the TableSheet reference at once. Useful for single-page reports.

Cell, Range

Gives you a TableRange object for the cell range you requested. Again, cell indexes start from 1.

TableRange — Represents a Range of Cells (Maybe One Cell)

You can place a single value or an array here. These values will be placed to the range's coordinates. Values, which are out of range, will be silently ignored.

CreateArray, this[int,int], FlushArray:

CreateArray creates an inner array of the range's size of the type, which can be directly understood by table processor (uno.Any for OOo and object for Excel).this[int,int] enables your interaction with the array created. Here indexes start from 0 and are relative to upper-left corner of the range. It's like with the usual array, which knows nothing about the range it will be placed to.The FlushArray method transfers the data from the inner array, created with CreateArray, to the table processor.

TableDiagram — Represents a Chart. New interface in v1.1

Name, XAxisName, YAxisName

Names of the diagram, X, Y axis.

DrawRect

Chart position rectangle.

XAxisNamesRange

Sets a range, which gives names to items on X axis. The values from the range are copied.

SetMainAxisNamesRange

Sets a range, which gives names to items (columns, etc.). The values from the range are copied.

MainAxisNames

Same as above, but works with a string array, not range.

MoveToRectOf

Moves chart to the rectangle of the given TableRange (gets range's coordinates and uses DrawRect mutator).

Implementation Comments

The effect of double TableRange.ColumnWidth may vary from Excel to OOo, because I don't know what double means for both. But this property is still valuable if you need to make all the columns as wide as a particular one after auto-sizing.

I didn't do any experiments with border color parameter (int color) in TableRange.BorderAround. I always use 0, and it gives me black. Maybe there's a way to use ColorIndexes conversion from the ConstConvert class somehow.

/* For Excel programmers */ Yes, I have no Font class. I don't need so many font properties and I decided to implement valuable parts of its functionality in Range classes.

Excel Specific

Excel likes color indexes and dislikes RGB. So RGB values like FontColor and BackgroundColor are converted to the nearest color, understood by Excel. For details see ConstConvert class.

I don't use Microsoft custom Excel wrapper (as warning suggests) since I had some problems with it while deploying software on some machines. I suppose there's a checkbox in Microsoft Office's installation, which installs custom wrappers, and by default the checkbox is off. Anyway, my method works

OpenOffice supports no line styles (except for double lines). So line styles are NOT SUPPPORTED for OO implementation.

OpenOffice dislikes a lack of values (when the array is smaller than the range) for AddArray, etc., but this is fixed in the wrapper.

OpenOffice doesn't know what a "decimal" data type is, has his own strange DateTime type (I can't make OO accept the value of its own DateTime type) and doesn't like bool values. So values of these types are hard-code-converted for OOo. See OORange.Conv method in the sources for details.

OpenOffice cross-platform design makes use of the uno.Any data type to communicate with the outer world (something like VARIANT I suppose). That's why while using OORange.Value — the array or value is COPIED ELEMENT BY ELEMENT to the array of uno.Any objects. To avoid this, consider using TableRange.CreateArray and TableRange.FlushArray, it should work faster.

OOApp.XSpreadsheetDocument gives you low-level OpenOffice Calc access. So does OOSheet.XSpreadsheet, OORange.XCellRange and OODiagram.XChartDocument

User NumberFormats in OOo should be created in some number format storage for each document, but a particular format can be created only once for a document. So references for those formats are stored in OOApp.numberFormats (of course private).

FitToPagesWide and FitToPagesTall (size decreasing to fit to the desired number of pages) IS NOT IMPLEMENTED for OOo.

AddPageNumbering IS NOT IMPLEMENTED, because this is a default OOo behaviour.

Each OOSheet has a reference to its OOApp, because I can't find the way to get the XSpreadsheetDocument reference via XSpreadsheet correctly (not by name, etc), and I need the document reference to work with number formats.

OpenOffice 3.0 support is added as described here. To use OpenOffice 2.x, you should remove all cli_*.dll references from the project and reference cli_*.dll from program/assembly folder inside the OpenOffice 2.x installation folder. If using OpenOffice 2.x, you can comment out OOApp.ConfigureOO3x() call from OOApp constructor.

OpenOffice 3.0 DLLs, used in the sources, are extracted from the openofficeorg1.cab file. They shouldn't be distributed with the tblproc.dll, since OpenOffice installs them in the GAC.

Build Requirements

.NET 2.0

Visual Studio 2005

OpenOffice.org 3.0 (maybe later versions will be compatible) and/or Microsoft Office XP or later. Not tested with Microsoft Office 2007.

You need to reference Microsoft.Office.Core and Microsoft Excel Object library.

You need to reference 6 OpenOffice .NET DLLs (cli_basetypes.dll, cli_cppuihelper.dll, cli_oootypes.dll, cli_uno.dll, cli_ure.dll, cli_uretypes.dll. I extracted them from OO installation .cab file, but they should reside in GAC after installation.

In client code, you need to reference only tblProc.dll, but you'll need to have referenced Microsoft DLLs in application folder. You shouldn't copy OO DLLs to app folder, since there can be newer versions in GAC.

Sample Code Discussion

It's quite easy. The grid on the form uses the custom DataSet, based on DataClass class. The TableProcessor combo on the toolbar allows you to select the type of the table processor. The Export button just sends the contents of grid to the processor (using a handy Export class — I use it frequently).

The "Create some colorized test doc" button runs the code, which tries to show most of valuable features.

OK, here's an update of the article. Now the code supports OOo 3.0, not 2.x. I made no tests, but doubtfully it is still compatible with 2.x. Anyway, minor changes can bring it back to support OOo 2.x (and discard compatibility with 3.0 for sure): re-reference .DLLs and, optionally, comment out 1 line in OOApp.cs. Anyway, if this version is not compatible with 2.x, I have no ideas how to make it work with both - 2.x and 3.0.

hi Alexander,
thank you very much for teach me do title in OpenOffice i have done yet. but i have a question other have you help me.I want insert colum ID in OpenOffice not do in datagird.ex: ID Name Address City
1 adam PA PA
2 smith NW NW
3 Alice Mexico MX
.. ..................

Exporting data from DataGridView is not the only way for creating a report. For example, you can write your code to export the whole datasource to OO. You can get a TableRange object and set it's cells values using an array.
For example (assuming you have a TablePage page object;
object[,] arr=new object[10,4];
arr[0,0]=1;
arr[0,1]="adam";
arr[0,2]="NW";
arr[0,3]="MX";
//.... and so on. You can fill an array in for or foreach cycle
TableRange rng=page.Range(1,1,10,4); //example coordinates. the first column has #1, the last - #4, so 4 columns
rng.Value=arr;

Or you can get ranges for each cell/row and fill their values. Example:
TableRange rng=page.Range(2,2,2,2);
rng.Value="Hello!";

I didn't test the code, but it should be something like that. You can examine the export code used in the sample to export DataGridView's data to see the third way for exporting data. It's similar to the first way I've shown above, but it uses an "internal" array in the TableRange object to simplify data conversion:
TableRange data = page.Range(page.CurrentRow, 1, page.CurrentRow + nStrings-1, nVisibleCols);
data.CreateArray();
//...filling the array created in TableRange object
//and now put the data to OO (or Excel, it depends on the actual type of page object)
data.FlushArray();

hi Alexander,
i want to do report for my company as use OpenOffice Calc.but i don't know how to insert header in OpenOffice for report ex:"BAO CAO KET QUA TTHUE NHA";month:"2008";Location:"HCM CITY".i have done load data from dataset in OO as souce code of you but i haven't done discription as top.i have to you help me to do it.thank you very much

If you're talking about changing document's properties (such as title, topic, etc., which are under "File->Properties..." menu item) - I don't know how to do it, never thout it can be useful.
Adding a title to the document's content should be quite easy. Assuming you have a sheet - a TableSheet object (in your case it will really be a OOSheet object). To get a TableSheet object you have to get an application object, using TableProcessor's factory methods and then create a sheet using LastPage or CreateNextPage method.
I usually add a header like that:
TableRange header=sheet.Range(sheet.CurrentRow,1,sheet.CurrentRow, nColumns);//nColumns-number of used columns
header.Merge();
header.Value="THE TEXT OF THE TITLE";
header.HAlign=HAlign.Center;
//other header text decoration using "header" varialbe
sheet.PrintRowsOnEachPage(sheet.CurrentRow,sheet.CurrentRow);//every printed page will contain the header
sheet.CurrentRow++;//to use AddArray correctly with other code

I made no tests nor compilation of the code above, but I hope it's really close to what you need

Thanks for URL. This may be useful for making simple wrappers for MS Word and OO Write.
As for the Excel and Calc wrappers, I added chart support to the library and hope I will compile and post update soon.