Clear Office

1 Adding assembly references

The functionality to manipulate spreadsheet files is scattered in several assemblies: Office.Base, Office.Drawing, Office.Chart, Office.Spreadsheet. Add references to these assemblies in your project

2 Creating, Opening and saving spreadsheet documents

As the name suggests, Office.Spreadsheet.SpreadsheetDocument represents a spreadsheet document. One can either create a new document by calling SpreadsheetDocument.Create() or open an existing one by calling SpreadsheetDocument.Open()

SpreadsheetDocument doc = SpreadsheetDocument.Create();

When you are done with the document, don’t forget to save it.

doc.Save (myFileName);

3 Accessing worksheets

A spreadsheet document contains one workbook which can contain several sheets. Most sheets are worksheets (sheets with rows and columns) but it is possible to have other types of sheets (Chart sheets, Dialog sheets). One can access sheets by index or name

Worksheet worksheet = doc.Workbook.Sheets[0] as Worksheet

4 Accessing cells

A worksheet contains a two dimensional set of cells. Cells can be accessed by row and column index. To be consistent with other .NET APIs, all indexes are zero based.

Cell cell = worksheet[2, 1];
Cells can also be accessed by label:
Cell cell = worksheet ["B3"];

5 Setting cell values

Each cell has a value and a set of properties. Cell values are usually of type string, double, DateTime or bool. Cell properties include Foreground, Background, FontSize, FontFamily, horizontal and vertical alignment.

cell.Value = "Yellow text";
cell.Background = Brushes.Black;
cell.Foreground = Brushes.Yellow;

6 Using Cell formats

Cells can specify a format. The format will be used to display the value of the cell.

Cell cell = worksheet["A1"]; cell.Value = 0.75;

The value is displayed as '0.75'

cell.Format = Format.Parse ("0.00 %");

The value is displayed as '75%'

7 Using ranges

Ranges represent a set of cells. Worksheet.GetRange() returns a range for a given label:

Range range = worksheet.GetRange ("A1:B3");

Like cells, one can set properties on ranges:

range.Background = Brushes.Red;

8 Merging cells

Cells can span more than one row or column. To merge cells, call Merge for a given range: Range range = worksheet.GetRange ("A1:B3"); worksheet.Merge (range);

9 Formulas

Cells can use a formula to define their value. The formula is defined as a string:

Cell a1 = worksheet["A1"];
a1.Value = 1;
Cell a2 = worksheet["A2"];
a1.Value = 2;
Cell a3 = worksheet["A3"];
a3.Formula = "A1+A2";
// now a3.Value == 3

10 Defined names

To simplify formulas, it is sometimes handy to name ranges. For example, instead of "SUM("A1:B3")", it is easier to use "SUM(SALES)" where SALES represents the range A1:B3. Worksheet.DefineName defines the name of a given range:

worksheet.DefineName("SALES", worksheet.GetRange ("A1:B3"));
worksheet["C4"].Formula= "SUM(SALES)";