Creating an OpenOffice Calc Document with C#(转贴)

http://c-programming.suite101.com/article.cfm/creating_an_openoffice_calc_document_with_c

 

 

How to Create, Modify and Save OpenOffice.org Calc Using C#

OpenOffice.org Calc does most things that Microsoft Excel can do. And for free. Not only that, but it provides a programming interface for the C# application developer.

 

Spreadsheets have been around for many years and are an integral part of many peoples day to day activities in offices around the world. The majority of those people use the versatile and powerful Microsoft Excel, but that's starting to change. Increasingly people are turning to a relative new comer: OpenOffice.org Calc. This is for a number of reasons:

• it is open source
• it is free
• it does virtually everything that Microsoft Excel does

Therefore the question for the C# programmer is "How difficult is it to automate OpenOffice.org Calc?". The answer is "Not very difficult at all". After just a few minutes the C# programmer will be able to produce an application that can:

• create a new Calc spreadsheet
• write to the spreadsheet's cells
• save and close the spreadsheet

The first step is, unsurprisingly, to add OpenOffice.org as a reference.

Adding OpenOffice.org Calc as a Reference to a C# Project

OpenOffice.org supplies the Common Language Interface (or CLI) namespaces for applications to access any of its objects, methods and properties. Each of the namespaces associated with the Common Language Interface have a name that start with the letters "cli" and so the C# programmer should select each of them and add them as references (as shown in figure 1 at the bottom of this article).

Using OpenOffice.org Calc References in a C# Project

Once the programmer has added the CLI references to their C# project then they can use any required namespaces:

using unoidl.com.sun.star.lang;

using unoidl.com.sun.star.uno;

using unoidl.com.sun.star.bridge;

using unoidl.com.sun.star.frame;

using unoidl.com.sun.star.text;

using unoidl.com.sun.star.beans;

using unoidl.com.sun.star.sheet;

using unoidl.com.sun.star.container;

using unoidl.com.sun.star.table;

These give the application access to the objects, their methods and their properties, and the programmer can use them to communicate with the OpenOffice.org Calc spreadsheet.

Creating a Blank OpenOffice.org Calc Document

The process of opening any OpenOffice.org document is very similar, regardless of the type of application. So, the methods used to open Writer (as discussed in Creating an OpenOffice Writer Document with C#) are the same as the methods used to open Calc. Therefore, the first step is to use the bootstrap method to start OpenOffice.org (or to access any existing instances):

XComponentContext oStrap = uno.util.Bootstrap.bootstrap();

The next step is to use OpenOffice.org's service manager to create a desktop:

XMultiServiceFactory oServMan = (XMultiServiceFactory) oStrap.getServiceManager();

XComponentLoader oDesk = (XComponentLoader) oServMan.createInstance("com.sun.star.frame.Desktop" );

And then a new Calc document is added to the desktop:

string url = @"private:factory/scalc";

PropertyValue[] propVals = new PropertyValue[0];

XComponent oDoc = oDesk.loadComponentFromURL(url, "_blank", 0, propVals);

If a user was to run the application at this point then a new, blank Calc document would appear on their computer's screen (as shown if figure 2).

Accessing the Sheets in a Calc Spreadsheet with C#

A C# application accesses the sheets in a Calc spreadsheet by:

• obtaining all of the sheets in the spreadsheet
• creating an index of the sheets
• using the index to access a particular sheet (in a blank spreadsheet there will be 3 sheets, indexed 0 to 2)

And the code to do this is:

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

XIndexAccess oSheetsIA = (XIndexAccess) oSheets;

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

In this example the first sheet (Sheet1) has been accessed and any of its cells can now be written to.

Accessing the Cells in a Calc Spreadsheet with C#

Each cell in a Calc sheet is indexed by its column and row, so that text (for example) is written to cell A1 by using:

XCell oCell = oSheet.getCellByPosition( 0, 0 ); //A1

((XText)oCell).setString("Cost");

And a number can be written to B1 by using:

oCell = oSheet.getCellByPosition( 1, 0 ); //B1

oCell.setValue(200);

And the "setFormula" methods is, of course, used to enter formulae (in this example into B3):

oCell = oSheet.getCellByPosition( 1, 2 ); //B3

oCell.setFormula("=B1 * 1.175");

In this way the programmer can populate the spreadsheet with any required information (as shown in figure 3).

Saving and Closing the Calc Document

A Calc document and Writer document are both closed in the same way, observing the correct OpenOffice.org file name format and with the same empty property values used to open file blank document:

string fileName = @"file:///C:/Reports/vat.ods";

((XStorable)oDoc).storeAsURL(fileName, propVals);

Finally (if required) the document can be closed and any memory that has been used can be freed up:

oDoc.dispose();

oDoc = null;

If the user runs the compiled application then they will see the Calc document open, the cells being filled and then the document closing again. If they look on their computer then they will find a new Calc file (or an updated one if they have run this before). And all this done automatically with just a few lines of C# code.

Read more at Suite101: Creating an OpenOffice Calc Document with C#: How to Create, Modify and Save OpenOffice.org Calc Using C# | Suite101.com http://c-programming.suite101.com/article.cfm/creating_an_openoffice_calc_document_with_c#ixzz0Yg0KV1Q7