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.

>But when I deployed it and run the application it not exporting OpenOffice file.
1. Any errors, exceptions?
2. You deployed what - I guess you deployed your own project, which uses TblProc.dll. If I'm right, maybe you can show a piece of of code, which exports data? Or you just deploy the sample?
3. You deployed it where? Is it a network share? Default .NET security policy rejects many operations if the binaries reside on a network share - in such a case you need to sign the code and configure .NET permissions.

(A) When Application run in VS.net (Server) Environment and produce oo report. It producing Open Office Excel file and Charts. Its OK.
(B) In IIS Server it not produce the file and processing goes endless. Not produce any error / exception.

(C) When wait long for localhost it shows

"An un handeled exception(System.CannotUnloadAppDomainException) occured in aspnet_wp.exp"
======================================================================
What I have done. Open Office 3.0.

1. Added the TblProc.dll as reference in my project.

2. five cli_ files are in GAC
Excluding cli_uno.dll file.

give HResult error when add cli_uno.dll as reference so not added in project and it is not

in GAC and in VS Server without it producing file.
----------------------------------------------------------------------