Working with spreadsheets in 10 easy steps
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()
doc = SpreadsheetDocument.Create();
When you are done with the document, don’t forget to save it.
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 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,
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";
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);
Cells can use a formula to define their value. The formula is defined as a string:
Cell a1 = worksheet["A1"];
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"));