Our demo is Windows Forms application (Picture 2), written in C# using Visual Studio 2008.

Picture 2: Demo application

Application can:

·         Create new Spreadsheet document.

·         Read existing Spreadsheet document.

·         Write created Spreadsheet document.

Creating new document and underlying model of application

Internal, Spreadsheet document is stored as DataSet. Each sheet is represented with DataTable, sheet’s row with DataRow, and sheet’s column with DataColumn. So, to create a new document we have to create new DataSet, with DataTables. Each DataTable has number of rows and columns that conforms to our needs.

To show data from our DataSet (and to allow editing that data) application dynamically creates tabs with DataGridViews (that are connected to our DataTables).

Through interface, user can read, write, edit data and add new rows to Spreadsheet document.

Zip component and XML parser

Although classes from System.IO.Packaging namespace (.NET 3.0) provides way to read and write ZIP files, they require different format of ZIP file. Because of that, our demo uses open source component called DotNetZip.

Using ZIP component we can extract files, get subdocument, replace (or add) subdocuments that we want and save that file as .ods file (which is ZIP file).

For processing documents we have used XmlDocument because it offers easy way to reach part that we want. Note that, if performance is crucial for you, you should use XmlTextReader and XmlTextWriter. That solution need more work (and code), but provides better performance.

Reading Spreadsheet Document

To read document we follow these steps:

1.    Extracting .ods file.

2.    Getting content.xml file (which contains sheets data).

3.    Creating XmlDocument object from content.xml file.

4.    Creating DataSet (that represent Spreadsheet file).

5.    With XmlDocument we select “table:table” elements, and then we create adequate DataTables.

6.    We parse child’s of “table:table” element and fill DataTables with those data.

7.    At the end, we return DataSet and show it in application’s interface.

Although ODF specification provides way to specify default row, column and cell style, implementations have nasty practice (that specially applies for Excel) that they rather write sheet as sheet with maximum number of columns and maximum number of rows, and then they write all cells with their style. So you could see that your sheet has more than 1000 columns (1024 in Calc and 16384 in Excel), and even more rows (and each rows contains the number of cells that is equal to the number of columns), although you only have write data to first few rows/columns.

ODF specification provides a way that you specify some element (like column/row/cell) and then you specify the number of time it repeats. So above behavior doesn’t affect the size of the file, but that complicates our implementation.

Because of that, we can’t just read the number of columns and add equal number of DataColumns to DataTable (because of performance issues). In this implementation, we rather read cells and, if they have data, we first create rows/columns they belong to, and then we add those cells to the DataTable. So, at the end, we allocate only space that we need to.

Writing Spreadsheet Document

To write document we follow these steps:

1.    Extracting template.ods file (.ods file that we use as template).

2.    Getting content.xml file.

3.    Creating XmlDocument object from content.xml file.

4.    Erasing all “table:table” elements from the content.xml file.

5.    Reading data from our DataSet and composing adequate “table:table” elements.

6.    Adding “table:table” elements to content.xml file.

7.    Zipping that file as new .ods file.

In this application, as template, we have use empty document. But application can be easy modified to use some other template (so, that you have preserved styles, etc).