http://c-programming.suite101.com/article.cfm/creating_an_openoffice_calc_document_with_c
![](https://i-blog.csdnimg.cn/blog_migrate/d4d89fa74436af268db53940ad0b8bb4.png)
![](https://i-blog.csdnimg.cn/blog_migrate/d4d89fa74436af268db53940ad0b8bb4.png)
![](https://i-blog.csdnimg.cn/blog_migrate/d4d89fa74436af268db53940ad0b8bb4.png)
![](https://i-blog.csdnimg.cn/blog_migrate/d4d89fa74436af268db53940ad0b8bb4.png)
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:
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):
The next step is to use OpenOffice.org's service manager to create a desktop:
And then a new Calc document is added to the desktop:
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:
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:
And a number can be written to B1 by using:
And the "setFormula" methods is, of course, used to enter formulae (in this example into B3):
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:
Finally (if required) the document can be closed and any memory that has been used can be freed up:
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